May 18, 2007
I have one Oracle Server (Version 10g) with some simple tables which should be replicated to an SQL 2005 Enterprise Server.
i got this error:
Error messages:
The process could not bulk copy into table '"dbo"."S_AGRZ"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Batch send failed
Violation of PRIMARY KEY constraint 'MSHREPL_38_PK'. Cannot insert duplicate key in object 'dbo.S_AGRZ'. (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627
To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253
bcp "RS"."dbo"."S_AGRZ" in "F:Microsoft SQL ServerMSSQL.2MSSQLReplDatauncACPRS2_DISTRIBUTION_S_AGRZ20070518023422S_AGRZ_2.bcp" -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SSVIESQL04RS2 -T -w (Source: MSSQLServer, Error number: 20253)
i set the publisher to drop the table and create a new one when i reinitialize with a new snapshot. it only works when i remove the unique key constraint. but after that the data in the replicated table has wrong entries. a select count ... will show the exacly same number as an select count ... on the original table direktly on the oracle server. i reinitialzied the replication multiple times with new snapshots. but the problem does not resolve. first i got a unique key constraint. then i typed truncate table ..., after that i removed the the unique constraint and than all data will be imported but there are some entries that are not existing in oracle.
my server is patched with the newest updates for sql 2005 (Version 9.0.3159).
for exempla the following statement will show the same results on oracle and sql 2005:
Oracle:
SELECT COUNT(*) FROM S_AGRZ
Result: 471.066
MS-SQL:
SELECT COUNT(*) FROM S_AGRZ
Result: 471.066
but, for example this will show differences:
Oracle:
SELECT * FROM S_AGRZ WHERE art_nr='C7972A'
Result:
ART_NR
AGRA_CD
AGR_CD
AGRZ_DTANLAG
AGRZ_USERANLAG
AGRZ_DTAENDG
AGRZ_USERAENDG
C7972A
HW
005
NULL
NULL
NULL
NULL
C7972A
KO
490
NULL
NULL
NULL
NULL
C7972A
R
KEIN
NULL
NULL
NULL
NULL
MS-SQL:
SELECT * FROM S_AGRZ WHERE art_nr='C7972A'
Result:
C7972A R KEIN NULL NULL NULL NULL
C7972A HW 005 NULL NULL NULL NULL
C7972A HW 240 NULL NULL 2007-05-11 09:13:19.000 SCHWARZJ
C7972A KO 490 NULL NULL NULL NULL
C7972A R AUT NULL NULL NULL NULL
C7972A KO 490 NULL NULL NULL NULL
The scripts for the tables:
Oracle:
CREATE TABLE S_AGRZ
(
ART_NR VARCHAR2(20 BYTE) NOT NULL,
AGRA_CD VARCHAR2(2 BYTE) NOT NULL,
AGR_CD VARCHAR2(15 BYTE) NOT NULL,
AGRZ_DTANLAG DATE,
AGRZ_USERANLAG VARCHAR2(10 BYTE),
AGRZ_DTAENDG DATE,
AGRZ_USERAENDG VARCHAR2(10 BYTE)
)
ALTER TABLE S_AGRZ ADD (
PRIMARY KEY
(ART_NR, AGRA_CD));
ALTER TABLE S_AGRZ ADD (
FOREIGN KEY (AGRA_CD)
REFERENCES S_AGRA (AGRA_CD));
ALTER TABLE S_AGRZ ADD (
FOREIGN KEY (AGRA_CD, AGR_CD)
REFERENCES S_AGR (AGRA_CD,AGR_CD));
MS-SQL:
USE [RS]
GO
/****** Object: Table [dbo].[S_AGRZ] Script Date: 05/18/2007 03:13:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[S_AGRZ](
[ART_NR] [nvarchar](20) NOT NULL,
[AGRA_CD] [nvarchar](2) NOT NULL,
[AGR_CD] [nvarchar](15) NOT NULL,
[AGRZ_DTANLAG] [datetime] NULL,
[AGRZ_USERANLAG] [nvarchar](10) NULL,
[AGRZ_DTAENDG] [datetime] NULL,
[AGRZ_USERAENDG] [nvarchar](10) NULL
CONSTRAINT [MSHREPL_37_PK] PRIMARY KEY CLUSTERED
(
[AGRA_CD] ASC,
[AGR_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
does anyone know how to solve this problem?
View 5 Replies
View Related
Mar 14, 2006
This thread is no longer active. I moved issue to a new, cleaner thread named: How can I get Transactional Republishing to work?
--------------------------------------------------------------------------------------------------------------------
I've tried for more than a week to figure this one out, but have not found a solution. I'm missing some critical information that perhaps someone can share. I'm desperate and embarassed that this has kept me stuck for so long. Thanks for any assistance.
In my simplist scenario, I'm transactionally replicating a single table from database A to database B and then from database B to database C. All databases are on the same XP server (in this simplist version of the problem). This is SQL 2005 RTM. Publication A to B replicates fine. Publication B to C can't even get the snapshot to work because I get the error:
Command attempted:
drop Table "dbo"."MyReplTable"
(Transaction sequence number: 0x00004174000000E100A300000000, Command ID: 41)
Error messages:
Cannot drop the table 'dbo.MyReplTable' because it is being used for replication. (Source: MSSQLServer, Error number: 3724) Get help: http://help/3724 (fyi - there is no help here)
I have a simple example script where I've renamed real names to fake names, that is exactly what I run to simulate the problem. Obviously the script is wrong, but where? I used the wizard to create the whole script (I know that is my first mistake ;-) ). After I start the snapshot jobs, I get the error above.
I presume that the sp_addarticle option @pre_creation_cmd = N'drop' is part of the problem. I don't have row filters. Also, I want to be able to replicate schema modifications when necessary (like add a column or drop a column)
Perhaps the sp_addpublication @repl_freq = N'continuous' option in the AtoB publication prevents the BtoC publication from ever getting a chance to do anything, if it never lets go of the table.
If it helps, here is my script with hopefully helpfully renamed objects. My real world scenario has many articles, but this is a simple version - 2 publications each having 1 article (the same table).
/****** Scripting replication configuration for server MyInstance. Script Date: 3/14/2006 11:16:58 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Installing the server MyInstance as a Distributor. Script Date: 3/14/2006 11:16:58 AM ******/
use master
exec sp_adddistributor @distributor = N'MyInstance', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData', @data_file_size = 4, @log_folder = N'C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:ThisFolderMY2005DBWorking
epldata', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', 'C:ThisFolderMY2005DBWorking
epldata', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'MyInstance', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:ThisFolderMY2005DBWorking
epldata', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
use [DatabaseA]
exec sp_replicationdboption @dbname = N'DatabaseA', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [DatabaseA]
exec sp_addpublication @publication = N'PubAtoB', @description = N'Transactional publication of database ''DatabaseA'' from Publisher ''MyInstance''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'PubAtoB', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
use [DatabaseA]
exec sp_addarticle @publication = N'PubAtoB', @article = N'MyReplTable', @source_owner = N'dbo', @source_object = N'MyReplTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'MyReplTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboMyReplTable', @del_cmd = N'CALL sp_MSdel_dboMyReplTable', @upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO
-----------------BEGIN: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseA]
exec sp_addsubscription @publication = N'PubAtoB', @subscriber = N'MyInstance', @destination_db = N'DatabaseB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PubAtoB', @subscriber = N'MyInstance', @subscriber_db = N'DatabaseB', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20060314, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseB]
exec sp_replicationdboption @dbname = N'DatabaseB', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [DatabaseB]
exec sp_addpublication @publication = N'PubBtoC', @description = N'Transactional publication of database ''DatabaseB'' from Publisher ''MyInstance''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'PubBtoC', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
use [DatabaseB]
exec sp_addarticle @publication = N'PubBtoC', @article = N'MyReplTable', @source_owner = N'dbo', @source_object = N'MyReplTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'MyReplTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboMyReplTable', @del_cmd = N'CALL sp_MSdel_dboMyReplTable', @upd_cmd = N'SCALL sp_MSupd_dboMyReplTable'
GO
-----------------BEGIN: Script to be run at Publisher 'MyInstance'-----------------
use [DatabaseB]
exec sp_addsubscription @publication = N'PubBtoC', @subscriber = N'MyInstance', @destination_db = N'DatabaseC', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PubBtoC', @subscriber = N'MyInstance', @subscriber_db = N'DatabaseC', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20060314, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'MyInstance'-----------------
View 7 Replies
View Related
