asked on

pass a variable to SET IDENTITY_INSERT ON or OF

In my script i have a variable that stores the table name.
For example

DECLARE @table_name varchar(50)
SET table_name ='tblClients'

Now i'm able to pass this variable to many statements like this:
EXECUTE ('select * from' + @table_name + '.... bla bla bla')

but i not able to pass some parameters to the SET IDENTITY_INSERT. The following statements don't work:
EXECUTE ('SET IDENTITY_INSERT [' + @nome_tabella + '] ON')
PRINT ('SET IDENTITY_INSERT [' + @nome_tabella + '] ON')
SELECT ('SET IDENTITY_INSERT [' + @nome_tabella + '] ON')

Do you have some hints? Thanks in advance

Ciao

Dario

JimV_ATL

You need to include Use [Database Name] in your string.

For example:

EXECUTE ('Use ' + @dbname + ' SET IDENTITY_INSERT [' + @nome_tabella + '] ON')
PRINT ('Use ' + @dbname + ' SET IDENTITY_INSERT [' + @nome_tabella + '] ON')

Scott Pletcher

What do you mean by "don't work"? Remember that you can have only one table per session that has this on. So, if you've already got a table in your session with insert identity on, you won't be able to set it on for a second table until you're set it back off for the first table.

gurmit

If using dymanic sql to perform an action, you should have the proper database permissions. for SET IDENTITY you need to have ddladmin or sysadmin permissions.

ASKER CERTIFIED SOLUTION

hakyemez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bdario

ASKER

Thanks to everyone for your suggestions, now i solved the problem!

Dario