The situation
We have a database containing many maintenance scripts and parameters (maintenance database). Ola scripts are also included in this database. This maintenance database is centrally managed and restored daily on all servers.
The Problem
Placing the command log file in this maintenance database is not an option, because this database is refreshed daily on all servers, causing all history for that server to be lost. For logging, we have a separate database on each server (log database). This should be the perfect place for the command log table (so that we can preserve the history). However, I cannot specify where the command log file is located; the procedures assume that this table is located in the same database as the procedure itself
What I tried
I have tried moving the command log table to this log database and creating a synonym for 'commandlog' in the maintenance database, but this does not work because the script explicitly searches for a table.
What I want
Either specify the location of the command log file, or check if a synonym for 'commandlog' exists in the scripts.
The code in each stored procedure should be something like:
IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog')
AND NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = 'Commandlog')
BEGIN
INSERT INTO @Errors ([Message], Severity, [State])
SELECT 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.', 16, 1
END
The situation
We have a database containing many maintenance scripts and parameters (maintenance database). Ola scripts are also included in this database. This maintenance database is centrally managed and restored daily on all servers.
The Problem
Placing the command log file in this maintenance database is not an option, because this database is refreshed daily on all servers, causing all history for that server to be lost. For logging, we have a separate database on each server (log database). This should be the perfect place for the command log table (so that we can preserve the history). However, I cannot specify where the command log file is located; the procedures assume that this table is located in the same database as the procedure itself
What I tried
I have tried moving the command log table to this log database and creating a synonym for 'commandlog' in the maintenance database, but this does not work because the script explicitly searches for a table.
What I want
Either specify the location of the command log file, or check if a synonym for 'commandlog' exists in the scripts.
The code in each stored procedure should be something like: