none
Using OPENROWSET for csv files

    Question

  • I have 2 instances of SQL2005 running on the same server.  I'm trying to use openRowset to get data from a csv file.  This is the query.

    SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}','SELECT * FROM \\share\file.csv')

    The query works fine on one instance, but not the other.  On the other instance I get this error.

    OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] System resource exceeded.".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

    Any help would be greatly appreciated.

     

    Thursday, January 04, 2007 7:32 PM

All replies

  • Do you have to use an ODBC driver?  OpenRowSet in Books Online had an example of opening a flat file that uses the updates to SQL since ODBC.
    Friday, January 05, 2007 2:29 AM
  • You have to use the following connection String

    SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir={Directory Path of the CSV File}','SELECT * FROM file.csv')

    Directory Path should be local or mapped path drive. It should not allow the UNC path.
    Try the following steps,

    Mapp your UNC path with local drive (like X: or Y: or Z: etc.)
    Then Use it on your OPENROWSET.

    SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=Z:','SELECT * FROM file.csv')

    Friday, January 05, 2007 5:41 AM
  • I don't this this answers the issue.  It's not that the query never works.  It works on one instance of SQLServer on the box but not the other.  So is there some configuration or permission for openrowset using  that is instance-specific?
    Thursday, January 11, 2007 5:02 PM
  • Hi Ron, did you ever find a solution to this problem? I'm currently having the exact same problem - the openrowset query was working about a week ago, but now just throws the same error you're getting.

    Looked everywhere for a solution, no luck so far :(
    Monday, March 12, 2007 1:01 PM
  • Sorry.  I'm afraid I never did get it to work right.  Luckily it worked on the production server.  If that ever fails, I'm toast.
    Tuesday, March 13, 2007 1:15 AM
  • Maybe its because of your Service Account of SQL Server?

    I suggest the Service Account User is taken to access the CSV File. If this Account does not have Access Rights to your share (or File), you cannot read the CSV File.

    Tuesday, March 13, 2007 12:01 PM
  • Use following syntax:

    select *

    from openrowset(BULK 'E:\data.txt', FORMATFILE='E:\format.xml') AS a

    Format file:

    <?xml version="1.0" ?>
    - <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    - <RECORD>
      <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="70" />
      <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
      <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="70" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
      <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
      <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
      </RECORD>
    - <ROW>
      <COLUMN SOURCE="1" NAME="f1" xsi:type="SQLVARYCHAR" />
      <COLUMN SOURCE="2" NAME="f2" xsi:type="SQLVARYCHAR" />
      <COLUMN SOURCE="3" NAME="f3" xsi:type="SQLVARYCHAR" />
      <COLUMN SOURCE="4" NAME="f4" xsi:type="SQLVARYCHAR" />
      <COLUMN SOURCE="5" NAME="f5" xsi:type="SQLVARYCHAR" />
      </ROW>
      </BCPFORMAT>
    Tuesday, March 13, 2007 3:40 PM
  • Look at the surface area security configuration on 2005.  I believe you have to enable the openrowset/opendatasource option under adhoc queries.  It may be setup on one machine and not the other.
    Tuesday, March 13, 2007 6:50 PM