@@Identity not returning Value

I have a stored procedure that inserts a record. I call the @.@.Identity variable and assign that to a variable in my SQL statement in my asp.net page.

That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @.@.Identity field is no longer being returned.

As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?

When I pass the value of the TicketID variable to a text field after the insert, it gives me "@.TicketID".

Anyone have any ideas?

CREATE PROCEDURE [iguser].[newticket]
(
@.Category nvarchar(80),
@.Description nvarchar(200),
@.Detail nvarchar(3000),
@.OS nvarchar(150),
@.Browser nvarchar(250),
@.Internet nvarchar(100),
@.Method nvarchar(50),
@.Contacttime nvarchar(50),
@.Knowledge int,
@.Importance int,
@.Sendcopy bit,
@.Updateme bit,
@.ClientID int,
@.ContactID int,
@.TicketID integer OUTPUT
)
 AS
INSERT INTO Tickets
(
Opendate,
Category,
Description,
Detail,
OS,
Browser,
Internet,
Method,
Contacttime,
Knowledge,
Importance,
Sendcopy,
Updateme
)
VALUES
(
Getdate(),
@.Category,
@.Description,
@.Detail,
@.OS,
@.Browser,
@.Internet,
@.Method,
@.Contacttime,
@.Knowledge,
@.Importance,
@.Sendcopy,
@.Updateme
)
SELECT
@.TicketID = @.@.Identity
INSERT INTO Contacts_to_Tickets
(
U2tUserID,
U2tTicketID
)
VALUES
(
@.ContactID,
@.TicketID
)
INSERT INTO Clients_to_Tickets
(
C2tClientID,
C2tTicketID
)
VALUES
(
@.ClientID,
@.TicketID
)

Fixed the problem, it was with my .net code|||The best practice is to constrain, you should use IDENT_CURRENT('Tickets') instead of @.@.IDENTITY when you are inserting into multiple tables. IDENT_CURRENT gives you the last Identity generated in a specific table, as @.@.IDENTITY has no constraint and returns the last Identity of any table in the session or scope.

Based on your execution @.@.IDENTITY will work, but I figured I would throw this out there anyway.

@@IDENTITY issues with SQL2000 on GoDaddy

Okay... GoDaddy's driving menuts!!! Anyone... please help.

I am doing an insert into a database with:

INSERT blah blah blah; SELECT @.@.IDENTITY;

