IDENTITY_INSERT issue - SQL Server Forums

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

SQL Server Development (2000)

IDENTITY_INSERT issue

Author

Topic

DNB
Starting Member

7 Posts

Posted - 2002-03-21 : 11:31:43

I am trying to execute a dynamic insert statement within a stored procedure. I pass in the name of the destination database. The syntax for the insert looks like this:

--Set @CurBatchDate = Convert(Int, @CurBatchDate)
Select @SQL = 'Insert '
Select @SQL = @SQL + @DestinationDatabase
Select @SQL = @SQL + '.t_InvoiceBatch (ib_InvoiceBatchID,
ib_BatchDesc, ib_BatchDate, ib_PeriodCode) Values ("'
Select @SQL = @SQL + RTrim(LTrim(Str(@CurInvoiceBatchID))) + '", "'
+ RTrim(LTrim(@CurBatchDesc)) + '", "' + RTrim(LTrim(Cast
(@CurBatchDate as VarChar))) + '", "' + RTrim(LTrim
(@CurPeriodCode)) + '")'
Exec (@SQL)

This statement works fine. But when I try to do the same with IDENTITY_INSERT statement it gives me an error:

-- turn the identity increment on so rows can be inserted
Select @SQL = 'Set IDENTITY_INSERT '
Select @SQL = @SQL + @DestinationDatabase
Select @SQL = @SQL + '.t_InvoiceBatch ON'
Exec (@SQL)

Anyone seen this before?

Thanks.

chuntley
Starting Member

8 Posts

Posted - 2002-03-21 : 11:36:21

Is it the Identity Insert giving you problems or the query after it? If it is the Identity Insert Are you passing in your dbname as [DBName].DBO or just [DBName] if its just DBName you need to add another period before '.t_InvoiceBatch'

Jay99

468 Posts

Posted - 2002-03-21 : 11:39:55

Print out your @sql variable b4 you execute it. Let's see what you are tyring to execute, rather than how you are building it.

chuntley is probably right cause this . . .


declare @sql varchar(500)
Select @SQL = 'Set IDENTITY_INSERT '
Select @SQL = @SQL + ' northwind'
Select @SQL = @SQL + '.dbo.orders ON'
Exec (@SQL)

...compiles and runs just fine

Jay
<O>

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-21 : 11:40:21

You also have to have dbo rights or db_ddladmin role to alter a table with that statement.

DNB
Starting Member

7 Posts

Posted - 2002-03-21 : 11:44:00

What I am passing in is 'RPMA1Archive.dbo' within the parameter @DestinationDatabase. The funny thing is that when I set the statement explictly:

Set IDENTITY_INSERT RPMA1Archive.dbo.t_InvoiceBatch ON

it works fine but with the dynamic statement I get the error that the explicit value cannot be inserted with the IDENTITY_INSERT set to off.

Jay99

468 Posts

Posted - 2002-03-21 : 11:49:17

AHHH!!! IDENTITY_INSERT is ON ONLY for the current session. Once you Exec(@SQL) that batch is over and IDENTITY_INSERT goes back to OFF for subsequent batches. You need to SET IDENTITY_INSERT and do the actual INSERT in the same EXEC() . . .

or is that not it . .

Jay
<O>

chuntley
Starting Member

8 Posts

Posted - 2002-03-21 : 11:51:31

Yep I just tried that on my server Jay99 is right put both statements in the same SQL varchar seperated by a ; and you should be good to go.

DNB
Starting Member

7 Posts

Posted - 2002-03-21 : 11:52:42

I followed the suggestion of 'Jay99' and the value of @SQL printed out to be:

Set IDENTITY_INSERT RPMA1Archive.dbo.t_InvoiceBatch ON

That looks ok to everyone?? However, when I ran the statement with the Exec(@SQL) I got the error:

Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 't_InvoiceBatch' when IDENTITY_INSERT is set to OFF.

I appreciate your feedback here guys. Just learning T-SQL.

DNB
Starting Member

7 Posts

Posted - 2002-03-21 : 11:54:44

I am a little slow on posting here. Will follow that last suggestion of putting them both in the same statement and get back to you.

DNB
Starting Member

7 Posts

Posted - 2002-03-21 : 11:57:02

Thumbs up guys! It has to be executed in the same statement. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources