Post reply

T-SQL works in QA but fails when part of a job

  • October 19, 2004 at 2:13 am

    #161662

    HI,

    the title says it all really, the update statement is below and i would be greatful for any comments on why this happens.

    thanks, ade

    --Change Links from class to course

    UPDATE t1

    SET t1.e_parent = t2.e_id

    FROM dbo.capd_moduleenrolment t1 LEFT OUTER JOIN

    dbo.capd_examsubjectentry t6 ON t6.ese_id = t1.e_id LEFT OUTER JOIN

    dbo.capd_moduleenrolment t5 ON t1.e_parent = t5.e_id INNER JOIN

    dbo.capd_person t3 ON t1.e_student = t3.p_id LEFT OUTER JOIN

    dbo.capd_student t4 ON t4.s_id = t3.p_id INNER JOIN

    dbo.capd_module t8 ON t5.e_module = t8.m_id INNER JOIN

    dbo.capd_moduleenrolment t2 ON t3.p_id = t2.e_student INNER JOIN

    dbo.capd_offering t7 ON t8.m_id = t7.o_destination INNER JOIN

    dbo.capd_module t11 ON t2.e_module = t11.m_id INNER JOIN

    dbo.capd_module t9 ON t7.o_source = t9.m_id INNER JOIN

    dbo.capd_offering t10 ON t11.m_id = t10.o_source AND t10.o_destination = t9.m_id

    WHERE (t6.ese_examsubjectreference IS NOT NULL) AND (t6.ese_examsubjectreference NOT LIKE '%cref%%') AND (t1.e_status = dbo.ToChar(1, NULL)) AND

    (t6.ese_subjectentryyear LIKE 2005) AND (t5.e_type = 'CL')

    GO

    ---------------

  • October 19, 2004 at 2:20 am

    #527131

    In what way does it 'fail' then..? Errormessages? Nothing happens? Other things than the expected happens? Permission problems? Object not found problems?

    .. there are quite a few reasons as to what can make something fail...

    /Kenneth

  • October 19, 2004 at 2:29 am

    #527133

    the only error message i get is that the job failed, the last step to run was step1 and the job was requested to run from step 1.

    there is definitley no permission problems

    ade

  • October 19, 2004 at 2:38 am

    #527135

    Well, if I had a penny every time someone said 'it's definetly not this is that'...

    In any case - when you run it successfully in QA, are you then logged in in QA as the same user that the SQL Agent service is using?

    Is the job setup in the context of the correct database?

    If the job fails there should be some output - try specifying an output file for the job and see what gets caught in there...

    /Kenneth

  • October 19, 2004 at 2:57 am

    #527141

    no penny from me this time as it was DEFINITLY not a permissions problem.

    However thankyou very much for pointing me to the output files, there was a syntax error in the statement concerning the 'GO' Command, when i took this out it worked a treat.

    Im not sure why 'GO' was causing the error though as it did work in QA just fine!?

    Thanks again, ade

  • October 19, 2004 at 7:16 am

    #527171

    'GO' is actually not a Transact SQL command, it's just a symbol marking 'end of batch'.

    Apparantly it was in the 'wrong' place for you when placed into the job.

    /Kenneth

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

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