On EVERY OTHER SERVER I have tried this on (local SQLExpress 2005's and my companies various SQL2000 servers, this always returns for me the row number (of autoIndex number of the row just added to the database). My autoIndex was created with the following code:

ALTER TABLE sites ADD autoIndex INT NOT NULL IDENTITY (1,1) PRIMARY KEY

The code I have tried getting the identity with is:

cmd.CommandText ="INSERT blah blah; SELECT @.@.IDENTITY;";

string autoIndex =Convert.ToString(cmd.ExecuteScalar());

cmd.Dispose();

I also tried:

// bulding of SQL INSERT here

cmd.ExecuteNonQuery();

cmd.Dispose();

cmd =

newSqlCommand(SQLDataSource_pics.SelectCommand, conn);

cmd.CommandType =

CommandType.Text;

cmd.CommandText =

"SELECT @.@.IDENTITY;";string autoIndex =Convert.ToString(cmd.ExecuteScalar());

Session.Add(

"autoIndex", autoIndex);

Most of the time, I get a value of "0" (zero) for autoIndex. Every now an then (5%) of the time I get back a correct value.

I don't know where to go from here.

Thank you.

Hayedid

Are they using MS SQL 2000 or MySQL?

Now I had a problem before doing that when I had a cluster of SQL servers. My Intermediate layer sent INSERTS to computer A, UPDATE to computer B and SELECT to computer C.

So as you can see C did not know about A until after a few seconds was received on the replication.

Al

|||

Have you thought of making it a Stored Procedure? do the insert inside the sProc, and then, inside the SProc, having Declared an Integer variable (like Set @.NewID), the last line (after the insert) would be Select @.NewID, returning the integer

Why are you converting the 'autoIndex ' to a string? Try making it an integer. If it's an identity field, it would return an integer

Then, you can do with it whatever you'd like after that.

|||

SQL2000. I am in the process of converting to MySQL to see if that resolves the problem.

I originally had autoIndex as an integer. I converted to a string simply because that the debugging procedure I am throwing the value into a Textbox to view it until I get this problem resovled.

I'll try the stored procedure.

Thanks.

|||

For anyone who's interested, I figured this one out. When I was testing this on my production server (hosted with GoDaddy), I went to the site through a masked domain. If I tested through the primary domain everything worked fine.

Not sure why this makes a difference. Any ideas? I was able to resolve the issue by forwarding my second domain without masking it.

Thank you everyone.

@@Identity in Stored procedure

Hi,

I have a stored procedure that insert data into 2 temp tables. The problem that I have is when I insert a data into a first table, how do I get the @.@.identity value from that table and insert it into the second table?? The following code is what I have:

Create #Temp1
@.StateID Identity,
@.State nvarchar(2),
@.wage money

INSERT INTO #Temp1 (State, wage)

SELECT State, Wage FROM Table1 INNER JOIN Table2 ON Table1.Table1_ID = Table2.Table2_ID

Create #Temp2
@.ID Identity
@.EmployeeID int
@.StateID Int
@.Field1 Money
@.Field2 Money

INSERT INTO #Temp2 (EmployeeID, StateID, Field1, Field2)

SELECT EmployeeID, StateID, Field1, Field2 FROM SomeTable

So, The first part I created a #Temp1 table and insert data into the table. Then after the insert, I want the @.@.Identity value and insert into the #Temp2 table. This is my first time doing stored procedure, so, I am wondering how do I retrieve the @.@.identity value and put into the second select statement. Please help.

ahTan

DECLARE @.NewID INT

SET @.NewID = @.@.IDENTITY

OR

DECLARE @.NewID INT

SELECT @.NewID = @.@.IDENTITY

|||

instead of @.@.IDENTITY use SCOPE_IDENTITY() like

declare @._ID int

Select @._ID = SCOPE_IDENTITY()

to know why to use it, please search for SCOPE_IDENTITY in SQL SERVER books online.

thanks,

satish.

|||

SCOPE_IDENTITY() is not necessary here since the table is being created in the same stored proc. There are no triggers that would be run or no side effects to doing an insert into the table, therefore @.@.IDENTITY will work fine.

SCOPE_IDENTITY() is useful when you may have a trigger that inserts into table2 when you insert into table1, in that case getting the value used in the IDENTITY column should be retrieved with SCOPE_IDENTITY() since @.@.IDENTITY will get you value from table2 and not table1. However you don't have to worry about that here.

Regards,

Tim

|||

Thank you guys for the answer. Now, the next problem is, how do I insert the @.@.Identity value into the #TempPayroll table?

|||

ahTan:

how do I insert the @.@.Identity value into the #TempPayroll table

just save the @.@.scope_identity from the first insert into another variable and use it as input to the 2nd insert

insert into table1....
select @.Identity1 = @.@.SCOPE_IDENTITY
insert into table2 (...) values(..., @.Identity1,...)

|||

Thank you all of you for the responses. I have managed to get the stored procedure to work when I tested it using the SQL analyzer. I am getting close to what I want. Ok, the stored procedure will be called using a sqldatasource. The following code call the stored procedure:

1For Each rowAs GridViewRowIn GridView1.Rows2 SqlDataSource2.InsertCommand ="_payroll"3 SqlDataSource2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure4 SqlDataSource2.InsertParameters.Add("pniID", 410)5 SqlDataSource2.Insert()6 SqlDataSource2.InsertParameters.Clear()7Next8 GridView1.Visible =False9 SqlDataSource2.SelectCommand ="_payroll"10 SqlDataSource2.SelectCommandType = SqlDataSourceCommandType.StoredProcedure11''SqlDataSource2.SelectParameters.Add("pniID", CInt(row.Cells(0).Text.Trim))12 GridView2.DataSourceID ="SqlDataSource2"13 GridView2.DataBind()

After the for each loop, I want to display all the data in a gridview.  The following code is the sql code in the stored procedure:

 
1CREATE PROCEDURE [dbo].[_payroll]2(3@.pniIDint4)56AS78SET NOCOUNT ON910CREATE TABLE #TempStates11(12StateIDInt IDENTITY,13Statenvarchar(2),14Wagemoney15)1617Create Table #TempPayrolls18(19TempPayrollIDint IDENTITY,20PNI_IDint,21EmployeeIDint,22StateIDint,23Deptnvarchar(50),24PerDiemmoney,25Mileagemoney,26Phonemoney,27Computermoney,28Cameramoney29)3031INSERT INTO #TempStates (State, Wage)32SELECT PaySheets.StateOfProject,33 PNI_Payroll.Amount * PNI_Payroll.QuantityAS Amount34FROM PNI_Payroll35INNERJOIN PNION PNI_Payroll.PNI_ID = PNI.PNI_ID36INNERJOIN TimeSheetsON PNI.TimeSheetID = TimeSheets.TimeSheetID37INNERJOIN PaySheetsON TimeSheets.PaysheetID = PaySheets.PaysheetID38WHERE (PNI.PNI_ID = @.pniID)AND (PNI_Payroll.DescriptionLIKE'%Salary%')3940DECLARE @.NewStateIDint41SELECT @.NewStateID =@.@.IDENTITY4243--SELECT * FROM #TempStates4445INSERT INTO #TempPayrolls (PNI_ID, EmployeeID, StateID)46SELECT PNI.PNI_ID, InspectorID, StateID = @.NewStateID47FROM PNI48WHERE PNI.PNI_ID = @.pniID4950INSERT INTO #TempPayrolls (PNI_ID, EmployeeID, Dept, PerDiem, Mileage, Phone, Computer, Camera)51SELECT DISTINCT PNI.PNI_ID,52 PNI.InspectorID,53 StateOfProject +' Inspector'AS Dept,54 (SELECT (Quantity * Amount)As PerDiemAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Per Diem (YES OR NO)')AS PerDiem,55 (SELECT (Quantity * Amount)As MileageAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Mileage')AS Mileage,56 (SELECT (Quantity * Amount)As PhoneAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Cell Phone (YES OR NO)')AS Phone,57 (SELECT (Quantity * Amount)As ComputerAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Computer')AS Computer,58 (SELECT (Quantity * Amount)As DigitalCamAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Camera')AS Camera59FROM PNI_PayRoll60INNERJOIN PNION PNI_PayRoll.PNI_ID = PNI.PNI_ID61INNERJOIN TimeSheetsON PNI.TimeSheetID = TimeSheets.TimeSheetID62INNERJOIN PaySheetsON TimeSheets.PaysheetID = PaySheets.PaysheetID63WHERE PNI.PNI_ID = @.pniID6465--Display data from the temp tables66SELECT TempPayrollID, PNI_ID, EmployeeID,67(SELECT StateFROM #TempStatesWHERE #TempStates.StateID = #TempPayrolls.StateID)AS State,68(SELECT WageFROM #TempStatesWHERE #TempStates.StateID = #TempPayrolls.StateID)AS Wage,69PerDiem, Mileage, Phone, Computer, Camera70FROM #TempPayrolls717273-- Turn NOCOUNT back OFF7475SET NOCOUNT OFF76GO77
  So, I am wondering how am I going to display all the data in the temp tables in the gridview2. Please help

|||

Satish is correct, use SCOPE_IDENTITY(). Although fevir's explaination is correct on why @.@.IDENTITY might work, it's misusing the command, and the wrong command to use.

Just because there are no triggers TODAY, doesn't mean that there won't ever be.

|||

Motley, what about the whole YAGNI thought? Today, as it sit, the temp table it being created directly above and so no triggers exits. If you eventually did put a trigger on a temp table (not sure why you'd do this) but you'd adjust at that time.

Ultimately you're correct in that either can be used, but I'm curious about your thoughts in regard the YAGNI argument.

Regards,

Tim

|||

YAGNI would only apply if there was an easy way and more difficult approach. Since we are talking about using:

SELECT @.NewStateID =@.@.IDENTITY

or

SELECT @.NewStateID =SCOPE_IDENTITY()

I see no point in using a function that really should be reserved for someone who is very well versed in T-SQL. It's rare that @.@.IDENTITY is the correct function to use when you need an identity value. Again, in this case, we want the identity value that was just inserted by the insert just above us. The function for that is SCOPE_IDENTITY. If we wanted to know the last identity value that the system generated in the scope of our connection, that would be @.@.IDENTITY. I have yet to EVER need @.@.IDENTITY in any project I've ever done. And while using @.@.IDENTITY can cause maintenaince problems, and unexpected results, the same is not true of SCOPE_IDENTITY. It will always return the result that we are expecting whether we have a trigger on the table today, tomorrow, or never.

I say this, because I can't count the number of times that people have gotten bitten because they had to add auditting tables to track changes to a table (or replication), only to find that the application is now creating bogus data because someone decided to use @.@.IDENTITY when they should have used SCOPE_IDENTITY().

@@identity in postgresql?

I'm suppose to migrate data at work from MS SQL server to Postgresql. Everything is done except, how can I obtain @.@.identity in postgresql?
Thanks in advance.You can use nextval() or currval().|||Check out the following link:

link (http://www.postgresql.org/idocs/)

@@IDENTITY in code

Hi all,

I encountered a problem with SQL server mobile.

I am inserting a row inside a database table through C# code and need to obtain the ID of the last inserted row if successful. I have the following in my code:

SqlCeHelper.ExecuteNonQuery(connectionString, "*INSERT STATEMENT*");

Int64 id = (Int64)SqlCeHelper.ExecuteScalar(connectionString, "SELECT @.@.IDENTITY");

The variable id always ends up NULL. Is this the correct approach to this? Is there anything else I can use to obtain the id of the last inserted row in SQL server mobile?

-- The class SqlCeHelper is a wrapper that I wrote, it works 100%.

Thanks

you should be using Identity_Scope() - its better. I havent worked with SqlCE but this is what I use, and is best used than @.@.Identity, and no idea if this works for SqlCe

My apologies if it does not

I also believe, perhaps I am wrong again, that you are executing 2 different queries, hence why you are receiving null for your last query - again, I may well be totally wrong here. It's a learning curve for me also

|||

Hi,

No...unfortunately SQL mobile doesn't support the function.

I though of the two separate statements as well. Is it possible to run two different SQL statements inside one command?

Thanks

|||

OK,

Fixed the problem. It really was executing these two statements totally separately.

The correct way to fix this is to add a couple lines inside my SQLCeHelper...

I now have something like this inside the SQLCeHelper:

publicstaticobject ExecuteInsert(string connectionString, string command, paramsSqlCeParameter[] commandParameters){

object retval = null;

try{

SqlCeConnection cn = newSqlCeConnection(connectionString);

SqlCeCommand c = newSqlCeCommand(command, cn);

foreach (SqlCeParameter p in commandParameters)

c.Parameters.Add(p);

cn.Open();

c.ExecuteNonQuery();

c = newSqlCeCommand("SELECT @.@.IDENTITY AS [IDENTITY]", cn);

retval = c.ExecuteScalar();

}

catch

{

throw;

}

return retval;

}

sql

@@Identity does not return the correct value.

@.@.Identity does not return the correct value.
Anyone have any ideas how to fix this,
and more important,
how does it get out of sync and return the wrong value?
Account.AccountID = (int IDENTITY, PRIMARY KEY )
/* Three statements executed together */
EXEC ('DBCC CheckIdent(Account)')
INSERT Account(AccountNumber, AccountAccountStatus,
AccountAgency, AccountAgencyClientID,
AccountPaymentAmount)
VALUES('9',2,1,'sadasd',787.8)
SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
(AccountID) as "Max(AccountID)" from Account
/* Results of three statements executed together */
Checking identity information: current identity
value '261', current column value '261'.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
(1 row(s) affected)
AccountID = @.@.IDENTITY Max(AccountID)
-- --
543 262
(1 row(s) affected)Do you have a trigger on the table? What does SCOPE_IDENTITY() give you?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ken" <KLomax@.Noteworld.com> wrote in message
news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
> @.@.Identity does not return the correct value.
> Anyone have any ideas how to fix this,
> and more important,
> how does it get out of sync and return the wrong value?
> Account.AccountID = (int IDENTITY, PRIMARY KEY )
> /* Three statements executed together */
> EXEC ('DBCC CheckIdent(Account)')
> INSERT Account(AccountNumber, AccountAccountStatus,
> AccountAgency, AccountAgencyClientID,
> AccountPaymentAmount)
> VALUES('9',2,1,'sadasd',787.8)
> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
> (AccountID) as "Max(AccountID)" from Account
> /* Results of three statements executed together */
> Checking identity information: current identity
> value '261', current column value '261'.
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> (1 row(s) affected)
> AccountID = @.@.IDENTITY Max(AccountID)
> -- --
> 543 262
> (1 row(s) affected)
>|||Do you have a trigger on the table, perhaps doing an insert to another
table?
If so, that's why you'll see that problem... Use SCOPE_IDENTITY() instead...
"Ken" <KLomax@.Noteworld.com> wrote in message
news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
> @.@.Identity does not return the correct value.
> Anyone have any ideas how to fix this,
> and more important,
> how does it get out of sync and return the wrong value?
> Account.AccountID = (int IDENTITY, PRIMARY KEY )
> /* Three statements executed together */
> EXEC ('DBCC CheckIdent(Account)')
> INSERT Account(AccountNumber, AccountAccountStatus,
> AccountAgency, AccountAgencyClientID,
> AccountPaymentAmount)
> VALUES('9',2,1,'sadasd',787.8)
> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
> (AccountID) as "Max(AccountID)" from Account
> /* Results of three statements executed together */
> Checking identity information: current identity
> value '261', current column value '261'.
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> (1 row(s) affected)
> AccountID = @.@.IDENTITY Max(AccountID)
> -- --
> 543 262
> (1 row(s) affected)
>|||Tibor:
You are the man!
I did not realize @.@.Identity was not scope specific.
SCOPE_IDENTITY() fixes the problem.
I have a trigger that inserts audit records on updates and
inserts, so @.@.Identity was returning the key of the audit
table, not the Account table.
THANK YOU.
Ken
>--Original Message--
>Do you have a trigger on the table? What does
SCOPE_IDENTITY() give you?
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Ken" <KLomax@.Noteworld.com> wrote in message
>news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
>> @.@.Identity does not return the correct value.
>> Anyone have any ideas how to fix this,
>> and more important,
>> how does it get out of sync and return the wrong value?
>> Account.AccountID = (int IDENTITY, PRIMARY KEY )
>> /* Three statements executed together */
>> EXEC ('DBCC CheckIdent(Account)')
>> INSERT Account(AccountNumber, AccountAccountStatus,
>> AccountAgency, AccountAgencyClientID,
>> AccountPaymentAmount)
>> VALUES('9',2,1,'sadasd',787.8)
>> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
>> (AccountID) as "Max(AccountID)" from Account
>> /* Results of three statements executed together */
>> Checking identity information: current identity
>> value '261', current column value '261'.
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> (1 row(s) affected)
>> AccountID = @.@.IDENTITY Max(AccountID)
>> -- --
>> 543 262
>> (1 row(s) affected)
>
>.
>|||Adam:
You are the man!
I did not realize @.@.Identity was not scope specific.
SCOPE_IDENTITY() fixes the problem.
I have a trigger that inserts audit records on updates and
inserts, so @.@.Identity was returning the key of the audit
table, not the Account table.
THANK YOU.
Ken
>--Original Message--
>Do you have a trigger on the table, perhaps doing an
insert to another
>table?
>If so, that's why you'll see that problem... Use
SCOPE_IDENTITY() instead...
>
>"Ken" <KLomax@.Noteworld.com> wrote in message
>news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
>> @.@.Identity does not return the correct value.
>> Anyone have any ideas how to fix this,
>> and more important,
>> how does it get out of sync and return the wrong value?
>> Account.AccountID = (int IDENTITY, PRIMARY KEY )
>> /* Three statements executed together */
>> EXEC ('DBCC CheckIdent(Account)')
>> INSERT Account(AccountNumber, AccountAccountStatus,
>> AccountAgency, AccountAgencyClientID,
>> AccountPaymentAmount)
>> VALUES('9',2,1,'sadasd',787.8)
>> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
>> (AccountID) as "Max(AccountID)" from Account
>> /* Results of three statements executed together */
>> Checking identity information: current identity
>> value '261', current column value '261'.
>> DBCC execution completed. If DBCC printed error
messages,
>> contact your system administrator.
>> (1 row(s) affected)
>> AccountID = @.@.IDENTITY Max(AccountID)
>> -- --
>> 543 262
>> (1 row(s) affected)
>
>.
>|||Wait, I thought Tibor was the man. Who exactly _is_ the man? : )
<anonymous@.discussions.microsoft.com> wrote in message
news:001b01c3aadb$85d144f0$a501280a@.phx.gbl...
> Adam:
> You are the man!
> I did not realize @.@.Identity was not scope specific.
> SCOPE_IDENTITY() fixes the problem.
> I have a trigger that inserts audit records on updates and
> inserts, so @.@.Identity was returning the key of the audit
> table, not the Account table.
> THANK YOU.
> Ken
> >--Original Message--
> >Do you have a trigger on the table, perhaps doing an
> insert to another
> >table?
> >
> >If so, that's why you'll see that problem... Use
> SCOPE_IDENTITY() instead...
> >
> >
> >"Ken" <KLomax@.Noteworld.com> wrote in message
> >news:059501c3aac8$d8d00b40$a001280a@.phx.gbl...
> >> @.@.Identity does not return the correct value.
> >>
> >> Anyone have any ideas how to fix this,
> >> and more important,
> >> how does it get out of sync and return the wrong value?
> >>
> >> Account.AccountID = (int IDENTITY, PRIMARY KEY )
> >>
> >> /* Three statements executed together */
> >> EXEC ('DBCC CheckIdent(Account)')
> >>
> >> INSERT Account(AccountNumber, AccountAccountStatus,
> >> AccountAgency, AccountAgencyClientID,
> >> AccountPaymentAmount)
> >> VALUES('9',2,1,'sadasd',787.8)
> >>
> >> SELECT @.@.IDENTITY as "AccountID = @.@.IDENTITY", Max
> >> (AccountID) as "Max(AccountID)" from Account
> >>
> >> /* Results of three statements executed together */
> >> Checking identity information: current identity
> >> value '261', current column value '261'.
> >> DBCC execution completed. If DBCC printed error
> messages,
> >> contact your system administrator.
> >>
> >> (1 row(s) affected)
> >>
> >> AccountID = @.@.IDENTITY Max(AccountID)
> >> -- --
> >> 543 262
> >>
> >> (1 row(s) affected)
> >>
> >
> >
> >.
> >|||I think we're gonna have to take this outside.
"Eric Sabine" <mopar41@.hyottmail.com> wrote in message
news:3fb54413$0$43854$39cecf19@.news.twtelecom.net...
> Wait, I thought Tibor was the man. Who exactly _is_ the man? : )
>

@@Identity c# help...

I am trying to follow other examples I have seen on the site, and am still getting the

Must declare the scalar variable "@.@.INDENTITY".

string sqlAdd =string.Format("INSERT INTO " + siteCode +"_campaign_table (campaign_name, prod_id, type) "

+

"VALUES('{0}', '{1}', '{2}'); SELECT @.@.INDENTITY", campaignName, prodID, type);SqlCommand comAdd =newSqlCommand(sqlAdd, con);

comAdd.CommandType =

CommandType.Text;

con.Open();

//comAdd.ExecuteNonQuery();int identity;

identity =

Decimal.ToInt32((decimal)comAdd.ExecuteScalar());

lblErrorMessageAdd.Text = identity.ToString();

con.Close();

You have a typo. It should be@.@.IDENTITY

Also, your code should use parameters; what you have currently (placeholders for string replacement) is insecure and subject to SQL injection attacks.|||thanks, Corrected a few things there and works as intended.|||Actually, since you are using SQL Server, the best idea is to SELECT SCOPE_IDENTITY(). See, for example, this blog post for an explanation:SCOPE_IDENTITY() and @.@.IDENTITY Demystified. I should have pointed this out on my last post; I am sorry for missing it.

@@Identity being over-written by Insert Trigger in stored procedure.

Hi All

I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).

I have been using the @.@.Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an after insert Trigger has been added to the table being updated.

Now the @.@.Identity is returning the identity value of the trigger that was called instead of the original table insert.

Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).

Thank in advance.

Eamon.Look at SCOPE_IDENTITY and/or IDENT_CURRENT

Regards,

hmscott|||Thank you very much hmscott. SCOPE_IDENTITY works a treat!!!

@@IDENTITY and thread safety

I am currently using @.@.Identity to retreive the Identity value for the PK field in a table that I am inserting data into. Essentially, my code looks like this:
SqlCommand cmd=new SqlCommand("Insert into table(... ;Select @.@.Identity from table",conn);
string identity=cmd.ExecuteScalar();

Testing this myself, it works fine, but I am worried as to how thread safe this is in a real-world environment? (i.e. with multiple users clicking at it). Is there a guaranteed way to make this thread safe- wrap it in a transaction maybe?
I think you should use scope_identity()|||I would search the online books on @.@.Identity. I'm sure the subject istouched upon. I seem to recall something about @.@.Identity applying tothe current connection or context but I forgot the details. In anycase, @.@.Identity is so widely used that if it wasn't thread safe we'dall be in serious trouble.
|||scope_identity()
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp
@.@.Identity
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp
@.@.Identity is thread safe, but scope_identity() is the way to go.|||

Everyone,

Thanks for the help. JHouse, those articles are great. Just for the benefit of others reading this thread, the key difference between the @.@.IDENTITY and Scope_Identity() is the @.@.IDENTITY returns the last identity value of any table your batch or procedure inserts into - implicit or explicit - while Scope_Identity() is explicit only. So if you insert into tableA that has a trigger that inserts into tableB. @.@.Identity will return the identity from tableB, and Scope_Identity will return the identity from tableA.

@@identity

hi,

I was wondering if someone could help me out with this stored procedure I have. I am trying to execute a transaction in one of my sps and am getting pk violations on 'OrderID'.
This where i encounter this error:

SELECT
 @.OrderID = @.@.Identity
/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
 OrderID,
 ProductID,
 Quantity,
 UnitCost
)
SELECT
 @.OrderID,
 ShoppingCart.ProductID,
 ShoppingCart.Quantity,
 Prices.UnitCost
FROM
ShoppingCart INNER JOIN
 Prices ON ShoppingCart.ProductID = Prices.ProductID
WHERE
 CartID = @.CartID

is there any way to rewrite this statement so that I can put it in the form insert()values(). ?is OrderID an autonumbering field?

if so - you shouldn't include it in your INSERT statement|||YEs it is ! Why do you not recommend putting it in the insert stetment ?

Anyways, I realized that my error wasn't really in the stored procedure, but in the function calling it.

Thanks.|||SQL will put the identity into the table automatically, what you're trying to do is over-write the value with the same number again...hence the duplicate key problem. So like the poster said, don't do it, get the help out and read what an identity column acutally is and how to use it.|||I'm not really overwriting the @.@.identity value, but assigning it to a variable. I then use the value of that variable in an insert statement on a totally separate table.
I solved my primary key issue and it wasn't related to the sql transaction i posted here, ....there was a logic error in my code.

So now even though its all working, you still think that the sql statement I'm using is incorrect ?|||Sorry I admit I was assuming the code that went before your example. The only suggest I would make would be to consider using SCOPE_IDENTITY rather than IDENTIY, tends to be safer in the long run.sql

@@identity

i have found loads of topics on this but have yet to find one that gives the answer i need.

I want to display the last insert id into a label/textbox after the INSERT function

how do i do this?

A question back at you -

Which database are you using?

|||

if the value you are using as an id is an idetity column and your database is sql server 2000 or 2005 then you can do something like this to get the last inserted value

strSQL = "";
strSQL += " Set Nocount on ";
strSQL += " Insert TableX(...) VALUES (...) ";
strSQL += " select IdentityInsert = IDENT_CURRENT( 'TableX' ) ";
strSQL += " set nocount off ";

hope this helps

|||

In sqlserver you can also do it with stored procedure:

CREATE procedure [dbo].[textbookop_insert])
as
begin
INSERT INTO [cosolu].[dbo].[cosolu_textbookop]
           ([textbookop_textbookid])
     VALUES
           (@.textbookop_textbookid)
return @.@.identity
end;
GO

You can insert the value using the following code:

 SqlConnection sqlconn = DB.CreateConn(); SqlCommand sqlcmd =new SqlCommand("textbookop_insert", sqlconn); sqlcmd.CommandType = CommandType.StoredProcedure; SqlParameter sqlpar = sqlcmd.Parameters.Add("ReturnValue", SqlDbType.Int); sqlpar.Direction = ParameterDirection.ReturnValue; sqlcmd.Parameters.AddWithValue("@.textbookop_textbookid", textbookop_textbookid); sqlconn.Open(); sqlcmd.ExecuteNonQuery();int camid = Convert.ToInt32(sqlcmd.Parameters["ReturnValue"].Value); sqlconn.Close();

Hope this helps.|||i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object|||im using sql server 2005|||

mike7510uk:

i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Hope that helps.

|||

After you do an insert you can return the key for the insert like:

INSERT INTO [dbo].[table] (name, phone)VALUES ("Denny", "555-1234")Return@.@.IDENTITY

Also be sure to read on the differences of SCOPE_IDENTITY() and @.@.IDENTITY - to be sure which one you should use to return the correct id.

|||so after i set Return @.@.IDENTITY how would that display it into a label / textbox?|||

Lee Dumond:

mike7510uk:

i am using an sqldatasource, dont know if that changes anything. I dont know how the syntax works when the connection is built into an object

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Hope that helps.

where is this file? i dont have any xsd files in my folders!!

|||

Sorry, I thought you were using an ObjectDataSource. I should have read more closely. My bad. (With the OnjectDataSource, you can use a strongly-typed Dataset that contains TableAdapters.)

In the case of using an SqlDataSource, you'll can use one of the methods above to return the identity value with the insert command.

|||

Lee Dumond:

If the connection is built into the object, you have a TableAdapter that is connected to the table you are using. This TableAdapter contains methods that pull data from the table, like Fill() and GetData(). You can easily add another method to this TableAdapter to get the last value that was generated for the identity column of its table.

You can doubleclick on the .xsd file for the sqlDataSource, which opens the DataSet Designer. Go to the table you want to work with (lets call it myTable) and right click on any existing method in there. From this menu, choose Add Query.

This will give you a handy-dandy TableAdapter Query Configuration Wizard. In this wizard:

1. When it says "Choose a Query Type", choose "SELECT which returns a single value".

2. When it says "Specify a SQL SELECT statement, type in

SELECTIDENT_CURRENT ('myTable')FROM myTable

3. When it says "Choose a Function Name", give it a name like "GetLastIdentityValue".

Voila, you're done. So now, in your page, lets say you want to grab the ID of the last record entered and disply it in a label (Label1).

int theID = (int)myTableAdapter.GetLastIdentityValue();Label1.Text = theID.ToString();

Notice there is a cast in the first line, because the value returned by IDENT_CURRENT is of type nullable Decimal.

Question: doesn't this risk getting a later insert's identity value? How can I get the scope_identity value through the table adapter like this but from the same database call that actually makes the insert (less risky)? Regardless, thanks for a great solution using table adapters!

|||

Yes, is which why you should call itimmediately, right after you call the Update method. As you correctly point out, it surely doesn't make sense to use it in any other manner.

Here is an example where it could come in handy. Say, for instance, you have two tables: Orders and OrderItems. The OrderItems table is going to contain an OrderID column that is a foreign key back to the Orders table (OrderID would be a primary key column in Orders).

When you place OrderItems in your dataset, you have no idea what value to put in that foreign key OrderID column, because the current order hasn't been saved yet. You have a classic chicken-and-egg situation. What to do?

Easy... when you create the OrderItems, just put in any dummy value you want (it is going to be replaced with the correct value in a minute).

When you've added all the items you want, the current order is finished and you'll want to save it. To do that, do an Update on the OrdersTableAdapter, then immediately call the GetLastIdentityValue() method you've set up:

ordersTableAdapter.Update(myDBDataSet.Orders);int orderId = (int) ordersTableAdapter.GetLastIdentityValue();

This gives you the number to put in that foreign key column of the OrderItems table. So, now you loop through the dataset of the OrderItems, set each OrderID value of each accordingly, then update that:

