OpenRowSet returns same results even if source table changes

Posted on 2012-08-24
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
    LVL 16

    Expert Comment

    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

    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

    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

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now