There are a few things going on here. First, yes, it does appear that SSMS (at least as of versions 18.11.1, 19.3, and 20.2) is upper-casing the instance name when it generates the statements for creating a SQL Agent Job (and doing so while not using the locale indicated by the instance-level collation). Upper-casing the instance name is an unnecessary step, which is also problematic as it creates a situation such as this. It would also create a similar error in cases involving instance names containing lower-case characters and instances using either case-sensitive or binary collations. (upon reviewing the code for sp_add_jobserver, I have determined that this behavior will not happen with case-sensitive or binary collations unless they are either Turkish or Azeri)
The problem, for anyone not quite sure what is going here, is this:
- The instance name contains a lower-case "i" (internally)
- When creating a SQL Agent job, SSMS pulls the internal instance name and upper-cases it, but not using the rules of the locale specified by the instance-level collation (meaning, the "i" becomes "I" when it should become "İ" in a Turkish or Azeri collation)
- The incorrectly upper-cased value is then passed into
msdb.dbo.sp_add_jobserver
- On line 42 of that stored proc, the already upper-cased, passed-in value is compared to
UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))), which also pulls the internal name (containing a lower-case "i") but upper-casing it locally which does respect the rules of the locale specified by the server-level collation (since msdb, being a system database, will in all but a few unintended cases, use the same collation as the server / instance). Meaning, the lower-case "i" becomes "İ" in a Turkish or Azeri collation.
- Since "I" <> "İ", the job is assumed to be an MSX job which then tries to look up the passed-in server name in
msdb.dbo.systargetservers on lines 83 - 85.
- Since the passed-in value is not in
msdb.dbo.systargetservers, the proc returns error 14262:
The specified @server_name ('<hostname>\<incorrectly_uppercased_instance_name>') does not exist.
Now, this problem is probably encountered rather infrequently because it's not that easy to create an instance with lower-case letters (and use either a Turkish or Azeri instance-level collation!). Using the Install Wizard, it will upper-case the instance name when you click the Next button. I'm not sure if older install wizards did that or not. If they didn't, then perhaps you can have a SQL Server 2019 instance with a lower-case name (internally, more on that in a moment) if you upgraded from a lower version that had a wizard that didn't upper-case the name. Otherwise, the only other way I can think of is to create the instance using the command line and a config file (unattended install?).
Why does the name column in sys.servers as well as the @@SERVERNAME global variable show a different casing than SERVERPROPERTY('servername')? And, why doesn't dropping and re-adding the server via sp_dropserver and sp_addserver fix that difference as well as the "create job" problem when re-added with all upper-case letters? Because:
- along with storing the name in
sys.servers, the name is also stored in the registry,
- the instance name in the registry isn't updated when re-adding the server via
sp_addserver,
@@SERVERNAME returns the name column in sys.servers while SERVERPROPERTY('servername') returns the value from the registry, and- SSMS is clearly pulling the value from
SERVERPROPERTY('servername').
Based on the four points noted directly above (which I have verified through testing), the first three explain why updating the server / instance name via sp_dropserver and sp_addserver (and restarting the instance) will cause the value returned by @@SERVERNAME to change but not the value returned by SERVERPROPERTY('servername').
And, just to be clear, the value stored in the registry (and returned by SERVERPROPERTY('servername')) is the official instance name, not the value stored in sys.servers (and returned by @@SERVERNAME). According to the documentation for sp_addserver, the official instance name can't be changed except by re-installing the instance:
The instance name of the Database Engine can't be changed. To change the instance name of a named instance, install a new instance with the desired name, detach the database files from old instance, attach the databases to the new instance, and drop the old instance.
This is likely due to how many places the instance name is used in the registry. HOWEVER, it is technically possible to change the instance name (via the registry) if you are merely changing the casing of the name. At least that appears to have worked for me, but I'm also not running a broad / extensive range of tests on SQL Server to find all potentially affected code paths: for me, the instance runs and I can still do basic things and haven't yet experienced any unexpected behavior. So, assuming the obligatory warning that editing the registry can cause unexpected system failures and/or unexpected behaviors and you should, at the very least, make a backup of the registry prior to manually editing it, I will point out that the instance name is stored in the following registry location:
- Key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<version_num>.<instance_name>
- Value:
(Default)
- Data:
<instance_name> (this is the value returned by SERVERPROPERTY('servername'))
In the Key name, <version_num> is:
- SQL Server 2012 = 11
- SQL Server 2014 = 12
- SQL Server 2016 = 13
- SQL Server 2017 = 14
- SQL Server 2019 = 15
- SQL Server 2022 = 16
You can view the registry value by going directly to that Key in RegEdit, using the reg query DOS command, or using the undocumented xp_regread extended stored proc as follows (in the example below, my instance name is SQL2019):
DECLARE @Out NVARCHAR(4000);
EXEC sys.xp_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.SQL2019',
N'', -- empty string = (Default) value
@Out OUTPUT;
PRINT @Out;
So, if you were to edit the registry, all you would need to do is replace any lower-case "i" with a Latin upper-case "I". Or, you can just upper-case the entire instance name so it will match what you have in sys.servers / @@SERVERNAME (probably the better choice).
The other option is to completely re-install the instance, this time making sure that the instance name is in all upper-case letters.
You can, and probably should, post this as a bug to Microsoft in their "Feedback" forum because it really shouldn't be upper-casing the instance name:
https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0
