Solved

SQL 2005 Agent Job Error : SQLSTATE 22001

Posted on 2010-09-17
7
847 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:JOSHUABT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 33706146
is this job running a sql statement, calling a stored procedure, running a package? other?
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)
0
 

Author Comment

by:JOSHUABT
ID: 33706189
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,
0
 
LVL 29

Expert Comment

by:QPR
ID: 33706444
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
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:JOSHUABT
ID: 33706584
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,
0
 
LVL 29

Expert Comment

by:QPR
ID: 33706602
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
0
 

Author Comment

by:JOSHUABT
ID: 33706609
QPR,
What is the package that you mentioned earlier?
How can I implement it?

Thanks,
0
 
LVL 29

Accepted Solution

by:
QPR earned 500 total points
ID: 33706823
An ssis package (if installed).
In a nutshell it gives you the ability to have data source and destination and to be able to clean or redirect data transfers. May or may not be the ideal solution depending on your needs or knowledge of the tech.

It gives you error handling and/or the ability to make decisions based on the state of the data. The packages can be scheduled as agent jobs.
First question is, what would you do with "bad" data. Then we can go from there
0

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question