0

I have a stored procedure in which I do an insert and retrieve the ID of the row by calling SCOPE_IDENTITY() 

...
INSERT INTO [RemoteDB].[dbo].[Table] (StageID, UserID, Date) 
VALUES (4, @userID, @date)

SET @id = SCOPE_IDENTITY()

INSERT INTO [RemoteDB].[dbo].[Table2] (ID) 
VALUES (@id)
...

Everything works fine when I my 3 databases on the same server, but in production, the one of the databases is remote (so I use a linked server).

The first insert of my query works, but the second doesn't run. I assume this is because of the SCOPE_IDENTITY().

My error is:

The OLE DB provider could not INSERT INTO... "The value violated the integrity constraints for the column.".

This means that it's not able to get the @id of the SCOPE_IDENTITY()

Note that the stored procedure that I execute are not on the remote server, they are on the local one.

What did I do wrong?

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3127554
  • 521
  • 1
  • 7
  • 28
  • I know, because it's not retrieving the ID, but it retrieves it when I have all db's on the same server. – user3127554 Dec 27 '17 at 13:58
  • `INSERT INTO [RemoteDB].[dbo].[Table] (StageID,UserID,Date) OUTPUT Inserted.ID VALUES (4,@userID, @date)` can this be of any help? – Arijit Mukherjee Dec 27 '17 at 14:00
  • My first insert works, it inserted a row. but it won't get the ID of the row (when i use a remote srv) – user3127554 Dec 27 '17 at 14:02
  • This explains to use scope_identity in remote server https://stackoverflow.com/questions/5708996/best-way-to-get-identity-of-inserted-row-in-linked-server – Ven Dec 27 '17 at 14:03
  • The error merely says youa re trying to insert null in a not null column. You can accomplish it by using sp_executeslq, see this answer https://stackoverflow.com/questions/5708996/best-way-to-get-identity-of-inserted-row-in-linked-server – jean Dec 27 '17 at 14:03

2 Answers2

2

SCOPE_IDENTITY (and similar) aren't going to work the way you think with linked servers. From the MSDN docs:

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, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Since your inserts are happening on a your remote server and your SCOPE_IDENTITY call is happening locally, they are executing within different scopes. Thus your SCOPE_IDENTITY call is probably returning NULL, hence the constraint violation.

You have a few options to fix this. The first would be to just write a stored proc on the remote server that you can execute that would either a) handle all the inserts in one proc or b) handle the single insert and return you the inserted identity. (Alternatively, you can use a dynamic-sql-esque solution to execute a statement on the remote server that does the same as the proposed stored procs.) The ultimate goal of whatever you choose to do is that you must make sure the SCOPE_IDENTITY call happens on the remote server in the same batch, otherwise it won't work.

Becuzz
  • 6,846
  • 26
  • 39
0

Please see attached image below. In my case, This is what I do.

On remote Server:

ALTER PROC [dbo].[SaveCustomerDetails]
(
  @customerId    int out,
  @customerName  varchar(100),
  @contactNo     varchar(100),
  @custType      varchar(1),
  @idNo          varchar(10)
)

On my local server:

DECLARE @customerId INT

EXEC RCMS.[dbo].[SaveCustomerDetails] @customerId out, 'TEST ACCOUNT', '', 'O', '1511'

SELECT @customerId as id

Specifically, note the 'out' keyword in

@customerId out
SWPhantom
  • 674
  • 5
  • 18
Gerry An
  • 1