Table rebuild in sql compare13 with TEXTIMAGE_ON

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
Tagged:

Best Answer

  • Eddie DEddie D Posts: 1,699 Rose Gold 5
    Accepted Answer
    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 Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]

Answers

  • Eddie DEddie D Posts: 1,699 Rose Gold 5
    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

    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • VarahalubabuVarahalubabu Posts: 7 New member
    edited November 22, 2018 6:43AM
    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 D:\RedGateRandD\RedGateScript -databaseName SyncDB -databasePassword @[email protected] -scriptFile D:\Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\CompleteMigrateSql.sql  -RequiredProductVersion latest
  • Eddie DEddie D Posts: 1,699 Rose Gold 5
    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.


    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • 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 DEddie D Posts: 1,699 Rose Gold 5
    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
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • VarahalubabuVarahalubabu Posts: 7 New member
    Thank you for response, we have verified with version you suggested and it is working now. 
Sign In or Register to comment.