Table rebuild in sql compare13 with TEXTIMAGE_ON

• 
  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 D November 21, 2018 14:06

  - 0 +
• 
  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
  

  Varahalubabu November 22, 2018 06:41

  - 0 +
• 
  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 D December 17, 2018 16:41

  - 0 +
• 
  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. 
  
  

  Varahalubabu December 18, 2018 05:40

  - 0 +
• 
  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 D February 28, 2019 17:36

  - 0 +
• 
  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 March 11, 2019 16:33

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

  Varahalubabu March 12, 2019 14:30

  - 0 +

Add comment

Please sign in to leave a comment.