Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Posts
    1

    Question Unanswered: text file querying

    recently i've been working on querying text files from sql. there is a little time delay but it works. i need to know if there is any possibility to insert and update the data stored in the flat text file

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I've never worked with text files in there given state, I would BCP or BULK INSERT the data into SQL Server, manipulate it there then write it back out. This way is much faster as well. which I found out on my own because I did try to do it using Linked Servers.

    Here is what I did, which is probable the steps that you took, but may be new to some other people.

    1) Created a test file where the data was tab delimited, placed it in C:\Temp
    2) Created a scheme.ini file and placed it in c:\temp as well, (see KB Q155512)
    3) used sp_AddLinkedServer , using Jet 4.0 as the provider, see the example in BOL for sp_AddLinkedServer at the bottom of the page

    Once I created the linked server I could read the data (SELECT) and I was able to write (INSERT) to the file as well, but could not UPDATE. I would get error 7333, the Provider does not support bookmarks. The workaround for me was to go back to my original plan, BULK INSERT into a table and manipulate the data in a SQL table.

    In conclusion INSERT were possible but not UPDATEs, maybe if a different OLE DB Provider is used with sp_AddLinkedServer ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •