August 22, 2008 at 3:40 am
I have some code that works fine in SQL Query Analyzer, but fails with an error when running as a job. Here's the code:
USE STXbaxt
-- Count number of differences
IF (SELECT COUNT(*) FROM xllrequisitions WHERE [Date Processed] IS NOT NULL AND CONVERT(CHAR(10),[Date Processed], 103) 0
BEGIN
-- Get values from xLLrequisitions table into a temp table
SELECT CONVERT(CHAR(20), [Requisition]) AS [REQUISITION], CONVERT(CHAR(30), [Part:Batch]) AS [PART:BATCH], CONVERT(CHAR(10), [Qty]) AS [QUANTITY], [Direction] AS [IN(+)/OUT(-)], CONVERT(CHAR(8), [BoxSize]) AS [BOX SIZE], CONVERT(CHAR(10),[Date Processed], 103) AS [DATE]
INTO tmp_xLLrequisitions
FROM xLLrequisitions
WHERE [Date Processed] IS NOT NULL
AND CONVERT(CHAR(10),[Date Processed], 103) <= CONVERT(CHAR(10), GETDATE() - 7, 103)
ORDER BY [Date Processed]
-- Send list of differences by email to administrator
USE master
EXEC xp_startmail
EXEC xp_sendmail @recipients = 'stores@senior.co.uk',
@query = 'USE STXbaxt
SELECT *
FROM tmp_xLLrequisitions',
@subject = 'Lean lift requisitions that have not been cleared',
@message = 'The attached file contains a list of requisitions that have not been cleared off the lean lift yet. Any old, obsolete or invalid ones should be deleted at the lean lift console.',
@attach_results = 'TRUE', @width = 250
EXEC xp_stopmail
-- Get rid of temp table
USE STXbaxt
DROP TABLE tmp_xLLrequisitions
END
This basically checks a list of outstanding requisitions (7 or more days old) and emails the list to key members of staff. Runs great in Query Analyzer, but when run as a job, fails with this error:
Executed as user: POYNTON\SQLAdmin. Line 8: Incorrect syntax near '20080822'. [SQLSTATE 42000] (Error 170). The step failed.
Looks like something to do with the dates in line 6, but I haven't been able to find out why, even by re-writing the code in various ways.
Note to all you code geniuses......yes, I know this is an inelegant way of achieving this, but I've found the @query element of xp_sendmail to be very fussy about functions included within the quotes. If I include the CONVERT functions in the @query string, it doesn't work.......no error message.....just nothing. Maybe you could explain that one too?
Any help would be most appreciated.
Cheers,
Mark
August 22, 2008 at 4:12 am
For some reason, the IF statement line in my code didn't come through properly. It should be:
IF (SELECT COUNT(*) FROM xllrequisitions WHERE [Date Processed] IS NOT NULL AND CONVERT(CHAR(10),[Date Processed], 103) 0
Also, the error seems to relate to the CONVERT function in the SELECT into a temp file statetement:
CONVERT(CHAR(10),[Date Processed], 103) AS [DATE]
Hope that's clearer!
August 22, 2008 at 4:13 am
And the code hasn't come through again! I must be a jinx. here it is again without the bold:
IF (SELECT COUNT(*) FROM xllrequisitions WHERE [Date Processed] IS NOT NULL AND CONVERT(CHAR(10),[Date Processed], 103) 0
August 22, 2008 at 4:14 am
Nope....didn't work. broken up onto two lines maybe?
IF (SELECT COUNT(*) FROM xllrequisitions WHERE [Date Processed] IS NOT NULL
AND CONVERT(CHAR(10),[Date Processed], 103) 0
August 22, 2008 at 4:15 am
OK, it's the does not equal signs that are the problem.......so........
IF (SELECT COUNT(*) FROM xllrequisitions WHERE [Date Processed] IS NOT NULL
AND CONVERT(CHAR(10),[Date Processed], 103) <= CONVERT(CHAR(10), GETDATE() - 1, 103)) DOES NOT EQUAL 0
August 22, 2008 at 4:15 am
Hooray! Easily pleased......me????
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply