@@Identity 1

[GShen]

Hi,
I have a stored procedure with 2 CURSORS in it. They are back to back. The 1st one does an insert into a table. It then updates another table using the Identity which just inserted. This works fine.

The second CURSOR does not allow me to obtain the @@IDENITY.
for the 2nd update. This cursor is inserting into a table on another server. I use an XACT_ABORT ON to allow this.
The table inserts fine but the next update (to a the same table as I am updating above using another field), does not have the IDENTITY in it. I tried printing it in Query Analzyer and it doesn't display anything which matches what is happening. In QA is does indeed show the records being updated. It has the proper record count, but like I said, since the @@Identity is blank or null, it updates my field to nothing.

Ideas anyone?

Thanks,

Remember when... everything worked and there was a reason for it?

 

[checkai]

why don't you print out your sp...statements like this make it hard to understand...

I tried printing it in Query Analzyer and it doesn't display anything which matches what is happening. In QA is does indeed show the records being updated

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[GShen]

Hi,
I was trying to keep it simple.
Like I said, it works with one cursor but not the other. Same exact format but it is inserting the data on a table from anther server. It is the only real differnence. But if you think it will help, here it is.

DECLARE CreditMemoCursor INSENSITIVE CURSOR FOR

SELECT tblrd_Customer
FROM tblReclamationDetail
INNER JOIN tblCustomerFile ON
PC_CUST_NUM = tblrd_Customer
WHERE tblrd_Value > 0 AND
tblrd_Authorized = 1
GROUP BY
tblrd_Customer,
tblrd_PeriodYear,
tblrd_InvoiceDate

SET @WorkError = @WorkError + @@ERROR

OPEN CreditMemoCursor

FETCH NEXT FROM CreditMemoCursor INTO @Customer

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO sqlserver1_Production.Production.dbo.tblCreditMemosgary
(CMCUST#,
Amount,
Type,
Note,
Authorized,
DateAuthorized,
Print_)
SELECT tblrd_Customer,
ROUND(SUM((tblrd_Value + tblrd_CreditChuteFee) * tblrd_Quantity) * .5,2),
'54',
'Reclamation for Period Ending ' + CONVERT(char(8),tblrd_InvoiceDate,1),
1,
CONVERT(char(8),GETDATE(),1),
0
FROM tblReclamationDetail
INNER JOIN tblCustomerFile ON
PC_CUST_NUM = tblrd_Customer
WHERE tblrd_Value > 0 AND
tblrd_Authorized = 1 AND
tblrd_Customer = @Customer
GROUP BY
tblrd_Customer,
tblrd_PeriodYear,
tblrd_InvoiceDate

SET @WorkError = @WorkError + @@ERROR

UPDATE tblReclamationDetail
SET tblrd_CreditMemoID = @@Identity
WHERE tblrd_Customer = @Customer AND
tblrd_Value > 0 AND
tblrd_Authorized = 1

SET @WorkError = @WorkError + @@ERROR

FETCH NEXT FROM CreditMemoCursor INTO @Customer

END

CLOSE CreditMemoCursor
DEALLOCATE CreditMemoCursor



Remember when... everything worked and there was a reason for it?

 

[Rauken]

You can't use @@identity on an update, it's just for insert or select into. And I think if I'm not wrong you have to put the @@identity right after the insert.

 

[Rauken]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp

From MS:
The scope of the @@IDENTITY function is the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure, which is executing in the context of the remote or linked server, gather the identity value and return it to the calling connection on the local server.

 

[GShen]

Rauken,
Thanks. I was just trying to save some work. I will create a SP on the other server and just pass it back to perform my update. By the way I thought the @@Identity had to be the 1st thing also but it does not. I guess it stays there till the next insert. The update in my procedure DOES indeed work properly. I was going to save the @@Identity 1st and then use the saved field for the insert but like I said, it works fine. If you think that I should change it for some reason and save it to a field right after the insert, please let me know.
THANKS FOR THE POST!!

Remember when... everything worked and there was a reason for it?

 

[LFCfan]

GShen,

Just for reference: you'd be safer using scope_identity rather than @@Identity. If the table you have just inserted into has a trigger, which inserts into another table, @@identity will return the the identity from the other table. scope_identity would return the identity from the first table, which is the one you want.
This is not in reference to the remote/linked servers, just generally!

HTH

 

[GShen]

thanks,
As in the past, I used my old technique for getting the identifier. I do a select max(id) from the file. It may be a little inefficient but I do not need to create a seperate stored procedure to perform the insert and get into looking at 2 sp's if I have to change something.

Again, thanks. I never used scope_identity. I will keep that in mind. We do not perform Triggers so it would not affect me....... but you never know. Better to do it just in case.
Have a great weekend.



Remember when... everything worked and there was a reason for it?