MdotButler
Programmer
I have a situation where I am converting a production MS SQL database from version 1 to version 2. The program I wrote dynamically creates an updateable remote view to the new table. I use SQL pass through to create a cursor of the version_1 table and perform the following (pseudo code).
The problem I have encountered is that any table that has an SQL IDENTITY column will fail on the TABLEUPDATE().
I have tried to issue a “SET IDENTITY_INSERT dbo.version2_table ON” using SQL pass-through prior to the tableupdate without success.
I am sure some of you have encountered this and was wondering if there is a tried and true solution.
TIA
Mark
Code:
OPEN DATABASE remotedbc
USE version_2
SCATTER MEMVAR MEMO BLANK
SELECT version_1
SCAN
SCATTER MEMVAR MEMO
*
SELECT version_2
APPEND BLANK
GATHER MEMVAR MEMO
=TABLEUPDATE()
*
SELECT version_1
ENDSCAN
The problem I have encountered is that any table that has an SQL IDENTITY column will fail on the TABLEUPDATE().
Code:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'Encounter' when IDENTITY_INSERT is set to OFF.
I have tried to issue a “SET IDENTITY_INSERT dbo.version2_table ON” using SQL pass-through prior to the tableupdate without success.
I am sure some of you have encountered this and was wondering if there is a tried and true solution.
TIA
Mark