Using identity_insert in a stopred procedure with a variable

[Remco]

I am using the SET IDENTITY_INSERT command in an SP. However, we need
the table in the command to be in a different database, the name of
which is passed to the SP in a variable.

The command I am using would look like:

EXEC( 'SET IDENTITY_INSERT ' + @dbname + '..tax_transaction ON' )

However, this does not seem to work, as the following INSERT statement
gives the error that IDENTITY_INSERT is set OFF.

Any help is appreciated.

 

[William \(Bill\) Vaughn]

What's the underlying problem you're trying to solve with this approach?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Remco]

We're moving data from one database to another for archiving purposes.
However, the name of the archive database varies from one installation
to another and from one environment to another.
Remco

 

[William \(Bill\) Vaughn]

ADO and ADO.NET are terrible at performing bulk copies. I suggest using
BulkCopy from within TSQL, DTS or the bulk copy utility. These can be
programmed to connect to any backend.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Remco]

Thank you for your suggestion. We have no problem with "bulk" copies.
It's only 4-5 records belonging to the same transaction at a time (user
triggered). The whole process works perfectly, the only problem now is
we have to make alternative copies of the SP for each database/client
instance. I'd rather parameterise that.

 

[William \(Bill\) Vaughn]

Ok... if you want to address alternative servers, you're going to have to
either setup linked servers on SQL Server and execute the code from there or
handle caching and switching connections on the client.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Remco]

You are confusing me, or I'm afraid your seeing more than there is.

The only problem is that

EXEC( 'SET IDENTITY_INSERT ' + @dbname + '..tax_transaction ON' ) ,
where @dbname = 'isrec'

does not work, but

SET IDENTITY_INSERT isrec..tax_transaction ON

works fine.

However, 'isrec' is variable.

 

[William \(Bill\) Vaughn]

We're both confusing each other. That's why I keep the old thread in the
message so I can follow the context.
Ah, I generally build a string
Declare @strSQL as VarChar(8000)
and concatenate to it a piece at a time
SET @strSQL = 'EXEC( 'SET IDENTITY_INSERT ' + @dbname +
'..tax_transaction ON' )
and then execute the string.
EXEC(@strSQL)

hth


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Remco]

This does not work. Executing the string does not set identity_insert
to ON. The next insert statement still causes an error on this.