Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PADRAIGBEIRNE
Starting Member
1 Post |
 Posted - 2012-04-04 : 06:16:42
|
| To Use SET IDENTITY_INSERT ON/OFF you must follow it with GOI'm trying to run something like it within a Proc that can be imagined as follows:DECLARE @TableName NVARCHAR(50), @SQL_ID NVARCHAR(100)SET @TableName = 'ATable'SET @SQL_ID='SET IDENTITY_INSERT '+@TableName+' ON' EXEC sp_executeSQL @SQL_ID But MSSQL2000 just ignores it presumably because GO is not accommodated?Is there any way around this e.g. an EXEC master..xp_...?I want to use in Audit Trail Table/Proc with UNDO/REDO |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-04-04 : 06:58:00
|
There isn't a constraint to use GO with identity_insert.Maybe you have forgotten to set identity_insert to off for a formerly processed table?identity_insert can only be set to on for one table at a time in the database.Or maybe it happens because sp_executeSQL is working in one session and your insert statement is executed in another session?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
|
|
|
SET IDENTITY_INSERT