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


ADVERTISEMENT

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 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 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

Monitor Transactional Replication Latency

Jun 12, 2008

CREATE PROCEDURE [dbo].[proc_replication_latency_monitor]
AS

-- Procedure created by Henning Frettem (Lumbago), www.sqlteam.com, 12th of June 2008

BEGIN

SET NOCOUNT ON

DECLARE
@PublishedDBName varchar(200),
@SQL nvarchar(2000),
@PublicationPK int,
@PublicationName varchar(200),
@PublicationDatabase varchar(200),
@TracerTokenID int,
@Parmameters nvarchar(500)

DECLARE @Publications table (
PublicationPK int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
PublicationDatabase varchar(200),
PublicationName varchar(200),
TracerTokenID int,
TracerTokenPostTime datetime
)

DECLARE @Latency table (
PublicationPK int,
DistributorLatency bigint,
Subscriber sysname,
SubscriberDB sysname,
SubscriberLatency bigint,
OverallLatency bigint
)

--> Cursor for fetching all publications in all databases
DECLARE curPublishedDatabases CURSOR LOCAL FAST_FORWARD FOR
SELECT [name] FROM sys.databases WHERE is_published > 0

OPEN curPublishedDatabases
FETCH curPublishedDatabases INTO @PublishedDBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'SELECT ''' + @PublishedDBName + ''', [name] FROM ' + @PublishedDBName + '.dbo.syspublications'
INSERT INTO @Publications (PublicationDatabase, PublicationName)
EXEC sp_executesql @SQL

FETCH NEXT FROM curPublishedDatabases INTO @PublishedDBName
END

CLOSE curPublishedDatabases
DEALLOCATE curPublishedDatabases

--> Cursor for posting tracer tokens
DECLARE curPublications CURSOR LOCAL FAST_FORWARD FOR
SELECT PublicationPK, PublicationDatabase, PublicationName FROM @Publications

OPEN curPublications
FETCH curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Parmameters = N'@PublicationName varchar(200), @TracerTokenID_OUT int OUTPUT';
SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_posttracertoken @publication = @PublicationName, @tracer_token_id = @TracerTokenID_OUT OUTPUT'
EXEC sp_executesql @SQL, @Parmameters, @PublicationName = @PublicationName, @TracerTokenID_OUT = @TracerTokenID OUTPUT

UPDATE @Publications SET
TracerTokenID = @TracerTokenID,
TracerTokenPostTime = GETDATE()
WHERE PublicationPK = @PublicationPK

FETCH NEXT FROM curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
END
CLOSE curPublications
DEALLOCATE curPublications

--> Wait two minutes for all tokens to be commited at all subscribers
WAITFOR DELAY '000:02:00.000'

--> Then check the results for each posted token
DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR
SELECT PublicationPK, PublicationDatabase, PublicationName, TracerTokenID FROM @Publications

OPEN curTokens
FETCH curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID
WHILE @@FETCH_STATUS = 0
BEGIN
--> Insert token history for each token
SET @SQL = N'EXEC ' + @PublicationDatabase + '.dbo.sp_helptracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + ''
INSERT INTO @Latency
(DistributorLatency, Subscriber, SubscriberDB, SubscriberLatency, OverallLatency)
EXEC sp_executesql @SQL

--> Make sure that the PublicationPK is added to the token history
UPDATE @Latency SET PublicationPK = @PublicationPK WHERE PublicationPK IS NULL

--> Clean up the tracer token
SET @SQL = N'EXEC ' + @PublicationDatabase + '.sys.sp_deletetracertokenhistory @publication = ''' + @PublicationName + ''', @tracer_id = ' + CAST(@TracerTokenID AS VARCHAR(50)) + ''
EXEC sp_executesql @SQL

FETCH NEXT FROM curTokens INTO @PublicationPK, @PublicationDatabase, @PublicationName, @TracerTokenID
END
CLOSE curTokens
DEALLOCATE curTokens

SELECT
b.PublicationDatabase,
b.PublicationName,
a.Subscriber,
a.SubscriberDB,
a.OverallLatency,
b.TracerTokenPostTime
FROM @Latency a
INNER JOIN @Publications b
ON a.PublicationPK = b.PublicationPK

END

--
Lumbago

View 7 Replies View Related

Transactional Replication And Snapshot Schedule

May 21, 2002

I've set up Transactional replication Pushed from SQL 7 to SQL 2000. All works fine accept I have noticed that the snapshot agent is running once a week and storing a lot of data I don't really need in c:msql7
epldataunc
eplicationnamedatastamp. This is gradually building up and as C is a smallish partition it'll only survive for another week.

My questions are:
1) Can the Snapshot schedule be disabled? I'm under the impression that it's only required when a new subscriber comes along which is never! If I do need a new subscriber I could just manually produce another snapshot.

2) Can I delete the previous snapshot folders under ReplData? On a development server I renamed one of the folders and the ground didn't open up below my feet.

3) What effect would changing the working folder used by the distribution agen have, the distributer is on the publisher (which as I mentioned is pushing) I've tried changing the working folder, on a development server, I manually ran a snapshot which whose BCP files all ended up in my new location and the Transactional Replication didn't seem to care.

I've looked in a few places for a difinitive answer and I'm not having much luck so any help (preferably before saturday) would be greatly appreciated.

Regards.

View 1 Replies View Related

Combining Transactional And Snapshot Replication

Feb 2, 2004

Hi

I am in the process of setting up replication between two SQL boxes - the first being the production SQL Server and the second being a secondary server which is to be used as part of a web portal solution. (In fact there are two pairs of SQL boxes - SQL 7 and SQL 2k, one of each on the LAN and one of each at the hosting site.)

We need to have near realtime replication, so transactional replication is the desired mechanism. The trouble is that not all tables have primary keys defined. Oh yes, and the main database is 20G in size and has >900 user tables! :-0 (The other databases are much better behaved!)

When I set up transactional replication, I am allowed only to include tables which have PKs defined. AFAIK, the initial transactional replication snapshot will also only include these same tables.

If I set up snapshot replication (separately), I can include all the tables in the database. However, I cannot then replicate in real time.

Can I combine the two replication schemes to deliver updates to the same target database:
- transactional replication delivering realtime updates to the tables with PKs during the day and
- snapshot replication updating all the tables once per 24hrs at night?

Or is there a better way of doing this?

I am not sure whether I can modify the existing schemas, as some of the databases are 'maintained' by an external provider. Even if I could, if I had to add a column to have a PK, I would potentially be adding to my diskspace requirement rather significantly...

TIA

View 2 Replies View Related

Transactional Replication && Snapshot Agent

Jun 27, 2007

Why would you schedule a Snapshot Agent to run daily, weekly, monthly, etc. for Transactional replication when the transactions are replicated once the change is committed to the Publication database?



Does this help to replicate DDL against the Subscriber?



Thanks,

Thomas LeBlanc

View 1 Replies View Related

Transactional Replication- Snapshot Agent

Sep 7, 2006

When you create a transactional Replication.Is there a way to disable to snapshot agent scheduler. I believe that there is a way to disble it when you create a publisher.



Help ME!!!!!!!!!!!!!!!

View 3 Replies View Related

How Often To Rebuild Snapshot In Transactional Replication

Mar 27, 2007

Hello,

We are running transactional replication in a high volume environment and we are starting to see high IO on the distribution database when the distribution cleanup runs. We only build the snapshot once a week and I was wondering if I rebuilt the snapshot more often it would reduce the size of the distribution database and the amount of IO that are being generated during the cleanup?



Thanks in advance

View 4 Replies View Related

Alternate Snapshot Location For Transactional Replication

Jun 2, 2006

Hi,
I have transactional replication setup with SQL 2000 on W2K3 cluster using updateable push subscribers. While setting up replication, we chose default location for snapshot folder that resides on non-clustered drive. Is there a way to change this location without disturbing current replication setup. I looked at the 'alternate snapshot location' solution, but it requires snapshot re-initialization. I am trying to do this with minimal effort and downtime.
Thanks,
np70

View 3 Replies View Related

Transactional Replication - Automating A Regular Snapshot

Feb 13, 2004

I have transactional replication running. I would like the Snapshot Agent to run regularly (once per day, probably, at 23:00) so that the target data are completely refreshed so that there is little possibility of the target getting out of sync.

The Snapshot Agent's job is kicking off OK, but the "snapshot was not generated because no subscriptions needed initialization". I know I can initialise the subscriptions manually - is there a way to do this so that the initialisation happens just before the snapshot job launches?

Or is this a bad thing to want to do?

TIA

View 1 Replies View Related

Initializing Transactional Replication Without A Snapshot In SQL 2000

Nov 10, 2006

SQL Server 2005 Books Online provides an article entitled, "Initializing a Transactional Subscription without a Snapshot". Is it possible in SQL Server 2000 to initialize transactional replication without a snapshot?

So far, I have been unable to find a similar procedure mentioned in the SQL 2000 Books Online. I was able to follow the 2005 procedure using SQL 2000 until I got to the step that says to enable the "Allow initialization from backup files" option on the "Subscription Options" tab of the "Publication Properties" dialog. But that option does not appear in the SQL 2000 version of the specified dialog box.

Any ideas?

View 4 Replies View Related

Do I Really Need A Snapshot (to Initialize Transactional Replication, In SQL2000)?

Feb 20, 2006

I have a pretty big (350 gb) OLTP database that I want to replicate in its entirety. I'm concerned about the impact of taking a snapshot of it (it is processing at some level pretty much 24x7). I know on SQL2005 there is the option to initialize from backup, but unfortunately we won't be on 2005 in time.

I'm thinking of doing something like this:

Set up the distributor, publication, and subscription
Turn off distribution agent
Set the publisher to "sync with backup"
Backup the publisher, full then log
Truncate tables MSrepl_transactions and MSrepl_commands in the distribution db (I don't have any other replication going on)
Turn off "sync with backup"
Restore the full and tran log backups to new subscriber db
Create subscriber stored procs in subscriber
Start up distribution agent

I'm looking for opinions on whether it's worth going this route to avoid taking the snapshot. Data integrity is the number one priority -- if I have to do a snapshot to ensure that, I will do it.

Thanks in advance!

Mike

View 1 Replies View Related

SQL 2012 :: Script Failure During Snapshot For Transactional Replication

Apr 9, 2014

I built a number of publications on a SQL Server 2005 box to replicate to a SQL Server 2012 subscriber. All the publications except one are fine. During the snapshot phase of schema script generation I get Script Failed for Table 'dbo.MediaDisplayLibraryFileData'. From the Replication monitor for the Snapshot Agent on the Publication I get, "Column FileData in object MediaDisplayLibraryFileData contains type VarBinaryMax, which is not supported in the target server version, SQL Server 2000." This message makes no sense since the target server version is 2012. I have even checked that the compatibility level was set to 110 before I started the process of setting up replication. How do I resolve this error?

View 0 Replies View Related

Transactional Replication - Pull Subscription Snapshot Being Applied Continuously

Jan 25, 2007

Hi

We have setup transactional replication between 2 databases on SQL Server 2000 SP3a  (~70GB), using a concurrent snapshot (to prevent locking out of the live database) to initilaise the data and a pull subscription from the second database.

From analysing the msdistribution_history table in the distribution database on the subscriber it appears that the snapshot is being applied in a continuous loop to the subscriber database. Viewing the comments column in the msdistribution_history table we can see the following sequence of events occuring

Initialising
Applied script 'snapshot.pre'
Then it applies all the schema files .sch
Then it applies all the index files .idx
The it bulk copies the data in (bcp)
Then it creates the Primary Keys
Then it applies all the trigger files .trg
Then it applies all the referential integrity files .dri

These all complete successfully but then the process kicks off again immediately after reapplying the snapshot.  We are unaware of any settings that may be causing this.

Any help on what maybe causing this would be much appreciated.

 

 

View 5 Replies View Related

Red X Show On Replication Monitor (publications Is Disconnected From Replication Monitor)

Jan 30, 2007

I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.

Everyone known how to fix this problem?

Thanks,

Frank

View 1 Replies View Related

Can Not Generate Snapshot (SQl Server 2005 Transactional Repl )

Sep 15, 2005

Hi,

View 6 Replies View Related

Looking For Tools To Monitor Sql And Applications

Jul 20, 2005

I am looking for the right tools to do application monitoring.I'm hoping to find one single tool that can do the entire job but ifit does not exist then a few monitoring apps would do as well.I need the ability to do the standard things like testing for ping,checking for windows services running and restarting them after somethreshold is met, and wmi.Some of the more tricky things I need it to do are:* Parse log files looking for specific error codes, and the ability toset an alert only if it sees that error X times over some period oftime.* Run custom slq queries like row counts and max values returned froma query and if that condition is met X times over some period oftime.* Run an external app that does its own custom testing and act on itsresults, which could be to parse the result file from the app.Keep in mind cost is not the isssue right now - so this can befreeware to some type of enterprise solution that our company can use.Does anyone know of any tools that you can point me towards?Thanks...

View 1 Replies View Related

Creating Snapshot / Transactional Table By Appending

Feb 6, 2015

I am very new to SQL coming from a SAS background.

I work at a College where I am trying to create a table that basically stores application data so that I can record how many applications we had on a set day.

For example I want to run it say bi-weekly and create a date/time stamp for each row and then the next time it runs it appends to the original table but with the new date/time stamp.

I have managed to create the table and the date/time stamp but I'm not sure how to do the next stage.

This is not the best practice as the table will get "big" quite quickly (although we're only dealing in the hundreds in terms of rows). Ideally I'd have a more transactional database that only appends a record if there is a change i.e. the status of the application has changed from "Received" to "Interview" or similar.

I'm not sure how to copy and paste the code dynamically in this forum but this is what I have got so far (see below).

SELECT
CC.cc_name as Academy,
SUBSTRING(CC2.cc_reference,1,4) as Cost_Centre,
MD2.m_reference as Course,
MD2.m_name as Course_Name,
CAST(MD2.m_start AS DATE) as Start_Date,

[Code] ...

View 4 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 Tools :: Activity Monitor Gives Access Is Denied

Apr 23, 2013

When I try to open Activity Monitor from SSMS I receive the message "Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)". - more details below.

I have a SQL Server 2012 Enterprise SP1 installed in an Active/Passive cluster configuration on Windows 2008 R2 Enterprise SP1. The problem happens using sa and a domain administrator.

------- more details -------
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
   at System.Management.ManagementScope.InitializeGuts(Object o)
   at System.Management.ManagementScope.Initialize()
  
[Code] .....

View 5 Replies View Related

SQL Server 2012 :: How To Monitor Replication Count Using Code

Jun 4, 2015

I Want to monitor Replication count of object (Table )if it is not equal to Publication (Table ) and subscriber (Table ), It have to send mail with count difference.

View 9 Replies View Related

SP2 On Server With Snapshot Replication

Aug 23, 2007

I have two clusters and one publishes snapshots and the other cluster subscribes. I am planning an SP2 installation but I see in the SP2 readme that there are only special considerations for merge and transaction replication.

Will I need to patch in a certain order and will it matter if I patch the publisher who is also the distributor and then a week later patch the subscriber?

Thanks

View 1 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 Tools :: How To Trace Or Monitor Previous Actions In 2012

Sep 12, 2015

how to trace or monitor previous actions in sql server 2012. Like previous using delete command . I want to know who is used that command in server.

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

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 View Related

Mirror Monitor Error: Server Replication Requires The Actual Server Name To Make A Connection

Oct 17, 2007

I'm getting the following error when launching the database mirroring monitor and attempt to connect to my database.


TITLE: Connect to Server
------------------------------

Cannot connect to dborat01.hs.pitt.edu.

------------------------------
ADDITIONAL INFORMATION:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'xxxxxx'. (Replication.Utilities)

------------------------------
BUTTONS:

OK
------------------------------

View 3 Replies View Related

SQL Server 2008 :: Snapshot Replication To Different Schema Name

Apr 25, 2015

I need to setup snapshot replication for 1 large table.

Source table: SourceDatabaseName.dbo.MyTable1
Target table: TargetDatabaseName.CustomSchema.MyTable1

In the above example, how can I specify the target DB's CustomSchema name (it's currently creating dbo, not CustomSchema)

View 1 Replies View Related

SQL Server 2005 - Replication - Snapshot Failing

Dec 14, 2006

I have a "merge" publication created, with only Procedures, Views and Functions. (Note I have other merge publications that replicate the tables from the same database). I keep getting the same error (see -below) on various procedures. I cannot find anything wrong with the procedures themselves. I also checked the offending procedure by removing it from the publication and compiling it in the database...it works fine. None of my other publications (all tables) encounter this error. * I have 3 others.

My version of SQL is 9.00.1399.06

Here is the message I am getting. Any help would be greatly appreciated. Note I changed the proc name due to client restrictions.


Message: StartIndex cannot be less than zero.
Parameter name: startIndex
Command Text:
select 'number' = convert(int, 0), 'definition' = definition
from sys.sql_modules
where object_id = object_id(@qualified_object_name)
union all
select 'number' = convert(int, procedure_number), 'definition' = definition
from sys.numbered_procedures
where object_id = object_id(@qualified_object_name)
Parameters: @qualified_object_name = [dbo].[pra_merge]

Stack: at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.Script()
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateNonTableArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, SqlSmoObject smoObject, Boolean quotedIdentifierOn, Boolean ansiNullsOn, Boolean textMode)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateStoredProcedureArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.MergeSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 52006)
Get help: http://help/52006

Source: mscorlib
Target Site: System.Text.StringBuilder Remove(Int32, Int32)
Message: StartIndex cannot be less than zero.
Parameter name: startIndex
Stack: at System.Text.StringBuilder.Remove(Int32 startIndex, Int32 length)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.TextModeOnObjectScripter.ProcessGetObjectScriptResult(SqlDataReader dataReader)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior) (Source: mscorlib, Error number: 0)

View 8 Replies View Related







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