Tags: driver, drivermicrosoft, following, msdasql, openrowset, programming, select, sql, statement, text, truncation

Openrowset Text Truncation

On Programmer » SQL

5,753 words with 3 Comments; publish: Thu, 29 May 2008 00:42:00 GMT; (20042.97, « »)

I've got a truncation problem with the following statement:

select *

from OpenRowset(

'MSDASQL',

'Driver={Microsoft Text Driver (*.txt;

*.csv)};DefaultDir=\\11.181.11.41\ftpusers\Homenet\xyz\;IMEX=1;',

'select * from xyz.txt')

or

select * from OpenRowSet(

'Microsoft.Jet.OLEDB.4.0',

'Text; Database=\\11.181.11.41\ftpusers\Homenet\xyz\; HDR=YES',

'select * from xyz.txt')

I have text values in the data file that are longer than 255 characters

that are being truncated to 255 characters.

I've been in the registry and have played with the values for

MaxScanRows but no matter what I do, the value cuts off at 255.

Any ideas?

All Comments

Leave a comment...

  • 3 Comments
    • You may be bumping into a Query Analyzer setting.

      Go to [Tools], [Options], [Results]. Change the [Maximum Characters per

      Column] setting.

      --

      Arnie Rowland, Ph.D.

      Westwood Consulting, Inc

      Most good judgment comes from experience.

      Most experience comes from bad judgment.

      - Anonymous

      <seamlyne.sql.itags.org.hotmail.com> wrote in message

      news:1156973997.452879.209270.sql.itags.org.e3g2000cwe.googlegroups.com...

      > I've got a truncation problem with the following statement:

      > select *

      > from OpenRowset(

      > 'MSDASQL',

      > 'Driver={Microsoft Text Driver (*.txt;

      > *.csv)};DefaultDir=\\11.181.11.41\ftpusers\Homenet\xyz\;IMEX=1;',

      > 'select * from xyz.txt')

      > or

      >

      > select * from OpenRowSet(

      > 'Microsoft.Jet.OLEDB.4.0',

      > 'Text; Database=\\11.181.11.41\ftpusers\Homenet\xyz\; HDR=YES',

      > 'select * from xyz.txt')

      > I have text values in the data file that are longer than 255 characters

      > that are being truncated to 255 characters.

      > I've been in the registry and have played with the values for

      > MaxScanRows but no matter what I do, the value cuts off at 255.

      > Any ideas?

      >

      #1; Thu, 29 May 2008 00:43:00 GMT
    • seamlyne.sql.itags.org.hotmail.com wrote:

      > I've got a truncation problem with the following statement:

      > select *

      > from OpenRowset(

      > 'MSDASQL',

      > 'Driver={Microsoft Text Driver (*.txt;

      > *.csv)};DefaultDir=\\11.181.11.41\ftpusers\Homenet\xyz\;IMEX=1;',

      > 'select * from xyz.txt')

      > or

      >

      > select * from OpenRowSet(

      > 'Microsoft.Jet.OLEDB.4.0',

      > 'Text; Database=\\11.181.11.41\ftpusers\Homenet\xyz\; HDR=YES',

      > 'select * from xyz.txt')

      > I have text values in the data file that are longer than 255 characters

      > that are being truncated to 255 characters.

      > I've been in the registry and have played with the values for

      > MaxScanRows but no matter what I do, the value cuts off at 255.

      > Any ideas?

      ----

      From: skass

      I can't reproduce your problem, but here are several possibilities I

      can think of:

      1. Could it be a display issue? In Query Analyzer, for example, there

      is a setting for the maximum number of characters to display

      (Tools|Options|Results|Maximum characters per column)

      2. You could be using an older version of the text driver. My version

      is 4.00.6304.00 (found in the Data Sources control panel applet, or as

      the version of C:\WINDOWS\SYSTEM32\odbcjt32.dll - on the server I

      assume.)

      3. Apparently MaxScanRows = 0 doesn't work for the text driver. Its

      meaning seems to depend on the value of an undocumented (as far as I

      see) registry entry UseMaxScanZeroAs. That entry has a default of "One"

      (yes, a string). I can't find any value other than "One" that works -

      others, including "All", "", "Zero", and "Unlimited", cause an error,

      but if I forget about zero and set MaxScanRows to hex FFFFFFFF, I get

      long lines without any problem, even if the first one is a ways down in

      the file.

      4. You changed the registry, but the change didn't "take." Changes to

      the driver's registry entries don't always take effect immediately, so

      if you try a large value of MaxScanRows, try the query from another

      connection before giving up. Short of rebooting the OS and SQL Server,

      I don't know how to guarantee that a change has taken effect, but my

      experience is that just a new connection will use a changed value.

      5. You aren't changing the registry entries for the SQL Server

      instance. I can't conveniently test your script in an environment

      where the client and SQL Server are on separate boxes, but I would

      think you have to change registry entries for the server. (For me, the

      registry entries to change are in

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text)

      Let me know if any of these helps.

      ------

      I had already tried every option you described EXCEPT changing

      MaxScanRows to hex FFFFFFFF. I was changing it to a decimal value of

      9999999 instead.

      The hex FFFFFFFF value solves the problem.

      Thanks a ton.

      p.s. The only thing to be careful of is that when you change this

      setting, all of the fields you are reading from the text file get

      converted to type TEXT. You'll need to use the convert statement on

      the fields before loading them into existing SQL tables in your

      database.

      #2; Thu, 29 May 2008 00:44:00 GMT
    • What happens if you run

      select *

      INTO Staging

      from OpenRowset(

      'MSDASQL',

      'Driver={Microsoft Text Driver (*.txt;

      *.csv)};DefaultDir=\\11.181.11.41\ftpusers\Homenet\xyz\;IMEX=1;',

      'select * from xyz.txt')

      followed by

      sp_help 'Staging'

      #3; Thu, 29 May 2008 00:45:00 GMT