Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


proc works in SSMS but not job

Posted on 2010-09-10
Medium Priority
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?


Question by:anushahanna
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
  • 3
  • 2

Accepted Solution

ia2189 earned 2000 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.

Assisted Solution

ia2189 earned 2000 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.

Author Comment

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?

Assisted Solution

ia2189 earned 2000 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?

Author Comment

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.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

705 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