foreach (myDBDataSet.OrderItemsRow rowin myDBDataSet.OrderItems){ row.OrderID = orderId;}orderItemsTableAdapter.Update(myDBDataSet.OrderItems);

Voila! All the foreign keys match, anf the integrity of the database is maintained.

|||

i did this in the end by using mysql and puttin it into the code behind.

All works good,

cheers for all help

|||

That is exactly the answer I needed... all the other projects here had been done in Deklarit, and after an update the DataRow held the new identity value... not so, apparently, with the xsd dataset in studio. This is exactly what I needed. Thanks so much!

Thomas

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"
Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"
|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>

@@identity

I am running an insert sp that looks like this
*****************************
@.x int,
@.x1 int,
@.x2 int,
@.NewID int
AS
BEGIN
INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
SET @.New ID = (Select @.@.IDENTITY)
INSERT INTO TABLE2 (@.newID,@.x)
END
***********************
I am getting a conversion error, "Error converting data type varchar to
int.". I cannot figure out the converstion process. I have tried a ton of
different options but nothing works.
I want to run both INSERTs in the same sp.
Any ideas...>SET @.New ID = (Select @.@.IDENTITY)
Try: @.NewID = @.@.IDENTITY
If you are on SQL Server 2000, look at Scope_Identity() in BOL.
--
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
M.Smith wrote:
> I am running an insert sp that looks like this
> *****************************
> @.x int,
> @.x1 int,
> @.x2 int,
> @.NewID int
> AS
> BEGIN
> INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
> SET @.New ID = (Select @.@.IDENTITY)
> INSERT INTO TABLE2 (@.newID,@.x)
> END
> ***********************
> I am getting a conversion error, "Error converting data type varchar to
> int.". I cannot figure out the converstion process. I have tried a ton of
> different options but nothing works.
> I want to run both INSERTs in the same sp.
> Any ideas...
>|||worked with SCOPE, thanks
"Lars Broberg" <lars.b@.elbe-data.nothing.se> wrote in message
news:ugIkZPFPFHA.2788@.TK2MSFTNGP09.phx.gbl...
> Try: @.NewID = @.@.IDENTITY
> If you are on SQL Server 2000, look at Scope_Identity() in BOL.
> --
> Lars Broberg
> Elbe-Data AB
> http://www.elbe-data.se
> Remove "nothing." when replying to private e-mail!
>
> M.Smith wrote:|||Hi
Unless you want to keep @.NEWID for later use then you may want to use
SCOPE_IDENTITY() directly in the insert statement.
John
"M.Smith" <martys@.bennyhinn.org> wrote in message
news:uxQhOmFPFHA.3372@.TK2MSFTNGP10.phx.gbl...
> worked with SCOPE, thanks
> "Lars Broberg" <lars.b@.elbe-data.nothing.se> wrote in message
> news:ugIkZPFPFHA.2788@.TK2MSFTNGP09.phx.gbl...
>|||Don't forget to specify the fileds in your INSERT statement.
INTO into TheTable (Field1,Field2) values (@.Value1,@.Value2)
JN.
"M.Smith" <martys@.bennyhinn.org> a crit dans le message de news:
%23Z$GiMFPFHA.1088@.TK2MSFTNGP14.phx.gbl...
>I am running an insert sp that looks like this
> *****************************
> @.x int,
> @.x1 int,
> @.x2 int,
> @.NewID int
> AS
> BEGIN
> INSERT INTO TABLE1 (x,x1,x2) VALUES (@.x,@.x1,@.x2)
> SET @.New ID = (Select @.@.IDENTITY)
> INSERT INTO TABLE2 (@.newID,@.x)
> END
> ***********************
> I am getting a conversion error, "Error converting data type varchar to
> int.". I cannot figure out the converstion process. I have tried a ton of
> different options but nothing works.
> I want to run both INSERTs in the same sp.
> Any ideas...
>

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>> I'm running the following within a For Next loop but the
>> @.@.identity value doesn't always show up. It only shows
>> for every 3rd record. Any ideas on why this might be?
>> Thanks,
>> Ben Bleser
>> sql = "INSERT INTO table . . . . "
>> conn.execute(sql)
>> sql = "Select @.@.Identity as Id"
>> set rsTmp = conn.execute(sql)
>> RequestID = rsTmp("Id")
>> rsTmp.close
>> set rsTmp = nothing
>> Response.write RequestID & "<br><br>"
>|||Hello Ben,
Take a look at SCOPE_IDNTITY() too - in books online.
Cheers
SQLCatZ

@@identity

Consider Table A having an Identity column.
If when I want insert a record to the table, I want to have the autogenerated value to be inserted into another column within the same table and same row, how am I going to do it?
rignhomCheck @.@.Identity from BOOKS ONLINE to get last-inserted identity value.|||Originally posted by Satya
Check @.@.Identity from BOOKS ONLINE to get last-inserted identity value.

Actually, to be safer, I have found that SCOPE_IDENTITY() is better than @.@.IDENTITY. Both do the same thing, but if you happen to have 2 identities being created within different execution scopes, SCOPE_IDENTITY always seems to return the correct one.|||Hi, what I want is
consider TableA with (Column1 Identity, Column2, Column3).

When I insert into this table
I want to issue sort of

INSERT INTO jobs (Column2,Colum3)
VALUES ('Col2Value', @.@.IDENTITY)

I want Column3 to have the same value as the newly autogenerated value for Column1.

Thanks
rignhom|||CREATE TRIGGER TRG_NAME ON TableA
FOR INSERT
AS
BEGIN
DECLARE @.ID BIGINT
SELECT @.ID = Column1 FROM INSERTED

UPDATE TableA
SET Column3 = @.ID
WHERE Column1 = @.ID
END|||if Column3 is a does not allow Null, would this trigger be useful?|||I don't know of another way of retrieving the id value, because the values for either @.@.identity and scope_identity() are only know after the insert in the table. To solve your problem you can define a default value of let's say 0 (zero), this value will then be overwritten by the trigger.|||hmm.. that's a way.. but would the cost be too high, since there's two write. And it may hit concurrency as well..|||Try this, use a computed column over a trigger

create table blah (ikey int identity(1,1), column2 varchar(20),column3 as ikey)

insert into blah select 'joe'

select * from blah

HTH|||Try this, use a computed column over a trigger

create table blah (ikey int identity(1,1), column2 varchar(20),column3 as ikey)

insert into blah select 'joe'

select * from blah

Can you explain what the intention is? Does your insert consists of two selects? How do you know the value for column3 before the actual insert?|||If you run this after running my prior SQL

select * from syscolumns where name in ('ikey','column2','column3')

