OpenRowSet returns same results even if source table changes
Posted on 2012-08-24
I am trying to convert FoxPro tables to SQL Server 2008 via an OpenRowSet. The older tables have key fields that weren't defined as properties, so some of the values are duplicates. The insert command bombs when it hits a duplicate (as expected), but when we clean the offending records from the source table and rerun the insert, it bombs on the same records, that should no longer exist.
This leads me to believe that the OpenRowSet runs it's select into a cached "table", and each subsequent execution accesses this "table", rather than rerunning the select on the source table. I have tried FreeProcCache and DropCleanBuffers. I have restarted both my machine and the server hosting the database (the machine, not just the SQL Server instance). None of these have worked. However, leaving the computers overnight does seem to "fix" the issue. This makes me think there is a time-related "life" to the cached version, but I don't know what it is or how to change it.
I would greatly appreciate any help in clearing this up, seeing that finding a duplicate in the morning and being unable to fix the problem for 24hrs is a waste.
SET IDENTITY_INSERT [schema].dbo.[table] ON
INSERT INTO [schema].[dbo].[table] (, , ...)
SELECT , , ... FROM OPENROWSET('VFPOLEDB','<path>';'';'DELETED=YES', 'SELECT * FROM table WHERE NOT DELETED()')
SET IDENTITY_INSERT [schema].dbo.[table] OFF
Please keep in mind the command works, but if a change is made to the source table after running the command, the changes will not show up in subsequent runs of the command.
Also, I am using the SQL Server Management Studio and logged in as the administrator.