December 12, 2005 at 3:21 pm
The following insert statement run on server1 returns null for the identity field LOGID.
INSERT INTO [server2].[mydb].dbo.ImportLog([User],FileType,[FileName],ImportedDateTime,SettlementDate,Status)
VALUES
('Automated','excel','test',GetDate(),NULL,'Importing...')
select SCOPE_IDENTITY(), IDENT_CURRENT('ImportedProcessorDataFiles'), @@IDENTITY returns all nulls, but I can get the value if I select max(logid)
Is there a way to do this without using max of the identity field?
December 12, 2005 at 6:47 pm
You haven't provided a lot of info about your table and your actual queries (and the table names in your two queries don't match), but if I read your situation properly you are trying to run the "select" also on server1.
Since you are "crossing servers", you are triggering a "hidden session" on server2. The part of your insert that directly affects table ImportLog (or ImportedProcessorDataFiles?) is not really running in your local session or batch on server1, so SCOPE_IDENTITY() and @@IDENTITY can't return any meaningful value.
Try it this way to see the difference when you force everything into one explicit batch:
-----------------
exec [server2].master.dbo.sp_sqlexec
"INSERT INTO [mydb].dbo.ImportLog([User],FileType,[FileName],ImportedDateTime,SettlementDate,Status)
VALUES ('Automated','excel','test',GetDate(),NULL,'Importing...')
select SCOPE_IDENTITY(), IDENT_CURRENT('[mydb].dbo.ImportLog'), @@IDENTITY"
-----------------
In that query I have guessed that ImportLog is the table name containing the logid identity col.
If you don't mind using IDENT_CURRENT() instead of @@IDENTITY, then you can also do:
-----------------
INSERT INTO [mydb].dbo.ImportLog([User],FileType,[FileName],ImportedDateTime,SettlementDate,Status)
VALUES ('Automated','excel','test',GetDate(),NULL,'Importing...')
exec [server2].[master].dbo.sp_sqlexec "select IDENT_CURRENT('[mydb].dbo.ImportLog')"
-----------------
Since IDENT_CURRENT() is not dependent on the particular session or batch/sp, etc, then its value is always accessible, once you reach the right object. 🙂
December 13, 2005 at 9:51 am
Here is a more accurate example:
On SERVER1 I have database DATABASE1 where there is a table IMPORTLOG with an identity field LOGID.
On SERVER2 DATABASE2 I have a view V_DATABASE1_IMPORTLOG defined as SELECT LOGID,OTHERFIELDS FROM [SERVER1].[DATABASE1].dbo.IMPORTLOG.
On SERVER2, DATABASE2 I have code that inserts into the view and tries to retrieve the identity value inserted:
DECLARE @LOGID int
INSERT V_DATABASE1_IMPORTLOG (OTHERFIELDS) VALUES ('OTHERDATA')
SELECT @LOGID = @@IDENTITY
Since this doesn't work, I was hoping for a way to do this without having a hardcoded reference to SERVER1 and DATABASE1 in the proc, I wanted to limit the references to the views only.
Thanks for your response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply