1

When running Ola's full SQL Server Maintenance Solution or the individual databasebackup script I get the following error:

MSG 207, Level 16, State 1, Procedure DatabaseBackup, Line 1545 Invalid column name 'schemaName'.

This is part of the @databasesInParallel section which it appears to be trying to run even though this variable is set to 'N' at the beginning of the script. I haven't altered anything and this script works perfectly on other servers.

  • Does this server have a case sensitive collation? – Erik Darling Dec 18 '18 at 22:23
  • Hi Erik, no checked that CI_AS , should have put that in the main issue – Stockburn Dec 18 '18 at 22:36
  • Do you by any chance have a different dbo.Queue table in your database? If I create the Queue table without the schemaName column, it gives me the exact same error while @databasesinParallel = 'N' : Msg 207, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 1545 [Batch Start Line 5] Invalid column name 'SchemaName'. (3 times). – Randi Vertongen Dec 19 '18 at 8:22
2

The dbo.Queue table exists, but does not have the SchemaName column. This can be due to a custom dbo.Queue table that has nothing to do with Ola's solution. You could rename the table in the maintenance plan or drop the existing tables.

Recreating the error

Step 1: Recreate the maintenance solution Procedure

Step 2 Create the dbo.Queue table without the SchemaName

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Queue]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Queue](
  [QueueID] [int] IDENTITY(1,1) NOT NULL,
  --  [SchemaName] [sysname] NOT NULL,
  [ObjectName] [sysname] NOT NULL,
  [Parameters] [nvarchar](max) NOT NULL,
  [QueueStartTime] [datetime] NULL,
  [SessionID] [smallint] NULL,
  [RequestID] [int] NULL,
  [RequestStartTime] [datetime] NULL,
 CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED
(
  [QueueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO

Step 3: Create the dbo.QueueDatabase table

Step 4: Try to take a backup

USE [MNGDB]
GO

EXEC  [dbo].[DatabaseBackup]
        @Databases = N'MNGDB',
        @Directory = N'G:\Data',
        @BackupType = N'FULL'

Error:

Msg 207, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 1545 [Batch Start Line 5] Invalid column name 'SchemaName'. Msg 207, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 1555 [Batch Start Line 5] Invalid column name 'SchemaName'. Msg 207, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 1561 [Batch Start Line 5] Invalid column name 'SchemaName'.


Why

The query fails because the table exists but the schemaname column does not. If table does not exist, it succeeds.


Testing the part that causes the error & resolving it

  DECLARE @DatabasesInParallel char(1)  = 'N', @SchemaName varchar(255) = 'test',  @ErrorMessage nvarchar(max)
  IF @DatabasesInParallel = 'Y'
  BEGIN

    BEGIN TRY

      SELECT  QueueID
      FROM dbo.[Queue]
      WHERE SchemaName = @SchemaName


    END TRY

    BEGIN CATCH
      IF XACT_STATE() <> 0
      BEGIN
        ROLLBACK TRANSACTION
      END
      SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CHAR(13) + CHAR(10) + ' '
      RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
    END CATCH

  END

Error message

Msg 207, Level 16, State 1, Line 1892
Invalid column name 'SchemaName'.

Dropping the two previously created tables

drop table dbo.[QueueDatabase]; 
drop table dbo.[Queue];

and rerunning the statement produces no error:

Commands completed successfully.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.