You will see that ikey and column 3 are almost identical (except for xoffset which I am not sure what it's for, BOL says internal use only)

That makes me think that in one insert it is seeing the insert of those two columns as the same, that's why you are able to specify the insert with only one value even though you have three columns. Not sure if that is answering your question and I'm not sure exactly how it works but I know it's faster and preferred than a Trigger.|||nice ...sql

@@Identity

Does this only work with an identity column?

my :

INSERT INTO [Contacts].[ekr].[Usrs]([id], [Uid], [Pw], [writ], [Maint])
VALUES(7, 'MM', 'WW', 1, 0)

IF @.@.ERROR > 0
RAISERROR('String, Severity level, State',16,122)
ELSE
SELECT @.@.Identity as NewOne

Returns : NewOne
NULLYes. See the BOL entry on @.@.Identity.

Regards,

hmscott

Does this only work with an identity column?

my :

INSERT INTO [Contacts].[ekr].[Usrs]([id], [Uid], [Pw], [writ], [Maint])
VALUES(7, 'MM', 'WW', 1, 0)

IF @.@.ERROR > 0
RAISERROR('String, Severity level, State',16,122)
ELSE
SELECT @.@.Identity as NewOne

Returns : NewOne
NULL

@@identity

I have a problem hopefully someone can help with...
I have a stored procedure which ...
Writes to table A (table A has an identity column)
Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
Updates a table; setting column X to the IDENTITY value just retrieved
Table A has a couple of triggers on it, one of which writes to a different
table - and that table has an identity column.
Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
identiy column for the table I wrote to (TABLE A), it returns the identity
value of the insert inside the triggers.
Any suggestions on how I get the correct value after writing to table A?
Hope this makes some sense!
Thanks> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
There is a pretty good explanation in the BOL about how it works
If you insert a value into TableA by using @.@.scope_identity() function you
will get the last values of the identity column of the TableA
"..." <...@.nowhere.com> wrote in message
news:emnME2LAGHA.3496@.TK2MSFTNGP11.phx.gbl...
>I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
>
>|||.... wrote:

> I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
If you reference SCOPE_IDENTITY() in the proc then it will return the
last IDENTITY value inserted in the current scope (not in the trigger).
If you reference @.@.IDENTITY it will return the IDENTITY value from ANY
scope (including the trigger).
To reference the TableA IDENTITY value in the trigger itself don't use
either of those functions. Refer to the INSERTED virtual table instead.
Your trigger code should accommodate the fact that there may be more
than one row inserted.
There is no such function as @.@.SCOPE_IDENTITY.
David Portas
SQL Server MVP
--|||SCOPE_IDENTITY should work for you
If you want the latest identity regardless of scope and session then
take a look at IDENT_CURRENT
IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
You would use it like this: SELECT IDENT_CURRENT('TableA') --TableA
would be your table name
http://sqlservercode.blogspot.com/|||not sure what @.@.scope_identity is -- SCOPE_IDENTITY() is the correct
function.
@.@.IDENTITY should have the last inserted identity value (the one
inserted into from the trigger)
SCOPE_IDENTITY() has last inserted identity from your session, so when
you check it, it should be as soon after the related insert as possible.
If this still doesn't help, could you post the proc and trigger code?
This will help posters give better answers.
... wrote:
> I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
>
>

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & "<br><br>"Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & "<br><br>"|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>

@@identitiy always returns a value of 1 from sp

The data is entered correctly but identity of new record is always sent back
as 1 .
Why is this always returning a value of 1 ?
sp:
Create PROCEDURE AddPlayer
@.SQLCMD nvarchar(1000)
AS
BEGIN
EXECUTE sp_executesql @.SQLCMD
END
SELECT SCOPE_IDENTITY()Without answering the original question, let me tell
you that your approach is totally wrong.
Why you have to pass all the sql from the client side
and use the SP just to call sp_executesql ?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"TJS" <nospam@.here.com> wrote in message
news:e3R8CnXHFHA.1172@.TK2MSFTNGP12.phx.gbl...
> The data is entered correctly but identity of new record is always sent
> back as 1 .
> Why is this always returning a value of 1 ?
> sp:
> Create PROCEDURE AddPlayer
> @.SQLCMD nvarchar(1000)
>
> AS
> BEGIN
> EXECUTE sp_executesql @.SQLCMD
> END
> SELECT SCOPE_IDENTITY()
>
>|||A much more important question is why are you using dynamic SQL to
insert a row? Don't do that if you can possibly avoid it. Instead, add
parameters for each column and insert them with a static INSERT
statement.
SCOPE_IDENTITY() won't see the IDENTITY value in this example because
sp_executesql has its own scope. Use @.@.IDENTITY instead, although if
you have a trigger on the table then @.@.IDENTITY will give you the last
value inserted by a trigger.
David Portas
SQL Server MVP
--|||The SQL is generated dynamically, so I just pass it through once developed.
Seemed like a good solution.
I tried using "SELECT @.@.IDENTITY" in the procedure but it still returns a
value of 1.
Is there a way to requery the table and get the maximum vaslue of the
identity , within the same procedure and have it return that value ?|||> The SQL is generated dynamically, so I just pass it through once
developed.
> Seemed like a good solution.
Then I suggest that you should do some serious studying of development
best practices in SQL. Dynamic SQL is something to be avoided unless
you have exceptional cause to need it. There are good reasons for this.
The following article discusses some of them:
http://www.sommarskog.se/dynamic_sql.html

> Is there a way to requery the table and get the maximum vaslue of the

> identity , within the same procedure and have it return that value ?
You should always be able to retrieve the IDENTITY value after an
INSERT by using an alternate key of the table because IDENTITY should
never be the only key. Having said that, the @.@.IDENTITY method should
work in the scenario you have given so if you need more help please
post some code that will reproduce the problem for us: A CREATE TABLE
statement and some code to call the SP.
David Portas
SQL Server MVP
--|||here is the revised code
sp:
Create PROCEDURE AddPlayer
@.SQLCMD nvarchar(1000)
AS
BEGIN
EXECUTE sp_executesql @.SQLCMD
END
SELECT @.@.IDENTITY|||It works for me:
CREATE TABLE T1 (x INTEGER IDENTITY PRIMARY KEY, z INTEGER NOT NULL
UNIQUE)
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (1)'
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (2)'
Result:
(1 row(s) affected)
---
1
(1 row(s) affected)
(1 row(s) affected)
---
2
(1 row(s) affected)
David Portas
SQL Server MVP
--|||Did you check to see if there is a trigger on this table?
David Portas
SQL Server MVP
--|||no triggers
I don't see where in your sp the identity being returned ?
what did you use to return the last identity
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109627060.294495.297220@.o13g2000cwo.googlegroups.com...
> Did you check to see if there is a trigger on this table?
> --
> David Portas
> SQL Server MVP
> --
>|||I was calling your SP:
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (1)'
If you get a different result then please post some complete code that
we can actually run, including the CREATE TABLE statement and INSERT
statement(s).
David Portas
SQL Server MVP
--

@@FETCH_STATUS... one per simultaneous execute? Hope not!

Hello,
In another question i am using @.@.FETCH_STATUS . Below is an excerpt from SQL
Books Online.
"Because @.@.FETCH_STATUS is global to all cursors on a connection, use
@.@.FETCH_STATUS carefully".
Sounds silly but can anyone define a "connection" for me with respect to Sql
Server. I dont want to get caught out by using this construct. Does the
above mean "watch yourself if your doing things like nesting udfs and stored
procs etc" or does it mean "dont use this construct in high volume
environments like a web application".
In other words if my DAL has a 2 users calling the same procedure at
precisely the same time and that SP/UDF uses @.@.FETCH_STATUS, im still safe
right? Because they are on different connections.
cmdSelect.Connection.Open
Or does SQL Server "define" a connection differently?
TIA
Ian
> In other words if my DAL has a 2 users calling the same procedure at
> precisely the same time and that SP/UDF uses @.@.FETCH_STATUS, im still safe
> right? Because they are on different connections.
That's right... these are different connections.
HTH
Kalen Delaney
www.solidqualitylearning.com
"Ian Evitable" <whole@.hog.com> wrote in message
news:%238eKHzctFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> In another question i am using @.@.FETCH_STATUS . Below is an excerpt from
> SQL
> Books Online.
> "Because @.@.FETCH_STATUS is global to all cursors on a connection, use
> @.@.FETCH_STATUS carefully".
> Sounds silly but can anyone define a "connection" for me with respect to
> Sql
> Server. I dont want to get caught out by using this construct. Does the
> above mean "watch yourself if your doing things like nesting udfs and
> stored
> procs etc" or does it mean "dont use this construct in high volume
> environments like a web application".
> In other words if my DAL has a 2 users calling the same procedure at
> precisely the same time and that SP/UDF uses @.@.FETCH_STATUS, im still safe
> right? Because they are on different connections.
> cmdSelect.Connection.Open
> Or does SQL Server "define" a connection differently?
> TIA
> Ian
>|||Kalen
if you are executing two cursors at the same time, then you will definitely
face problem. if latest fetch status for the other cursor is 1 or 2, by the
time you are in cursor1 then latest fetch status for the cursor1 is 1 or 2
not zero(though actually not)
In otherwords it is like @.@.identity but specific to connection.
Regards
R.D
"Kalen Delaney" wrote:

>
> That's right... these are different connections.
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Ian Evitable" <whole@.hog.com> wrote in message
> news:%238eKHzctFHA.3500@.TK2MSFTNGP09.phx.gbl...
>
>|||That's true, but the OP just wanted to know about connections. He seemed
pretty clear that the docs said the @.@.FETCH_STATUS was the last fetch on the
connection, so if you have two or more on the SAME CONNECTION, you can have
problems.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:29EA91F8-AA2B-476F-BFBB-938840F57BF1@.microsoft.com...
> Kalen
> if you are executing two cursors at the same time, then you will
> definitely
> face problem. if latest fetch status for the other cursor is 1 or 2, by
> the
> time you are in cursor1 then latest fetch status for the cursor1 is 1 or 2
> not zero(though actually not)
> In otherwords it is like @.@.identity but specific to connection.
> Regards
> R.D
>
> "Kalen Delaney" wrote:
>
>|||Yes thanks RD but after Kalens' original clarification i now understand. The
idea of what constitutes a connection in Sql Server is the same as in
dotNet.
If however i want to something really fancy using multiple cursors on the
same connection then i should take extra special care that my use of
@.@.Fetch_STATUS is acting on the/signalling the status of the cursor at hand.
I thought that was the way it would be but i needed to be sure least i get
stuck debugging some wild goose chase.
Thanks again.
Ian
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23nBoMZjtFHA.1472@.TK2MSFTNGP15.phx.gbl...
> That's true, but the OP just wanted to know about connections. He seemed
> pretty clear that the docs said the @.@.FETCH_STATUS was the last fetch on
the
> connection, so if you have two or more on the SAME CONNECTION, you can
have
> problems.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:29EA91F8-AA2B-476F-BFBB-938840F57BF1@.microsoft.com...
2
the
>
>sql

@@FETCH_STATUS Reset

Sorry if this is a dumb question, but I am new to SQL Server and trying a simple cursor. Works fine the first time I execute, but then @.@.Fetch_Status stays at -1 and it will not work again. If I disconnect and then connect it will work once again. What I am doing wrong?

declare @.entityid int, @.datafrom nvarchar (255),

@.datato nvarchar (255), @.updatedate datetime

declare statustime1 cursor

local scroll static

for

select audit.entityid, audit.datafrom, audit.datato, audit.updatedate

from audit

, orders

where audit.entityid = orders.orderid

and audit.entitytypeid = 4

order by orders.orderid, audit.updatedate desc

open statustime1

While @.@.fetch_status = 0

Begin

fetch next from statustime1

into @.entityid, @.datafrom, @.datato, @.updatedate

print @.entityid

end

close statustime1

deallocate statustime1

go

The main thing that you are doing 'wrong' is using a CURSOR.

SQL Server operates most efficiently handling SET based data. (NOT row-wise data). A major mistake for most developers is keeping the 'recordset' mindset.

CURSORS are considered by some to be 'evil incarnate', whereas others recognize that there is a very small and highly limited use for them.

Most likely, your operation can be handled in a single set based query, with orders of magnitude imporvements in speed and reduced blocking.

If you want to post what you are attempting to accomplish, we may be able to better direct you to a set base solution.

But the main thing to understand is that in your learning, avoid using CURSORs. Push yourself to master set based logic. You will be much happier with the results.

|||

Thanks very much. I need to calculate the number of business days between two status events. Each is recorded in a row in the table, eg,

Order Event1 Date

1 Open June 1

1 Hold June 10

1Close June 30

2 Open July 1

2 hold July 10

I plan to fetch a row, save the values, fetch another row and calculate the days, continuining unitl a change in order and write output accordingly.

If you can suggest a set based approach I am all for it, however can anyone tell me why the @.@.fetch_status is not being reset.

|||

There are a few 'tools' that make the DBA's job much simplier. One of those tools is have a Calendar table in the database.

With a Calendar table, it is simple to JOIN against other tables with date values and derive the span of time between such dates. You may explore using a Calendar table with this resource.

With a Calendar table, you can easily meet your requiremens with a single query.

You need a FETCH before the WHILE statement. And then normally, the FETCH inside the loop is the last line -not the first line.

|||

I actually have a time dimension table so that will be a help, but can you show how we can get the data to do the subtraction between the two dates using set based approach. The trick is bringing together two rows that follow each other in sequence for the same order. I can't figure how to JOIN to do that.

I agree on fetch first outside the loop and so on, but that does not help with the question. Why does @.@.fetch_status still have a -1 even after the close and deallocate? It only seems to reset when I disconnect and that can't be right.

Thanks,

|||

You can use the function to fetch this data..

Sample.,

Code Snippet

Create function dbo.TotalBusinessDays

(

@.Start datetime,

@.End Datetime

)

returns int

as

Begin

Declare @.Result as int;

Set @.Result = 0;

While @.Start<=@.End

Begin

Select @.Result = @.Result + Case When datepart(weekday,@.Start) in (1,7) Then 0 Else 1 End

Set @.Start = @.Start + 1

End

return @.Result;

End

Go

Code Snippet

Create Table #events (

[Order] int ,

[Event1] Varchar(100) ,

[Date] datetime

);

