January 9, 2012 at 8:41 am
I have several SQL Agent Jobs that run everyday. I've just added another, and have encountered an issue...
If I run my query manually all the case statements work in the way I wrote them, but for some reason when the same query runs through SQL Agent it ignores things in my CASE Statement and therefore my results differ.
I am connecting to a remote server through Linked Server, would this affect it? I don't know why it would as the other jobs would be affected if that was the case
This is an example
CASE
WHEN DATEPART(dw, c2.dateofrefe+1) = 1 THEN (c2.dateofrefe - 6)
WHEN DATEPART(dw, c2.dateofrefe+1) = 2 THEN c2.dateofrefe
WHEN DATEPART(dw, c2.dateofrefe+1) = 3 THEN (c2.dateofrefe - 1)
WHEN DATEPART(dw, c2.dateofrefe+1) = 4 THEN (c2.dateofrefe - 2)
WHEN DATEPART(dw, c2.dateofrefe+1) = 5 THEN (c2.dateofrefe - 3)
WHEN DATEPART(dw, c2.dateofrefe+1) = 6 THEN (c2.dateofrefe - 4)
WHEN DATEPART(dw, c2.dateofrefe+1) = 7 THEN (c2.dateofrefe - 5)
END WeekOfReferral
if a referral was referred yesterday 08/01/2012 then the WeekOfReferral should be 02/01/2012. This is the result I get when I run it manually. When it runs through SQL Agent then the WeekOfReferral changes back to the original 08/01/2012.
Why would this be?
January 9, 2012 at 8:57 am
I would guess this is caused by a difference in the local settings for each user - you and the user that connections to the linked server are made as. Check what the language is for your login, and for the linked server user. Are they different?
By the way, you don't need a case expression for that query. If you use the modulo (%) operator, you can write it in one line. That wouldn't stop you having the issue you're having, though.
John
January 9, 2012 at 9:08 am
Any reasons for executing a job on Linked Server? Does the job work per expectations on Local Server (where it is created)?
January 9, 2012 at 9:19 am
John Mitchell-245523 (1/9/2012)
I would guess this is caused by a difference in the local settings for each user - you and the user that connections to the linked server are made as. Check what the language is for your login, and for the linked server user. Are they different?By the way, you don't need a case expression for that query. If you use the modulo (%) operator, you can write it in one line. That wouldn't stop you having the issue you're having, though.
John
Thanks for the reply, I'll check those settings.
The case statement is part of a larger query, I just took the part that was the issue out and shown in my post
January 9, 2012 at 9:24 am
Dev (1/9/2012)
Any reasons for executing a job on Linked Server? Does the job work per expectations on Local Server (where it is created)?
Yes, because the Linked Server has different security settings, and we are only granted read only. So to get around these issues, I have set up linked server on our own server to feed tables of data that we use and have our own levels of security
Long Story but this is the easiest way of doing it
January 9, 2012 at 9:31 am
I understood you don’t have access to another server except read / execute. But my question is still unanswered. Can someone verify that the job runs successfully on the Another Server? If we are sure the jobs runs & give the desired results on local server, we can narrow down the debugging to linked server settings only.
January 9, 2012 at 4:41 pm
Dev (1/9/2012)
I understood you don’t have access to another server except read / execute. But my question is still unanswered. Can someone verify that the job runs successfully on the Another Server? If we are sure the jobs runs & give the desired results on local server, we can narrow down the debugging to linked server settings only.
Sorry, yes the job runs fine, its only the bit around the case statement that differs whether its run as a job or not
January 10, 2012 at 2:51 am
jez.lisle (1/9/2012)
John Mitchell-245523 (1/9/2012)
I would guess this is caused by a difference in the local settings for each user - you and the user that connections to the linked server are made as. Check what the language is for your login, and for the linked server user. Are they different?By the way, you don't need a case expression for that query. If you use the modulo (%) operator, you can write it in one line. That wouldn't stop you having the issue you're having, though.
John
Thanks for the reply, I'll check those settings.
The case statement is part of a larger query, I just took the part that was the issue out and shown in my post
I have checked the languages on both my Server and the Linked Server, My Server was set to BritishEnglish for my Login and for my Login on the LinkedServer it was just English. I have changed my Server Login to English and am trying the Job again to see what that does.
Why would there be a difference from BritishEnglish to English?
If this doesnt sort the problem what other ways could I do this?
January 10, 2012 at 2:59 am
Why would there be a difference from BritishEnglish to English?
Because it can cause the value of DATEFIRST to be different, which is what your CASE expresssion relies on.
Have a read of this[/url] and the ensuing discussion.
The case statement is part of a larger query, I just took the part that was the issue out and shown in my post
Doesn't matter - if you wish to, you can still simplify it in the way I suggested.
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply