Identify the query causing collation conflict

Question

I have inherited a new SQL Server database instance.

The SQL Server Agent error Log is full of collation errors - new errors are arising every 10 seconds. But detail of error is minimal - 'cannot resolve collation conflict between' etc...

Now I understand collation conflicts, and given a specific query with a conflict, I would now how to investigate and correct.

How do I identify the query (queries) that are causing the collation conflict?

I have checked SQL Agent Jobs - there are no jobs running often enough to cause the constant errors.

I can use SQL Profiler to highlight errors being posted to the SQL Server log (together with the query), but can't do that for errors posted to SQL Agent Log.

Everywhere on the internet explains how to resolve collation conflicts of a specific query. But I can't even identify what query is 'causing' the collation conflict.

---

OK, managed to use an Extended Event session to record events for 'Error Reported'.

Found my collation error being reported against the following SQL_text:

EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

which I understand is necessary when you have alerts configured.

I appear to have about 20 or so alerts configured on this instance - many are the expected ones (Severity 019-025) but a whole bunch of others for Low Disk, Process Blocking, Failed Logins etc.

Why is there a collation conflict with this standard Microsoft stored procedure?

---

Server has collation Latin1_General_CI_AS. Other system dbs have same 'except for' msdb, which has SQL_Latin1_General_CP1_CI_AS.

Collation is a mess on the instance - half the databases have one collation, half have the other.

Should msdb have same collation as other system dbs? Can I/should I change its collation?

Answer 1

Server has collation Latin1_General_CI_AS. Other system dbs have same 'except for' msdb, which has SQL_Latin1_General_CP1_CI_AS

That is the source of the collation conflict error messages, which I'm sure state that the conflict is between Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS.

You will have to get msdb to have a collation of Latin1_General_CI_AS in order to fix this. There are a couple of ways to address this.

1. You could rebuild the entire instance which would give a consistent collation to the system DBs (which kinda do need to be the same collation), though that doesn't necessarily help any of your user DBs.
2. You could use the undocumented internal operation to do this, which I have documented here: Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?, though it is unsupported so Microsoft won't help if you run into any problems. However, with both collations being Latin1_General and Case-Insensitive, Accent-Sensitive, it's highly unlikely (though to be fair, not impossible) that you would run into any problems (as most problems would be due to changing code pages for VARCHAR data and/or changing sensitivities, neither of which are happening here). This option requires the instance be set to a collation that it isn't already set to. Since the instance is set to Latin1_General_CI_AS, you would have to pick another, which could just be SQL_Latin1_General_CP1_CI_AS. At that point, you are technically free to run it again to change everything to Latin1_General_CI_AS.

Answer 2

I blog about this here: https://straightforwardsql.com/posts/investigating-errors-with-extended-events/

The gist of it - You create an extended Event session with the event error_reported and capture additional information - database, SQL text and tsql stack.

CREATE EVENT SESSION [Error_reported] ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
          sqlserver.server_instance_name /* good practice for multi server querying */
        , sqlserver.client_app_name     /* helps locate the calling app */
        , sqlserver.client_hostname     /* calling computer name */
        , sqlserver.server_principal_name   /* can be switched to a user */
        , sqlserver.database_id /* can be switched to a database_name */
        , sqlserver.sql_text    /* grab calling parameters from input buffer */
        , sqlserver.tsql_stack  /* get the whole stack for parsing later */
    )
    WHERE 
    (
        severity > 10
        /* Please test and provide additional filters! */
    )
)
ADD TARGET 
    package0.event_file
    (
        SET 
            filename=N'Error_reported'
            , max_file_size= 20 /* MB */
    )

You should customize the filter on this event so it captures only errors you're interested in.

I've found that the collation errors have numbers 446 and 451 using this code:

SELECT 
    *
FROM sys.messages AS m
WHERE
    m.language_id = 1033
    AND m.[text] LIKE '%cannot resolve collation conflict between%'

Then you'll either find it in the SQL text or parse the stack against the plan cache.

DECLARE @stackOrFrame xml = 'Paste the <frames></frames> here'

;WITH
xmlShred AS
(
    SELECT
        COALESCE
        (
            CONVERT(varbinary(64), f.n.value('.[1]/@handle', 'varchar(max)'), 1),
            CONVERT(varbinary(64), f.n.value('.[1]/@sqlhandle', 'varchar(max)'), 1)
        ) AS handle,
        COALESCE
        (
            f.n.value('.[1]/@offsetStart', 'int'),
            f.n.value('.[1]/@stmtstart', 'int')
        ) AS offsetStart,
        COALESCE
        (
            f.n.value('.[1]/@offsetEnd', 'int'),
            f.n.value('.[1]/@stmtend', 'int')
        ) AS offsetEnd,
        f.n.value('.[1]/@line', 'int') AS line,
        f.n.value('.[1]/@level', 'tinyint') AS stackLevel
    FROM @stackOrFrame.nodes('//frame') AS f(n)
)
SELECT
    xs.stackLevel,
    ca.outerText,
    ca2.statementText
FROM
    xmlShred AS xs
    CROSS APPLY sys.dm_exec_sql_text(xs.handle) AS dest
    CROSS APPLY (SELECT TRIM(dest.text)  FOR XML PATH(''), TYPE) AS ca(outerText)
    CROSS APPLY
    (
        SELECT 
            SUBSTRING
            (   
                dest.text,
                (xs.offsetStart / 2) + 1,
                ((
                    CASE
                        WHEN xs.offsetEnd = -1
                            THEN DATALENGTH(dest.text)
                        ELSE xs.offsetEnd
                    END 
                    - xs.offsetStart
                ) / 2) + 1
            )
        FOR XML PATH(''), TYPE
    ) AS ca2(statementText)
ORDER BY xs.stackLevel
OPTION (RECOMPILE);