Turn off IDENTITY_INSERT for Dataset insert

Question

I am using a dataset to insert data being converted from an older database. The requirement is to maintain the current Order_ID numbers.

I've tried using:

SET IDENTITY_INSERT orders ON; 

This works when I'm in SqlServer Management Studio, I am able to successfully

INSERT INTO orders (order_Id, ...) VALUES ( 1, ...); 

However, it does not allow me to do it via the dataset insert that I'm using in my conversion script. Which looks basically like this:

dsOrders.Insert(oldorderId, ...); 

I've run the SQL (SET IDENTITY_INSERT orders ON) during the process too. I know that I can only do this against one table at a time and I am.

I keep getting this exception:

Exception when attempting to insert a value into the orders table System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'orders' when IDENTITY_INSERT is set to OFF.

Any ideas?

Update

AlexS & AlexKuznetsov have mentioned that Set Identity_Insert is a connection level setting, however, when I look at the SQL in SqlProfiler, I notice several commands.

• First - SET IDENTITY_INSERT DEAL ON
• Second - exec sp_reset_connection
• Third to n - my various sql commands including select & insert's

There is always an exec sp_reset_connection between the commands though, I believe that this is responsible for the loss of value on the Identity_Insert setting.

Is there a way to stop my dataset from doing the connection reset?

Answer 1

You have the options mixed up:

SET IDENTITY_INSERT orders ON 

will turn ON the ability to insert specific values (that you specify) into a table with an IDENTITY column.

SET IDENTITY_INSERT orders OFF 

Turns that behavior OFF again and the normal behavior (you can't specify values for IDENTITY columns since they are auto-generated) is reinstated.

Marc