Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

proc works in SSMS but not job

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
anushahanna
Asked:
anushahanna
  • 3
  • 2
3 Solutions
 
ia2189Commented:
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
 
ia2189Commented:
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
 
anushahannaAuthor Commented:
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
 
ia2189Commented:
Correct, it should work the same.  Is the job step set to the correct database?  Can you post the job step code?
0
 
anushahannaAuthor Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now