cpeters5
asked on
Perl SQL Server error - SQL-01004
Well,
I wrote a simple Perl program to read from one table, one record at a time. Process, then insert into another.
Got SQL-01004 error when read a particular row.
--------------------------
DBD::ODBC::st fetchrow_array failed: [Microsoft][ODBC SQL Server Driver]String data, right truncation (SQL-01004) at Y:\work\cp\scripts\DataSto
DiagnosisLabIntegrate.pl line 32.
--------------------------
How do I fix this?
I wrote a simple Perl program to read from one table, one record at a time. Process, then insert into another.
Got SQL-01004 error when read a particular row.
--------------------------
DBD::ODBC::st fetchrow_array failed: [Microsoft][ODBC SQL Server Driver]String data, right truncation (SQL-01004) at Y:\work\cp\scripts\DataSto
DiagnosisLabIntegrate.pl line 32.
--------------------------
How do I fix this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I had the same problem when selecting from a text field in sql server 2005. I had thought the same thing Adam314 was saying, but that turned out to be incorrect.
(note: The default for LongReadLen is 80 and LongTruncOK is False.)
So I was thinking, like Adam314, if you set a large enough LongReadLen, you could leave LongTruncOK to false, because it would never need to truncate anything since there was so much room for the text line. In my testing, I tried this and kept getting the error:
DBD::ODBC::st fetchrow_array failed: [Microsoft][SQL Native Client]String data,
right truncation (SQL-01004) at C:\myprogram.pl line 1007.
I made LongReadLen BIGGER AND BIGGER. It was up to 64000! I STILL GOT THE ERROR.
What appears to be happening is that Sql server ignores the LongReadLen when LongTruncOK is set to false. It just overrides it, apparently. Of course, I don't have the source code to sql server, but I can see that is what it is doing. I set the LongReadLen much lower and set LongTruncOK to true, and everything worked, NO MORE ERROR.
There you have it. Yet more illogical, counter-intuitive, incomprehensible behavior from MicroSquish.
(note: The default for LongReadLen is 80 and LongTruncOK is False.)
So I was thinking, like Adam314, if you set a large enough LongReadLen, you could leave LongTruncOK to false, because it would never need to truncate anything since there was so much room for the text line. In my testing, I tried this and kept getting the error:
DBD::ODBC::st fetchrow_array failed: [Microsoft][SQL Native Client]String data,
right truncation (SQL-01004) at C:\myprogram.pl line 1007.
I made LongReadLen BIGGER AND BIGGER. It was up to 64000! I STILL GOT THE ERROR.
What appears to be happening is that Sql server ignores the LongReadLen when LongTruncOK is set to false. It just overrides it, apparently. Of course, I don't have the source code to sql server, but I can see that is what it is doing. I set the LongReadLen much lower and set LongTruncOK to true, and everything worked, NO MORE ERROR.
There you have it. Yet more illogical, counter-intuitive, incomprehensible behavior from MicroSquish.
I re-read through the documentation, and it seems this is the way those 2 options are supposed to work:
LongReadLen - Any data longer than this will be truncated (regardless of LongTruncOK)
LongTruncOK
- If false, any truncations (data longer than LongReadLen) will generate an error
- If true, an truncations will not generate an error
So, data should be truncated regardless of LongTruncOK, it just determines if it generates an error... so my previous comment was not correct.
I can't verify the MS SQL Server operation, but from what you say, it sounds like a bug. You might want to submit a bug report.
LongReadLen - Any data longer than this will be truncated (regardless of LongTruncOK)
LongTruncOK
- If false, any truncations (data longer than LongReadLen) will generate an error
- If true, an truncations will not generate an error
So, data should be truncated regardless of LongTruncOK, it just determines if it generates an error... so my previous comment was not correct.
I can't verify the MS SQL Server operation, but from what you say, it sounds like a bug. You might want to submit a bug report.
Are you sure you want LongTruncOK to true? With it true, if you have data longer than 20480 bytes, it will be truncated (cut off) to that length.
If this isn't what you want, the DBI documentation has a solution to automatically set LongReadLen.