SQL Delta Logo
SQL Delta Banner

Error copying data to updated table structure

Support and information about SQL Delta for SQL Server Version 5

Error copying data to updated table structure

Postby anderscassidy » Thu Jan 09, 2014 1:01 pm

We are encountering an invalid generated script when comparing 2 tables from different versions of a database. The latest version of the schema has some of the old columns removed and some new columns added. The SQL generated by SQL Delta to copy existing data from the old structure to the new structure is inserting data into the wrong columns.

I updated to the latest v5 components today and created a script to reproduce the error (it includes the SQL Delta generated sync output:

Code: Select all

set nocount on
go
use [master]
go

create database old_db
create database new_db
go

use old_db
go

create table dbo.catalog_master
(
   cm_catalog_master_id  int not null,
   cm_catalog_parent_id  int null,
   cm_grouping_id        nvarchar(255) not null,
   cm_folder_title       nvarchar(255) not null,
   cm_folder_level       tinyint not null,
   cm_catalog_path       nvarchar(max) not null,
   cm_folder_hier        varchar(512) not null,
   cm_display_order      varchar(100) null,
   cm_is_visible         bit not null,
   cm_is_deleted           bit not null,
   cm_load_id            int not null
)
go

--Insert sample data
INSERT INTO [old_db].[dbo].[catalog_master]
           ([cm_catalog_master_id]
           ,[cm_catalog_parent_id]
           ,[cm_grouping_id]
           ,[cm_folder_title]
           ,[cm_folder_level]
           ,[cm_catalog_path]
           ,[cm_folder_hier]
           ,[cm_display_order]
           ,[cm_is_visible]
           ,[cm_is_deleted]
           ,[cm_load_id])
     VALUES
           (5,
              NULL,
              '_LearningPrograms',
              'Learning Programs',
              1,
              '/Learning Programs/',
              '.5.',
              'cc_verizon_migr_cc',
              1,
              0,
              160041
           )
GO

use new_db
go

create table dbo.catalog_master
(
   cm_catalog_master_id         int not null identity(1,1),
   cm_grouping_id               nvarchar(255) not null,
   cm_cds_guid                  uniqueidentifier null,
   cm_folder_title               nvarchar(256) not null,
   cm_is_visible               bit not null,
   cm_is_deleted                    bit not null,
   cm_load_id                  int not null,
   cm_folder_type_id            int,
   cm_is_series               bit not null,   
   cm_catalog_hierarchy_id_string   varchar(512) null,
   cm_catalog_hierarchy_id         as hierarchyid::Parse(cm_catalog_hierarchy_id_string) persisted,
   cm_parent_hierarchy_id         as hierarchyid::Parse(cm_catalog_hierarchy_id_string).GetAncestor(1) persisted,   
   cm_level                  as hierarchyid::Parse(cm_catalog_hierarchy_id_string).GetLevel() persisted,
   cm_order                  int null
)
go

--Switch to old_db to update it with generated code
use old_db
go

--At this point I run SQL Delta to compare the two databases.  The outout of the sync operation is pasted below

-- 
-- Script to Update dbo.catalog_master in 10.110.1.56.old_db
-- Generated Thursday, January 9, 2014, at 07:39 AM
-- 
-- Please backup 10.110.1.56.old_db before executing this script
-- 
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

PRINT 'Updating dbo.catalog_master Table'
GO

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

SET NUMERIC_ROUNDABORT OFF
GO


IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   CREATE TABLE [dbo].[tmp_catalog_master] (
   [cm_catalog_master_id] [int] IDENTITY (1, 1) NOT NULL,
   [cm_grouping_id] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [cm_cds_guid] [uniqueidentifier] NULL,
   [cm_folder_title] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [cm_is_visible] [bit] NOT NULL,
   [cm_is_deleted] [bit] NOT NULL,
   [cm_load_id] [int] NOT NULL,
   [cm_folder_type_id] [int] NULL,
   [cm_is_series] [bit] NOT NULL,
   [cm_catalog_hierarchy_id_string] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [cm_catalog_hierarchy_id] as ([hierarchyid]::Parse([cm_catalog_hierarchy_id_string])) PERSISTED,
   [cm_parent_hierarchy_id] as ([hierarchyid]::Parse([cm_catalog_hierarchy_id_string]).GetAncestor((1))) PERSISTED,
   [cm_level] as ([hierarchyid]::Parse([cm_catalog_hierarchy_id_string]).GetLevel()) PERSISTED,
   [cm_order] [int] NULL
)
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   SET IDENTITY_INSERT [dbo].[tmp_catalog_master] ON
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   INSERT INTO [dbo].[tmp_catalog_master] ([cm_catalog_master_id], [cm_grouping_id], [cm_cds_guid], [cm_folder_title], [cm_is_visible], [cm_is_deleted], [cm_load_id], [cm_folder_type_id], [cm_is_series], [cm_catalog_hierarchy_id_string], [cm_order])
   SELECT [cm_catalog_master_id], [cm_grouping_id], NULL, [cm_folder_title], [cm_is_visible], [cm_is_deleted], [cm_load_id], [cm_display_order], 0, NULL, NULL
   FROM [dbo].[catalog_master]
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   SET IDENTITY_INSERT [dbo].[tmp_catalog_master] OFF
GO

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
   DROP TABLE [dbo].[catalog_master]
GO

sp_rename N'[dbo].[tmp_catalog_master]', N'catalog_master'

IF @@ERROR <> 0
   IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO

IF @@TRANCOUNT = 1
BEGIN
   PRINT 'dbo.catalog_master Table Updated Successfully'
   COMMIT TRANSACTION
END ELSE
BEGIN
   PRINT 'Failed To Update dbo.catalog_master Table'
END
GO

--End SQL Delta output
use master
go
if db_id('old_db') is not null
begin
   alter database old_db set single_user with rollback immediate
   drop database old_db
end
go
if db_id('new_db') is not null
begin
   alter database new_db set single_user with rollback immediate
   drop database new_db
end



Output when I run the script:

Updating dbo.catalog_master Table
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'cc_verizon_migr_cc' to data type int.
Msg 15225, Level 11, State 1, Procedure sp_rename, Line 338
No item by the name of '[dbo].[tmp_catalog_master]' could be found in the current database 'old_db', given that @itemtype was input as '(null)'.
Failed To Update dbo.catalog_master Table



Please confirm if this is a bug and whether it can be fixed?

Thanks

Anders
anderscassidy
 
Posts: 6
Joined: Tue Aug 30, 2011 8:11 am

Re: Error copying data to updated table structure

Postby david » Mon Jan 13, 2014 2:30 am

Hi,

Thanks for the detail, I tested the compare and sync using Version 6 and everything scripted as required.
To download version 6 visit client.sqldelta.com/download and it can be installed side by side with V5.
Version 5 attempts to keep all possible data and incorrectly in this instance.
Given the complexity of the change I doubt V5 will be updated to correct this error.

Regards
David
david
Site Admin
 
Posts: 423
Joined: Mon Nov 10, 2003 11:22 pm

Re: Error copying data to updated table structure

Postby anderscassidy » Tue Jan 21, 2014 5:13 pm

Thanks for the reply. A couple of questions:

1) Are new licenses required for v6 (we have several for v5)?
2) Is the command line supported in v6 as I am running it as part of an automated build?
anderscassidy
 
Posts: 6
Joined: Tue Aug 30, 2011 8:11 am

Re: Error copying data to updated table structure

Postby david » Wed Jan 22, 2014 10:44 pm

Hi,

You can install V6 along side V5 using the same reg code and you are licensed separately for V6 (as long as you have an up to date update subscription)

V6 has command line interface however it does not translate/import very well from V5. We have created on completion events that now run within the GUI mode and are more extensive than the old command line function. The on completion events are hopefully easier to configure than manually changing the XML project code. For command line mode you just enter the project name as the command line parameter and Delta 6 runs that project executing any command line events.

Regards
David
david
Site Admin
 
Posts: 423
Joined: Mon Nov 10, 2003 11:22 pm


Return to Support Version 5