JOSHUABT
asked on
SQL 2005 Agent Job Error : SQLSTATE 22001
Hello,
SQL2005.Ent.x86.SP3 is running on W2K3.Ent.x86.SP2.
One of it SQL Agent job frequently fails with the following message :
"String or binary data would be truncated. [SQLSTATE 22001]"
It seems like some data value doesn't meet the column data type settings when inserted into the table especially exceeding max char length.
Is there any way to capture the data value that causing this problem?
This SQL agent job inserts a set of records from one table to another as one shot ( no loop ).
Thank you for your help.
SQL2005.Ent.x86.SP3 is running on W2K3.Ent.x86.SP2.
One of it SQL Agent job frequently fails with the following message :
"String or binary data would be truncated. [SQLSTATE 22001]"
It seems like some data value doesn't meet the column data type settings when inserted into the table especially exceeding max char length.
Is there any way to capture the data value that causing this problem?
This SQL agent job inserts a set of records from one table to another as one shot ( no loop ).
Thank you for your help.
ASKER
This SQL job is running every two hours and execute a sql statement.
It does not call a stored proc or run a packaga.
There are frequent data entry typo, which cannot be validated at the application level, which it is supposed to be and re-coding is not an option.
Most of the time they are caused by string-length exceedign max width and sometiems by special characters.
If there is a way to capture the invalid data during insert operation, I can rather quickly correct its value.
Thanks,
It does not call a stored proc or run a packaga.
There are frequent data entry typo, which cannot be validated at the application level, which it is supposed to be and re-coding is not an option.
Most of the time they are caused by string-length exceedign max width and sometiems by special characters.
If there is a way to capture the invalid data during insert operation, I can rather quickly correct its value.
Thanks,
U could try convert(varchar,x,y) prior to the insert.
X being the field and y being the length of the destination column. This would assume you knew the field and were ok with the truncating to happen.
You could change this to a package which then give you the ability to handle all sorts of validations and inline changes
X being the field and y being the length of the destination column. This would assume you knew the field and were ok with the truncating to happen.
You could change this to a package which then give you the ability to handle all sorts of validations and inline changes
ASKER
QPR,
Truncation is not an option.
If I can capture the invalid record, that will be best for now.
Is there any way to do that?
Thanks,
Truncation is not an option.
If I can capture the invalid record, that will be best for now.
Is there any way to do that?
Thanks,
well you can loop through each value prior to insert and check it's length then you can insert it into an exception table rather than your real table and then deal to them after
ASKER
QPR,
What is the package that you mentioned earlier?
How can I implement it?
Thanks,
What is the package that you mentioned earlier?
How can I implement it?
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you bypass the middle man (agent) and try to run the query/sp/package directly to see if you can get some more specific info?
Either that or simply look and compare the table design of the source and destination tables, see which is "smaller" in the destination and then drill down to the data in the source to find the culprit(s)