August 21, 2013 at 5:52 am
The following is performed on a SQL Server 2012 SP1 installation.
I have been having an issue with stored procedure that performs a ‘sliding window’ solution on a partitioned table that is partitioned by date. The procedure will successfully execute in SSMS but identical TSQL executed in a SQL Agent job will fail with the following error:
Executed as user: NT SERVICE\SQLAgent$SQL2012. The specified partition range value could not be found. [SQLSTATE 42000] (Error 7715). The step failed
The dynamic SQL statement it fails on is:
[ALTER PARTITION FUNCTION SharepointStatsPF() MERGE RANGE ('01/09/2010');
I have identified the cause of my problem; all logins explicitly created have the default language set to ‘British English’ whereas the ‘NT SERVICE\SQLAgent$SQL2012’ login, created by the installation process has the default language set to ‘English’. Changing this to ‘British English’ resolves the problem.
My question is, if the default language of the ‘NT SERVICE\SQLAgent$SQL2012’ login be permanently left as British English, would this cause problems elsewhere?
August 21, 2013 at 12:32 pm
I've never done anything with multiple languages in SQL server so I can't tell you if the change to the agent account will be a problem, but since it caused this problem it probably could cause other problems if changed.
I'd suggest using the ISO standard for the date yyyymmdd and this problem will go away without making changes to users.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 21, 2013 at 4:00 pm
It should not cause a problem if you stop playing with fire and stop using unsafe date formats. Stick to YYYYMMDD, which is always interpreted the same way.
So in two years you migrate to a new server, everyone has forgotten this hiccup, and you forget to change the system locale to English (United Kingdom) before you install SQL Server (which is probably what happned in this case) and the problem reoccurs.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 22, 2013 at 1:04 am
Thanks for the feedback. I will follow the YYYYMMDD route
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply