usp_sqlwatch_internal_add_table - CI Collation With CS DB On Same Instance With "Dupe" Table Name

[mattcargile]

Did you check DOCS to make sure there is no workaround? https://sqlwatch.io/docs/

• [x] Yes

Describe the bug The step dbo.usp_sqlwatch_internal_add_table within the Agent Job SQLWATCH-INTERNAL-CONFIG fails during the below scenario.

1. SQLWATCH/model db in CI collation
2. CS database exists on same instance
3. More than one table name with the same name within the CS database. ( i.e. dbo.TableName and dbo.TABLENAME )

To Reproduce Steps to reproduce the behavior:

1. Create SQL Server Instance with CI collation.
2. Create database with CS collation
3. Create two tables within this database with a name of dbo.TableName and dbo.TABLENAME
4. Install-DbaSqlWatch -SqlInstance $servername -Database SQLWATCH
5. Check History on SQLWATCH-INTERNAL-CONFIG

Expected behavior Step dbo.usp_sqlwatch_internal_add_table shouldn't fail.

Screenshots N/A

Windows Server (please complete the following information):

• OS Version: Microsoft Windows Server 2012 R2 Standard

SQL Server (please complete the following information):

• SQL Version: SQL Server 2017 (RTM-CU20)
• SQL Edition: Standard

SQL Server Management Studio (SSMS -> about -> copy info):

SQL Server Management Studio                            15.0.18386.0
SQL Server Management Objects (SMO)                     16.100.46437.65
Microsoft Analysis Services Client Tools                15.0.19618.0
Microsoft Data Access Components (MDAC)                 10.0.18362.1
Microsoft MSXML                                         3.0 5.0 6.0 
Microsoft .NET Framework                                4.0.30319.42000
Operating System                                        10.0.18363

SQLWATCH version (from DACPAC or from sysinstances) 4.2.0.28234 from SQLWATCH.dbo.sqlwatch_app_version. Additionally I used Install-DbaSqlWatch under dbatools version 1.1.15

Additional context The global temp table, ##98308FFC2C634BF98B347EECB98E3490 definition causes the error.

https://github.com/marcingminski/sqlwatch/blob/7a57ce4f617772a97f28391f8d31182b15cd7171/SqlWatch.Monitor/Project.SqlWatch.Database/dbo/Procedures/usp_sqlwatch_internal_add_table.sql#L17-L19

The error happens at this piece of the code. Once the loop reaches the CS database with the "duplicate" table then it fails.

https://github.com/marcingminski/sqlwatch/blob/7a57ce4f617772a97f28391f8d31182b15cd7171/SqlWatch.Monitor/Project.SqlWatch.Database/dbo/Procedures/usp_sqlwatch_internal_add_table.sql#L33-L38