Solved

proc works in SSMS but not job

Posted on 2010-09-10
5
293 Views
Last Modified: 2012-05-10
a direct execute works. but when in a step in a job, it says

"String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed."

by executing in as it is with an exec command, i have confirmed that the logic is good. i confirmed that the data manipulation has happened correctly also :

it is basically an insert statement

delete database2....tablename
use database1
insert into database2....tablename
select ......... from ......

in the step in the job, it is executing from database1.

do you think it is a data tpye issue, and if so, why only from the job or from direct execution?

thanks



0
Comment
Question by:anushahanna
  • 3
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
ia2189 earned 500 total points
ID: 33648829
The error message is related to the database field being too small.  An example would be having a field set to 50 characters and you're trying to insert a record that is 51 characters.  A value in the select statement is too long.
0
 
LVL 3

Assisted Solution

by:ia2189
ia2189 earned 500 total points
ID: 33648845
Try and find the field that's too long by using the len(field_name) function, or even the max(len(field_name)) function, or increase the field sizes on the table you're inserting the data into.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33649008
hmm.. they all seem to be OK. I checked the max(len(field_name)) value and compared it to the schema definition of that table.

regardless, if this is the issue, it should fail in a regular exec procname format also, right?
0
 
LVL 3

Assisted Solution

by:ia2189
ia2189 earned 500 total points
ID: 33650573
Correct, it should work the same.  Is the job step set to the correct database?  Can you post the job step code?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33651511
ia2189- it was a tricky place where there was a overflow of characters, which in some data runs - so it was a data issue. thanks for the clue and which made it easy to look out for it.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

825 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