This is driving me crazy and makes no sense and wanted to see if anyone else has come across this and can help me - or if i can just share the pain.
i have a stored procedure that takes a table name (it exists in 2 databases on the same SQL server) and will
- delete all the data in the local table
- set the IDENTITY_INSERT to OFF for the local table
- copy all the data from the table in the other database to the local table
easy stuff. this works for pretty much every table until i get to a couple. then i get :
An explicit value for the identity column in table 'table name' can only be specified when a column list is used and IDENTITY_INSERT is ON.
no idea why it happens for these few tables. there is nothing different about them that i can see (but i may not be looking in the right places)
SELECT @sql = 'DELETE ' + @tablename
SELECT @sql = 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
SELECT @sql = 'INSERT INTO ' + @tablename + ' SELECT * FROM ' + @DB + '..' + @tablename
@tablename is the tablename, @DB is the other database, the stored procedure is run from the local database.
any pointers/advice/ideas even would be appreciated.