Linked Server to comma delimted Text Files RRS feed

  • Question

  • I am getting errors when trying to setup a Linked Server to a comma delimeted text file.  The linked server gets added ok, but when I try to see the tables I get errors.  I have access to the file, I have verified this, I can open the file on the server without any issues.   I have setup an Linked server to a different excel file (in the same directory)  using Jet driver and specified Excel as the Product Name and Excel 8.0 as the provider string.  


    I have found many web search references to setting up the schema.ini file for the text file in the same directory.  But, when I used the ODBC Admin to create an odbc entry for the text file, there is no Jet driver available so I used the MSoft Text Driver.  Then when I hit Define Format for the file and then the Guess button and then then Hit Ok, I get an error

    'Can't save (null) into (Null).  If I then hit Define Format again, it indicates the schema.ini is corrupt.


    Here is what I manually used to create the Text File.


    EXEC sp_addlinkedserver EDIR, 'Jet 4.0','Microsoft.Jet.OLEDB.4.0','\\server\f$\UAD2\wkfexp.csv', NULL, 'Text'


    command completed.


    Here is the error when I try to see the tables.

    EXEC sp_tables_ex 'EDIR'


    OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EDIR" returned message "'\\server1\f$\UAD2\wkfexp.csv' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".

    Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EDIR".



    Anyone successfully create a Linked Server to a comma delimted text file?  Am I using the correct parameters for the sp_addlinkedserver? 
    • Moved by Tom PhillipsModerator Saturday, February 6, 2010 4:35 AM TSQL Question (From:SQL Server Database Engine)
    Thursday, October 16, 2008 12:47 PM


All replies