Cannot resolve collation conflict for equal to operation

[SQLWilts]

Hi,
Odd one here:
We have a number of SQL 2000 instances which we use SSMS to manage. Normally there are no problems, but on this occassion we have hit an odd problem.

When we right click on any (system or user) database we get the "Cannot resolve collation conflict for equal to operation" error. Running sp_helpdb on all of the system databases shows them to all have the same collation (I thought that tempdb may be different).

Any clues what may be causing this? Is it likely to cause any other problems? I haven't noticed anything else causing problems, but then I tend to run everything from script, which seem to work fine.

 

[SQLWilts]

Oddly, it is only happening in SSMS. EM works fine!! Curiouser and curiouser...

 

[gmmastros]

sp_helpdb will only show the default collation for the database. It's possible to have a column in a table that does not match the default collation.

There are 3 levels of collations to deal with.

There is a default collation for the server which is used when you create a new database without specifying the collation.

There is a default collation for each database which is used when you create a "string" column (or variable) without specifying the collation.

There is a collation for each string column.

The following code will show you what all the defaults are (for the server and databases) and what the actual collation is for each object. This will not solve your problem, but hopefully it will help you find it.

BTW, this takes about 5 seconds to run on my server with about 20 databases, most containing LOTS of "string" columns. I have 35,000 rows in the output.

Create Table #Temp(Name VarChar(500), Collation VarChar(100))

Insert Into #Temp
Select 'Server Default', Convert(VarChar(100), ServerProperty('Collation'))

exec sp_msforeachdb 
    'Insert Into #Temp 
     Select ''Database Default: '' + ''?'', 
            Convert(VarChar(100), databasePropertyEx(''?'',''Collation''))'

exec sp_msforeachdb 
    'Insert Into #Temp 
     Select Table_Catalog + '','' + Table_Name + '','' + Column_Name, 
            Collation_Name
     From   [?].Information_Schema.Columns
     Where  Collation_Name > '''''

Select * From #Temp

Drop Table #TEmp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLWilts]

Thanks for that George - very useful info.
I have worked with different colations before, and have even walked through that very dark valley of changing the collation of a database mid way through its life, so I know about the varying levels.
My confusion though is why I can right click on a database node in Enterprise Manager and see the properties of any database no problems, but when I try to repeat this in Management Studio I get the collation error.
Very odd.
If I ever get to the bottom of it, I will post back

 

[SQLWilts]

It appears to be the MSDB database. It is the only DB that has a different collation to the rest.

Only way that I know of to change the MSDB collation is - rebuildm!

And we can't do that on this live system

I'm confused, though, why this happens when we look at any database property in SSMS, but is fine in EM...

 

[gmmastros]

I have a "throw away" instance of SQL Server. Can you share some more info. Is ALL of msdb showing a different collation, or is it just part of it (the default vs. columns in tables). I would be willing to mess around with the collation on my local instance to see if it can be changed.

What is your preferred collation, and what collation is MSDB set to?

I suspect that SSMS must be "doing" something different when you look at database properties. You could try running profiler to try and catch what's going on. If you can see the query that is executed against msdb, you may be able to narrow this problem down a little further.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh.... shame on Microsoft.

When you open the properties page for a database, there's lots of information that is shown. One piece of info is the the last time a database was backed up. Guess where this info is stored... Yep. MSDB.

Worse yet... microsoft is using sub-queries. Yuck.

one line of the select clause:

(select max(backup_finish_date) from msdb..backupset where type = 'D' and [!]database_name = dtb.name[/!]) AS [LastBackupDate],

dtb is an alias for master.dbo.sysdatabase
database_name is a column in msdb..backupset

Microsoft is also left outer joining to a temp table in the from clause.

FROM
master.dbo.sysdatabases AS dtb
LEFT OUTER JOIN #tmplog t ON t.dbname = dtb.name

but... when the temp table is built, they don't specify a collation for the temp table, which is built in TempDB. So.... if the collation of the master database does not match the collation of TempDB, you would also get a collation error there, too.

I'm a little fuzzy here, but I think TempDB is recreated every time the SQL Server service is started. I don't know what the default collation of TempDB would be. I suspect it's either the server's default collation, or it could be set to match Master, or it could be set to match Model. Either way... this is also a potential problem.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh yeah.... back to the original question...

I don't have EM installed, so I can't check, but I suspect that the properties page for a database does not show the last time a database was backed up. Or, if it does, there's probably a different query executed to get that information.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

It appears as though there is no easy way to change the collation of the system db's.

When I run profiler and then right click a db -> Properties, SSMS executes this:

				create table #tmplog (dbname sysname null, log_size float null, log_space_used float null, status int) 
				INSERT INTO #tmplog EXEC ('DBCC SQLPERF(LOGSPACE)')
			


SELECT
dtb.name AS [Name],
(select count(*) from master.dbo.sysprocesses p where dtb.dbid=p.dbid) AS [ActiveConnections],
dtb.cmptlevel AS [CompatibilityLevel],
dtb.crdate AS [CreateDate],
CAST(NULL AS float) AS [DataSpaceUsage],
CAST(0 AS bit) AS [DboLogin],
(select top 1 fg.groupname from dbo.sysfilegroups as fg where fg.status & 0x10 <> 0 ) AS [DefaultFileGroup],
'' AS [DefaultSchema],
CAST(dtb.dbid AS int) AS [ID],
CAST(NULL AS float) AS [IndexSpaceUsage],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible],
CAST(0 AS bit) AS [IsDbAccessAdmin],
CAST(0 AS bit) AS [IsDbBackupOperator],
CAST(0 AS bit) AS [IsDbDatareader],
CAST(0 AS bit) AS [IsDbDatawriter],
CAST(0 AS bit) AS [IsDbDdlAdmin],
CAST(0 AS bit) AS [IsDbDenyDatareader],
CAST(0 AS bit) AS [IsDbDenyDatawriter],
CAST(0 AS bit) AS [IsDbOwner],
CAST(0 AS bit) AS [IsDbSecurityAdmin],
CAST(DATABASEPROPERTYEX(dtb.name, 'IsFulltextEnabled') AS bit) AS [IsFullTextEnabled],
CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else category & 16 end AS bit) AS [IsSystemObject],
(select max(backup_finish_date) from msdb..backupset where type = 'D' and database_name = dtb.name) AS [LastBackupDate],
(select max(backup_finish_date) from msdb..backupset where type = 'L' and database_name = dtb.name) AS [LastLogBackupDate],
suser_sname(dtb.sid) AS [Owner],
rtrim(dtb.filename) AS [PrimaryFilePath],
dtb.category AS [ReplicationOptions],
CAST(NULL AS float) AS [Size],
t.log_size*(100-t.log_space_used)*10.24 AS [SpaceAvailable],

case
    -- if all these are false then we are in the Normal state
	-- except some return NULL if it's AutoClosed
    when (DATABASEPROPERTY(dtb.name,'IsInLoad') = 0 and
         (DATABASEPROPERTY(dtb.name,'IsInRecovery') = 0 or DATABASEPROPERTY(dtb.name,'IsInRecovery') is null) and
         (DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 0 or DATABASEPROPERTY(dtb.name,'IsNotRecovered') is null) and
          DATABASEPROPERTY(dtb.name,'IsSuspect') = 0 and
          DATABASEPROPERTY(dtb.name,'IsOffline') = 0 and
          DATABASEPROPERTY(dtb.name,'IsInStandBy') = 0 and
         (DATABASEPROPERTY(dtb.name,'IsShutDown') = 0 or DATABASEPROPERTY(dtb.name,'IsShutDown') is null) and
          DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 0) then 1 
    else 0
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsInLoad') = 1 then 2 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 and 
	     DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 1 then 4 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 then 8 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsSuspect') = 1 then 16 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsOffline') = 1 then 32 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsInStandBy') = 1 then 64 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsShutDown') = 1 then 128 
	when DATABASEPROPERTY(dtb.name,'IsShutDown') is null then (512 + 128)
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 1 then 256 
	else 0
end
			 AS [Status],
'' AS [UserName],
CAST(CHARINDEX(N'_CS_', CAST(DATABASEPROPERTYEX(dtb.name, 'Collation') AS nvarchar(255))) AS bit) AS [CaseSensitive],
CAST(DATABASEPROPERTYEX(dtb.name, 'Collation') AS sysname) AS [Collation],
CAST(( case LOWER(convert( nvarchar(128), DATABASEPROPERTYEX(dtb.name, 'Updateability'))) when 'read_write' then 1 else 0 end) AS bit) AS [IsUpdateable],
CAST(DATABASEPROPERTYEX(dtb.name, 'Version') AS int) AS [Version],
dtb.name AS [DatabaseName],
dtb.name AS [DatabaseName2]
FROM
master.dbo.sysdatabases AS dtb
LEFT OUTER JOIN #tmplog t ON t.dbname = dtb.name
WHERE
(dtb.name=N'pubs')
 
				drop table #tmplog

Can you please open SSMS, open a query window (on one of your user databases), and run this query manually. If I am not mistaken, this query will give you the same collation error.

I think Microsoft could make a few minor changes to allow this query to work.

Try this:

				create table #tmplog (dbname sysname [!]collate Database_default [/!]null , log_size float null, log_space_used float null, status int) 
				INSERT INTO #tmplog EXEC ('DBCC SQLPERF(LOGSPACE)')
			


SELECT
dtb.name AS [Name],
(select count(*) from master.dbo.sysprocesses p where dtb.dbid=p.dbid) AS [ActiveConnections],
dtb.cmptlevel AS [CompatibilityLevel],
dtb.crdate AS [CreateDate],
CAST(NULL AS float) AS [DataSpaceUsage],
CAST(0 AS bit) AS [DboLogin],
(select top 1 fg.groupname from dbo.sysfilegroups as fg where fg.status & 0x10 <> 0 ) AS [DefaultFileGroup],
'' AS [DefaultSchema],
CAST(dtb.dbid AS int) AS [ID],
CAST(NULL AS float) AS [IndexSpaceUsage],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible],
CAST(0 AS bit) AS [IsDbAccessAdmin],
CAST(0 AS bit) AS [IsDbBackupOperator],
CAST(0 AS bit) AS [IsDbDatareader],
CAST(0 AS bit) AS [IsDbDatawriter],
CAST(0 AS bit) AS [IsDbDdlAdmin],
CAST(0 AS bit) AS [IsDbDenyDatareader],
CAST(0 AS bit) AS [IsDbDenyDatawriter],
CAST(0 AS bit) AS [IsDbOwner],
CAST(0 AS bit) AS [IsDbSecurityAdmin],
CAST(DATABASEPROPERTYEX(dtb.name, 'IsFulltextEnabled') AS bit) AS [IsFullTextEnabled],
CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else category & 16 end AS bit) AS [IsSystemObject],
(select max(backup_finish_date) from msdb..backupset where type = 'D' and database_name [!]collate database_default [/!]= dtb.name) AS [LastBackupDate],
(select max(backup_finish_date) from msdb..backupset where type = 'L' and database_name [!]collate database_default[/!] = dtb.name) AS [LastLogBackupDate],
suser_sname(dtb.sid) AS [Owner],
rtrim(dtb.filename) AS [PrimaryFilePath],
dtb.category AS [ReplicationOptions],
CAST(NULL AS float) AS [Size],
t.log_size*(100-t.log_space_used)*10.24 AS [SpaceAvailable],

case
    -- if all these are false then we are in the Normal state
	-- except some return NULL if it's AutoClosed
    when (DATABASEPROPERTY(dtb.name,'IsInLoad') = 0 and
         (DATABASEPROPERTY(dtb.name,'IsInRecovery') = 0 or DATABASEPROPERTY(dtb.name,'IsInRecovery') is null) and
         (DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 0 or DATABASEPROPERTY(dtb.name,'IsNotRecovered') is null) and
          DATABASEPROPERTY(dtb.name,'IsSuspect') = 0 and
          DATABASEPROPERTY(dtb.name,'IsOffline') = 0 and
          DATABASEPROPERTY(dtb.name,'IsInStandBy') = 0 and
         (DATABASEPROPERTY(dtb.name,'IsShutDown') = 0 or DATABASEPROPERTY(dtb.name,'IsShutDown') is null) and
          DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 0) then 1 
    else 0
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsInLoad') = 1 then 2 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 and 
	     DATABASEPROPERTY(dtb.name,'IsNotRecovered') = 1 then 4 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsInRecovery') = 1 then 8 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsSuspect') = 1 then 16 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsOffline') = 1 then 32 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsInStandBy') = 1 then 64 
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsShutDown') = 1 then 128 
	when DATABASEPROPERTY(dtb.name,'IsShutDown') is null then (512 + 128)
	else 0 
end |
case 
	when DATABASEPROPERTY(dtb.name,'IsEmergencyMode') = 1 then 256 
	else 0
end
			 AS [Status],
'' AS [UserName],
CAST(CHARINDEX(N'_CS_', CAST(DATABASEPROPERTYEX(dtb.name, 'Collation') AS nvarchar(255))) AS bit) AS [CaseSensitive],
CAST(DATABASEPROPERTYEX(dtb.name, 'Collation') AS sysname) AS [Collation],
CAST(( case LOWER(convert( nvarchar(128), DATABASEPROPERTYEX(dtb.name, 'Updateability'))) when 'read_write' then 1 else 0 end) AS bit) AS [IsUpdateable],
CAST(DATABASEPROPERTYEX(dtb.name, 'Version') AS int) AS [Version],
dtb.name AS [DatabaseName],
dtb.name AS [DatabaseName2]
FROM
master.dbo.sysdatabases AS dtb
LEFT OUTER JOIN #tmplog t ON t.dbname = dtb.name
WHERE
(dtb.name=N'pubs')
 
				drop table #tmplog

changes highlighted in red.

Just for completeness sake, I am using SSMS 2005 Version 9.00.3042.00 (as displayed in Help -> About).



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLWilts]

George,

I think that you have hit the nail on the head in your bit about:

(select max(backup_finish_date) from msdb..backupset where type = 'D' and database_name = dtb.name) AS [LastBackupDate],

As you intimated, it appears that it was built by an apprentice in his first year!

In fact mate - SPOT ON!!

I changed the trigger that sits on the "backupset" table to use "collate SQL_Latin1_General_CP1_CI_AS) on the database name comparison and altered the column "database_name" to use the collation SQL_Latin1_General_CP1_CI_AS.

Works now...have a star. Thanks loads.

I do wonder, though, how msdb ended up with a different collation in the first place...

Thanks again George