INSERT INTO @srcdest
		(id
       ,srcdest_name
       ,dbtype
       ,dsn
       ,userid
       ,password
       ,maxpoolsize
       ,maxnumconn
       ,member_access
       ,ir_control_schema
       ,ir_notification_address
       ,default_qualifier
       ,default_owner
       ,connection_string_override
       ,integrated_security)
SELECT 
		newid() as id, 
		v3.[srcdest_name],
		3 as dbtype, 
		v3.dsn, 
		v3.userid,
		v3.password,
		v3.maxpoolsize,
		v3.maxnumconn,
		v3.member_access,
		v3.ir_control_schema,
		v3.ir_notification_address,
		v3.default_qualifier, 
		v3.default_owner, 
		v3.connection_string_override,
		v3.integrated_security
  FROM @srcdest v3
  left outer JOIN @srcdest staging
  on v3.srcdest_name=staging.srcdest_name and staging.dbtype=3
  where v3.dbtype=1 and v3.ir_control_schema is not null and staging.id is null
  ^

  create table @property(
	regname nvarchar(128) NOT NULL,
	regtype nvarchar(128) NOT NULL,
	regbool bit NULL,
	regword int NULL,
	regsz   nvarchar(4000) NULL,
	regbinary varbinary(4000) NULL,
 constraint property_pkey primary key nonclustered (regname),
)
^

create table @ss_position(
	subscription_id uniqueidentifier NOT NULL,
	ss_state smallint NULL,
	ss_bookmark varchar(max) NULL,
	ss_committed_count bigint NULL,
	ss_indoubt_bookmark varchar(max) NULL,
	ss_indoubt_count bigint NULL,
	ss_lasttime datetime NULL,
 constraint ss_position_pkey primary key nonclustered (subscription_id),
 constraint ssposition_fkey_subscription foreign key (subscription_id) references @subscription (id) on delete cascade
)
^

alter table @srcdest add dbms_type smallint not null default 0
alter table @srcdest add capabilities int not null default 0
^

alter table @ir_group add cdc_audit_level smallint
alter table @ir_group add cdc_audit_use_target smallint
alter table @ir_group add cdc_audit_sp varchar(128)
^
update @ir_group set cdc_audit_level=0, cdc_audit_use_target=0
^

update @ir_subscription set capture_options = capture_options & 0xFFFBFFDF
^ 

update @ir_subscription set capture_options = capture_options & 0xFFFFFFBF
where subscription_id in (select s.id from @subscription s where s.rrn_column <> 0)
^

update @ir_subscription set capture_options = capture_options | 0x40
where subscription_id in (select s.id from @subscription s where s.rrn_column = 0)
^

update @ir_subscription
set capture_options = capture_options | 0x200000
where subscription_id in(
SELECT subscription_id
  FROM @sqconstraints
  where constraint_type='P'
  group by subscription_id
  having count(*)>0
)
^

update @ir_subscription
set capture_options = capture_options | 0x200000
where subscription_id in(
SELECT subscription_id
  FROM @sqindexes
  where is_unique=0
  group by subscription_id
  having count(*)>0
)
^

update @ir_subscription
set capture_options = capture_options | 0x400000
where subscription_id in(
SELECT id
  FROM @subscription
  where rrn_column>0
)
^

USE @\q
^

CREATE PROC @\s.SQLOGGER 
	#group_id as uniqueidentifier,
	#subscription_id as uniqueidentifier,
	#resync_state as char(1),
	#transaction_id as binary(10),
	#change_row_counter as bigint ,
	#change_row_sequence as bigint ,
	#change_row_log_detail as varchar(512) ,
	#change_row_member as varchar(10) ,
	#change_row_timestamp as datetime ,
	#change_row_nanosecond as int ,
	#change_row_type as char(1) ,
	#action_taken as char(1) ,
	#result_row_count as int ,
	#flagged_count as bigint ,
	#keylog_ts as datetime ,
	#result_error as varchar(max) ,
	#before_key as varchar(max) ,
	#before_key_edited as varchar(max) ,
	#after_key as varchar(max) ,
	#after_key_edited as varchar(max) ,
	#after_image as varchar(max) 

AS 

INSERT INTO @ir_keylog
           (
            group_id
           ,subscription_id
           ,resync_state
           ,transaction_id
           ,change_row_counter
           ,change_row_sequence
           ,change_row_log_detail
           ,change_row_member
           ,change_row_timestamp
           ,change_row_nanosecond
           ,change_row_type
           ,action_taken
           ,result_row_count
           ,result_error
           ,flagged_count
		   ,keylog_ts
           ,before_key
           ,before_key_edited
           ,after_key
           ,after_key_edited
           ,after_image)
     VALUES
           (
			#group_id ,
			#subscription_id,
			#resync_state ,
			#transaction_id ,
			#change_row_counter ,
			#change_row_sequence ,
			#change_row_log_detail  ,
			#change_row_member  ,
			#change_row_timestamp  ,
			#change_row_nanosecond  ,
			#change_row_type  ,
			#action_taken ,
			#result_row_count ,
			#result_error  ,
			#flagged_count  ,
			#keylog_ts ,
			#before_key  ,
			#before_key_edited  ,
			#after_key  ,
			#after_key_edited  ,
			#after_image  
 )
 ^
