title: SQL Server: getting SCOPE_IDENTITY() not working with linked server
body: <p>I have a stored procedure in which I do an insert and retrieve the ID of the row by calling <code>SCOPE_IDENTITY()</code> </p>
  
  <pre><code>...
  INSERT INTO [RemoteDB].[dbo].[Table] (StageID, UserID, Date) 
  VALUES (4, @userID, @date)
  
  SET @id = SCOPE_IDENTITY()
  
  INSERT INTO [RemoteDB].[dbo].[Table2] (ID) 
  VALUES (@id)
  ...
  </code></pre>
  
  <p>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). </p>
  
  <p>The first insert of my query works, but the second doesn't run. I assume this is because of the <code>SCOPE_IDENTITY()</code>. </p>
  
  <p>My error is: </p>
  
  <blockquote>
    <p>The OLE DB provider could not INSERT INTO... "The value violated the integrity constraints for the column.".</p>
  </blockquote>
  
  <p>This means that it's not able to get the <code>@id</code> of the <code>SCOPE_IDENTITY()</code> </p>
  
  <p>Note that the stored procedure that I execute are not on the remote server, they are on the local one. </p>
  
  <p>What did I do wrong?</p>
  
  <p><a href="https://i.stack.imgur.com/1jl1F.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1jl1F.png" alt="enter image description here"></a></p>