CODEWITHSUNDEEP
sqlsql-server-2012ssms
Pepys
Pepys

Reputation: 991

SELECT SCOPE_IDENTITY performs differently

Another problem here with SCOPE_IDENTITY() returning NULL

The situation is the following: I have a virtual machine running Windows Server 2012R2 with SQL Server 2012. On my own machine I am using same SQL Server and Windows 8.1

Executing the following query in Management Studio: 

INSERT INTO [DB1].[Extract].[Log]([Package], [StartTime])
VALUES ('Bla', GETDATE())

SELECT SCOPE_IDENTITY() AS 'Identity';

returns the identity of the row inserted on my PC BUT in the virtual machine returns NULL (still inserts the row), therefore an SSIS package cannot execute.

What the hell is going on? Could it be some additional permissions to the database?

I cannot miss to say "It works on my machine!" :)

Upvotes: 0

Views: 148

Answers (2)

Joseph Fallon
Joseph Fallon

Reputation: 26

Did you do a restore of the database between servers or just copy the data in to a new table on one server via a query or SSIS package ?

Upvotes: 0

Matt Whitfield
Matt Whitfield

Reputation: 6574

SCOPE_IDENTITY() will return null if the table being inserted into doesn't have an IDENTITY column - so it is worth double checking your schema on the machine where it is not working to ensure that there is, in fact, an IDENTITY column.

Upvotes: 2

Related Questions