July 25, 2019 at 1:35 pm
Using 2012 Standard Edition SP1
Bit of a weird one. I have a scalar UDF that takes two dates and finds the difference between them excluding non-working days which are held in a table in the dbo schema
select [dbo].[GetWorkingDaysForRTT] ('2019-03-25','2019-03-26')
For one SQL user account I get the value 0 and for the other I get the value 1
Interestingly, If I take the body of the function and run it in management studio, both accounts will return the same result (0)
I thought it might be down to permissions as one account is sysadmin and the other is a reporting acount, normally restricted to running stored procedures in the dev schema, but has been granted execute rights on the functions to enable the sprocs to run. I have upped the permissions and granting explicit permissions on the table referenced in the UDF and have also upped the reporting account to sysadmin but this does not seem to have fixed the problem.
Any ideas.
TIA
Aaron
July 25, 2019 at 3:23 pm
Any ideas.
I suspect different values of datefirst
SET DATEFIRST
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql
however, it's better to shed light on the function code and show us what it actually does
Using 2012 Standard Edition SP1
it's worth to update to SP4 and latest CU to avoid already forgotten by everyone weird issues
http://sqlserverbuilds.blogspot.com/#sql2012x
July 25, 2019 at 3:45 pm
Hi Andrey, It turned out to be a little bit more subtle than that.
I eventually traced it to the account setup. One was set to Language = British English and the other to English (which equates to us_english). you can test this by doing
select @@Language
As part of the language settings, the start day of the week is different by default (yes SET DATEFIRST will fix this) but the result is that the value returned by
SELECT DATEPART(DW,GETDATE())
will be different and the function tries to find this in an array to see if the DW is a workday and was obviously getting the wrong answer.
After updating the user account, I had to quit all connections (not just SPIDs) and the new settings took effect on the reconnect.
July 25, 2019 at 5:32 pm
Aaron, i'm that you have found the root cause.
I thought about different languages, but assumed that all users on the server have the same language (usually).
Anyway, thanks for the reply. Hope it will help others next time with the similar issues.
July 25, 2019 at 5:35 pm
Hi Andrey, It turned out to be a little bit more subtle than that. I eventually traced it to the account setup. One was set to Language = British English and the other to English (which equates to us_english). you can test this by doing
select @@Language
default language can be checked quickly in sys.server_principals (default_language_name column)
July 25, 2019 at 6:11 pm
If you can provide the code for your function - I am positive someone here can modify the code to be language agnostic. The code should not be affected by the language of the user when determining working/non-working days.
Ideally - you would have a calendar table that defines those attributes so you don't have to calculate it every time. However - if it is a simple enough calculation it shouldn't cause any significant performance issues if written appropriately.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply