Solomon Rutzky

@PaulWhite If the OP is using a default instance such that the "server name" is just the hostname, it must be sending a lower-case "i", even if in most scenarios it would be pre-upper-cased according to the character set (as noted in the doc you linked). If SSMS were retrieving a pre-upper-cased "i" that was upper-cased according to non-Turkish rules, then it wouldn't be affected by the UPPER() function in sp_add_jobserver, and the error would not occur.

@PaulWhite Thanks for that info. However, I don't believe that's impacting this particular issue. In order for the OP's error to occur, a lower-case "i" needs to be returned by SERVERPROPERTY('ServerName') such that it can be upper-cased in two different ways. While I can't test with a hostname containing a lower-case "i", I am testing with a named instance containing a lower-case "i" and can see that SSMS is indeed upper-casing it incorrectly when generating the sp_add_jobserver statement.

@halid.sert Just updated my answer, please review..

@halid.sert I figured out the issue, why you are seeing different casing between @@SERVERNAME and SERVERPROPERTY('servername'), and a possible fix. Will post tomorrow as I'm out of time for today.

@halid.sert Those questions might matter. You shouldn't be seeing casing differences in the output of those three methods of getting the Server Name. I was thinking that maybe the issue was with SSMS -> script generation as well, but that wouldn't explain why either @@SERVERNAME and/or SERVERPROPERTY('servername') had different casing. I will try to do a little more testing in the morning.

@halid.sert Just to confirm: both [name] FROM sys.servers and @@SERVERNAME return upper-case, while SERVERPROPERTY() returns lower-case? If true, that is very odd. I thought that @@SERVERNAME and SERVERPROPERTY('servername') both took the value from ``sys.servers`.

@halid.sert Thanks again for the info. When was the last time you restarted the instance? Can you restart the instance again and re-execute the two server name-based queries in your comment directly above this one. Is the output of @@servername, serverproperty('servername') different after the restart?

@halid.sert Thanks for that info. Yes, instance-level collation and system DB collation can be different, typically the result of restoring a system DB created on an instance using a different collation. Does the output of this query show the same values as the first query in my comment above: SELECT [name], CONVERT(VARBINARY(128), [name]) FROM sys.servers WHERE [server_id] = 0;. Also, what is the output of: SELECT DATABASEPROPERTYEX(N'master', 'collation');? Is it also "Turkish_CI_AS"?

Hi there. Something seems a little off here. Are you saying that you can't execute the job creation script on the same instance that the script was generated from? Which "i" character is in your @@SERVERNAME? Please execute SELECT @@SERVERNAME, CONVERT(VARBINARY(128), @@SERVERNAME); to verify (and update the question with the query result if you are allowed to). Is the name all upper or lower-case, or mixed? Also, please update the question with the output of this query: SELECT DATABASEPROPERTYEX(N'msdb', 'collation') AS [DB], SERVERPROPERTY('collation') AS [Instance];.

Let us continue this discussion in chat.

@user9516827 what type of functions? T-SQL or SQLCLR or built-in? UDFs or TVFs? Also, are those unique session IDs, or are they shared with entries that do have login names filled out? Maybe remove the WHERE ses.[login_name] = N'' and instead add ORDER BY ses.[session_id].

@user9516827 Interesting. Only correlation I see so far is that the "empty" login names are currently running, while the rows with login names are currently sleeping (with 1 exception). Also, I did some testing on my own related to connection pooling, and this behavior does not seem to be due to that.

@user9516827 Not sure which columns are most useful at this point, but at least: status, command, last_request_end_time, host_name, program_name, and host_process_id. Also, need to get at least one row of those where login name is the value that is showing up with 70 connections (to compare with).

@user9516827 Can you at least execute this query: SELECT con.*, '--' AS [---], ses.*, '--' AS [---], req.* FROM sys.dm_exec_connections con INNER JOIN sys.dm_exec_sessions ses ON con.[session_id] = ses.[session_id] LEFT JOIN sys.dm_exec_requests req ON req.[session_id] = con.[session_id] WHERE ses.[login_name] = N''; ? Is there anything currently running for those? I am wondering if this is due to connection pooling.

@H.79 and user: yes, we know that from the original query / screen shot based on the SID being the same. What we need to figure out is why 120 rows for that login are missing the login_name value, but the other 70 rows for the same login have it filled out as expected.

@H.79 Yes, very much so. The documentation, docs.microsoft.com/en-us/sql/relational-databases/…, even confirms that it was only included in SQL Server 2005 and newer for backwards compatibility and should not otherwise be used.

For the moment, try either SELECT * FROM sys.sysprocesses WHERE [loginame] = N''; and/or SELECT * FROM sys.dm_exec_connections con INNER JOIN sys.dm_exec_sessions ses ON con.[session_id] = ses.[session_id] LEFT JOIN sys.dm_exec_requests req ON req.[session_id] = con.[session_id] WHERE ses.[login_name] = N''; to get a sense of what is going on. No need to aggregate result when debugging as you lose all of the details.

@H.79 and user9516827: It would likely be even better to just stop using the very old and deprecated as of SQL Server 2005 compatibility views (i.e. the ones starting with sys.sys*. It is far better to use some combination of: sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests, and sys.server_principals.

Hello Tibor (and @DanGuzman ). Just to follow up on this: the MS doc team updated the "Deprecated database engine features in SQL Server 2019" page a few days ago. It now states: "SQL Server 2019 (15.x) does not deprecate any features beyond those deprecated in prior releases:" (emphasis added) followed by links to the 2016 and 2017 deprecated features pages.

Tibor: yer welcome :-). And correct, the guidelines and query were also added in this most recent update. You can see all of the additions in this commit: github.com/MicrosoftDocs/sql-docs/commit/…

Hi Tibor. I understand what you're saying, though I still disagree. However, it occurs to me that we don't need to compare interpretations when Microsoft should simply tell us what they mean. So, I have asked the documentation team to update the page that you linked to (regarding SQL Server 2019 not deprecating any features) with explicit wording: What exactly does "SQL Server 2019 does not deprecate any features" mean?. And now we wait 🙃.

Tibor (and @DanGuzman ): Features can be deprecated and never removed. Deprecated indicates that a feature has been superseded by something better and is best to not use. MS is still deprecating features as can be seen in this discussion I had with them early last year (I hadn't remembered it when I commented earlier): github.com/MicrosoftDocs/sql-docs/issues/3989 . Also, Azure SQL isn't exactly "versionless", they're just the most current version with deprecated features not being removed to improve backwards compatibility. @@MICROSOFTVERSION should report the correct value.

Tibor: Deprecation doesn't guarantee removal; the doc states the "feature is in maintenance mode only. No new changes will be done, including those related to inter-operability with new features...For new development work, we do not recommend using deprecated features.". That GitHub issue was them adding the deprecation notice. Features like NTEXT are definitely deprecated even if never removed. And, SQL Server still tracks deprecated features: DECLARE @ INT; SELECT * FROM sys.dm_os_performance_counters WHERE [object_name] LIKE N'%:Deprecated Features%' ORDER BY [cntr_value] DESC;

@DanGuzman and Tibor: I think you're misreading the SQL 2019 doc (due to MS being forgetful when creating that page). It should have the same note at the top that the 2017 page has: "This list is identical to the SQL Server 2016 (13.x) list. There are no *new deprecated or discontinued Database Engine features announced for SQL Server 2019 (15.x).*" (I adjusted for 2019 and emphasized). I think the context makes it clear that the 2016 list is still deprecated given that it also includes TEXT / NTEXT types, numbered procedures, system tables, etc. I definitely recommend using semicolons.

@SteveC Ha! I wish. No, just a frustratingly slow / chaotic year due to lock-down and kids in elementary school at home all day doing "remote learning". But, glad to hear that my articles and answers have been helpful, and that you like my SQL# SQLCLR library :-). I definitely appreciate all of that positive feedback 😺

@SteveC Sounds and looks interesting. Sure, we should do that sometimes. Things are kinda crazy busy at the moment, but hopefully that won't last forever ;-). I didn't see any contact info for you either here or on GitHub, but I can be reached via: sqlquantumleap.com/contact

@SteveC That did throw me for a loop as well at first. But the parser I tried gave me the character position that it didn't like and it was the newline added by SSMS, so I removed it and it next complained about the next newline. That's when I tried storing the JSON output as NVARCHAR(MAX). But still, why even use JSON in the first place?

@SteveC Hey. So, I'm not meaning to drag this out since you said that it's working for you. I'm just saying that it appears to me that using either XML or JSON to get the definition back to C# is unnecessary overcomplication. I could be wrong as I'm not seeing what you're seeing, but this question was addressing behavior specific to SSMS; returning OBJECT_DEFINITION() should work in all other cases.

@SteveC Also, I'm still not sure why you aren't just returning SET NOCOUNT ON; SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_helptext')) AS [def]; as NVARCHAR(MAX). It's possible I'm missing something as I'm not familiar with either OpenAPI swagger.json files or the .NET Core WebAPI. It just seems like wrapping up as either XML or JSON only to parse it back to a string is two additional, yet unnecessary, steps.

@SteveC Regarding the JSON output, there might be a bug in how it outputs to both Results to Text and Results to File as it appears to chop the result into lines of 2048 chars each for some reason. That is what breaks the online JSON parsers as I just tried that myself and it only worked when I removed the actual newlines (not the escaped ones). I found that storing the JSON output to NVARCHAR(MAX) allowed it to work in both cases: SET NOCOUNT ON; DECLARE @B NVARCHAR(MAX) = (select OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_helptext')) AS [def] FOR JSON PATH); SELECT @B;

@SteveC Again, glad it's working, but I'm still confused ;-). Returning the output of OBJECT_DEFINITION() shouldn't remove any formatting, and should be identical to the XML except it wouldn't start with <Code><![CDATA[ and end with ]]></Code>. This question is specific to viewing the definition within SSMS, which flattens everything to one row (Results to Grid), or cuts off at 8k characters (using PRINT). Results to Text used to have a lower limit but now can return up to 2 MB: SET NOCOUNT ON; SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_helptext'));. XML, however, can be unlimited.

Hi @SteveC . Yer welcome, glad it worked for you. However, if you are not viewing it in SSMS, then why bother with any of this in the first place? Simply getting the return value from OBJECT_DEFINITION(OBJECT_ID(N'schema_name.object_name')) should be sufficient. Meaning, converting that output to XML is most likely an unnecessary step for your purposes; the only reason this method is being used in this context is to preserve formatting while viewing in SSMS.

In either case, I am for the moment out of both time and ideas...

@MaxB well, that's less than helpful. I think we have stumbled upon something significant. Though maybe you are using a feature in Standard Edition that isn't available in Express, but I don't think it would result in that "error on page" message. Besides, was this actually a brand new copy only backup, or an existing backup? And are you restoring into Standard or Express Edition?

@MaxB You might want to try the following on both good and bad DBs: DBCC CHECKDB(N'problem_db') WITH EXTENDED_LOGICAL_CHECKS;

Also, can you take a copy only backup of the working DB on the good instance and restore that as a different name to the instance with the bad DB?

@MaxB perhaps we found the problem ;-) what exact DBCC CHECKDB statement did you run to analyze the problem DB? perhaps a different setting would have detected this?

@MaxB Interesting... You restored into the same instance, right, hence the need for a different name? Can you try my other suggestion of restoring into a different instance, even SQL Server Express 2017 running on your local machine... you said the DB was only 5 GB so that should work given the 10 GB max for Express Edition..

Also, do you by any chance have resource governor running? and please use the "reply" link on the left side of the message so it sends a notification :)

@MaxB Yes, I was considering asking you to restore the problem DB to the same instance as a different DB name (be sure to change folders, etc for restored data and log files)....

@MaxB Also, can you install a new instance of SQL Server 2017 on a different computer (it can even be 2017 Express) and restore the DB to that instance and see if it works?

@MaxB just to confirm, all DB properties are the same between working and non-working DB?

@MaxB Ok, now it's time to uncomment the , * (i.e. line 2) in my sys.databases query and compare between working and non-working DBs..

@MaxB does it match on the system that works?

@MaxB either way, the SID in the DB for "dbo" needs to match the owner_sid reported by sys.databases

@MaxB Then you likely need to sync the dbo owner SID in the restored (i.e. problem) DB with the local logins SID, but first we should check the DB itself using:

@MaxB are the owners Windows logins or SQL Server logins?