Solved

proc works in SSMS but not job

Posted on 2010-09-10
5
294 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

735 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