Usage of @@Identity spanning two databases

Hi all,

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 problem:
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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

and if this doesn't work create also a stored in the other database that will do the insert and return the SCOPE_IDENTITY() back

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChilliSauceAuthor Commented:
If you mean something like:
db-A.dbo.SCOPE_IDENTITY() then its not working, I have already tried that.

We also don't have the option to use the db-A to create a new sproc that returns the Identity. The purpose here is to use the db-B's procedure to insert into db-A's tables (success) and return the @@Identity of the record inserted in db-A (no success).

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch
Return Types

IDENT_CURRENT( 'table_name' )
Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

As sachinpatil10d mentioned you should use SCOPE_IDENTITY() instead of  @@IDENTITY cause there may be other transaction doing insertions...

And simple usage of the SCOPE_IDENTITY()

DECLARE @id   int

INSERT INTO db_a.dbo.tablex (.....)
VALUES (........)


ChilliSauceAuthor Commented:
What you saying is that if I use SCOPE_IDENTITY() in my case i.e.
in a sproc of dbo-B, I use dbo-A tables for insertaion, will this function return the insertion row number for dbo-A.table1?

If that is the case then I will try this and let you know.

Anthony PerkinsCommented:
It would be so much easier if you posted your code.  We could then quit guessing and give you the solution.
ChilliSauceAuthor Commented:
Thanks everyone. The Scope_Identity() helped.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.