Change error logging for sys.sp_MSrepl_raiseerror

I have merge replication setup to a bunch of mobile subscribers. I want everything managed from the server and I want everything to run continuously. The reason we want this is that we want subscribers to automatically synchronize data within 60 seconds of plugging into the network from any location. We do not want to write anything to have to detect connectivity and initiate it. We do not want it initiated from a subscriber either. This creates a SEVERE logging issue and has halted a roll out at 2 different customers. We can't get beyond about 60 subscribers on a system that was running 3700 subscribers in SQL Server 2000. The server quite literally freezes and becomes non-responsive 1 - 2 times per day. We've tracked this back to the volume of logging into the Windows event log that is being caused by the replication engine.

When subscribers are disconnected, they throw constant errors, to the tune of 1 error every 2 minutes into the SQL Server error log as well as the Windows event log. This is because someone decided that these should be level 18 errors. There is no error. The publisher could not contact the subscriber, so I want it to simply log an error, shut up, go back to sleep, and then try again. I do NOT want a message in my SQL Server error log and I do NOT want a message in my Windows event log.

We are currently logging over 60,000 messages per day into both the Windows event log and the SQL Server error logs for something that we KNOW is a NORMAL operational state of the system. The merge agent doesn't have a parameter that I can feed it to ignore 14151 errors.

The culprit is in sys.sp_MSrepl_raiseerror. Since it is a system object, I can't override it and change the severity level of the error. So, right now we are stuck and the SQL Server error log as well as Windows event logs are being rendered quite useless on the system.

The offending code is:

raiserror (14151, 18, -1, @.agent, @.agent_name, @.message)

Does anyone have any idea how I can forcefully change sys.sp_MSrepl_raiseerror or in some other way suppress the logging of 14151 errors from the Windows and SQL Server logs?

More information.

We have continuous, push merge replication setup to a bunch of subscribers that routinely disconnect. It is merge replication, because we need to make changes at both publisher and subscriber in a disconnected model and this is exactly what merge replication was designed to do. It is setup as push, because we want all agents to be run from the distributor for centralized management and monitoring. It is continuous, because we want as low of latency as possible and for any subscriber that plugs in to begin synchronizing as soon as possible. In otherwords, we have a normal configuration, with very normal and reasonable requirements and we are using the merge engine in precisely the way it was designed and intended to be used.

However, it seems that running all of your agents at the distributor is absolutely incompatible with having disconnected subscribers with merge replication. It is quite literally impossible to run any architecture of any size with merge replication that has disconnected subscribers, unless you are running your agents at the subscriber and are then forced to manage them at the subscriber.

How did I reach this? Quite simply. replmerg.exe is the merge replication engine. It is coded to detect when a subscriber can not be contacted. (A normal state when a subscriber is disconnected.) When it detects one of these, it makes a call to sp_MSadd_merge_history90 and passes it a bunch of parameters, including a value of 6 for the run status. (This is coded directly into replmerg.exe and there is NO way to override this behavior.) When you pass a value of 6 for the run status to sp_MSadd_merge_history90, the last section of code makes a call to sp_MSrepl_raiserror and passes the value of 6 for the run status to it. When you pass a value of 6 to this procedure, it causes a level 18 error message of 14151 to be raised. Since anything level 16 and above is considered fatal, it writes to the SQL Server error log as well as the Windows event log.

Now, this is where the idiocy of this. The merge agent doesn't shut down when this error is thrown. It in fact goes to sleep, wakes up 1 minutes later, and goes through the same routine again. If the error was so fatal, why would the merge engine continue to run? Because, the NORMAL operational state of merge replication is to HAVE a subscriber disconnected.

So, we now have a situation where the merge engine is designed to run with disconnected subscribers as well as when those subscribers are disconnected, the merge engine throws level 18 error messages into the error log and Windows event log, and then goes to sleep so that it can continue to throw level 18 error messages for a situation that it was designed for. What does this mean? It means that as you add in several dozen subscribers, you get quite literally thousands of fatal error messages thrown into the event and error logs. Somewhere around the 60 subscriber mark, you throw fatal error messages at such as pace that the server itself quite literally freezes because of all of the error logging that it is forced to do based on a normal operational state.

So, from my testing thus far, using merge replication with push subscriptions is only suitable it you have 40 of fewer subscribers that are connected a majority of the time. If they are disconnected more than 50% of the time, I can only get about 30 subscribers running in a stable mode without causing the server to lock up. If I turn this around and run the agents at the subscriber where I lose any centralized management of replication, I can scale to well over 400 subscribers. Same publication, same subscription, same hosts. In both of these tests, I'm using multiple instances of SQL Server Express edition on a pool of 5 physical machines. I have ZERO data moving, the only activity happening is the replication engine simply trying to connect and poll for changes. In all of the push cases, the publisher locks up and becomes unresponsive requiring a reboot. The only discernable difference is that when agents are offloaded to subscribers, I don't throw level 18 errors into the event log and error log. Before someone says that it's also because I'm offloading the merge agent, I lock up the publisher in a push mode when I go beyond 30 subscribers who are mostly disconnected. However, I can also load up 100+ subscribers on a single machine in a pull mode (more than 3 times the number of agents as I could get in a push mode) without having a machine lock up. We've traced the cause of the server freeze back to the amount of error logging that is being forced on it and nearly 100% of the error logging is occuring from the single state of having a subscriber disconnected.

So, I've currently halted a couple of major customer upgrades of in excess of 5,000 processors, because this single issue would cause their environments to melt to the ground long before we ever go them upgraded. I have a deployment halted at another customer and one up in the air at a 4th customer. In all cases, I'm starting to evaluate 3rd party products to replace the replication engine, because it very clearly does NOT scale in a very normal configuration.

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!
It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change data type

Hello!!!
I need to change the data type of a column in the replication.
How can I do that?
Thanks
Wait till "off hours"/ drop Subscription/ drop article/ change data type on both tables/ add article/ add subscription. If theres no good time to do it, you can re-snapshot the data as well.
"David" <davbarquero@.hotmail.com> wrote in message news:OvRppwFnEHA.3196@.TK2MSFTNGP10.phx.gbl...
Hello!!!
I need to change the data type of a column in the replication.
How can I do that?
Thanks
|||David,
in SQL Server 2000 directly this isn't possible.
Mostly, people drop the subscription and follow Chris's method.
As an alternative, using the existingframework you could add a new column with the new datatype (sp_repladdcolumn), do an update on the table to populate the column, then drop the old column (sp_repldropcolumn) and then do this again to create the column having the same original name.
NB in SQL Server 2005 this is directly possible (only using Alter table in Beta 2 though).
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> escribi en el mensaje news:eOJxZYJnEHA.3428@.TK2MSFTNGP11.phx.gbl...
David,
in SQL Server 2000 directly this isn't possible.
Mostly, people drop the subscription and follow Chris's method.
As an alternative, using the existingframework you could add a new column with the new datatype (sp_repladdcolumn), do an update on the table to populate the column, then drop the old column (sp_repldropcolumn) and then do this again to create the column having the same original name.
NB in SQL Server 2005 this is directly possible (only using Alter table in Beta 2 though).
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Change condition if the first doesn't exists

Have two tables:

code:

CREATE TABLE [Table1]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Number] [varchar] (50) NOT NULL ,
[TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
DEFAULT (getdate()),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table2]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Table1Id] [int] NOT NULL ,
[LingoId] [int] NOT NULL ,
[Header] [nvarchar] (150) NOT NULL ,
[Description] [ntext] NOT NULL ,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
(
[Table1Id]
) REFERENCES [Table1] (
[Id]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

What I would like is that if LingoId = 2 in the query below doesn't exists
than it should fall thru and use the values where LingoId = 1. LingoId = 1
always exists for each Table2.Id. Is this doable?

code:

SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = 2) --Something should happen here I quess.

SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Header ELSE
T2_1.Header END) as Header,
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Description ELSE
T2_1.Description END) as Description
FROM
dbo.Table1
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 2) T2_2
ON dbo.Table1.Id = T2_2.Table1Id
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 1) T2_1
ON dbo.Table1.Id = T2_1.Table1Id
HTH, Jens Suessmeyer.|||Got an answer elsewhere that did the trick.
"Senna" wrote:

>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||First, you are filtering the result with values from table2. That will
effectively be an INNER JOIN. Did you mean it like that?
If you want to use only one query then something like this could be good
enough:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = case when exists (select table1.id from table1
inner join table2 on table1.id = table2.id where table2.LingoID = 2) then 2
else 1 end)
MC
"Senna" <
Senna@.discussions.microsoft.com>
wrote in message
news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||The solution, as I mention above, looked liked this:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
INNER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
dbo.Table2.LingoId = 2
or (dbo.Table2.LingoId = 1
and not exists
(select *
from table2 cn
where cn.table1id = dbo.Table2.Table1Id
and cn.lingoid = 2))
ps. Thank for your time and answer Jens.
"Senna" wrote:

>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||Yes, it was an inner join. :)
See the other post with the solution I went with. Thanks anyway for your
time and effort.
"MC" wrote:

> First, you are filtering the result with values from table2. That will
> effectively be an INNER JOIN. Did you mean it like that?
> If you want to use only one query then something like this could be good
> enough:
> SELECT
> dbo.Table1.Id,
> dbo.Table1.Number,
> dbo.Table1.[TimeStamp],
> dbo.Table2.Header,
> dbo.Table2.Description
> FROM
> dbo.Table1
> LEFT OUTER JOIN
> dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
> WHERE
> (dbo.Table2.LingoId = case when exists (select table1.id from table1
> inner join table2 on table1.id = table2.id where table2.LingoID = 2) then
2
> else 1 end)
>
> MC
>
> "Senna" <Senna@.discussions.microsoft.com> wrote in message
> news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
>

Change Column Size

Hi

I am using one Publisher with many subscribers for Merge replication of a database.
I wanted to change size one column in a table. So I added a dummy column (through Filter column).
Copied data, dropped old column (through Filter column), added new column, copied data, and dropped dummy column.
On subscriber A, replication is OK. On subscriber B, I get the following message.
--
Initializing
Connecting to Publisher 'REPLIC'
Retrieving publication information
Retrieving subscription information
A column was added to or dropped from the replicated table.
The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber.
The schema script 'exec sp_repladdcolumn '[dbo].[Items]','C1',[NVARCHAR(1000) NULL], '%', 1' could not be propagated to the subscriber.
A column was added to or dropped from the replicated table.
--

I made a new snapshot, reintialized subscription with upload, and tried to sychronize again.
But same message.

Question 1. Is there anyway to make replication?
Question 2. If not, can I at least upload data changes before dropping and making new publication?

Imtaar

is this sql 2005 or sql 2000?

change column length

Is there any tricks to change a column length in a table participating transaction replication? Is there any 'work around' without dropping & re-creating the articles?
i am aware of adding a new column (sp_repladdcolumn) and dropping an existing column (sp_repldropcolumn)
TIA
MoorthyYou can try an Alter Table statement to update the column. Test this out on a new publication before you alter the production table.||||
| You can try an Alter Table statement to update the column. Test this
out on a new
| publication before you alter the production table.
--
You can't use ALTER TABLE to change column properties on a table
participating in replication. From Books Online:
Schema Changes on Publication Databases
"Important Schema changes to a published table must be made only through
the replication publication properties dialog box in SQL Server Enterprise
Manager or through replication stored procedures. Do not make schema
changes to published tables using the SQL ALTER TABLE statements in a tool
such as SQL Query Analyzer or by using SQL Server Enterprise Manager visual
database tools. Changes made to the schema of a published table using these
tools will not be propagated to Subscribers."
A workaround to this limitation would be to:
1. add a new column, using replication stored procedures
2. update the new column with contents of old column
3. drop the old column. using replication stored procedures
Hope this helps,
--
Eric Cárdenas
SQL Server support

change "sa" password -> replication out

Hello,
I have several servers SQL with replications they are a priori, configure
all similar, nevertheless on the one if I change the password of the account
"sa" replications are not done any more. Here is the message of the log "
The job failed. The Job was invoked by User sqladm. The last step to run
was step 1 (Run agent.)." Only the password of "sa" has to change "sa" is
owner job. Then why the problem comes of sqladm? With the other servers not
of problem.
Thank you.
Vincent,
are you using trusted connections or sql authentication for your agents?
Have a check in the distributor properties to verify, as it would seem your
agents are using 'sa' to log in. If this is the case, the password will need
updating here also.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

central subscriber

A central subscriber whose table is updated by multiple publishers and the
transactional push replication breaks on one of the publishers.
Does
a. If you do a new snapshot for that publisher does it automatically delete
the existing data in that table for that publisher assuming you have the
proper constraints in place for a unique column and then transactional
replication procedes as normal after the snapshot?
-or-
b. You have to wipe out the whole table and redo all the snapshots, i.e
reinitialize all the subscriptions.
-or-
c. something else
Any hard data in BOL,white papers, or other references to support either of
the possibilities?
a) But only if you user filters, and set up your name conflicts correctly.
To do this, right click on your publication, select properties, click on the articles tab, click on the browse button (the three dots) and then click on the snapshot tab. In the name conflicts section, pick delete all data that matches the row filter.
For Filters click on your Filter Commands tab, and add a filter condition. You might have to make changes to your schema to add a column which uniquely identifies your publisher.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

caveats for synchronous hardware based replication?

Hello,
Thinking about synchronous hardware based mirroring using EMCs clariion
product line over a dedicated fiber connection between two geographically
separate data centers. I realize that there is a basic speed of light issue
here regarding the distance I can have between the two sites and still keep
my servers synched real-time.
Would someone please shed some more light on what is involved here? What's
the maximum distance I can have betwen these sites and why? Any other
caveats with this setup I should be aware of? Thanks.
BTW - running an OLTP environment SQL2000.
I don't believe that these solutions are truly synchronous, and second its
way slower than the speed of light.
The most significant factors are price and skill set required. If you think
replication is tough wait till you get behind EMC's SRDF.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mb" <mb@.discussions.microsoft.com> wrote in message
news:8AB9A09B-325B-4A59-9C54-1CC65BD1348B@.microsoft.com...
> Hello,
> Thinking about synchronous hardware based mirroring using EMCs clariion
> product line over a dedicated fiber connection between two geographically
> separate data centers. I realize that there is a basic speed of light
issue
> here regarding the distance I can have between the two sites and still
keep
> my servers synched real-time.
> Would someone please shed some more light on what is involved here?
What's
> the maximum distance I can have betwen these sites and why? Any other
> caveats with this setup I should be aware of? Thanks.
> BTW - running an OLTP environment SQL2000.
|||I'm curious why you state the hardware mirroring is not truly synchronous?
It was my understanding that if distances were kept under a certain limit
then this solution could be considered to be essentially synchronous.
Oh, just got your replication book Hilary - great read!
"Hilary Cotter" wrote:

> I don't believe that these solutions are truly synchronous, and second its
> way slower than the speed of light.
> The most significant factors are price and skill set required. If you think
> replication is tough wait till you get behind EMC's SRDF.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "mb" <mb@.discussions.microsoft.com> wrote in message
> news:8AB9A09B-325B-4A59-9C54-1CC65BD1348B@.microsoft.com...
> issue
> keep
> What's
>
>
|||My understanding from EMC is that there are two modes. 1) Synchronous and 2)
asynchronous.
In the Synchronous mode, data is written to the source and the app receives
the commit. Then under the covers the write is written to the destination.
So, its not a true split write. Asynchronous has queuing built in.
Let me check with my EMC rep and confirm this.
Glad you like the book!
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mb" <mb@.discussions.microsoft.com> wrote in message
news:1910F129-8553-42BA-BD15-8B8517D9BC0B@.microsoft.com...[vbcol=seagreen]
> I'm curious why you state the hardware mirroring is not truly synchronous?
> It was my understanding that if distances were kept under a certain limit
> then this solution could be considered to be essentially synchronous.
> Oh, just got your replication book Hilary - great read!
> "Hilary Cotter" wrote:
its[vbcol=seagreen]
think[vbcol=seagreen]
clariion[vbcol=seagreen]
geographically[vbcol=seagreen]

Cause INSERT statements without column lists to fail

Hi Hilary, Paul....
I have another real problem I hope you can help with.
Setup replication now with no problems but one!
The database tables fail from the application thats using them because of
the ROWQUID column.
I noticed that JUST before the articles are created it shows this;
SQL Server requires that all merge articles contain a uniqueidentifier
column with a unique index and the ROWGUIDCOL property. SQL Server will add a
uniqueidentifier column to published tables that do not have one when the
first snapshot is generated.
Adding a new column will:
? Cause INSERT statements without column lists to fail
? Increase the size of the table
? Increase the time required to generate the first snapshot
So is there ANY way of getting round this bug because my developer said that
they will not alter their software and the way the tables are accessed.
So, any ideas.
First you have to find out if the code does do something like this
Insert into tablename
select * from anothertablename
This is what will break with merge replication. Ask him specifically about
this. Personally good developers would never do anything like this. They
would also access the database through stored procedures which would allow
you, the dba, to modify something like this.
Another method is to have the application talk to a view which only contains
the non rowguid column. This isn't very scalable.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:53394C5B-05FA-418C-99CA-FB8A8FF5769A@.microsoft.com...
> Hi Hilary, Paul....
> I have another real problem I hope you can help with.
> Setup replication now with no problems but one!
> The database tables fail from the application thats using them because of
> the ROWQUID column.
> I noticed that JUST before the articles are created it shows this;
> SQL Server requires that all merge articles contain a uniqueidentifier
> column with a unique index and the ROWGUIDCOL property. SQL Server will
> add a
> uniqueidentifier column to published tables that do not have one when the
> first snapshot is generated.
> Adding a new column will:
> Cause INSERT statements without column lists to fail
> Increase the size of the table
> Increase the time required to generate the first snapshot
> So is there ANY way of getting round this bug because my developer said
> that
> they will not alter their software and the way the tables are accessed.
> So, any ideas.
|||Hi,
No it looks like they have hard coded this one particluar SQL statement.
So what acutally changes in the database that affects the table INSERT
commands?.
Is their a way round this. If there isnt then its back to square one for
me...
Thanks
"Hilary Cotter" wrote:

> First you have to find out if the code does do something like this
> Insert into tablename
> select * from anothertablename
> This is what will break with merge replication. Ask him specifically about
> this. Personally good developers would never do anything like this. They
> would also access the database through stored procedures which would allow
> you, the dba, to modify something like this.
> Another method is to have the application talk to a view which only contains
> the non rowguid column. This isn't very scalable.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> ""confused"" <confused@.discussions.microsoft.com> wrote in message
> news:53394C5B-05FA-418C-99CA-FB8A8FF5769A@.microsoft.com...
>
>
|||You say one particular SQL Statement - is it in a stored procedure? If so,
just modify the stored procedure so it contains all the column name except
the rowguid column.
ie before
insert into mytable
select * from myothertable
after
insert into mytable
select col1,col2, col3, col4,... from myothertable.
If this can't be done change the table the app writes to so it is now a
view. So if the app wrote to Mytable rename mytable as mytable and create a
view called mytable. This view will look like this create view mytable as
select col1, col2, col3, col4,... from mytable1
This doesn't scale well. How many nodes in your system? You might want to
look at bi-directional transactional replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:8DEDF1FC-8926-4072-9D22-91715B8FEE3D@.microsoft.com...[vbcol=seagreen]
> Hi,
> No it looks like they have hard coded this one particluar SQL statement.
> So what acutally changes in the database that affects the table INSERT
> commands?.
> Is their a way round this. If there isnt then its back to square one for
> me...
> Thanks
> "Hilary Cotter" wrote:
|||Hi Hilary,
I think I found the problem but I dont know how to get to it.
The dependencies show 3 objects that have a green plus sign next to them.
These objects are PHONE_ADD, PHONE_DELETE, PHONE_AMEND but I cant see these
anywhere in the database?.
Any ideas?
TIM
"Hilary Cotter" wrote:

