Antivirus install to db server: value

Showing posts with label value. Show all posts

Wednesday, March 28, 2012

question about retrieving identity value

hi,
according to the SQL Server Books Online, there're 3 system functions return
last-generated identity values: IDENT_CURRENT, @.@.IDENTITY, and
SCOPE_IDENTITY. My colleague wrote a stored procedure like the following:
...
BEGIN TRAN
...
INSERT INTO [TABLE_WITH_IDENTITY_COLUMN] ...
SET @.ID = IDENT_CURRENT('TABLE_WITH_IDENTITY_COLUM
N')
...
COMMIT TRAN
...
I think using IDENT_CURRENT may be a problem in multiuser environment,
because it returns the last identity value generated in any session. But he
said it's ok, since these statements are put in a transaction and SQL Server
will lock the table. Is it true?Hi
He is wrong. SQL Server may not lock the whole table, so it is possible that
2 processes can insert into the same table, at the same time.
SCOPE_IDENTITY is the correct one to use.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:72F8BDEC-FA0F-4880-B1E7-FFBDB8102955@.microsoft.com...
> hi,
> according to the SQL Server Books Online, there're 3 system functions
> return
> last-generated identity values: IDENT_CURRENT, @.@.IDENTITY, and
> SCOPE_IDENTITY. My colleague wrote a stored procedure like the following:
> ...
> BEGIN TRAN
> ...
> INSERT INTO [TABLE_WITH_IDENTITY_COLUMN] ...
> SET @.ID = IDENT_CURRENT('TABLE_WITH_IDENTITY_COLUM
N')
> ...
> COMMIT TRAN
> ...
> I think using IDENT_CURRENT may be a problem in multiuser environment,
> because it returns the last identity value generated in any session. But
> he
> said it's ok, since these statements are put in a transaction and SQL
> Server
> will lock the table. Is it true?

Monday, March 26, 2012

Question about OUTPUT clause

In the past when inserting a record into a table with an identity column, to return the new identity value I used one of SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY.

Question: will this sql 2005 approach also provide the newly added identity value?

insert into TestTable -- ID column of testtable is an Identity field

output inserted.id, inserted.col1 into @.insertedRecords

values('row 20')

select ID from @.insertedRecords

TIA,

Barkingdog

Sure can. Did your attempts not work? Here is a script to demo:

create table testTable
(
testTableId int identity primary key,
value varchar(10) unique
)
go

declare @.insertedRows table (
testTableId int primary key,
value varchar(10) unique
)

insert into testTable (value) -- ID column of testtable is an Identity field
output inserted.testTableId, inserted.value into @.insertedRows
values('row 20')

select scope_identity()
select * from @.insertedRows

declare @.insertedRows table (
testTableId int primary key,
value varchar(10) unique
)

insert into testTable (value) -- ID column of testtable is an Identity field
output inserted.testTableId, inserted.value into @.insertedRows
select 'row 21'
union all
select 'row 22'

select * from @.insertedRows

|||

I haven't encoutnered any problems but I was just wondering if my original thought was correct.

Thanks.

Barkingdog

sql

Friday, March 23, 2012

Question about Null value in cross tab

Dear all,
I have a question about formula in crystal report.
I have created a cross tab. In the group field, the null value will display blank.
How can it display other text if it is null instaed of blank?
Thanks alotHi,

Right Click the summary field, select "Format Field", go to "Common" Tab.

You can find X-2 for "Display" String option. Click that button and write the following formula

If IsNull(CurrentFieldValue) = True then
0.00
else
CurrentFieldValue

This will display 0 if that field contains Null and the actual value if it is not null.

Hope this will work.|||Hi
Thank you for your reply
I have modify the formula from you and place it in the right place.
However, it doesn't work.
The blank group title has not be changed to the text that I expected.
Do you have any suggestion?|||Is that field is blank or null? If it is blank, just check with blank space instead of checking null.|||I have checked both of it
but still cannot work
Here is my code
for the null value:
if IsNull({table.field}) then
"Unspecific"
else
CurrentFieldValue

for checking space
if {table.field}=" " then
"Unspecific"
else
CurrentFieldValue

both of them are shown nothing is the cross tab field
Do u have any other suggestion?
Thanks for your help!!!!|||I have an other question to ask you
I want to change the subtotal to other currency
and the exchange rate is store in other table
I don't know how to get the exchange rate for each of the countries
How can i do this by the formula?|||you are so nice and very helpful~~~|||Thanks,

For the first question, Hope you have written the formula in "Display String" (Format)section.

Try to check CurrentFieldValue instead of {table.field}.

I checked with zero, it worked.

For the second question, if you have any linking field like country code, country name use that field to link with the other table which contains the exchange rate. Write the formula by using that field.|||Thank you for reply!
I also check it with non-null value
it also works!
however, when I check it with null value, it shows nothing~
I have check it with sql statement, it is null value in the database

How come this happens??

Thanks~|||I have a new question again.

The crystal report have not show the record that is zero
How can I show the zero record??

Thanks very muchsql

Tuesday, March 20, 2012

Question about IIF() function?

I tried to replace the value on rows using the IIF() function and it did not work. The following is the mdx code.

WITH MEMBER [Measures].[ParameterCaption] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified"))) ON ROWS FROM ( SELECT ( STRTOSET(@.DIMSourceSource, CONSTRAINED) ) ON COLUMNS FROM [AP Statistics by Patient Type])

What is wrong with the IIF() function....

IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified")))

Thanks

The problem is that you are returning strings and the row axis needs a set of members. You would need to put your IIF in a calculated measure on the columns and also define a set of members on the rows.

At a guess it would probably need to look something like the following (my changes in red).

WITH MEMBER [Measures].[ParameterCaption]

AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.LEVEL.ORDINAL'

MEMBER [Measures].[PatientCalc] AS IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified")))

SELECT {[Measures].[ParameterCaption]

, [Measures].[ParameterValue]

, [Measures].[ParameterLevel]

, [Measures].[PatientCalc]}

ON COLUMNS ,

[DIM_Patient].[In Out Patient].Members ON ROWS

FROM ( SELECT ( STRTOSET(@.DIMSourceSource, CONSTRAINED) ) ON COLUMNS

FROM [AP Statistics by Patient Type])

Question about GridView update query

I try to calculate a field value and update other. It almost works

UPDATE t_Shopping_cart SET [Product_code] = @.Product_code, [Quantity] = @.Quantity, [Total] = Quantity * Price WHERE ([Product_code] = @.Product_code)

I try to update t_Shopping_cart table with quantity values from grid view and calculate sum of products (quantity*Price). Best I can get is that quantity values are old. Some comments about my query:Price is also a field in table and control. [Product_code] = @.Product_code part is probably not needed.

Any ideas?

Leif

So there is a column in t_Shopping_cart with the name Price?
Then maybe you can use this:

UPDATE t_Shopping_cart SET [Quantity] = @.Quantity, [Total] = @.Quantity * Price WHERE ([Product_code] = @.Product_code)

|||

"So there is a column in t_Shopping_cart with the name Price?" Yes. I'll try that "UPDATE t_Shopping_cart SET [Quantity] = @.Quantity, [Total] = @.Quantity * Price WHERE ([Product_code] = @.Product_code)" tomorrow.

Thanks

Leif

|||

Np, feel free to get back to me if it doesn't work!

|||

Hi. A short question. Is this a good way to this. The more I read about ways to do this the more ways there seems to be calculating sub totals and probably checking duplicates too.

Regards

Leif

Question about getting a value

I have the following script (Trimmed down to show you)

1 Dim myConnection As OleDbConnection
2 Dim myCommand As OleDbCommand
3 Dim intUserCount As Integer
4 Dim LoggedInUserId As Integer
5 Dim strSQL As String
6
78strSQL = "SELECT intUserID FROM tblUsers " _
9& "WHERE txtUsername='" & Replace(txtUsername.Text, "'", "''") & "' " & "AND txtPassword='" & Replace(txtPassword.Text, "'", "''") & "';"
1011 myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _
12 & "Data Source=" & Server.MapPath("DB.mdb") & ";")
1314 myCommand = New OleDbCommand(strSQL, myConnection)
1516 myConnection.Open()
17LoggedInUserId = myCommand. <<<<
18 myConnection.Close()

I have bolded the line where I am stuck (17)... I need to return the the value of the selected row and append it to the variable shown... What command do I need to use?

since you would be returning only one value from your SELECT statement, ExecuteScalar would work the best.|||

Thanks ... I had actually put that and got it to work, but what if I need to return a specific row (For example say my select was grabbing 2 columns)?? What would I need to use then??

Thanks again in advance...

|||

Forgot to put as when I use ASP I can simply put something like

mycommand("intUserID")

?? Don't seem to be able to do anything like that with .NET ? Or can you??

|||You could use ExecuteReader.|||

Hey ndinakar ...

So how would I use that ..

ExecuteReader("intUserID") ... ?? Like that??

|||Please check out the documentation or even Google. I can point you in the right direction but I cannot write code for you.|||If you could point me towards a code sample using this method it would be appreciated... everything I find on Google doesn't show any examples?|||It took me 5 seconds to find this link on google:http://authors.aspalliance.com/aspxtreme/sys/data/sqlclient/SqlCommandClassExecuteReader.aspx|||

From link:

C#:void ExecuteReaderDemo ( string query, string connString ) { SqlConnection myConn = new SqlConnection ( connString ); SqlCommand myCommand = new SqlCommand ( query, myConn ); myCommand.Connection.Open ( ); SqlDataReader myReader = myCommand.ExecuteReader ( CommandBehavior.CloseConnection );while ( myReader.Read ( ) ) { Response.Write ( myReader.GetString ( 0 ) ); } myReader.Close ( ); myConn.Close ( );}VB:Public Sub ExecuteReaderDemo ( queryAs String, connStringAs String ) Dim myConnAs New SqlConnection ( connString ) Dim myCommandAs New SqlCommand ( query, myConn ) myCommand.Connection.Open ( ) Dim myReaderAs SqlDataReader = _ myCommand.ExecuteReader ( CommandBehavior.CloseConnection )While myReader.Read ( ) Response.Write ( myReader.GetString ( 0 ) )End While myReader.Close ( ) myConn.Close ( )End Sub

You can also reference the columns like (Instead of myReader.GetString(MyColumnNumber)):

C#: myReader["Mycolumn"]

VB: myReader("MyColumn")

|||Fantastic ... Thanks!!!

Monday, March 12, 2012

question about display mask & select number N to M records

Two questions:
1. With SQL, how can I setup display mask for value:
eg. display 100000 as $100,000
2. How can I select number N to M records in a table.
eg. select No. 50 - 100 records from a table. ( not top 50)
Thanks,
Guyang> 1. With SQL, how can I setup display mask for value:
> eg. display 100000 as $100,000
(a) I wouldn't rely on Enterprise Manager for data viewing / modification...
use a development tool for that, like Query Analyzer.
(b) there is no such thing as a "display mask" in SQL Server... this is
something that cute GUIs do. The data is not stored that way; if you want
it to be *presented* that way, write a view, e.g. SELECT CONVERT(VARCHAR,
moneyColumn, 1) FROM table
> 2. How can I select number N to M records in a table.
> eg. select No. 50 - 100 records from a table. ( not top 50)
SELECT TOP 50 * FROM
(SELECT TOP 100 * FROM table
ORDER BY some_column) x
ORDER BY some_column DESC
If you really need it to come back 50 -> 100, then
SELECT * FROM
(
SELECT TOP 50 * FROM
(SELECT TOP 100 * FROM table
ORDER BY some_column) x
ORDER BY some_column DESC) y
ORDER BY some_column

Friday, March 9, 2012

Question about Database Collations

I sent a couple of detached databases to a customer. When he installed his SQL Server he got database collation value of Latin1_General_CI_AS however, when he attached my databases they have a collation value of SQL_Latin1_General_CPI_CI_AS.

Now my stored procedures that use temp files are failing and a message is coming out SQL Server "Cannot resolve collation conflict for equal to operation"

I am assuming that the temp database has one collation value and my databases has another.

Q. Can anyone tell me the difference between the two collations? He is in Australia, I am in Canada - would he get a different default than I do?

Q. Can I safely alter the collation sequence of either the databases I sent or the temp database so that they match?

I have never run across this before but this is the first time I shipped the database offshore.

Thanks for any help you can give me. I am going to have to send my database to the Caribbean pretty soon and I need to know if this is going to happen there as well.

Well now that I've solved the problem I wonder what is the reasoning behind the defaults chosen by install.

First I can find no provision in the SQL Install to change the Default collation value.

My customer in Australia installed SQL Server on a new installation of Windows Server 2003 and got Latin1_General_CI_AS.
I built a brand new instance of Windows Server 2003 and when I installed SQL I got SQL_Latin1_General_CP1_CI_AS

The problem with my databases was in the stored procedures that used temporary tables - the collation values were different in the two databases and any comparisons on character fields failed.

I can work around the problem by altering all the stored procedures to include the collation clause to override the collation of tempdb. Is this the best way? I'm not sure. Is it a good practice to always include the collation clause when defining a table? I've always looked at it as kind of an annoyance -- never again Sad
|||

I am replying to this fairly old post because others may be wondering why they get the SQL_Latin1_General_CP1_CI_AS collation.

I believe that this is the 'old fashioned' collation used by versions of SQL Server prior to 2000. It seems to be offered on upgrade, whereas Latin1_General_CI_AS is used in from scratch installations. Certainly the latter is preferred.

I'm disappointed that SQL2005 doesn't have a change collation wizard and you need to create a DMO script in order to change the collation of existing databases.

|||
did you know which were the installation parameters you choose to get SQL_Latin1_General_CP1_CI_AS?

thanks for your help
|||

Andres,

Upon installation, you need to select "Collation designator and sort order:"
Set it to "Latin1_General"
then, check the box for "Accent - sensitive"

That should do it!