>I am using remote views to update data in an sql server database. This is all good. My problem is:
>
>One thing I'm doing is using VFP as a meta data tool in order to affect structure changes on a sql server database. When I determine that a table needs a stucture change, I do the following:
>
>1. Execute and SQLEXEC that pulls the records from SQL Server into a VFP cursor
>2. DROP and CREATE the SQL Server table
>3. Create a remote view that connects to the new SQL Server table
>4. Append the records from the cursor (that was holding the data) into my remote view
>5. Issue tableupdate()
>
>This is all beatiful except:
>I use identity columns on my sql server database. If there are deleted records in my sql server database, when the data gets appended into my remote view and then updated to SQL Server, SQL Server "renumbers" my identity column because I created a new table.
>
>Thus, I lose the missing ids, which then breaks relationships, etc, etc.
>
>Does anyone know how I might SET IDENTITY_INSERT ON inside of the connection that SQL Server is working with. Or, does anyone have a better way to do this?
>
>Dave

Hi Dave,

I wouldn't use remote view in this scenario, it seems unnecessary to copy data twice over a wire when you can do it all on the server. Just copy data into different table on the same server or simply rename original one (sp_rename), modify original table or create a new one, SET IDENTITY_INSERT ON (if you need to) and copy data back. I believe this will work a lot faster and more reliable.

If you still want to use remote view, use CURSORGETPROP to obtain connection handle for the view, call SQLEXEC against it with SET IDENTITY_INSERT ON and after that you are ready for the TABLEUPDATE.

Thanks,
Aleksey.