Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

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
0
bdario
Asked:
bdario
1 Solution
 
JimV_ATLCommented:
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')
0
 
Scott PletcherSenior DBACommented:
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.
0
 
gurmitCommented:
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.
0
 
hakyemezCommented:
;;;;;;;;;;;; SQL SYNTAX ROW DELIMETER ;;;;;;;;;;;;;;;
DECLARE @stmt1 VARCHAR(4000),@stmt2 VARCHAR(4000),@stmt3 VARCHAR(4000)

SET @stmt1='SET IDENTITY_INSERT ['+@tableName+'] ON'
SET @stmt2='INSERT INTO '+@tableName+' VALUES(....)'
SET @stmt3='SELECT * FROM blablabla2'

SET @stmt1 = @stmt1+';'+@stmt2+';'+@stmt3
EXECUTE @stmt1
0
 
bdarioAuthor Commented:
Thanks to everyone for your suggestions, now i solved the problem!

Dario
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now