Copyright © 2008 ASC Associates, Technical Support support@ascassociates.biz , Sales Enquiries sales@ascassociates.biz
Converting Access Append Queries and IDENTITY_INSERT Problems

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.

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


Copyright © 2008 ASC Associates, Technical Support support@ascassociates.biz , Sales Enquiries sales@ascassociates.biz