Red Gate forums :: View topic - Issue comparing tables using FILESTREAM data
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Issue comparing tables using FILESTREAM data

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
kreitmey



Joined: 10 Jul 2013
Posts: 2

PostPosted: Wed Jul 10, 2013 3:10 pm    Post subject: Issue comparing tables using FILESTREAM data Reply with quote

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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_Document_FS] ON
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
SET IDENTITY_INSERT [dbo].[tmp_rg_xx_Document_FS] OFF
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TABLE [dbo].[Document_FS]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@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
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@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?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Mon Jul 15, 2013 12:59 pm    Post subject: Reply with quote

Sorry, at this point I don't know what is happening. This is a new error.

I have a backup from another customer experiencing the same problem so once that's up I will let you know what I find.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6669

PostPosted: Tue Jul 16, 2013 1:08 pm    Post subject: Reply with quote

I have found an issue in SQL Compare that happens in SQL 2102 when you have a filestream column with a unique index on it.

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.
Back to top
View user's profile Send private message
kreitmey



Joined: 10 Jul 2013
Posts: 2

PostPosted: Tue Jul 23, 2013 5:56 pm    Post subject: Reply with quote

Is there an estimate on when this bug will be fixed?
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group