> You say one particular SQL Statement - is it in a stored procedure? If so,
> just modify the stored procedure so it contains all the column name except
> the rowguid column.
> ie before
> insert into mytable
> select * from myothertable
> after
> insert into mytable
> select col1,col2, col3, col4,... from myothertable.
> If this can't be done change the table the app writes to so it is now a
> view. So if the app wrote to Mytable rename mytable as mytable and create a
> view called mytable. This view will look like this create view mytable as
> select col1, col2, col3, col4,... from mytable1
> This doesn't scale well. How many nodes in your system? You might want to
> look at bi-directional transactional replication.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> ""confused"" <confused@.discussions.microsoft.com> wrote in message
> news:8DEDF1FC-8926-4072-9D22-91715B8FEE3D@.microsoft.com...
>
>
|||Where are you seeing these green arrows?
Can you do the following for me?
select name from sysobjects where name in (PHONE_ADD, PHONE_DELETE,
PHONE_AMEND) and type='u'
GO
select object_name(id),name from syscolumns where name in (PHONE_ADD,
PHONE_DELETE, PHONE_AMEND)
GO
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
""confused"" <confused@.discussions.microsoft.com> wrote in message
news:ACD9DC12-A2C2-4E65-83C8-3D4E39EE8109@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> I think I found the problem but I dont know how to get to it.
> The dependencies show 3 objects that have a green plus sign next to them.
> These objects are PHONE_ADD, PHONE_DELETE, PHONE_AMEND but I cant see
> these
> anywhere in the database?.
> Any ideas?
> TIM
> "Hilary Cotter" wrote:

Caught in a Replication Catch-22, SQLServer2005

I am currently moving from SQL2K to 2005 and am having trouble incorporating
replication in 2005.
First off, I've found myself in a loop where I have two publications that
"don't exist" even though they show up in my Local Publishers list. I can't
delete tables that are articles in those publications because the tables are
being replicated. However, if I try to delete the publication, I get the
message that the publication doesn't exist. So the tables I need to drop are
bound to a publication that doesn't exist.
I've tried looking in the distribution and master databases to find the
missing links but I can't find anything. Without spending a lot of time
going through how I got in this mess, I'd just like to know if there are some
sp's that I can use to purge these publications once and for all.
Thanks in advance.
Roger.
You don't say which type of replication you are using. My experience
is only with merge replication, in this case I would suggest using
sp_removedbreplication in addition to normal sp_dropmergepublication
(or delete from the Gui)
Please see KB324401 for recommendations from MS. http://
support.microsoft.com/kb/324401
Tim Hill
On Feb 9, 3:33 pm, Roger Denison
<RogerDeni...@.discussions.microsoft.com> wrote:
> I am currently moving from SQL2K to 2005 and am having trouble incorporating
> replication in 2005.
> First off, I've found myself in a loop where I have two publications that
> "don't exist" even though they show up in my Local Publishers list. I can't
> delete tables that are articles in those publications because the tables are
> being replicated. However, if I try to delete the publication, I get the
> message that the publication doesn't exist. So the tables I need to drop are
> bound to a publication that doesn't exist.
> I've tried looking in the distribution and master databases to find the
> missing links but I can't find anything. Without spending a lot of time
> going through how I got in this mess, I'd just like to know if there are some
> sp's that I can use to purge these publications once and for all.
> Thanks in advance.
> --
> Roger.
|||I had a similar problem to this. The only way round it was to script
the publications & subscriptions, remove replication and then
re-enable.
On Fri, 9 Feb 2007 07:33:00 -0800, Roger Denison
<RogerDenison@.discussions.microsoft.com> wrote:

>I am currently moving from SQL2K to 2005 and am having trouble incorporating
>replication in 2005.
>First off, I've found myself in a loop where I have two publications that
>"don't exist" even though they show up in my Local Publishers list. I can't
>delete tables that are articles in those publications because the tables are
>being replicated. However, if I try to delete the publication, I get the
>message that the publication doesn't exist. So the tables I need to drop are
>bound to a publication that doesn't exist.
>I've tried looking in the distribution and master databases to find the
>missing links but I can't find anything. Without spending a lot of time
>going through how I got in this mess, I'd just like to know if there are some
>sp's that I can use to purge these publications once and for all.
>Thanks in advance.