Solved

proc works in SSMS but not job

Posted on 2010-09-10
5
290 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now