Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Collation issue with CommandLog #151

Closed
sqlslinger opened this issue Oct 10, 2018 · 4 comments

Comments

Projects
None yet
3 participants
@sqlslinger
Copy link

commented Oct 10, 2018

Potential issue with Collations on compile of DatabaseIntegrityCheck. Please see conversation below. Issue was resolved when the collation was checked for columns in the CommandLog table, and it was seen that an unusual collation was defined for those CommandLog columns. Changing the collation for those table columns resolved the issue.

However, Ola felt a different collation should not cause this error, and asked us to post an issue.

Transcript below:


From: Ola Hallengren <ola@hallengren.com>
Date: October 4, 2018 at 5:29:59 PM EDT
To: Ralph Joseph <rbj_1931_4310@yahoo.com>
Subject: Re: DatabaseIntegrityCheck: compile error
It is good that you found the issue.

However I think that this is still a bug in the script. It should work with different collations. Could you report it on GitHub.
https://github.com/olahallengren/sql-server-maintenance-solution/issues

/Ola



On Thu, 4 Oct 2018 at 23:23, Ralph Joseph <rbj_1931_4310@yahoo.com> wrote:
Hi Ola,
Good catch as to where to look!
The error in the compile was with the join to CommandLog.

So I checked CommandLog.

SELECT c.name,
       c.collation_name
  FROM SYS.COLUMNS c
  JOIN SYS.TABLES t ON t.object_id = c.object_id
WHERE t.name = 'commandlog'
 
The wrong Collation was in the table columns. Not sure how this happened.

Changed the collation and now everything works fine.

Thanks for all your help.
And sorry for all the trouble.
Now I know what to check.

Thanks again,
Ralph.

On Oct 4, 2018, at 4:43 PM, Ola Hallengren <ola@hallengren.com> wrote:
Do you understand, where the collation Latin1_General_CI_AI comes from?

/Ola

On Thu, Oct 4, 2018 at 10:28 PM, Ralph Joseph <rbj_1931_4310@yahoo.com> wrote:
No modifications. 
Absolutely no modifications are made to your code, so we can easilly drop in the updates without any changes on our side.

This is just a view on the CommandLog table without the xml column. Because a linked server does not permit this.

However, the error would be the same on the following join:

from master.sys.databases d
left join master.dbo.CommandLog c on c.DatabaseName = d.name 

Sorry for confusing you and bothering you.
It has something to do with the server collation. I am trying to track this down.
Only one server out of hundreds has this problem!


On Oct 4, 2018, at 4:18 PM, Ola Hallengren <ola@hallengren.com> wrote:
There is something strange here:

CommandLogView 

I don't recognize this code. Have you done some modifications to the code?

/Ola

On Thu, Oct 4, 2018 at 9:30 PM, Ralph Joseph <rbj_1931_4310@yahoo.com> wrote:
Hi Ola,
Did some more checking today.

———————————————
I get a similar error doing the following join:
 
from master.sys.databases d
left join master.dbo.CommandLogView c on c.DatabaseName = d.name 
 
Msg 468, Level 16, State 9, Line 9
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation.
 
Out of over a hundred servers, this is the only one that has this problem.
 
Perhaps it is because the server collation is different from the master collation?
 
ServerCollation SQL_Latin1_General_CP1_CI_AS
 
master  SQL_Latin1_General_CP1_CI_AS
tempdb  SQL_Latin1_General_CP1_CI_AS
model   SQL_Latin1_General_CP1_CI_AS
msdb    SQL_Latin1_General_CP1_CI_AS
 
the compile for DatabaseIntegrityCheck fails with the error :
 
Msg 468, Level 16, State 9, Procedure DatabaseIntegrityCheck, Line 929 [Batch Start Line 9]
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
 ——————————————-
 

On Oct 3, 2018, at 5:55 PM, Ralph Joseph <rbj_1931_4310@yahoo.com> wrote:
Hi Ola,
Thanks for your quick reply.
I am trying to compile version 2018-07-16 18:32:21
Same error.
The other procs compile fine.

Thanks again,
Ralph

On Oct 3, 2018, at 5:26 PM, Ola Hallengren <ola@hallengren.com> wrote:
Hi Ralph,

Could you try downloading the latest version, and see if you get the same error with that.

Let me know how it goes.

Best regards

Ola



On Wed, Oct 3, 2018 at 11:19 PM, Ralph Joseph <rbj_1931_4310@yahoo.com> wrote:

Dear Ola,
I have been using your Maintenance Solution for many years and appreciate your hard work in this.
 
I have a question below.
I cannot compile DatabaseIntegrityCheck on one of my servers.
And get the following error:
 
DatabaseIntegrityCheck
 
line :
     ON tmpDatabases.DatabaseName = CommandLog.DatabaseName 
 
Msg 468, Level 16, State 9, Procedure DatabaseIntegrityCheck, Line 929 [Batch Start Line 9]
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
 
If this line is replaced with the following, then it compiles. However I would prefer not to change this.
replaced by:
     ON tmpDatabases.DatabaseName COLLATE DATABASE_DEFAULT= CommandLog.DatabaseName COLLATE DATABASE_DEFAULT
 
then it compiles
 
Here are the system db collations
 
master  1              NULL     SQL_Latin1_General_CP1_CI_AS
tempdb                2              NULL     SQL_Latin1_General_CP1_CI_AS
model   3              NULL     SQL_Latin1_General_CP1_CI_AS
msdb     4              NULL     SQL_Latin1_General_CP1_CI_AS
 
Do you have any idea why the error. Could there be a problem with my version of CommandLog table which was created 2017-06-20?
 
Thanks for your help.
Ralph Joseph. 

/label ~bug_report

@srutzky

This comment has been minimized.

Copy link

commented Oct 11, 2018

There are two things going on here:

  1. This Particular Case

    The query in DatabaseIntegrityCheck that produced the error is an UPDATE to @tmpDatabases (view the full code here). Now, unless the collation is explicitly set when declaring a table variable, the collation will be the current database's default collation (effectively being: DATABASE_DEFAULT), not the instance's default collation (which is the default used for temp tables). The declaration in this code does not explicitly set the collation, so it uses the current database's default collation. This means that the collation used for the table variables here can change if the databases's default collation is changed.

    The collation used for string columns in CommandLog start out the same way (using the database's default collation if the COLLATE keyword is not supplied), but once set it won't change if the database's collation changes.

    So, either:

    1. the database was restored from a server with a different collation, restored to the current server, and then the database's collation was changed, which did not change the collation of any columns in any tables that already existed.
    2. the table was scripted from another server where the database had a different default collation
    3. the table was created correctly, but someone came along and manually changed the collations to be Latin1_General_..., just as you did to change them back to match the SQL_Latin1_General_....

    If all of the columns in CommandLog had the same incorrect collation, then that seems like the result of a restore or scripting (assuming that the option to include column collations was checked) from another database. You can check the collations of the columns in other tables in this database, if there are any other tables. If there are other tables and they have matching Latin1_General_ collations then that looks more like a restore. If they have the SQL_Latin1_General_... collation that matches the system dbs, then that points more to someone scripting the table out to create here and forgetting to remove the COLLATE keywords from each column definition.

  2. The Code

    While the code does not prevent this error, it is an error that can only occur if someone changes the collation of either the database or the CommandLog.DatabaseName column. Neither of those changes is routine, nor anything that should be expected to be handled by this (or pretty much any) code. Hence, this is not a bug. It is just a nuance (er, difficulty 😾 😿 ) of having more than 1 string column referenced in a predicate or expression. So classify it as: user error.

    Still, if it is desirable to have the code be resilient to such changes, then line 938:

    ON tmpDatabases.DatabaseName = CommandLog.DatabaseName

    can be changed to instead be:

    ON tmpDatabases.DatabaseName = CommandLog.DatabaseName COLLATE DATABASE_DEFAULT

    In most cases (i.e. when no collation changes are made), this change will have no effect as the collation of CommandLog.DatabaseName will already be the database's default. And the collation of tmpDatabases.DatabaseName always matches DATABASE_DEFAULT (because it is a table variable, and assuming that no COLLATE was specified in the DECLARE @tmp.. TABLE), even if the database's collation is changed.

@olahallengren

This comment has been minimized.

Copy link
Owner

commented Oct 28, 2018

I know this is a real special case. I added a COLLATE DATABASE_DEFAULT to handle it.

Thank you for the great explanation of the issue.

@srutzky

This comment has been minimized.

Copy link

commented Oct 28, 2018

@olahallengren Sounds good. And you're welcome 😺 .

@srutzky

This comment has been minimized.

Copy link

commented Dec 6, 2018

FYI: I just submitted two UserVoice suggestions that would be a better fit than DATABASE_DEFAULT for cases like this:

  1. Add special collation INSTANCE_DEFAULT to work like COLLATE DATABASE_DEFAULT but uses instance's default collation
  2. Allow collation set by variable when using the COLLATE clause (at least in expressions)

Please support (i.e. vote for) them 😸 👊

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.