Issue comparing tables using FILESTREAM data
kreitmey
Posts: 3
Using SQL server 2012 (11.0.2100), I create the following table in two separate empty databases with FILESTREAM enabled
CREATE TABLE [dbo].[Document_FS](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[GuidPK] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[FileName] [varchar](512) NOT NULL,
[FileSize] [bigint] NOT NULL,
[FileDescription] [varchar](255) NULL,
[IsActive] [bit] NOT NULL,
[FileData] [varbinary](max) FILESTREAM NULL,
CONSTRAINT [PK_Documents_FS] PRIMARY KEY CLUSTERED
(
[DocumentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [FILESTREAM],
CONSTRAINT [UQ__Document_FS_GUIDPk] UNIQUE NONCLUSTERED
(
[GuidPK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [FILESTREAM]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Document_FS] ADD CONSTRAINT [DF_Document_FS_GuidPK] DEFAULT (newid()) FOR [GuidPK]
GO
On one of the databases, i issue the following command
ALTER TABLE document_fs ADD testAdd1 bigint null
I then do a SQL compare using version 10.4.8.87 against the two databases i created. The deployment script created is as follows
/*
Run this script on:
dbn-sqldev-04\oms.b - This database will be modified
to synchronize it with:
dbn-sqldev-04\oms.a
You are recommended to back up your database before running this script
Script created by SQL Compare version 10.4.8 from Red Gate Software Ltd at 7/10/2013 10:06:22 AM
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping constraints from [dbo].[Document_FS]'
GO
ALTER TABLE [dbo].[Document_FS] DROP CONSTRAINT [PK_Documents_FS]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[Document_FS]'
GO
ALTER TABLE [dbo].[Document_FS] DROP CONSTRAINT [DF_Document_FS_GuidPK]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Rebuilding [dbo].[Document_FS]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_Document_FS]
(
[DocumentID] [int] NOT NULL IDENTITY(1, 1),
[GuidPK] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF_Document_FS_GuidPK] DEFAULT (newid()),
[FileName] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FileSize] [bigint] NOT NULL,
[FileDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsActive] [bit] NOT NULL,
[FileData] [varbinary] (max) FILESTREAM NULL,
[testAdd1] [bigint] NULL,
CONSTRAINT [UQ__Document_FS_GUIDPk] UNIQUE NONCLUSTERED ([GuidPK])
)
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_Document_FS] ON
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_rg_xx_Document_FS]([DocumentID], [GuidPK], [FileName], [FileSize], [FileDescription], [IsActive], [FileData]) SELECT [DocumentID], [GuidPK], [FileName], [FileSize], [FileDescription], [IsActive], [FileData] FROM [dbo].[Document_FS]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_Document_FS] OFF
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DECLARE @idVal BIGINT
SELECT @idVal = IDENT_CURRENT(N'[dbo].[Document_FS]')
IF @idVal IS NOT NULL
DBCC CHECKIDENT(N'[dbo].[tmp_rg_xx_Document_FS]', RESEED, @idVal)
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TABLE [dbo].[Document_FS]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_rename N'[dbo].[tmp_rg_xx_Document_FS]', N'Document_FS'
EXEC sp_rename N'[dbo].[Document_FS].[tmp_rg_xx_UQ__Document_FS_GUIDPk]', N'UQ__Document_FS_GUIDPk', N'INDEX'
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_Documents_FS] on [dbo].[Document_FS]'
GO
ALTER TABLE [dbo].[Document_FS] ADD CONSTRAINT [PK_Documents_FS] PRIMARY KEY CLUSTERED ([DocumentID])
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
When this script is executed, i get the following errors in the messages
Dropping constraints from [dbo].[Document_FS]
Dropping constraints from [dbo].[Document_FS]
Rebuilding [dbo].[Document_FS]
Msg 2714, Level 16, State 5, Line 1
There is already an object named 'UQ__Document_FS_GUIDPk' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
(1 row(s) affected)
Msg 1088, Level 16, State 11, Line 1
Cannot find the object "dbo.tmp_rg_xx_Document_FS" because it does not exist or you do not have permissions.
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.tmp_rg_xx_Document_FS'.
(1 row(s) affected)
Msg 1088, Level 16, State 11, Line 1
Cannot find the object "dbo.tmp_rg_xx_Document_FS" because it does not exist or you do not have permissions.
(1 row(s) affected)
Msg 2501, Level 16, State 45, Line 4
Cannot find a table or object with the name "[dbo].[tmp_rg_xx_Document_FS]". Check the system catalog.
(1 row(s) affected)
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 374
No item by the name of '[dbo].[tmp_rg_xx_Document_FS]' could be found in the current database 'b', given that @itemtype was input as '(null)'.
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 279
Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.
Creating primary key [PK_Documents_FS] on [dbo].[Document_FS]
Msg 4902, Level 16, State 1, Line 1
Cannot find the object "dbo.Document_FS" because it does not exist or you do not have permissions.
(1 row(s) affected)
The database update failed
Any ideas on what is going on, and why I am recieving this error?
CREATE TABLE [dbo].[Document_FS](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[GuidPK] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[FileName] [varchar](512) NOT NULL,
[FileSize] [bigint] NOT NULL,
[FileDescription] [varchar](255) NULL,
[IsActive] [bit] NOT NULL,
[FileData] [varbinary](max) FILESTREAM NULL,
CONSTRAINT [PK_Documents_FS] PRIMARY KEY CLUSTERED
(
[DocumentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [FILESTREAM],
CONSTRAINT [UQ__Document_FS_GUIDPk] UNIQUE NONCLUSTERED
(
[GuidPK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [FILESTREAM]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Document_FS] ADD CONSTRAINT [DF_Document_FS_GuidPK] DEFAULT (newid()) FOR [GuidPK]
GO
On one of the databases, i issue the following command
ALTER TABLE document_fs ADD testAdd1 bigint null
I then do a SQL compare using version 10.4.8.87 against the two databases i created. The deployment script created is as follows
/*
Run this script on:
dbn-sqldev-04\oms.b - This database will be modified
to synchronize it with:
dbn-sqldev-04\oms.a
You are recommended to back up your database before running this script
Script created by SQL Compare version 10.4.8 from Red Gate Software Ltd at 7/10/2013 10:06:22 AM
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Dropping constraints from [dbo].[Document_FS]'
GO
ALTER TABLE [dbo].[Document_FS] DROP CONSTRAINT [PK_Documents_FS]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Dropping constraints from [dbo].[Document_FS]'
GO
ALTER TABLE [dbo].[Document_FS] DROP CONSTRAINT [DF_Document_FS_GuidPK]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Rebuilding [dbo].[Document_FS]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_Document_FS]
(
[DocumentID] [int] NOT NULL IDENTITY(1, 1),
[GuidPK] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF_Document_FS_GuidPK] DEFAULT (newid()),
[FileName] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FileSize] [bigint] NOT NULL,
[FileDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsActive] [bit] NOT NULL,
[FileData] [varbinary] (max) FILESTREAM NULL,
[testAdd1] [bigint] NULL,
CONSTRAINT [UQ__Document_FS_GUIDPk] UNIQUE NONCLUSTERED ([GuidPK])
)
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_Document_FS] ON
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_rg_xx_Document_FS]([DocumentID], [GuidPK], [FileName], [FileSize], [FileDescription], [IsActive], [FileData]) SELECT [DocumentID], [GuidPK], [FileName], [FileSize], [FileDescription], [IsActive], [FileData] FROM [dbo].[Document_FS]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_Document_FS] OFF
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DECLARE @idVal BIGINT
SELECT @idVal = IDENT_CURRENT(N'[dbo].[Document_FS]')
IF @idVal IS NOT NULL
DBCC CHECKIDENT(N'[dbo].[tmp_rg_xx_Document_FS]', RESEED, @idVal)
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TABLE [dbo].[Document_FS]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_rename N'[dbo].[tmp_rg_xx_Document_FS]', N'Document_FS'
EXEC sp_rename N'[dbo].[Document_FS].[tmp_rg_xx_UQ__Document_FS_GUIDPk]', N'UQ__Document_FS_GUIDPk', N'INDEX'
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_Documents_FS] on [dbo].[Document_FS]'
GO
ALTER TABLE [dbo].[Document_FS] ADD CONSTRAINT [PK_Documents_FS] PRIMARY KEY CLUSTERED ([DocumentID])
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
When this script is executed, i get the following errors in the messages
Dropping constraints from [dbo].[Document_FS]
Dropping constraints from [dbo].[Document_FS]
Rebuilding [dbo].[Document_FS]
Msg 2714, Level 16, State 5, Line 1
There is already an object named 'UQ__Document_FS_GUIDPk' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
(1 row(s) affected)
Msg 1088, Level 16, State 11, Line 1
Cannot find the object "dbo.tmp_rg_xx_Document_FS" because it does not exist or you do not have permissions.
(1 row(s) affected)
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.tmp_rg_xx_Document_FS'.
(1 row(s) affected)
Msg 1088, Level 16, State 11, Line 1
Cannot find the object "dbo.tmp_rg_xx_Document_FS" because it does not exist or you do not have permissions.
(1 row(s) affected)
Msg 2501, Level 16, State 45, Line 4
Cannot find a table or object with the name "[dbo].[tmp_rg_xx_Document_FS]". Check the system catalog.
(1 row(s) affected)
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 374
No item by the name of '[dbo].[tmp_rg_xx_Document_FS]' could be found in the current database 'b', given that @itemtype was input as '(null)'.
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 279
Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.
Creating primary key [PK_Documents_FS] on [dbo].[Document_FS]
Msg 4902, Level 16, State 1, Line 1
Cannot find the object "dbo.Document_FS" because it does not exist or you do not have permissions.
(1 row(s) affected)
The database update failed
Any ideas on what is going on, and why I am recieving this error?
Comments
I have a backup from another customer experiencing the same problem so once that's up I will let you know what I find.
If SQL Compare determines there should be a table rebuild, it does not properly construct a temporary index name to use for the index; it uses the index name from the original table instead. So when it tries to rename the index, it cannot find the proper index by name.
This has been logged as bug SC-6439.