October 9, 2002 at 6:49 am
Hi all,
I am using MS SQL Server 2000 as backend and VB6 as front end. In my database I have to use identity field in dynamic sql for some reasons. When I used scope_identity() for getting the last number generated from the same scope, it returned null. Please help me, it is urgent and I am working on a multi user project. If it is not possible, please tell me the remedy for this
Thanks in advance
What I did is:
Table1
A int identity(1,1)
B varchar(30)
declare @sql nvarchar(100)
set @sql = 'insert into table1 (b) values(''test'')'
exec sp_executesql @sql
select scope_identity()
October 9, 2002 at 10:04 am
Remember that when a dynamic SQL statement is executed, it is executed in a separate batch from the calling statement. This means it's also a different scope. Therefore SCOPE_IDENTITY() in the calling batch won't work. You can do something akin to:
declare @sql nvarchar(100)
set @sql = 'insert into table1 (b) values(''test''); SELECT SCOPE_IDENTITY()'
exec sp_executesql @sql
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Edited by - bkelley on 10/09/2002 10:04:51 AM
K. Brian Kelley
@kbriankelley
October 9, 2002 at 10:55 am
Dear bkelley
Thank you for help!..
Mr. chrhedga also helped me, now i got two different method to solve the problem. That is using @@identity
Any way Thanks a lot
with lov
Kiran
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply