SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
 
CREATE OR ALTER PROCEDURE [dbo].[_MultiDBUpdate]
(
    @isql nvarchar(max),                        -- SQL to execute
    @DatabaseServerName nvarchar(max) = NULL   -- pass NULL to run on all database servers
)
AS
BEGIN
/*
 
Copyright 2026 ITIQPro.com, parts copyright Maintenance Connection Inc.
 
Why:
- PM execution depends on dbo._MultiDBUpdate in Registration DB.
- This patch applies explicit COLLATE handling in the server-name join.
 
- this is NOT covered by any SMA.
- Accrueent requires to have your MS SQL Server, reg database and ent database(s) set to SQL_Latin1_General_CP1_CI_AS to install and run MC properly
- ITIQPro, Maintenance Connection Canada, Asset Pro Solutions Inc. provides this with no warrantee as a TEMPORARY solution for
- customers who are set up with an unsupported (anything other than SQL_Latin1_General_CP1_CI_AS is unsupported) collation.
*/
    SET NOCOUNT ON;
 
    DECLARE @isql_orig nvarchar(max);
    SET @isql_orig = @isql;
 
    IF ISNULL(@isql_orig, '') = ''
    BEGIN
        -- No SQL to process.
        RETURN;
    END
 
    DECLARE DatabaseServerCursor CURSOR STATIC FOR
    SELECT
        cr.dbserver_name
    FROM (
        SELECT 'localhost' AS name
        UNION
        SELECT name
        FROM sys.servers
    ) a
    INNER JOIN (
        SELECT DISTINCT REPLACE(dbserver_name, '.accruentsystems.com', '') AS dbserver_name
        FROM container_resource
    ) cr
        ON a.name COLLATE SQL_Latin1_General_CP1_CI_AS = cr.dbserver_name COLLATE SQL_Latin1_General_CP1_CI_AS
    ORDER BY cr.dbserver_name;
 
    OPEN DatabaseServerCursor;
 
    DECLARE @DatabaseName nvarchar(max);
    DECLARE @CurrentDatabaseServerName nvarchar(max);
    DECLARE @ContainerGUID uniqueidentifier;
    DECLARE @ResourceGUID uniqueidentifier;
    DECLARE @SQLCmd nvarchar(max);
    DECLARE @SysDatabases nvarchar(max);
    DECLARE @DatabaseNameReplacement nvarchar(max);
 
    FETCH NEXT FROM DatabaseServerCursor INTO @CurrentDatabaseServerName;
 
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF @DatabaseServerName IS NOT NULL AND @CurrentDatabaseServerName <> @DatabaseServerName
        BEGIN
            FETCH NEXT FROM DatabaseServerCursor INTO @CurrentDatabaseServerName;
            CONTINUE;
        END
 
        IF OBJECT_ID('tempdb.dbo.#DatabaseInfo') IS NOT NULL
            DROP TABLE #DatabaseInfo;
 
        CREATE TABLE #DatabaseInfo
        (
            ContainerGUID uniqueidentifier NOT NULL,
            ResourceGUID uniqueidentifier NOT NULL,
            DatabaseName nvarchar(max) NOT NULL
        );
 
        IF @CurrentDatabaseServerName = 'localhost'
            SET @SysDatabases = 'master.sys.databases';
        ELSE
            SET @SysDatabases = '[' + @CurrentDatabaseServerName + '].master.sys.databases';
 
        SET @SQLCmd =
            'INSERT #DatabaseInfo (ContainerGUID, ResourceGUID, DatabaseName) ' +
            'SELECT CR.container_guid, CR.resource_guid, TRIM(C.container_type_code) + TRIM(C.container_code) DatabaseName ' +
            'FROM container C ' +
            'JOIN container_resource CR ON (C.container_guid = CR.container_guid) ' +
            'WHERE TRIM(C.container_type_code) + TRIM(C.container_code) IN (SELECT [name] FROM ' + @SysDatabases + ') ' +
            'AND REPLACE(CR.dbserver_name,''.accruentsystems.com'','''') = ''' + @CurrentDatabaseServerName + ''' ' +
            'AND C.is_enabled = 1 AND CR.is_enabled = 1 ' +
            'ORDER BY DatabaseName;';
 
        EXEC (@SQLCmd);
 
        DECLARE DatabaseInfoCurs CURSOR LOCAL FOR
        SELECT ContainerGUID, ResourceGUID, DatabaseName
        FROM #DatabaseInfo
        ORDER BY DatabaseName;
 
        OPEN DatabaseInfoCurs;
        FETCH NEXT FROM DatabaseInfoCurs INTO @ContainerGUID, @ResourceGUID, @DatabaseName;
 
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            IF @CurrentDatabaseServerName = 'localhost'
                SET @DatabaseNameReplacement = @DatabaseName;
            ELSE
                SET @DatabaseNameReplacement = '[' + @CurrentDatabaseServerName + '].' + @DatabaseName;
 
            SET @isql = REPLACE(@isql_orig, '@dbname', @DatabaseNameReplacement);
            EXEC (@isql);
 
            FETCH NEXT FROM DatabaseInfoCurs INTO @ContainerGUID, @ResourceGUID, @DatabaseName;
        END
 
        CLOSE DatabaseInfoCurs;
        DEALLOCATE DatabaseInfoCurs;
 
        IF OBJECT_ID('tempdb.dbo.#DatabaseInfo') IS NOT NULL
            DROP TABLE #DatabaseInfo;
 
        FETCH NEXT FROM DatabaseServerCursor INTO @CurrentDatabaseServerName;
    END
 
    CLOSE DatabaseServerCursor;
    DEALLOCATE DatabaseServerCursor;
 
    RETURN 0;
END
GO

/* Verification: confirm proc text includes explicit COLLATE */
SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS ProcedureName,
       definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'dbo._MultiDBUpdate')
  AND definition LIKE '%COLLATE SQL_Latin1_General_CP1_CI_AS%';
GO