This article describes a problem that will occur when converting an append query to a SQL Server stored procedure, where the query appends directly into a column which has an identity property (autonumber).

We start with an Access database containing two tables, one with data and the other empty.

In each table the primary key is SQLVersionID and is an autonumber.

Now consider the following standard append query.

Once we have upsized and converted the application, we have a stored procedure in SQL Server replacing the append query.

CREATE PROCEDURE [dbo].[CopyDrivers_To_TempDrivers] AS BEGIN INSERT INTO [TEMPDrivers] ( [SQLVersionID], [SQLVersion], [ODBCDriverName], [OLEDBDriverName] ) SELECT [tblDrivers].[SQLVersionID], [tblDrivers].[SQLVersion], [tblDrivers].[ODBCDriverName], [tblDrivers].[OLEDBDriverName] FROM [tblDrivers] End GO

And a pass-through query in Access that fires the stored procedure.

The pass-through query contains the following SQL calling the stored procedure.

However when we execute the stored procedure from the management studio, or execute the pass-through query, you will get the following error.

Cannot insert explicit value for identity column in table....when IDENTITY_INSERT is set to OFF.

This happens because SQL Server demands that you state when you want to do an insert into an identity column, and you must also state when you are finished doing this (otherwise you will get errors when you try it on another table, whilst the same connection is active).

SET IDENTITY_INSERT [TEMPDRIVERS] ON SET IDENTITY_INSERT [TEMPDRIVERS] OFF

The simplest solution is to modify the stored procedure as follows: -

ALTER PROCEDURE [dbo].[CopyDrivers_To_TempDrivers] AS BEGIN SET IDENTITY_INSERT [TEMPDRIVERS] ON INSERT INTO [TEMPDrivers] ( [SQLVersionID], [SQLVersion], [ODBCDriverName], [OLEDBDriverName] ) SELECT [tblDrivers].[SQLVersionID], [tblDrivers].[SQLVersion], [tblDrivers].[ODBCDriverName], [tblDrivers].[OLEDBDriverName] FROM [tblDrivers] SET IDENTITY_INSERT [TEMPDRIVERS] OFF End GO

The executing either the pass-through query, or the stored proecdure directly in the management studio will work.

Top of Page

MUST+SQL automatically removes problems with cascades in relationships removing any cascade operations but leaving the relationships active. Click below to improve your development productivity.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz