4

Posting this in case anyone else runs into this issue... (fix below) After updating to SQL 2016 SP1, I also updated to the latest version of the sp_Blitz procs. Unfortunately, sp_BlitzIndex now blows up in the 'Gathering Computed Column Info.' section around line 1586.

Here is the error text:

Gathering Computed Column Info.
Failure populating temp tables.
Msg 2787, Level 16, State 1, Procedure sp_BlitzIndex, Line 1620 [Batch Start Line 4]

Invalid format specification: 
    '%.%' THEN 1 ELSE 0 END AS is_function,
    'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + 
    ' ADD ' + QUOTENAME(c.name) + ' AS ' + cc.definition  + 
    CASE WHEN is_persisted = 1 THEN ' PERSISTED' ELSE '' END + ';' AS [column_definition]
    FROM    [Production-db].sys.computed_columns AS cc
    JOIN    [Production-db].sys.columns AS c
    ON      cc.object_id = c.object_id
    AND cc.column_id = c.column_id
    JOIN    [Production-db].sys.tables AS t
    ON      t.object_id = cc.object_id
    JOIN    [Production-db].sys.schemas AS s
    ON      s.schema_id = t.schema_id
    OPTION (RECOMPILE);'.

Msg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1624 [Batch Start Line 4]

Production-db database failed to process. 
Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in add operator occurring in SELECT statement column 11.

The solution was to add a COLLATE statement to line 1599 (line 1605 in the delivered script file):

CASE WHEN is_persisted = 1 THEN '' PERSISTED'' ELSE '''' END + '';'' collate SQL_Latin1_General_CP1_CI_AS AS [column_definition]

closed as off-topic by Tom V, Mikael Eriksson, Mike Walsh, swasheck, Marcello Miorelli Nov 23 '16 at 0:13

If this question can be reworded to fit the rules in the help center, please edit the question.

  • This is better suited as a pull request on their GitHub page. – Randolph West Nov 22 '16 at 18:45
  • This is due to a conflict between your DB and the system DBs. There's no way to account for this for every collation out there, especially since most DBs have the same collation setting as the server. I suppose you could force collation, but that is a lot of overhead for a few edge cases. – Steve Mangiameli Nov 22 '16 at 19:11
  • 4
    I'm voting to close this question as off-topic because it's not really a question and has been taken up by the folks over at Ozar's github – Tom V Nov 22 '16 at 19:25
  • 1
    Hi Clayton, welcome to the site. Don't be offended by my vote to close this question. I just don't feel it is an actual question but more of a bug report. I appreciate you're trying to help other people with your post and if you posted it as a question with a self answer it could have been a fit for the QA format (in my humble opinion) – Tom V Nov 22 '16 at 19:27
6

We've been working together on this error over here:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607

Turns out that the problem is contained databases. If you have a contained database, sp_BlitzIndex fails. (I've never actually seen a contained database in the wild before, so that's kinda amazing - wonder how long this has been broken.)

Not the answer you're looking for? Browse other questions tagged or ask your own question.