''SET IDENTITY_INSERT '' + @TablesName + '' ON'' dosn''n work

Question

''SET IDENTITY_INSERT '' + @TablesName + '' ON'' dosn''n work

fasttrack

SSCertifiable

Points: 6071
More actions
July 1, 2005 at 8:35 pm

#164773

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 1

The proplem is one of scope. You are opening a session setting the 'SET IDENTITY_INSERT" on/off then closing the session returning the identity_insert property back to off the default value. Try something like:

SET IDENTITY_INSERT ON

do your inserts here then

SET IDENTITY_INSERT OFF

HTH Mike

fasttrack SSCertifiable Points: 6071 More actions · Answer 2

Just to explain better...

the statement

SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' ON'

is put before the ISERT statement and after

I' llwrite

SET @SqlCheckIdentity = 'SET IDENTITY_INSERT ' + @TablesName + ' OFF'

I need to use @TablesName because this parameter ie retrieved form a catalog table containing the name of table on which I have to build the INSERT statement.

Thank...

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

As Michael pointed it is outf scope. Your dynamic statements will have a seperate scope that the connection you are in.

if you want to do it Dynamic do it thsi way.

DECLARE @sql VARCHAR(8000)

SET @sql = 'SET IDENTITY_INSERT ' + @TablesName + ' ON '

SET @sql = @sql + 'INSERT INTO ' + @TablesName + '(col1, col2, col3) VALUES ( ''VAL1'', ''VAL2'', val3) '

SET @sql = @sql + 'SET IDENTITY_INSERT ' + @TablesName + ' OFF '

EXEC (@SQL)

Regards,
gova

fasttrack SSCertifiable Points: 6071 More actions · Answer 4

Yes,

thank a lot

I thought the same thing and is working