Update MS SQL table containing identifier column

[MdotButler]

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).

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().

[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

 

[Olaf Doschke]

Your conversion is so simple in moving all records 1:1 from version1 to version2 tables, you could do this easily with SQL Server Management Studio with a "Copy Database" Task. Connect to any database, in the object explorer expand the databases node, right click on any database and choose "Tasks", then "Copy Database". Now follow the instructions of the wizard.

As long, as you can access both databases (they don't need to be the same SQL Server version), you can do this.

Bye, Olaf.

 

[Mike Lewis]

Mark,

I have tried to issue a "SET IDENTITY_INSERT dbo.version2_table ON" using SQL pass-through prior to the tableupdate without success.

The problem is that SET IDENTITY_INSERT is scoped to the connection. Your SQL pass-through will be running on one connection, and your remote views will be on another. Each connection will be unaware of the settings of the other.

You can get round that by sharing the connection, but that leads to several other issues.

If you have sole use of the server while you are converting, you might find it easier to remove the IDENTITY atrribute in the target table before you start; then populate the ID fields by whatever means is appropriate, and restore the IDENTITY when you have finished.

There are many other ways of moving data from/to SQL Server, including the tools built into SQL Server. Or, do as you you are doing at present, but use SQL pass-through entirely.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[MdotButler]

Olaf, there is a bunch of code between the scatter and gather which splits fields from the original tables to new ones, initializes new fields and converts values of other fields. I just wanted to show the basic flow for illustration purposes.

Mark

 

[MdotButler]

Mike you mentioned "sharing the connection". How would I grab the handle used by the connection in the database for use in the pass-through? I do not see a DBGETPROP() which will return the handle. What types of "issues" are you referring too? This sounds like a solution worth exploring.

I do have exclusive control of the version 2 database as it is only used for testing which I do have control of. I had thought of doing an ALTER to drop the identity field and add it back to the table as an integer. After the conversion reset the field back to an identity field. I am looking into this currently.

I had considered using pass-through but I do have many issues trying to construct insert statements due to data types... I would have to use stored procedures and pass data as parameters... I thought this approach was the fastest method of putting together the conversion. I am only using FoxPro as it is a great tool. The actual database is used in a web application.

Thanx
Mark

 

[Mike Lewis]

Mark,

I didn't realise at first that you were moving data from one SQL Server database to another -- using VFP as a staging post. There's nothing wrong with that, but it seems to add unnecessary complexity. Have you thought of doing it in a SQL Server script instead?

You could do it with something like an INSERT .... SELECT, or maybe a SELECT .... INTO. You could write the whole thing in SQL, and run it in the Query Analyzer. That way, you'd avoid any problems with data conversion, and you could SET IDENTITY_INSERT, given that you'd be working in the same connection the whole time.

Just a thought.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Understood, Mark.

regarding a full SPT solution: You can make a SPT cursor updatable and use it as a kind of remote view this way. Eg send the SQL SELECT * FROM table2 WHERE 1=0 to SQL Server and have an empty SPT cursor, then set some properties via CURSORSETPROP() and use TABLEUPDATE() to store data from the SPT cursor into the SQL Server table.

By the way I don't understand what field type issues you could have with SPT, that you don't have with remote views, be it through SQL sent by SQLExec and using stored procs in them.

It's rather vice versa: You can insert any fieldtype via SPT, while remote views only support VFP field types, as they are VFP cursors. The only way to overcome this is by mapping field types, but again I see more options with T-SQL used by SQLExec().

Regaring the handle you could reuse, you need to get it by CURSORGETPROP() of the view cursor: CursorGetProp('ConnectHandle','remoteviewalias'). Nevertheless, I'd say Mike is right with his warning about the issues coming up with remote views. I cannot even put my fingers on it, but there are some configuration settings to do. I don't work with views and even less with remote views.

Last not Least, I understood it was mainly a SQL Server upgrade being the reason, then a copy database task can do this first step and transformations of table structures could be done afterwards. If it's rather about the transformations due to an upgrade of data and application and you're much more familar with VFP, that explains what you're doing there, okay. I'd prefer doing the transformations within SQL Server and with T-SQL.

Regarding the IDENTITY fields, I think switching the fields to simple integers is the easiest solution and you're on track with that idea.

Bye, Olaf.

 

[MdotButler]

Thanx Mike and Olaf. I have just overcome the identity issue in a round about way. For anyone reading along I will pass the solution on.

I built a temporary table in the version 2 database by doing a "select * into TempTable where 1=2". I then "DROPPED" the identity column and re-added it as an integer field in the "TempTable". All done using SQL pass-through. I then changed my remote view to update the "TempTable".

I ran the conversion as before but updating the "TempTable" instead of the original table with the identity column.

After the conversion was done I then built a string (lcFields) of field names and constructed the following using textmerge:

SET IDENTITY_INSERT dbo.<<pcTable>> ON
INSERT <<pcTable>> (<<lcFields>>) SELECT <<lcFields>> FROM TempTable
SET IDENTITY_INSERT dbo.<<pcTable>> OFF

Problem solved (at least for now). Thanx again for your suggestions.

Mark

 

[Mike Lewis]

Glad to hear you found a solution, Mark -- and a relatively simple one.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business