Oracle To SQL Server Transactional Replication Error

Jun 6, 2007

Hi,



I'd appreciate any help i can get here. I'm working with an MS SQL Server 2005 SP2 db on a Windows 2003 OS. I set up replication from an Oracle 10g instance a few months ago, and everything was working until i installed SP2 for SQL Server.



I get the following error:

2007-06-06 16:06:22.488 Status: 4096, code: 20024, text: 'Initializing'.

2007-06-06 16:06:22.488 The agent is running. Use Replication Monitor to view the details of this agent session.

2007-06-06 16:06:23.082 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.

2007-06-06 16:06:23.082 ORA-01008: not all variables bound

2007-06-06 16:06:23.082 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.



I can get snapshot replication to work just fine, but transactional replication throws up the error above.



Anyone with any ideas on how i can proceed?



Thanks in advance!



Regards,

Rama.

View 7 Replies


ADVERTISEMENT

Oracle 10g -&&> SQL 2005 Enterprise Transactional Replication

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

Transactional Replication - Error 207

Jan 19, 2003

Hi Guys,

I have a small problem with the transactional replication when I run the initial snapshot and distributing some of the stored procedures. For eg., Say if the stored procedure has the following statements:

---------------------------------------
create procedure abc
as
declare @paytype varchar(50)
set @paytype='check'

insert into acct select cusname,cusacctno,@paytype from cuspay
---------------------------------------

I get the following error:
for statement:

set @paytype='check'
--------------------
Invalid column name 'check'.
(Source: ServerSQL1 (Data source); Error number: 207)

I tried changing to double quotes. even tried adding article using query analyzer without using EM

exec sp_addarticle @publication = N'ReplicationSrc', @article = N'abc', @source_owner = N'dbo', @source_object = N'abc', @destination_table = N'abc', @type = N'proc schema only', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000001, @status = 16
GO

Still I get the same error. The server has SQL server 2000 SP2 running with remote distributor and pull subscription.

Am I missing anything.


Please help.

Thanks,
Anu

View 2 Replies View Related

Transactional Replication Error

May 5, 2006

I am running sql 2000 sp3.
A pull subscription failed with the following.

errormsg: Cannot drop the table 'table_name' because it is being used for replication.

errordetail: Cannot drop the table 'table_name' because it is being used for replication.
(Source: SQLTEST2 (Data source); Error number: 3724)

This error started after a field was dropped and a new field added to one of the articles in the publication - changes were done through Enterprise Manager.

Why does this error come up. The Default Table Article Properties dialog box>>>Snapshot tab has the option "If a table with the same name as the destination table exists at the Subscriber>>>DROP the existing table and recreate it.

So if this is an option in replication for a Subscriber how come the error message says it cannot drop the table.

Any insight appreciated.

View 2 Replies View Related

Transactional Replication Error

Jun 5, 2006

Hello,
I am a newbie to replication, I recently came up with this error.
Cannot use DROP VIEW with 'dbo.vw_UndeliverableEmail' because
'dbo.vw_UndeliverableEmail' is a table.Use DROP TABLE.
I tried running these two queries to make sure.
SELECT * from syscomments where id = object_id('vw_UndeliverableEmail')
sp_help vw_UndeliverableEmail
I am sure that it is a view.
I am not sure how to proceed since the object referred is indeed a view rather than a table as the Replication Error tries to point out.
The details :
SQL Server 2005 / Transactional Replication/ Pull Subscription Any thoughts/helps/suggestions are most welcome.
Thanks

View 4 Replies View Related

Transactional LOB Replication Error

Feb 8, 2006

We are trying to replicate a varchar(max) column and receive the following error when trying to insert a record on the publisher:



Length of LOB data (75679) to be replicated exceeds configured maximum 65536.



The distribution agent is set up to use the oledb streaming profile so I can't figure out where this setting is.

View 6 Replies View Related

Updatable Transactional Replication, Queue Reader Error

Jul 3, 2007

I have just started getting the following error from the queue reader but I can't see why.



The Queue Reader Agent has encountered the error ''Row handle is invalid.'' when connecting to ''Database'' on ''ServerName''. Ensure that the publication and subscription are defined properly and that both servers are running.



Does anybody know what it means / how I can fix the problem?



Thanks

View 11 Replies View Related

Cannot Drop The Table Transactional Replication Error 3724

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

SQL 2000 Transactional Replication-skiperrors Parameter With Error Logging

Mar 15, 2007

I have used skiperrors parameter for 2601,2627 and 20598 errors in my Transaction replication setup. When transactions with above error are encountered, its skipped but I€™m not getting sql command that is skipped in MSrepl_errors table. Since SQL server 2000 is not saving transaction sequence number in the MSrepl_errors we are not getting the command, which have the errors.


Is there any way to track the commands those are skipped in transactional replication.

View 11 Replies View Related

Combining Peer-to-Peer Transactional Replication With Standard Transactional Replication

Dec 1, 2006

Hello,

I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:

We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.

Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?

We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.

That's the scenario.

Any feedback is welcome and appreciated.

Thanks,
Terence

View 4 Replies View Related

SQL Server - Transactional Replication Questions

Sep 5, 2005

Our database has grown to the point where our current server is struggeling with the query load. One option is to get a 4 processor machine with 16GB of RAM, but I'm also looking at transactional replication as a solution. Currently we run dual Xeon with 4GB of ram (using the /3GB switch in the OS) We have SQL 2000 Enterprise.
The idea is to setup a secondary server with transactional replication pushed from the main server, so that some SELECT-only queries can be executed on the secondary server - thus taking load of the main one. We should be able to add PKs to the small number of tables that currently don't have them, and we should be able to run all updates / inserts on the main server.I'll setup a push-subscription for the entire DB (maybe excluding some log tables) and then for ceratain stored procedures I'll direct our applications to use the backup instead of the main server.
So: Is this a good idea? Is it easy to backup the server using transactional replication? How much extra overhead will this mean for the main server?

View 1 Replies View Related

Sql Server 2005 -Not Allow The Transactional Replication

Aug 29, 2006

we have the standard licensed copy for sql server 2005

while configure transaction replication not allow to move

message is required licesence for replication again i have to get license for

replication

please advice

View 2 Replies View Related

SQL Transactional Replication Distribution Server Struggling

Jun 5, 2006

We are attempting to rollout a name and address system to 10,000 users who will use an application connected to an MSDE database.

We are using transactional replication to distribute data updates to them. Clients are connecting via the On-Idle feature of Synchronization Manager to grab transactions.

Server spec:
Network card: 1GB
Processors: 2* Xeon 3.2Ghz
Server spec: DL380 2Gb memory
Concurrent connections set to: 600
Disc: RAID 10 with 6400 controller

We are not using hyper-threading.

So far we have rolled the system out to 3500 subscribers, 500 per day.

Each day a subsciber will receive at least 400 transactions and 5000 commands.

Latency is 6 seconds, delivery rate 180 commands per second at less busy times.
Latency is 14 seconds, delivery rate 127 commands a second at busy times.

I have seen it get as slow as 0.04 commands a seconds at busy times.

The server becomes incredibly slow when there are more than 50 concurrent connections.

We are seeing 100 CPU for most of the day as clients connect to the distributor at various times. Lunchtime is particularly busy when people go to lunch, leaving their machines idle. We see lots of "time-outs" and "unable to connect to distributor" messages on the replication monitor during peak times.

What can we do to improve the performance of the distribution server?

Are we being over-ambitious by selection SQL Replication for this scenario?

Thank you for any help!

Best wishes

Julian

View 1 Replies View Related

SQL Server 2008 :: Schema Changes With Transactional Replication

Jun 15, 2015

How do we know our schema changes will propagate to subscriber without breaking the replication? Is there any t-sql command to find out the option true or false?

View 2 Replies View Related

SQL Server 2008 :: Transactional Replication (Snapshot Agent)

Mar 19, 2015

Is there a way to force the snapshot to use a format file when bcp'ng the data to the subscriber?

The publisher and subscriber schemas are slightly different. Mainly added columns to the subscriber.

We have a post snapshot script that updates these additional columns on the subscriber.

I'd like to force the snapshot to use a format file, so it doesn't error when initially loading the data to the subscriber.

Is this possible?

View 1 Replies View Related

SQL Server Admin 2014 :: Transactional Replication With Different Versions

May 17, 2015

We have installed 2014 sql server. We have currently 2008r2. We have to run the real time report. So we need to set up transactional replication b/n those two servers. We need to use 2008r2 as publisher and 2014 as subscriber.

Is it ok to have subscriber higher version than the publisher?

View 6 Replies View Related

SQL Server Admin 2014 :: Transactional Replication And CDC On Subscriber

Jun 15, 2015

Any experience of success or failure setting up CDC on the subscriber end of transactional replication?

Also for a bonus answer, why are explicit index operations not permitted (I'm assuming this is even on the publisher?) From BOL:

• Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.

View 0 Replies View Related

SQL Server Admin 2014 :: Setting Up Transactional Replication

Sep 10, 2015

I have created a publication on SQL 2014 SP1/CU1 it is going to a distribution server also a SQL 2014 database. The subscriber is also SQL 2014 SP1/CU1. When I set it up there are no errors. I fire up Replication MGR and it looks good after the snapshot and distribution agent have been started. Again, no errors. When I do a tracer token the information gets to the distributer in 1 - 4 seconds but it never gets to the subscriber, the tracer token monitoring screen only shows pending for distributor to subscriber.

I can upload the scripts used to create the publication and/or the subscription but am a little hesitant because of server names in the scripts.

View 2 Replies View Related

SQL Server 2008 :: Identify Which Indices Are Replicated - Transactional Replication

Feb 20, 2015

Is there a script to find which non-clustered indices are replicated? I know i can do this easily through GUI , having a script will make my life much easier ....

View 0 Replies View Related

SQL Server 2008 :: Transactional Replication And Subscriber Log Growth Errors

Jun 14, 2015

I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup

The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?

View 3 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot In Multiple Files

Sep 15, 2015

We have a large database with a small number of large tables in it (and a larger number of SMALLER tables), and it is a publisher for a transactional replication scenario. When I create a snapshot to initialize a new subscription, I notice with the larger tables that sometimes it generates multiple files in the snapshot folder, usually in multiples of 16, and numbers them like this:

MyTable_3#1.bcp
MyTable_3#2.bcp
...
MyTable_3#16.bcp

With other tables, I'll get just one LARGE snapshot file, named:

MyOtherTable_4.bcp

In the latter case, the file can be very large (most recent is 38GB).

In both cases, the subscription will eventually be initialized, but the smaller files will generate separate log entries every few minutes in the Replication Monitor, showing 'Bulk Copied data into 'MyTable' (34231221 rows)', whereas the larger table will generate only ONE log entry, showing 'Bulk coping data into table 'MyOtherTable', and it may take a couple of hours before there is anything else showing...except for an entry saying, 'The process is running and is waiting for a response from the server.'

My question is: what would be the difference between the two tables that would result in one generating MULTIPLE snapshot files, the other only a single, much larger one? The only difference I can see in the table definition is that the one generating multiple files has a clustered index, whereas the others do not.

View 0 Replies View Related

SQL Tools :: Script To Monitor Server Transactional And Snapshot Replication

Apr 28, 2015

Any TSQL script to monitor replication(Transactional, Snapshot) with current status ? I have tried below script but it giving error.

"Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested."

DECLARE @srvname VARCHAR(100)
DECLARE @pub_db VARCHAR(100)

View 4 Replies View Related

Transactional Replication Setup (SQL Server Could Not Configure 123456 As A Distributor

Jul 17, 2007

Hello



I have server "123456" SQL 2005 On clustered environment. I added permissions to snapshot folder, it is on sharing for both nodes.



I got error during the set up as:



Microsoft.SqlServer.ConnectionInfo



Additional Information:

An exception occurred while executing a TSQL or Batch(

Microsoft.SqlServer.ConnectionInfo)



An error occurred during the execution of xp_cmdshell. A call to "Create Process" failed with error code 5



Destination path d:Microsoft SQL ServerMSSQL.1MSSQLData is not Valid. Unable to list directory contents. Specify a valid destination path

Changed database context to 'master' (Microsoft SQL Server, Error 15121)



The datafolder exists, sql cluster service account from domain added in to snapshot folder, administrators access also on this folder.



Please advice how to troubleshoot this issues. Thanks, Jay

View 1 Replies View Related

Sql Server Merge Replication Vs. Sql Server Transactional With Updatable Subscribtion

Jun 12, 2006

Hi,

Transactional replication allows updatable subscriptions where changes at the subscriber are replicated up to the publisher, this can happen via Immediate Updating subscriptions, Queue subscriptions and P2P (new in SQL 2005), all forms of Transactional replication.

Any compared document between merge replication and Transactional with updatable subscribtion ?

Thanks,

Tarek Ghazali

SQL Server MVP

View 4 Replies View Related

Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication

Sep 13, 2007

Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.

View 2 Replies View Related

SQL Server Admin 2014 :: Transactional Replication With Filtered Published Data

Apr 15, 2015

From distribution db, which table(s) store info about filtered data?

View 0 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot - Does Not Generate Any Files For New Article

Oct 22, 2015

There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.

When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).

When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.

The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.

I have tried:

- dropping/re-adding the article in question.

- Setting up a new Snapshot Folder

- Validated all the settings and configurations

I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.

View 0 Replies View Related

Transactional Replication From SQL Server 2005 To SQL Server 2000

Nov 8, 2006

I am trying to test simple replication (only tables) of a database that resides on a SQL Server 2005 instance to a SQL Server 2000 instance. The Publisher and Distributer are set up on the SQL Server 2005 instance for Transactional replication. The subscriber is set up on in the 2000 instance. Replication Monitor shows the following error after applying a few scripts:
"Category: SQLSERVER Source SQLSERVER2000 Number: 170 Message: Line 6: Incorrect syntax near
'max'."


Here SQLSERVER2000 is the name of my 2000 instance, as should be obvious.

Beyond this point, replication fails. Any pointers as to where the problem could lie? Is this a known backward compatibility issue? I've checked all tables in the database and none contain any datatype that is new to 2005 (the database was actually created in and for SQL Server 2000.

Replication from 2000 to 2005 works fine, but the other way round is failing as described above. Any clues?

View 3 Replies View Related

Transactional Replication - Updatable Subscribers - Image Data Type- Sql Server 2000

Aug 21, 2007

Sql Server 2000


I am looking for bidirectional transactional replication using updatable subscribers (queued or immediate) . Is it possible to replicate the image data from the updatable subscribers to the publisher. I understood that the Image data can't be replicated to the publisher from the updatable subscriber. I am not using the WRITETEXT or UPDATETEXT. I am using just INSERT and UPDATE for image data type transactions.

Any thoughts on this is greatly appreciated.

Thanks in advance

View 3 Replies View Related

SQL Server 2005 - Transactional Replication Involving Depricated Text And Ntext Data Types

Dec 2, 2005

I'm currently trying out transactional replication with updatable subscriptions across two 2005 servers.

View 3 Replies View Related

Replication :: Transactional Replication Removal Sequence

Sep 2, 2015

I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication.  I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS.  I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication.  SQL-P (publisher), 

SQL-D (distributor) and SQL-S (subscriber).  Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.

View 6 Replies View Related

Applying SQL Transactional Log To Oracle (Critical)

Jun 7, 2002

We need to apply the transactional log for business hours (9-5) everyday to a oracle instance which will be seeded with SQL Data for once. And then every day the transactional log would be applied.

We need to do this as eventually this server is moving to oracle.

At this point if somebody could help me to read the transactional log that would be great.

Thanks,

View 1 Replies View Related

Merge Replication Set Off Transactional Replication

Oct 9, 2007

I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.

This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??

Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??

thanks...

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved