How to set Identity_Insert in dynamic sql?

Hi there,

I need to temporary set identity_insert off on a table in dynamic sql. Here is a sample from my stored procedure:

Create t_Table Table4test
(
ColID Int Not Null Identity(1,1)
, ColContent varchar(30) Not Null
)

Insert t_Table4test Values ( 'Test1' )
Insert t_Table4test Values ( 'Test2' )

Insert t_Table4test Values ( 'Test3' )

Declare
@.cCmd nvarchar(3000)
, @.cTableName nvarchar(15)

-- So far all is fine...
Select @.cTableName = 't_Table4test'
/*
Now I need to insert a new record, BUT this time WITH ColID
so I have to set Identity_Insert to On
*/
Select @.cCmd = 'Set Identity_Insert GateKeeper.dbo.' + @.cTableName + ' On'

Execute( @.cCmd )

-- Now the insert part
Select @.cCmd = 'Insert t_Table4test Values ( 4, ' + Char(39) + 'Test3' + Char(39) + ')'

Execute( @.cCmd )

When it is executed I am getting following error:
Cannot insert explicit value for identity column in table 't_Table4test' when IDENTITY_INSERT is set to OFF.

When I print @.cCmd they are printed in proper order, meaning Set first and then Insert after it, but when it use Exec instead it always checks for Identity status first and fails!

HEEEELP!!!

...and thanks in advance.

Every time you call EXECUTE it runs the command as a separate batch with its own context, so you need to put everything in the @.cCmd and EXECUTE just once. You can simply concatenate the INSERT after the identity_insert command, something like this

Select @.cCmd = 'Set Identity_Insert GateKeeper.dbo.' + @.cTableName + ' On '

Select @.cCmd = @.cCmd + 'Insert t_Table4test (col1, col2) Values ( 4, ' + Char(39) + 'Test3' + Char(39) + ')'

Execute( @.cCmd )

Note that you must specify the column names in the insert statement when you use identity_insert, and don't forget the space between the two commands (I added a space after the word On in the first statement.

|||

Can you explain why you think you need to use dynamic SQL in this way. In your example the table name is hardcoded in the dynamic sql.

If the table name is being passed in you need to be very careful because you can be exposed to a sql injection vulnerability.

You may be interested to read this article http://www.sommarskog.se/dynamic_sql.html

|||I already tried it and it does NOT work. To make sure all is correct instead of Execute I used Print, executed results and worked. However, when ran in sproc I got a same error message.|||Yes I can, the reason behind it is that this sproc is executed for different tables and table name is passed as one of parameters. The table name in my example is hardcoded just to make it simpler.

This db and the application runs on the intranet, for small number of users, so there is no real SQL injection danger.|||You need to do it all as one EXECUTE command. Every EXECUTE is its own batch, so the setting gets reset as soon as the batch ends.|||

Can you post you stored procedure so we can try and find the problem.

|||

This shows the code works, you should use the sp_executesql as much as possible

if object_id('tableForInsert') is not null

drop table tableForInsert

go

create table tableForInsert (col1 int identity(1,1),col2 int)

go

declare @.sql nvarchar(1000)

set @.sql = 'set identity_insert tableForInsert on

insert into tableForInsert (col1, col2) values (@.col1, @.col2)'

exec sp_executesql @.sql, N'@.col1 int,@.col2 int',10,10

set @.sql = 'set identity_insert tableForInsert on

insert into tableForInsert (col1, col2) values (11, 12)'

execute( @.sql)

|||If you are in SQL2005 you can use a semicolon to demark line ends. Thus you would have something like the following.

USE tempdb
GO
IF OBJECT_ID('tempdb..t_Table4Test') IS NOT NULL
DROP TABLE t_Table4Test

CREATE TABLE t_Table4test
(
ColID int NOT NULL Identity(1,1)
, ColContent varchar(30) NOT NULL
)

INSERT t_Table4test VALUES ( 'Test1' )
INSERT t_Table4test VALUES ( 'Test2' )
INSERT t_Table4test VALUES ( 'Test3' )

DECLARE
@.cCmd nvarchar(3000)
, @.cTableName nvarchar(15)

-- So far all is fine...
SELECT @.cTableName = 't_Table4test'

SELECT * FROM t_Table4Test

SELECT @.cCmd = 'SET IDENTITY_INSERT tempdb.dbo.' + @.cTableName + ' On;'

-- Now the insert part
SELECT @.cCmd = @.cCmd + ' INSERT t_Table4test(ColID,ColContent) VALUES ( 4, ' + CHAR(39) + 'Test3' + CHAR(39) + ');'
SELECT @.cCmd = @.cCmd + ' SET IDENTITY_INSERT tempdb.dbo.' + @.cTableName + ' Off;'

EXEC sp_Executesql @.cCmd
SELECT * FROM t_Table4Test
Select @.cCmd = 'Set Identity_Insert GateKeeper.dbo.' + @.cTableName + ' On;'

Select @.cCmd = @.cCmd + 'Insert t_Table4test Values ( 4, ' + Char(39) + 'Test3' + Char(39) + ');'
Select @.cCmd = @.cCmd + 'Set Identity_Insert GateKeeper.dbo.' + @.cTableName + ' Off;'

EXEC sp_executesql @.Cmd