OpenRowSet returns same results even if source table changes

Posted on 2012-08-24
Medium Priority
Last Modified: 2013-11-22
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] ([1], [2], ...)
          SELECT [1], [2], ... 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.
Question by:icegator2
  • 3
LVL 16

Expert Comment

ID: 38331892
Try clearing the source table entirely and re-running the query. If you get nothing (as you should) then try re-filling it and try the query again.  Alternatively, have you tried using SSIS - it might be easier! You could also maybe weed out dupes in the SSIS.



Author Comment

ID: 38336851
I tried clearing the source table (zapping, not just deleting) and the SQL Server query returned 40271 rows full of data (none of which existed).

I cannot use SSIS, because it doesn't seem to like the OLEDB provider file for FoxPro (it is as up-to-date as it can be), unless I'm in Windows XP, which is why I'm using a stored procedure for the conversion to begin with. After selecting the FoxPro provider, I can press "properties" and get "Unexpected unrecoverable error" or press "next" and get "Feature is not available". This happens on the Win Server 2008 machine and my Win 7 (32bit) machine, however in XP mode I can get the import to run, but I'm not allowed to save my settings. In short, SSIS is not a viable option for me.
LVL 16

Accepted Solution

DcpKing earned 1500 total points
ID: 38339164
Ok. Accept that the thing is bust (it may not be, but after 3 days you must be getting impatient!). Let's just stop trying this and do it another way. Can you export from the FoxPro system to .csv files? If so, go that route. If the Fox system is dead, install a copy of Access that will read the tables and get at them that way.

Good luck - let us know how you get on!


LVL 16

Expert Comment

ID: 39669784
Obviously that worked! Sometimes you just have to remember rule 2 of the three magic rules of programming:

If it's getting insanely difficult then you're either doing it wrong or doing the wrong thing   [if the universe runs on e=mc^2, how difficult can a computer  problem be?]

hth - have a good weekend!


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Loops Section Overview

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question