What are the challenges you face when working across database platforms? Take the survey
Options

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:

Comments

  • Options
    Tianjiao_Li ,
    I have tried with SQL Compare 13.6.1.7928 still rebuilt is happening.
    As you commented I am trying to update my SQL Compare to 13.6.3. as could not find such version in sql toolbelt. where can i find that version can you suggest us on this. Please find my above comment to find script which we are using for sync. Below is the resulted script.

    Script Generated by SQL Comapare 13:

    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
    -- This statement writes to the SQL Server Log so SQL Monitor can show this deployment.
    IF HAS_PERMS_BY_NAME(N'sys.xp_logevent', N'OBJECT', N'EXECUTE') = 1
    BEGIN
        DECLARE @databaseName AS nvarchar(2048), @eventMessage AS nvarchar(2048)
        SET @databaseName = REPLACE(REPLACE(DB_NAME(), N'\', N'\\'), N'"', N'\"')
        SET @eventMessage = N'Redgate SQL Compare: { "deployment": { "description": "Redgate SQL Compare deployed to ' + @databaseName + N'", "database": "' + @databaseName + N'" }}'
        EXECUTE sys.xp_logevent 55000, @eventMessage
    END
    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

Sign In or Register to comment.