September 10, 2010 at 7:04 am
hi,
I schedule a job every morning, but it fails ...so I have to run it manually. When manually run, it always is successful.
The error i get when i schedule it is :-
String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
Regards,
SUSHANT
Regards
Sushant Kumar
MCTS,MCP
September 10, 2010 at 7:18 am
More details please....
September 10, 2010 at 7:35 am
You probably have a conflict in a field size. Check to make sure if you are importing data in that your fields are large enough to accept them.
Some more information would help though. Maybe providing more of the error log or the code of what your job is actually doing.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 10, 2010 at 7:45 am
The job code is :--
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
DECLARE @subject VARCHAR(250), @message VARCHAR(4000), @query VARCHAR(4000),
@recipients VARCHAR(250)
DECLARE @ITEMNMBR VARCHAR(31),@ITEMDESC CHAR(101), @PRCLEVEL VARCHAR(25),@UOFM VARCHAR(10),
@UOMPRICE NUMERIC(19,5), @QTYBSUOM NUMERIC(19,5), @CALPRICE NUMERIC(19,5),
@ACTUALPRICE NUMERIC(19,5)
-- SELECT * INTO IV00108_05APR07_0700PM FROM IV00108
-- Table to hold Price Changes
DECLARE @KITPRICES TABLE
(
ITEMNMBR CHAR(31),
ITEMDESC CHAR(101),
PRCLEVEL CHAR(25),
UOFM CHAR(10),
QTYBSUOM NUMERIC(19,5),
UOMPRICE NUMERIC(19,5),
CALPRICE NUMERIC(19,5)
)
-- Get Old and New Prices
INSERT INTO @KITPRICES
(
ITEMNMBR,
ITEMDESC,
PRCLEVEL,
UOFM,
QTYBSUOM,
UOMPRICE,
CALPRICE
)
SELECT
IV.ITEMNMBR,
IV.ITEMDESC,
PL.PRCLEVEL,
PL.UOFM,
PL.QTYBSUOM,
PL.UOMPRICE,
dbo.rtw_fn_Kit_Price(IV.ITEMNMBR, PL.PRCLEVEL)
FROM
IV00101 IV
JOIN IV00108 PL
ON IV.ITEMNMBR = PL.ITEMNMBR
WHERE
IV.ITEMTYPE = 3 AND
PL.PRCLEVEL = 'VG' AND -- Remove (PL.PRCLEVEL = 'C&C' OR ... C& C For the RMS Go Lve .
-- Included in this run only
--IV.ITEMNMBR NOT IN
--(SELECT ITEMNMBR FROM IV00104 WHERE ITEMNMBR LIKE '%-CN%' AND CMPITUOM != 'CN')
PL.UOMPRICE != dbo.rtw_fn_Kit_Price(IV.ITEMNMBR, PL.PRCLEVEL)
ORDER BY
IV.ITEMNMBR,
PL.PRCLEVEL
-- Update Prices
UPDATE IV00108 SET
UOMPRICE = KP.CALPRICE
FROM
IV00108 PL
JOIN @KITPRICES KP
ON PL.ITEMNMBR = KP.ITEMNMBR
AND PL.PRCLEVEL = KP.PRCLEVEL
AND PL.UOFM = KP.UOFM
WHERE
KP.CALPRICE > 0
-- Return Results
DECLARE @TBL2 TABLE
(
ITEMNMBR VARCHAR(25),
ITEMDESC CHAR(101),
PRCLEVEL VARCHAR(25),
UOFM VARCHAR(10),
QTYBSUOM NUMERIC(19,5),
UOMPRICE NUMERIC(19,5),
CALPRICE NUMERIC(19,5),
ACTUALPRICE NUMERIC(19,5)
)
INSERT @TBL2
SELECT
KP.*, PL.UOMPRICE ACTUALPRICE
FROM
IV00108 PL
JOIN @KITPRICES KP
ON PL.ITEMNMBR = KP.ITEMNMBR
AND PL.PRCLEVEL = KP.PRCLEVEL
AND PL.UOFM = KP.UOFM
WHERE
KP.CALPRICE > 0
IF ((SELECT COUNT(*) FROM @TBL2) > 0)
BEGIN
SET @subject = 'Kit Prices Updated on: ' + CAST(GETDATE() AS VARCHAR(100))
SET @message = 'The following Kit Prices where updated on - '
SET @message = @message + CAST(GETDATE() AS VARCHAR(100)) +
CHAR(13) + CHAR(13)
CREATE TABLE ##mail_body
(
ITEMNMBR VARCHAR(25),
ITEMDESC CHAR(101),
PRCLEVEL VARCHAR(25),
UOFM VARCHAR(10),
QTYBSUOM NUMERIC(19,5),
UOMPRICE NUMERIC(19,5),
CALPRICE NUMERIC(19,5),
ACTUALPRICE NUMERIC(19,5)
)
INSERT ##mail_body
SELECT * FROM @TBL2
SET @query = N'
SELECT
CAST(PR.PRCLEVEL AS CHAR(4)) [Price Level],
CAST(PR.ITEMNMBR AS CHAR(12)) [Item],
PR.ITEMDESC [Description],
CAST(PR.UOFM AS CHAR(3)) [UOM],
CAST(PR.UOMPRICE AS NUMERIC(5,2)) [Old Price],
CAST(PR.CALPRICE AS NUMERIC(5,2))
[New Price]
FROM
##mail_body PR
ORDER BY
PR.PRCLEVEL,
PR.ITEMNMBR'
EXEC master..xp_sendmail
@recipients = @recipients,
@message = @message,
@query = @query,
@subject = @subject,
@width = 200 -- Changed from 150 to see if the e-mail's sent will look better, Alexis Thomas Aug 16th 06
DROP TABLE ##mail_body
END
ELSE
BEGIN
SET @subject = 'No Kit Prices were found to update at: '
+ CAST(GETDATE() AS VARCHAR(100))
SET @message = 'No Kit Prices were updated at - '
+ CAST(GETDATE() AS VARCHAR(100)) + CHAR(13) + CHAR(13)
EXEC master..xp_sendmail
@recipients = @recipients,
@message = @message,
@subject = @subject,
@width = 200 -- Changed from 150 to see if the e-mail's sent will look better, Alexis Thomas Aug 16th 06
RETURN 0
END
Regards
Sushant Kumar
MCTS,MCP
September 10, 2010 at 7:46 am
why doesn't that error comes, when i run manually??
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
September 10, 2010 at 8:51 am
You could add some auditing to it, and save the output to a text file so you can read where the error occurs.
Add a few PRINT 'starting step x' lines in between each insert/update, and then next time it fails, open the log file and see which step it broke at. You can probably narrow it down from there.
September 10, 2010 at 8:53 am
what is the failure msg you geting?
Are you running this job under correct operator?
----------
Ashish
September 10, 2010 at 9:34 am
@ derrick
Can you please elaborate, how to narrow down the error.. the print statements script ? and at where...
@ ashish
The error i wrote at starting the post.
It runs under sa.
Regards,
SUSHANT
Regards
Sushant Kumar
MCTS,MCP
September 10, 2010 at 11:41 am
Add an output file to the Job Step (under Advanced)
In the job script after each statemet (like -- Table to hold Price Changes etc.) add a PRINT statement like
PRINT 'Starting -- Table to hold Price Changes'
This will help you to narrow down the issue.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 10, 2010 at 1:11 pm
@ pradeep
Thanks a lot
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply