'SET IDENTITY_INSERT ' + @T + ' ON' doesn't work

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

'SET IDENTITY_INSERT ' + @T + ' ON' doesn't work

Author

Topic

erncelen
Starting Member

15 Posts

Posted - 2005-07-01 : 22:23:30

Hi,

I have a big problem:
If I try to execute SET IDENTITY_INSERT as below,

DECLARE @SqlCheckIdentity varchar (255)
SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON'
EXEC (@SqlCheckIdentity)

Or

DECLARE @SqlCheckIdentity varchar (255)
SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' OFF'
EXEC (@SqlCheckIdentity)

the command is not executed....in store procedure (in query analyzer say when execute the insert statement: Cannot insert explicit value for identity column in table '@TablesName' when IDENTITY_INSERT is set to OFF).

I mean in this way I cannot set the identity ON/OFF

Do you know how to execute SET INSERT.... with parameters?

Thank a lot

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-07-01 : 23:03:15

I was able to get it to work when I did this:

SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON INSERT INTO ' + @TablesName + '(col1, col2) VALUES(100, ''abc'')'
EXEC (@SqlCheckIdentity)

In other words, I put the INSERT statement in the same string as the SET IDENTITY_INSERT statement. There's probably a better way to do this, but I don't know it offhand.

andy8979
Starting Member

36 Posts

Posted - 2005-07-02 : 03:46:05

This problem occurs when the client-cached cursor query isn't invalidated and the query uses the prior setting of SET IDENTITY_INSERT OFF when it tries to insert the row.
Check the following article it has some details for the problem.

http://www.windowsitpro.com/Article/ArticleID/44901/44901.html

Anuj.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-07-04 : 07:07:23

It's to do with the SCOPE of the statement.
An EXEC statement needs everything in it to be IN SCOPE.

Executing the SET IDENTITY, etc outside the EXEC (@SQL) statement is in a different SCOPE from code written inside the EXEC (@SQL).

Read up on same in BOL...or search here for more detailed explainations.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources