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

Answer 2

You want to do SET IDENTITY_INSERT ON to allow you to insert into identity columns.

It seems a bit backwards, but that's the way it works.

Answer 3

It seems that you're doing everything right: SET IDENTITY_INSERT orders ON is the right way on SQL Server's side. But the problem is that you're using datasets. From the code you've provided I can say that you're using typed dataset - the one that was generated in Visual Studio based on the database.

If this is the case (most likely) then this dataset contains a constraint that does not allows you to set values for orderId field, i.e. it's the code that does not allow specifying explicit value, not SQL Server. You should go to dataset designer and edit properties of orderId field: set AutoIncrement and ReadOnly to false. But the same changes can be performed in run time. This will allow you to add a row with explicit value for orderId to a dataset and later save it to SQL Server table (you will still need SET IDENTITY_INSERT).

Also note that IDENTITY_INSERT is a connection-level setting so you need to be sure that you're executing corresponding SET exactly for the same connection that you will be using to save your changes to the database.

Answer 4

I would use Profiler to determine whether your SET IDENTITY_INSERT orders ON; is issued from the same connection as your subsequent inserts, as well as the exact SQL being executed during inserts.

Answer 5

AlexS was correct, the problem was the Insert_Identity worked, but it is a connection level setting, so I needed to set the Insert_Identity within a transaction.

I used Ryan Whitaker's TableAdapterHelper code

and I created an update command on my tableadapter that ran the Identity_Insert. I then had to create a new Insert command with the Identity column specified. I then ran this code

SqlTransaction transaction = null;

try
{
     using (myTableAdapter myAdapter = new myTableAdapter())
     {   
         transaction = TableAdapterHelper.BeginTransaction(myAdapter);
         myAdapter.SetIdentityInsert();
         myAdapter.Insert(myPK,myColumn1,myColumn2,...);
     }

     transaction.Commit();
 }
 catch(Exception ex)
 {
     transaction.Rollback();
 } 
 finally
 {
     transaction.Dispose();
 }