Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Unanswered: OPENROWSET and OPENDATASOURCE truncation to 255 characters

    I have the following T-SQL commands which run without errors:

    select Description_Text
    from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
    DefaultDir=D:\Import;','select * from company_finance.csv')

    select Description_Text
    from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source=D:\Import;Extended Properties="Text;HDR=Yes;FMT=Delimited"')...compan y_finance#csv

    When I run either command on our TEST server, all the text in the fields retrieved is returned, but when I run on our PRODUCTION server, all fields are truncated to 255 characters. The source file company_finance.csv is identical on both servers. Both servers have the same O/S (Win 2K Server), same version of SQL Server 2000 and MDAC (2.71). I cannot figure out if the problem is on the SQL Server side, or has something to do with some registry settings or something else, but both the Text Driver and the Jet drivers produce the similar results on each servers. Are there some config settings in SQL Server or in the registry I can look at to determine why the truncation is occurring? Help!

    Thanks for your time.

  2. #2
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    Are you verifying the length from within Query Analyzer? It has a configurable setting for Characters per Column. Go to the Tools - Options- Results menu option to change this.
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  3. #3
    Join Date
    Dec 2004
    Posts
    2
    No, sorry, Query Analyzer isn't the problem.

    If I add the DATALENGTH() to the query, i.e.:

    select DATALENGTH(Description_Text)
    from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
    DefaultDir=D:\Import;','select * from company_finance.csv')

    For long text values I get the actual length on the TEST server, but on my PRODUCTION server, all long text values (greater than 255) show a DATALENGTH of 255.

Posting Permissions

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