{"action":"created","issue":{"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607","repository_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit","labels_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607/labels{/name}","comments_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607/comments","events_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607/events","html_url":"https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607","id":191074965,"number":607,"title":"On SQL 2016 SP1, the latest version of sp_BlitzIndex fails with a collation error.","user":{"login":"SQLMonger","id":13158108,"avatar_url":"https://avatars.githubusercontent.com/u/13158108?v=3","gravatar_id":"","url":"https://api.github.com/users/SQLMonger","html_url":"https://github.com/SQLMonger","followers_url":"https://api.github.com/users/SQLMonger/followers","following_url":"https://api.github.com/users/SQLMonger/following{/other_user}","gists_url":"https://api.github.com/users/SQLMonger/gists{/gist_id}","starred_url":"https://api.github.com/users/SQLMonger/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SQLMonger/subscriptions","organizations_url":"https://api.github.com/users/SQLMonger/orgs","repos_url":"https://api.github.com/users/SQLMonger/repos","events_url":"https://api.github.com/users/SQLMonger/events{/privacy}","received_events_url":"https://api.github.com/users/SQLMonger/received_events","type":"User","site_admin":false},"labels":[{"id":103941167,"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/labels/bug","name":"bug","color":"fc2929","default":true},{"id":103973451,"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/labels/sp_BlitzIndex","name":"sp_BlitzIndex","color":"eb6420","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":8,"created_at":"2016-11-22T17:36:59Z","updated_at":"2016-11-22T18:28:44Z","closed_at":null,"body":"After updating to SQL 2016 SP1, I also updated to the latest version of the sp_Blitz procs (v4.5 20161115). Unfortunately, sp_BlitzIndex now blows up in the 'Gathering Computed Column Info.' section around line 1586. \r\n\r\nHere is the error text:\r\n\r\nGathering Computed Column Info. Failure populating temp tables. Msg 2787, Level 16, State 1, Procedure sp_BlitzIndex, Line 1620 [Batch Start Line 4]\r\n\r\nInvalid 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);'.\r\n\r\nMsg 50000, Level 16, State 1, Procedure sp_BlitzIndex, Line 1624 [Batch Start Line 4]\r\n\r\nProduction-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.\r\n\r\nThe solution was to add a COLLATE statement to line 1599 (line 1605 in the delivered script file):\r\n\r\nCASE WHEN is_persisted = 1 THEN '' PERSISTED'' ELSE '''' END + '';''  collate SQL_Latin1_General_CP1_CI_AS AS [column_definition]\r\n\r\nCheers,\r\n\r\nClayton\r\n\r\nPosted here first: [http://dba.stackexchange.com/questions/156049/sp-blitzindex-v-4-5-2016-11-15-failing-with-collation-error](http://dba.stackexchange.com/questions/156049/sp-blitzindex-v-4-5-2016-11-15-failing-with-collation-error)"},"comment":{"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/comments/262323974","html_url":"https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607#issuecomment-262323974","issue_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/607","id":262323974,"user":{"login":"SQLMonger","id":13158108,"avatar_url":"https://avatars.githubusercontent.com/u/13158108?v=3","gravatar_id":"","url":"https://api.github.com/users/SQLMonger","html_url":"https://github.com/SQLMonger","followers_url":"https://api.github.com/users/SQLMonger/followers","following_url":"https://api.github.com/users/SQLMonger/following{/other_user}","gists_url":"https://api.github.com/users/SQLMonger/gists{/gist_id}","starred_url":"https://api.github.com/users/SQLMonger/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SQLMonger/subscriptions","organizations_url":"https://api.github.com/users/SQLMonger/orgs","repos_url":"https://api.github.com/users/SQLMonger/repos","events_url":"https://api.github.com/users/SQLMonger/events{/privacy}","received_events_url":"https://api.github.com/users/SQLMonger/received_events","type":"User","site_admin":false},"created_at":"2016-11-22T18:28:44Z","updated_at":"2016-11-22T18:28:44Z","body":"We do have several tables with persisted computed columns. Let me know if you need anything else.\r\n"}}
{"actor":{"display_login":"SQLMonger"}}