James ran into a BizTalk Server 2004 problem today where SQL Server was reporting the following error (amongst others):
Warning: The table ‘#catalogs’ has been created but its maximum row size (8281) exceeds the maximum number of bytes per row (8060).
Duncan beat me in pointing out that there’s a KB article about this problem and how to fix it, at which James remarked
that Microsoft‘s solution was incomplete as it didn’t create the Primary Key on the table. The correct solution was:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[adm_OtherBackupDatabases]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[adm_OtherBackupDatabases]
GO
drop table [dbo].[adm_OtherBackupDatabases]
GO
CREATE TABLE [dbo].[adm_OtherBackupDatabases]
([DefaultDatabaseName] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL,
[DatabaseName] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL ,
[ServerName] [nvarchar] (80) COLLATE Latin1_General_CI_AS NOT NULL ,
[BTSServerName] [nvarchar] (80) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
([DefaultDatabaseName] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL,
[DatabaseName] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL ,
[ServerName] [nvarchar] (80) COLLATE Latin1_General_CI_AS NOT NULL ,
[BTSServerName] [nvarchar] (80) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[adm_OtherBackupDatabases] WITH NOCHECK ADD
CONSTRAINT [adm_OtherBackupDatabases_PK] PRIMARY KEY CLUSTERED
( [DefaultDatabaseName],[BTSServerName] ) ON [PRIMARY]
GO
CONSTRAINT [adm_OtherBackupDatabases_PK] PRIMARY KEY CLUSTERED
( [DefaultDatabaseName],[BTSServerName] ) ON [PRIMARY]
GO
This is what I enjoy about working at Solidsoft, there’s some quality people here !