Insert Into #events Values('1','Open','June 1 2007');

Insert Into #events Values('1','Hold','June 10 2007');

Insert Into #events Values('1','Close','June 30 2007');

Insert Into #events Values('2','Open','July 1 2007');

Insert Into #events Values('2','hold','July 10 2007');

Select

[Order]

,dbo.TotalBusinessDays([Open],[Hold]) OpenToHold

,dbo.TotalBusinessDays([Hold],[Close]) HoldToClose

From

(

Select

[Order]

, Max(case when [Event1] = 'Open' Then [Date] end) as [Open]

, Max(case when [Event1] = 'Hold' Then [Date] end) as [Hold]

, Max(case when [Event1] = 'Close' Then [Date] end) as [Close]

from

#events

Group bY

[Order]

) as Data

|||

I am impressed, looks pretty good. I may have oversimplied the example however,so how would it work for this data?

Order Event1 Date

3 Open July 1

3 Hold July 10

3 open july 20

3 hold Aug 1

3 open Aug 3

3 fill Aug 20

And I still would like to know why @.@.fetch_status stays -1 after I close and even deallocate the cursor.

|||

@.@.FETCH_STATUS is similar to a static variable with connection scope.

It holds the last know value.

If you try this, in a new connection window, without a CURSOR:

SELECT @.@.FETCH_STATUS

It will return [ 0 ].

It will reset on the next execution of FETCH.

|||

Bingo! You just solved it for me. I was being dense, and I was missing the point that @.@.fetch_status resets on the execution of fetch. It is not reset on open cursor. So I really need the first fetch outside the while loop and problem solved! My confusion is I am too familiar with that other DB (evil DB2) where SQLCODE would be zero after the open (lol) ..

Very interesting discussion and if you can think of set process approach to handle the more general case of multiple status changes during the order I would love to hear it.

|||

Here another sample,

Code Snippet

Create Table #orders (

[Order] int ,

[Event1] Varchar(100) ,

[Date] datetime

);

Insert Into #orders Values('3','Open','July 1 2007');

Insert Into #orders Values('3','Hold','July 10 2007');

Insert Into #orders Values('3','open','july 20 2007');

Insert Into #orders Values('3','hold','Aug 1 2007');

Insert Into #orders Values('3','open','Aug 3 2007');

Insert Into #orders Values('3','fill','Aug 20 2007');

Select * into #temp from #orders order By 1,3

Alter table #temp Add RowId int Identity(1,1)

select

[Pre].[Order],

[Pre].[Event1] +' to ' + [Post].[Event1],

dbo.TotalBusinessDays([Pre].[Date],[Post].[Date])

from

#temp [Pre]

Join #temp [Post] on [Pre].RowId = [Post].RowId-1

|||Since everyone was so nice to tell you why cursors are bad and give alternatives, and no one has answered your question:

The problem with your code is you need to change it to:

open statustime1

fetch next from statustime1

While @.@.fetch_status = 0

Begin

print @.entityid

fetch next from statustime1

into @.entityid, @.datafrom, @.datato, @.updatedate

end

In your current code you never did a fetch, so @.@.fetch_status is uninitiallized.

|||

Actually Tom, I think that I did answer that question.

Arnie Rowland wrote:

You need a FETCH before the WHILE statement. And then normally, the FETCH inside the loop is the last line -not the first line.

And

Arnie Rowland wrote:

@.@.FETCH_STATUS is similar to a static variable with connection scope.

It holds the last know value.

...

It will reset on the next execution of FETCH.

|||I am sorry Arnie, I miss that one. :< You did answer the question.

@@FETCH_STATUS loops?

I've seen in several examples where cursors are used particularly on
this board that people use
While @.@.FETCH_STATUS <> -1
BEGIN
IF @.@.FETCH_STATUS <> -2 BEGIN
..
END
END
On most of the Microsoft BOL examples I see that they seem to always
use...
WHILE @.@.FETCH_STATUS = 0
BEGIN
..
END
I have looked up the different meanings
"0 FETCH statement was successful.
-1 FETCH statement failed or the row was beyond the result set.
-2 Row fetched is missing."
http://msdn.microsoft.com/library/d... />
s_1c1f.asp
To get to the point, my question is why use one method over the other?
Is there some drawback to useing the "@.@.FETCH_STATUS = 0" method that
might not be apparent?
Do I need to worry about endless loops?
As you might surmise I have a UDF that is running much to long, and
when I look at the number of times my objects have been called, the
number is larger than it should be.Why? Consistency. Almost every fetch loop I've run across looks like this:
FETCH
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- do something here
FETCH
END
When you or the poor slob who has to maintain your code looks at it six
months, one, or two years from now, it will be a lot easier to read if it
uses the above pattern.
<Contraptor@.gmail.com> wrote in message
news:1132634990.886050.150040@.g49g2000cwa.googlegroups.com...
> I've seen in several examples where cursors are used particularly on
> this board that people use
> While @.@.FETCH_STATUS <> -1
> BEGIN
> IF @.@.FETCH_STATUS <> -2 BEGIN
> ...
> END
> END
> On most of the Microsoft BOL examples I see that they seem to always
> use...
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> ...
> END
> I have looked up the different meanings
> "0 FETCH statement was successful.
> -1 FETCH statement failed or the row was beyond the result set.
> -2 Row fetched is missing."
> http://msdn.microsoft.com/library/d...>
als_1c1f.asp
> To get to the point, my question is why use one method over the other?
> Is there some drawback to useing the "@.@.FETCH_STATUS = 0" method that
> might not be apparent?
> Do I need to worry about endless loops?
> As you might surmise I have a UDF that is running much to long, and
> when I look at the number of times my objects have been called, the
> number is larger than it should be.
>|||Ok, so al long as I'm not doing anything to change the @.@.FETCH_STATUS
between when I FETCH and check for a valid @.@.FETCH_STATUS I have no
need to worry about infinite loops if using the
FETCH
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- do something here
FETCH
END
method.
Now I just need to figure out why my UDF totally iced our test server
last night.

@@fetch_status in trigger

in the trigger for update and insert i have the following cursor

declare crs cursor static local for
SELECT [id] FROM inserted

open crs
fetch next from crs into @.v1

print @.@.fetch_status

while @.@.fetch_status = 0
begin
print @.v1

fetch next from crs into @.v1
end

close crs
deallocate crs

the problem is that the @.@.fetch_status is now always -1 and I'm pretty sure that it worked some time ago and i can't figure what is changed.

any ideas ?
thnx.Why would you want to do this? What's the real trigger look like?

Can you post that code?

A cursor in a trigger would more than likely perform poorly...|||I use cursor in trigger cause i can have inserts/updates from multiple sources and i don't want to break the logic.

code in attach|||fixed.

cursor threshold was 0; changed to -1

@@FETCH_STATUS

Hi there..
I have this problem: I run my stored proc alot of times for testing, but one
time it want work.
It just give me back this message: The command(s) completed successfully.
I checked the @.@.FETCH_STATUS and it became -1, why is that when it worked
before..
Can any body help me'
THX@.@.FETCH_STATUS is for cursors, you want to use @.@.ERROR instead if it's 0
then there is no error
http://sqlservercode.blogspot.com/
"Hussain Al-Mousawi" <hal_mousawi@.hotmail.com> wrote in message
news:uv6dVVTRGHA.2532@.TK2MSFTNGP10.phx.gbl...
> Hi there..
> I have this problem: I run my stored proc alot of times for testing, but
> one time it want work.
> It just give me back this message: The command(s) completed successfully.
> I checked the @.@.FETCH_STATUS and it became -1, why is that when it worked
> before..
> Can any body help me'
> THX
>

@@FETCH_STATUS

hi.
i have a question about @.@.FETCH_STATUS when doing a cursor loop.
i found 2 examples and i want to know what the diffrence is between
them,
the first one i found in the help:
DECLARE Employee_Cursor CURSOR FOR
SELECT LastName, FirstName FROM Northwind.dbo.Employees
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor
here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
the next exaple was an example in the QA:
DECLARE <cursor_name, sysname, test_cursor> CURSOR
READ_ONLY
FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
DECLARE @.name varchar(40)
OPEN <cursor_name, sysname, test_cursor>
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
DECLARE @.message varchar(100)
SELECT @.message = 'my name is: ' + @.name
PRINT @.message
END
FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
END
CLOSE <cursor_name, sysname, test_cursor>
DEALLOCATE <cursor_name, sysname, test_cursor>
here it first says, while @.@.FETCH_STATUS <> -2 and then says
only if @.@.FETCH_STATUS <> -1
in my world this would result in the same thing, since according to the
help the 3 diffrent statuses is 0, -1 and -2.
what is the diffrence between the two? and wich one is the "right" one
to use?
/Anders0
The FETCH statement was successful.
-1
The FETCH statement failed or the row was beyond the result set.
-2
The row fetched is missing.
-oj
<aebohlin@.gmail.com> wrote in message
news:1141228790.445782.79910@.v46g2000cwv.googlegroups.com...
> hi.
> i have a question about @.@.FETCH_STATUS when doing a cursor loop.
> i found 2 examples and i want to know what the diffrence is between
> them,
> the first one i found in the help:
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
> here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
> the next exaple was an example in the QA:
> DECLARE <cursor_name, sysname, test_cursor> CURSOR
> READ_ONLY
> FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
> DECLARE @.name varchar(40)
> OPEN <cursor_name, sysname, test_cursor>
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> -- PRINT 'add user defined code here'
> -- eg.
> DECLARE @.message varchar(100)
> SELECT @.message = 'my name is: ' + @.name
> PRINT @.message
> END
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> END
> CLOSE <cursor_name, sysname, test_cursor>
> DEALLOCATE <cursor_name, sysname, test_cursor>
> here it first says, while @.@.FETCH_STATUS <> -2 and then says
> only if @.@.FETCH_STATUS <> -1
> in my world this would result in the same thing, since according to the
> help the 3 diffrent statuses is 0, -1 and -2.
> what is the diffrence between the two? and wich one is the "right" one
> to use?
> /Anders
>|||You don't need <>-2 checking for READONLY CURSOR. If the cursor is Ket-Set
driven cursor, you may read non-existing rows. Then you need to cehck for <>
-2
Refer "Cursor Types" in BOL
--
Thanks & Rate the Postings.
-Ravi-
"aebohlin@.gmail.com" wrote:

> hi.
> i have a question about @.@.FETCH_STATUS when doing a cursor loop.
> i found 2 examples and i want to know what the diffrence is between
> them,
> the first one i found in the help:
> DECLARE Employee_Cursor CURSOR FOR
> SELECT LastName, FirstName FROM Northwind.dbo.Employees
> OPEN Employee_Cursor
> FETCH NEXT FROM Employee_Cursor
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM Employee_Cursor
> END
> CLOSE Employee_Cursor
> DEALLOCATE Employee_Cursor
> here it uses @.@.FETCH_STATUS = 0, wich would be while it is successfull.
> the next exaple was an example in the QA:
> DECLARE <cursor_name, sysname, test_cursor> CURSOR
> READ_ONLY
> FOR <select_statement, , SELECT au_fname FROM pubs.dbo.authors>
> DECLARE @.name varchar(40)
> OPEN <cursor_name, sysname, test_cursor>
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> -- PRINT 'add user defined code here'
> -- eg.
> DECLARE @.message varchar(100)
> SELECT @.message = 'my name is: ' + @.name
> PRINT @.message
> END
> FETCH NEXT FROM <cursor_name, sysname, test_cursor> INTO @.name
> END
> CLOSE <cursor_name, sysname, test_cursor>
> DEALLOCATE <cursor_name, sysname, test_cursor>
> here it first says, while @.@.FETCH_STATUS <> -2 and then says
> only if @.@.FETCH_STATUS <> -1
> in my world this would result in the same thing, since according to the
> help the 3 diffrent statuses is 0, -1 and -2.
> what is the diffrence between the two? and wich one is the "right" one
> to use?
> /Anders
>|||thanks alot.sql