search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

DBCC check fails due to case difference in server name on CS collation server #722

Open Pineapple-lumps opened 1 year ago

Pineapple-lumps commented 1 year ago

Description of the issue A DBCC check fails when the server is using a case sensitive collation. This is because the @@servername is in lowercase, whereas SERVERPROPERTY('ServerName') is in upper case.

SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU18-GDR) (KB5021124) - 15.0.4280.7 (X64)

Version of the script Version: 2022-12-03 17:23:44

What command are you executing? [dbo].[DatabaseIntegrityCheck]

What output are you getting? Msg 50000, Sev 16, State 1, Line 1017 : The @@SERVERNAME does not match SERVERPROPERTY('ServerName').

ghost commented 1 year ago

It's been like this for long. One solution could be to LOWER( ) both values...but sql_variant...

The workaround I'm using is to create and run OH skripts in a DBMaint database that is CI_AS. A better approach than running it in master. Keep master clean and small.

Pineapple-lumps commented 1 year ago

Thanks IverJoc. I'd modified the SP to use UPPER on both values. Which essentially does the same thing.

Good tip about changing the db collation. I assume you haven't noticed any collation conflicts?

Stiffboard commented 1 year ago

Hi, did UPPER work without any issues? That's good. I haven't experienced any other collation related conflict. The problem is Case sensitive.