SQL Server Performance

T-SQL runs fine in Query Qnalyzer, but not as a job.........why?

Discussion in 'T-SQL Performance Tuning for Developers' started by rock_and_roll, Aug 22, 2008.

  1. rock_and_roll New Member

    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
    SELECTCONVERT(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 [BOXSIZE], 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 havenot been cleared off the lean lift yet. Any old, obsolete or invalidones 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


    Thisbasically checks a list of outstanding requisitions (7 or more daysold) and emails the list to key members of staff. Runs great in QueryAnalyzer, but when run as a job, fails with this error:

    Executed as user: POYNTONSQLAdmin. Line 8: Incorrect syntax near '20080822'. [SQLSTATE 42000] (Error 170). The step failed.

    Lookslike something to do with the dates in line 6, but I haven't been ableto find out why, even by re-writing the code in various ways.

    Noteto all you code geniuses......yes, I know this is an inelegant way ofachieving this, but I've found the @query element of xp_sendmail to bevery fussy about functions included within the quotes. If I include theCONVERT functions in the @query string, it doesn't work.......no errormessage.....just nothing. Maybe you could explain that one too?

    Any help would be most appreciated.

    Cheers
  2. Luis Martin Moderator

    When you run using QA, are you using the same POYNTONSQLAdmin?
  3. fatica New Member

    Did you find out what the problem is here? I'm having the exact same issue, except the error is always today's date in YYYYMMDD format.
    Thanks!
  4. satya Moderator

    I believe this is a 2 fold problem, first is to extract data to temp tabel and then email process. So I would say to log the scheduled job completely in order to find the exact problem, also running profiler during the process will give some hint too.
  5. AjayWadehra New Member

    try changin the select to this..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_xLLrequisitionsFROM
    xLLrequisitionsWHERE [Date Processed] IS NOT NULL
    AND
    [Date Processed] <= DATEADD( DAY, -7, GETDATE() )ORDER
    BY [Date Processed]

Share This Page