Weekday different executing procedure directly or executing by SQL Server Agent

Question

Post reply

Weekday different executing procedure directly or executing by SQL Server Agent

jorgesixto

SSC Rookie

Points: 34
More actions
September 23, 2024 at 8:40 am

#4461435

Hi
I have a little big problem.
I have a job that uses the Weekday for calculate the two year change hour days.
When I execute the procedure in management studio the result of Monday shows the number 7 of weekday, when execute with SQL SERVER Agent, Monday show day 1 of weekday. I check the two windows users, mine and the windows user (I created a windows user that run the SQL Server and SQL Server Agent services), an all are configured that Monday is first day of week. All region and dateformats are the same.
Someone knows where are the "config flag" for this?
Actually I executed first the SET DATEFIRST, for jump this problem, but I want to know where is the correct configuration.
Thanks a lot to all.
Jorge

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

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Ed B Hall of Fame Points: 3646 More actions · Answer 2

I think it's based on the default language for the login which can differ from the instance default language.

If the default is us_english then datefirst = 7 but most European countries have datefirst =1.

select *
from sys.syslanguages

I found this query that I think shows the default language for logins, but I could be wrong.

Perhaps the account that sql agent is running under has a different setting.

select  [name], [type_desc],
        default_language_name,default_database_name 
from sys.server_principals 
where [type] not in ('R','C')

jorgesixto SSC Rookie Points: 34 More actions · Answer 3

Hi,

All the logins are SQL Server login. With the same user. SQL Server and SQL Server Agent services are initiated by same Windows user.

The only diference is run procedure directly from SQL Server Management Studio or run JOB in Agent.

At this moment I updated all procedures with SET DATEFIRST

jorgesixto SSC Rookie Points: 34 More actions · Answer 4

Wait a moment. I don't know what user uses SQL Server Agent by default... .is possible this is the problem...

Ken McKelvey SSCoach Points: 18830 More actions · Answer 5

SELECT servicename, service_account
FROM sys.dm_server_services;

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 6

If you would post the code you're using, perhaps we could solve all of your problems with a bit of a magic temporal formula that doesn't rely on any settings whatsoever.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)