Usage of @@Identity spanning two databases

Posted on 2011-10-04
Medium Priority
Last Modified: 2012-05-12
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.

Question by:ChilliSauce
LVL 15

Accepted Solution

Eyal earned 189 total points
ID: 36908784
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

Author Comment

ID: 36908848
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).


Expert Comment

ID: 36908860
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.

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Expert Comment

ID: 36908875
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.
LVL 26

Assisted Solution

tigin44 earned 186 total points
ID: 36908878

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 (........)



Author Comment

ID: 36909026
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.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 36914144
It would be so much easier if you posted your code.  We could then quit guessing and give you the solution.

Author Closing Comment

ID: 36930368
Thanks everyone. The Scope_Identity() helped.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question