T-SQL works in QA but fails when part of a job
-
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
---------------
-
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
-
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
-
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
-
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
-
'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