Table rebuild in sql compare13 with TEXTIMAGE_ON

Answers

• Eddie D Redgate › Posts: 1,807 Rose Gold 5

  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
  
  

  Eddie Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter
• Varahalubabu Posts: 7 New member

  November 22, 2018 6:41AM 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 \RedGateRandD\RedGateScript -databaseName SyncDB -databasePassword @pword@ -scriptFile \Work\Office\SQL\RedGate\SqlComapre\CommandLine\WithoutOption\TESTFG\CompleteMigrateSql.sql  -RequiredProductVersion latest
  

  qua10g3s.log 29.7K

  0 · Share on Twitter
• Eddie D Redgate › Posts: 1,807 Rose Gold 5

  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.
  
  
  

  Eddie Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter
• Varahalubabu Posts: 7 New member

  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. 
  
  

  0 · Share on Twitter
• Eddie D Redgate › Posts: 1,807 Rose Gold 5

  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

  Eddie Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter
• Varahalubabu Posts: 7 New member

  March 12, 2019 2:30PM

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

  0 · Share on Twitter