Skip to content

Make the location of Commandlog table dynamic, or check for a synonym if the table is not found #1001

Description

@wfvdijk

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

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions