Job failing everyday

Question

Post reply

Job failing everyday

SkyBVI

SSCoach

Points: 15128
More actions
September 1, 2010 at 7:32 am

#239798

I have a job which runs a stored procedure:-
/* Code of a stored proc
DECLARE @POPRCTNM AS VARCHAR(40)
--RETRIEVE RECORDS FROM EP00300
DECLARE EP00300_JOB_CURSOR CURSOR FAST_FORWARD FOR SELECT DISTINCT(POPRCTNM)
FROM EP00300
OPEN EP00300_JOB_CURSOR
FETCH NEXT FROM EP00300_JOB_CURSOR INTO @POPRCTNM
WHILE @@FETCH_STATUS = 0
BEGIN
--CALL UPDATE PRICES STORED PROC
EXEC sp_EP_Update_Price_Records @POPRCTNM
FETCH NEXT FROM EP00300_JOB_CURSOR INTO @POPRCTNM
END
--clean up before exiting
CLOSE EP00300_JOB_CURSOR
DEALLOCATE EP00300_JOB_CURSOR
*/
THIS JOB keeps failing everyday with the following error:-
Executed as user: Domainname\gpsqlagent. CHECKING FOR Y RECORDS [SQLSTATE 01000] (Message 0) CHECKING FOR Y RECORDS [SQLSTATE 01000] (Message 0) CHECKING FOR Y RECORDS [SQLSTATE 01000] (Message 0) CHECKING FOR Y RECORDS [SQLSTATE 01000] (Message 0) Y RECORDS EXIST [SQLSTATE 01000] (Message 0) String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
What should I do?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

only 2 possible options :

1 - DECLARE @POPRCTNM AS VARCHAR(40)

It's possible that the data column is more than varchar(40).

2 - The SP you call is failling on some records.

The best way to trace that is to setup some sort of reporting table. Then for each item you loop through, record it in the new table (before calling the sp). Then run the sp and the item that makes the sp crash will be the last row inserted in the reporting table.

Once you have that, debug the SP. You most likely have a column varchar(40) in one table updating to varchar(30) in another table with a column value of more than 30 characters.

kyleheath33 Ten Centuries Points: 1089 More actions · Answer 2

Hi,

If you look at the link below it gives a good explanation into the error, basically your trying to insert a string that can't fit into the column due to the width so either shorten the string or edit the column width to resolve.

http://www.sql-server-performance.com/faq/string_or_binary_data_truncated_p1.aspx

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I'm not sure anymore, but it might also be the case if the parameter is varchar(30). I don't remember if that throws and error, but it sure can make the proc fail.

SkyBVI SSCoach Points: 15128 More actions · Answer 4

@ ninja

But when I run this sp manually (in SSMS), it runs fine...

So i should change the @poprctnm as varchar(40) to varchar(50)?? in case the column width have to be increased?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

No, you need to find the 1 sku that makes the proc fail. When you read the executions results from the job you see that it succeeds a few times before failing.

The only clue I can give you is that you most likely have a very unusual data column in 1 or more rows.

IE : Your standard data may be 1.3 or 9.8 and then for some reason you get a 0.92487621347213094782134576213047.

The proc will run fine untill it meets that problem. That's why I told you to log what is going on untill you find the row that fails. Once you have that you can compare with rows that succeeds and see that is different between the 2.

That's the best help I can give you without seeing the code and doing it for you. Which in this case is most likely impossible to do without me going to your work place or some other drastic solution.