Usage of @@Identity spanning two databases
Posted on 2011-10-04
Am developing an application where the data is saved/selected in/from two different databases say:
db-B has a procedure that uses tables from db-A like so:
SELECT * FROM db-A.dbo.table1
and so on
Its working find for SELECTION sprocs but now I have a requirement to add through a SPROC which returns/selects an @@Identity at the end. That is, when the insertion is made I want the ID of the record inserted to use in the rest of the code.
The insertion procedure is part of db-B, say SPROC-db-B but it is using the tables from db-A for insertion purposes. This is handled as stated above (db-A.dbo.table1 etc) but when it comes to the @@Identity part (at the end of SPROC-db-B), it gets the last record (I presume) of the db where the SPROC is contained, i.e. db-B.
I simply need the @@Identity to return the last record inserted in the db-A.
Is that possible using "Identity"? Or any other method?
Let me know if further info is required.