Table rebuild in sql compare13 with TEXTIMAGE_ON

Varahalubabu · November 20, 2018 9:02AM

Table rebuild is happening when TEXTIMAGE_ON is there in the table creation statement

if source and target have different file groups. It is happening in SQL Compare13 but not in SQL Compare12 even if i use ifg option in sync step.

It is stopping us to setup automate deployments for DB. Can any one help us on this.

Script in Source:

CREATE TABLE [dbo].[MANAGE_FIELD_LABEL_NAME](

[FIELD_LABEL_ID] [BIGINT] IDENTITY(1,1) NOT NULL,

[FIELD_LABEL_NAME] [NVARCHAR](200) NULL,

[FIELD_DESCRIPTION] [VARCHAR](3000) NULL,

[FIELD_KEY] [NVARCHAR](MAX) NULL,

[FIELD_DESCRIPTION_TEST] [VARCHAR](100) NULL,

CONSTRAINT [PK_MANAGE_FIELD_LABEL_NAME_FIELD_LABEL_ID] PRIMARY KEY CLUSTERED

(

[FIELD_LABEL_ID] ASC

)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Script in Target:

CREATE TABLE [dbo].[MANAGE_FIELD_LABEL_NAME](

[FIELD_LABEL_ID] [BIGINT] IDENTITY(1,1) NOT NULL,

[FIELD_LABEL_NAME] [NVARCHAR](200) NULL,

[FIELD_DESCRIPTION] [VARCHAR](3000) NULL,

[FIELD_KEY] [NVARCHAR](MAX) NULL,

CONSTRAINT [PK_MANAGE_FIELD_LABEL_NAME_FIELD_LABEL_ID] PRIMARY KEY CLUSTERED

(

[FIELD_LABEL_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [FG_Test]

) ON [FG_Test] TEXTIMAGE_ON [FG_Test]

I have used following command line syntax to replicate this.

C:\Program Files (x86)\Red Gate\SQL Compare 13>SQLCompare.exe /transactionIsolationLevel:SERIALIZABLE /exclude:Additional /scriptFile:"D:\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\CompleteMigrateSql.sql" /showWarnings /report:"D:\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\isa2y4hu.xml" /reportType:Xml /assertidentical /force /OutputWidth:1024 /scripts1:"D:\RedGateRandD\RedGateScript" /server2:AHS-LP-196 /database2:SyncDB /out:"D:\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\qua10g3s.log" /options:adus,we,cs,cfgps,dp2k,drd,nc,dacia,ib,icc,ict,ich,ic,icm,icn,idsn,idc,ie,ifg,if,ift,iip,isi,ii,iilp,iit,ik,ims,infr,ipi,ip,iq,isoa,isb,ist,isn,iscn,itst,iup,iw,iweo,iwe,iwn,nacm,oec,ucl

Eddie D · March 11, 2019 4:33PM

Hi

I have some good news.

Bug, SC-10497 has been fixed in V13.7.5.9811 released today.

To upgrade, use the Check for Updates feature of SQL Compare, or use this link to download.

Please do upgrade and confirm the problem is resolved in your environment.

Many Thanks
Eddie

Eddie D · November 21, 2018 2:06PM

Hi, thank you for your support request.

What is the full version number of SQL Compare V13 you are using? Also the full version number of the SQL Compare V12 you are or were using (if known)?

I ask the above question, as a similar problem was resolved in V13.1.10.5564 back in January 2018.

Many Thanks
Eddie

Varahalubabu · November 22, 2018 6:41AM

Hi Thank you for your response,

Because of this issue we could not able to take decision on DB Sync automation.

I have tested in SQL Compare v13.6.4.8.8181 and SQL Compare v12.3.3.4490

Result of SQL Compare v12.3.3.4490 :

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

SET XACT_ABORT ON

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

GO

BEGIN TRANSACTION

GO

@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Altering [dbo].[MANAGE_FIELD_LABEL_NAME]'

GO

@ERROR <> 0 SET NOEXEC ON

GO

ALTER TABLE [dbo].[MANAGE_FIELD_LABEL_NAME] ADD

[FIELD_DESCRIPTION_TEST] [VARCHAR] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

GO

@ERROR <> 0 SET NOEXEC ON

GO

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON

GO

SET DATEFORMAT YMD

GO

SET XACT_ABORT ON

GO

COMMIT TRANSACTION

GO

@ERROR <> 0 SET NOEXEC ON

GO

DECLARE @Success AS BIT

SET @Success = 1

SET NOEXEC OFF

IF (@Success = 1) PRINT 'The database update succeeded'

ELSE BEGIN

@TRANCOUNT > 0 ROLLBACK TRANSACTION

PRINT 'The database update failed'

END

GO

For above results i have used below command line syntax:

C:\Program Files (x86)\Red Gate\SQL Compare 12>SQLCompare.exe /transactionIsolationLevel:SERIALIZABLE /include:staticData /exclude:Additional /scriptFile:"D:\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\CompleteMigrateSql.sql" /showWarnings /include:Identical /report:"D:\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\isa2y4hu.xml" /reportType:Xml /assertidentical /force /OutputWidth:1024 /scripts1:"D:\RedGateRandD\RedGateScript" /server2:AHS-LP-196 /database2:SyncDB /out:"D:\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\qua10g3s.log" /options:cfgps,dp2k,nc,f,icc,idsn,IgnoreTSQLT,iu,IgnoreUserProperties,iw,iweo,incd,tofpf,ucl,ifg

Result of SQL Compare v13.6.4.8.8181 :

/*

Generated on 22/Nov/2018 11:51 by Redgate SQL Change Automation v3.1.1.2920

*/

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

SET XACT_ABORT ON

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

GO

BEGIN TRANSACTION

GO

@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Dropping constraints from [dbo].[MANAGE_FIELD_LABEL_NAME]'

GO

ALTER TABLE [dbo].[MANAGE_FIELD_LABEL_NAME] DROP CONSTRAINT [PK_MANAGE_FIELD_LABEL_NAME_FIELD_LABEL_ID]

GO

@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Rebuilding [dbo].[MANAGE_FIELD_LABEL_NAME]'

GO

CREATE TABLE [dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME]

(

[FIELD_LABEL_ID] [BIGINT] NOT NULL IDENTITY(1, 1),

[FIELD_LABEL_NAME] [NVARCHAR] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[FIELD_DESCRIPTION] [VARCHAR] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[FIELD_KEY] [NVARCHAR] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[FIELD_DESCRIPTION_TEST] [VARCHAR] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

)

GO

@ERROR <> 0 SET NOEXEC ON

GO

SET IDENTITY_INSERT [dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME] ON

GO

@ERROR <> 0 SET NOEXEC ON

GO

INSERT INTO [dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME]([FIELD_LABEL_ID], [FIELD_LABEL_NAME], [FIELD_DESCRIPTION], [FIELD_KEY]) SELECT [FIELD_LABEL_ID], [FIELD_LABEL_NAME], [FIELD_DESCRIPTION], [FIELD_KEY] FROM [dbo].[MANAGE_FIELD_LABEL_NAME]

GO

@ERROR <> 0 SET NOEXEC ON

GO

SET IDENTITY_INSERT [dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME] OFF

GO

@ERROR <> 0 SET NOEXEC ON

GO

DECLARE @idVal BIGINT

SELECT @idVal = IDENT_CURRENT(N'[dbo].[MANAGE_FIELD_LABEL_NAME]')

IF @idVal IS NOT NULL

DBCC CHECKIDENT(N'[dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME]', RESEED, @idVal)

GO

@ERROR <> 0 SET NOEXEC ON

GO

DROP TABLE [dbo].[MANAGE_FIELD_LABEL_NAME]

GO

@ERROR <> 0 SET NOEXEC ON

GO

EXEC sp_rename N'[dbo].[RG_Recovery_1_MANAGE_FIELD_LABEL_NAME]', N'MANAGE_FIELD_LABEL_NAME', N'OBJECT'

GO

@ERROR <> 0 SET NOEXEC ON

GO

PRINT N'Creating primary key [PK_MANAGE_FIELD_LABEL_NAME_FIELD_LABEL_ID] on [dbo].[MANAGE_FIELD_LABEL_NAME]'

GO

ALTER TABLE [dbo].[MANAGE_FIELD_LABEL_NAME] ADD CONSTRAINT [PK_MANAGE_FIELD_LABEL_NAME_FIELD_LABEL_ID] PRIMARY KEY CLUSTERED ([FIELD_LABEL_ID]) WITH (FILLFACTOR=80)

GO

@ERROR <> 0 SET NOEXEC ON

GO

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON

GO

SET DATEFORMAT YMD

GO

SET XACT_ABORT ON

GO

COMMIT TRANSACTION

GO

@ERROR <> 0 SET NOEXEC ON

GO

DECLARE @Success AS BIT

SET @Success = 1

SET NOEXEC OFF

IF (@Success = 1) PRINT 'The database update succeeded'

ELSE BEGIN

@TRANCOUNT > 0 ROLLBACK TRANSACTION

PRINT 'The database update failed'

END

GO

For above results i have used below powershell line syntax:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NonInteractive -ExecutionPolicy Bypass -File "D:\RedGateRandD\Docs\RedGateLib\SqlChangeAutomationRunner.ps1" sync -databaseUserName user1 -databaseServer AHS-LP-196 -options "ifg" -transactionIsolationLevel Serializable -package

\RedGateRandD\RedGateScript -databaseName SyncDB -databasePassword @pword@ -scriptFile

\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\CompleteMigrateSql.sql -RequiredProductVersion latest

Eddie D · December 17, 2018 4:41PM

Hi

I can reproduce this error.

I am not convinced that the TEXTIMAGE_ON keyword is the cause of the problem. I created a second copy of the table omitted TEXTIMAGE_ON and still experience the table rebuild process in the deployment script.

Sadly my test system does not have SQL Compare V12 to test against. I will continue investigating and let you know my observations and results.

Varahalubabu · December 18, 2018 5:40AM

Hi,

Thank you for your response.

If we use Ignorefilegroup option it doesn't rebuild if we omit TEXTIMAGE_ON keyword in script.

Can you check the script files which i have used for this issue in above comments. If source and target doesn't have any changes except file group even if script has TEXTIMAGE_ON keyword table rebuild doesn't happen.
Now add new column in to table in the source trying to sync you can see table rebuild.

For your reference you can use above scripts to replicate the scenario.

Eddie D · February 28, 2019 5:36PM

Hi

I have continued to investigate this problem.

Using V12.3.3.4490:
If the Ignore Filegroup, partition schemes and partition functions option is enabled, there is no table rebuild.

If the Ignore Filegroup, partition schemes and partition functions option is disabled, the table rebuild takes place due to the differences in the Filegroup names on which the table is created upon and in the GUI a warning message occurs that a table rebuild is required.

Using 13.7.4.9637:
If the Ignore Filegroup, partition schemes and partition functions option is enabled, a table rebuild occurs in the deployment script but there is no warning that this action is going to occur.

If the Ignore Filegroup, partition schemes and partition functions option is disabled, the table rebuild takes place due to the differences in the Filegroup names on which the table is created upon and a warning occurs that a table rebuild is required.

Therefore I have submitted Bug Report SC-10437 due to the behaviour I found. I will update when I have further news.

Many Thanks
Eddie

Varahalubabu · March 12, 2019 2:30PM

Thank you for response, we have verified with version you suggested and it is working now.

Table rebuild in sql compare13 with TEXTIMAGE_ON

Best Answer

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk