Collation conflict on sp_sql_perf_mon_logger

[wsmelton]

Describe the bug
Documentation documented here states the following:

We use Latin1_General_CI_AS collation

The server I'm trying to deploy the solution to is configured under that same collation.

Collation conflict on server that is configured to Latin1_General_CI_AS

To Reproduce
Steps to reproduce the behavior:

1. Pull down latest version of dbatools
2. Run Install-DbaSqlWatch -SqlInstance myserver -Database SQLWATCH
3. Receive error on collation conflict:

WARNING: [03:26:50][Publish-DbaDacPackage] Deployment failed | Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 468, Level 16, State 9, Procedure sp_sql_perf_mon_logger, Line 130
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the like
operation.
Error SQL72045: Script execution error.  The executed script:
CREATE PROCEDURE [dbo].[sp_sql_perf_mon_logger]
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @product_version AS NVARCHAR (128);
DECLARE @product_version_major AS DECIMAL (10, 2);
DECLARE @product_version_minor AS DECIMAL (10, 2);
DECLARE @sql_memory_mb AS INT;
DECLARE @os_memory_mb AS INT;
DECLARE @memory_available AS INT;
DECLARE @percent_idle_time AS REAL;
DECLARE @percent_processor_time AS REAL;
DECLARE @date_snapshot_current AS DATETIME;
DECLARE @date_snapshot_previous AS DATETIME;
DECLARE @sp_whoisactive_destination_table AS VARCHAR (255);
DECLARE @sql AS NVARCHAR (4000);
SET @product_version = CONVERT (NVARCHAR (128), serverproperty('productversion'));
SELECT @product_version_major = substring(@product_version, 1, charindex('.', @product_version) + 1),
       @product_version_minor = parsename(CONVERT (VARCHAR (32), @product_version), 2);
SELECT @sql_memory_mb = CONVERT (INT, value)
FROM   sys.configurations
WHERE  name = 'max server memory (mb)';
I


ComputerName : cma-cac1-sql
InstanceName : MSSQLSERVER
SqlInstance  : myserver
Database     : SQLWATCH
Status       : (Failed)

Expected behavior
Install to create database and deploy solution.

Screenshots
Unable to provide due to client environment.

Windows Server (please complete the following information):

• OS Version: Window Server 2016

SQL Server (please complete the following information):

• SQL Version: SQL Server 2016 SP2
• SQL Edition: Enterprise

[marcingminski]

Hi, the error tells me the target collation is in fact different, I wonder if I mistakenly used the old SQL collation but documented the new one.

‘’’
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the like
operation.
‘’’

Here are some ideas how to solve it:
https://sqlwatch.io/docs/known-issues-and-limitations/collation-conflict/

As you are trying to deploy with dbatools, best option would be to create empty database first and then run Install-DbaSqlWatch. It will populate the empty db.

However, I will also assess a change to the proc to force default collection in the like operator.

Hope this helps.

[marcingminski]

fixed in 9b8a0bf