OpenRowSet returns same results even if source table changes

[CajunCenturion]

I am converting a number of FoxPro tables to SQL Server 2008 using the OpenRowSet function. The original tables didn't have key fields defined as identities, and some of them have duplicate values. As expected, the insert command bombs on those; however, when I remove the duplicates from the source table and rerun the insert, it bombs on the exact same records. This leads me to believe that the OpenRowSet runs its select against a cached table - created during the initial run - rather than the original source table each time. I have tried FreeProcCache and DropCleanBuffers to no avail. I have restarted both the workstation and the database server, but again, to no avail. However, leaving the computers up overnight fixes the problem. This further suggests that a cache is created and exits within the database; however, with an expiration time. I would appreciate any information on how to manage this cache, if that's truly what it is.

Here is some sample code:

SET IDENTITY_INSERT [schema].dbo.[table] ON
     INSERT INTO [schema].[dbo].[table] (<field list>)
          SELECT <field list> FROM OPENROWSET('VFPOLEDB','<path>';'';'DELETED=YES', 'SELECT * FROM table WHERE NOT DELETED()')

The command is being executed from a stored procedure, and again, the command works fine on its first run. It just doesn't honor changes made in the source table for subsequent runs. For what it's worth, I am doing this from SQL Server Management Studio logged in as the administrator.

I know some of you will suggest alternate ways of accomplishing this task, and while I make take advantage of that down the road, right now my priority is to resolve these cache management issues.

Thanks.

--------------
Good Luck
To get the most from your Tek-Tips experience, please read
FAQ181-2886
Wise men speak because they have something to say, fools because they have to say something. - Plato

 

[gmmastros]

Do you have a process that occurs nightly to remove deleted rows from the FoxPro tables? I suspect that you might, but this is just a guess.

Since you have "Deleted=YES", you shouldn't need to use a where clause. So... try this:

SET IDENTITY_INSERT [schema].dbo.[table] ON
     INSERT INTO [schema].[dbo].[table] (<field list>)
          SELECT <field list> FROM OPENROWSET('VFPOLEDB','<path>';'';'DELETED=YES', 'SELECT * FROM table')

Lastly, I would like to mention that it is common for SQL developers to use a "staging" table. Basically, the staging table would have all the right columns, and the data would be imported in to this staging table. Then, you would have another process to remove duplicate rows and/or fix other potential issues prior to moving this data in to the main table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CajunCenturion]

==> Do you have a process that occurs nightly to remove deleted rows from the FoxPro tables?
No, this is a conversion effort. Any suggestions on what appears to be a cache issue?

--------------
Good Luck
To get the most from your Tek-Tips experience, please read
FAQ181-2886
Wise men speak because they have something to say, fools because they have to say something. - Plato