Merge republish Schema changes

Hello,

I'm working on a replication topology that is completely merge. We have a single consolidated instance (SQL 2005 SP1 Standard) that holds all data and is a continuous push merge publication filtered by region to regional instances (SQL 2005 SP1 Standard). Then we have individual user instances (SQL Express SP1) that pulls from the republished regional instances which is filtered by user. Both publications have Replicate Schema Changes set to true.

I'm testing out changes to tables and sps on a test system I've been using this process:

1-Run Snapshot on the Consolidated instance

2-Verify all published articles have a status of 2 in sysmergearticles

3-Run Regional Snapshot

4-Verify all published articles have a status of 2 in sysmergearticles

5-Run alter table scripts

6-Once all three levels have the table changes, run the alter sp scripts

I've gotten to step 5 and and the changes get replicated to the regional instance just fine however only the existing column changes get replicated to the SQLExpress instance, not the new columns. Looking at the articles in the regional publication it shows the new columns, but they are not selected. I know I can manually select them (or probably write a script that adds them to the publication although sp_repladdcolumn has been depreciated), but isn't there a way to make this a completely automated process since it's just a republished database? Also is the process I'm using the correct one?

Thank you,

Aaron Lowe

Is your publication property replication_ddl set to true?|||I apologize for not being clearer in my original post. I had said that replicate schema changes was set to true, this is the replication_ddl property that I was referring to. Thanks, Aaron|||when you add a new column, the column should get replicated to all nodes in your topology. Is the new column not getting replicated at all? Where in your topology are you adding the new columns - publisher, republisher or subscriber?|||I'm adding the columns at my original publisher (the consolidated one). As I said it is pushed down to my subscribers that republish the data (the regional ones that are pushed from the consolidated one), it just doesn't get all the way down to my final subscribers (the individual sqlexpress ones that pull the data). Looking at the properties of the publication on the republisher it shows the columns in the publication but they are not selected.|||if replicate_ddl option is truly enabled at both the publisher and the republisher, then I'm not sure what the problem is. You verified the replicate_ddl column is set to 1 in sysmergepublications table in the published database at both the publisher and republisher?|||

Well, I believe it's correct, here's what is in the sysmergepublications:

Consolidated database (original publisher)

publication name, replicate_ddl

Consolidated, 1

Region, 0

Regional database (republisher)

publication name, replicate_ddl

Consolidated, 1

Region, 1

SQL Express database (subscriber)

publication name, replicate_ddl

Consolidated, 0

Region, 1

Also the status in sysmergearticles in the consolidated db is 2 (active). There are two sets of articles in the sysmergearticles table in the regional db, one for each the consolidated and regional publication. The records in sysmergearticles for the consolidated publication has a status of 1 (Unsynced) while the records for the regional publication have a status of 2 (active). The status in the SQLExpress pull subscriptions is all 1 (Unsynced).

Thanks,

Aaron

|||Can you try your scenario with SP2? We fixed somewhat similar issue in SP2.sql

merge replication, help!

Hi,

I'm setting up a merge replication between one publisher(sql server 2005 standard sp2) and a couple of subscribers(sql server 2005 express sp2). They are connected with each other through VPN tunnels(1.5M adsl connections). I'm using pull replication, every 20 minutes. The initial snapshot replication was finished properly for each subscriber, but after several hours, the subscribers keep getting such kind of error message "Another merge agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)". it looks like the vpn tunnel is not good sometimes, and the merge agent just sits there waiting. then another request comes in, it cannot get the handler of that agent which is held by the previous request. So how can i configure the server to release the agent when a new request comes in? or setup the timeout for each request?

Any idea would be appreciated.

Thank you very much!
Maybe you can consider -QueryTimeout parameter for the merge agent?|||thanks for reply.
but would u be able to tell me how to do that?
|||oh, sorry, i found it. the current timeout is 300, just 5 minutes. but i set the merge replication every 20 minutes. then how come this happens?
|||QueryTimeout is how long the merge agent will wait on a given query before timing out. The 20 minutes you specified sounds like the scheduled intervals at which the subscription will synchronize.sql

merge replication, help!

Hi,

I'm setting up a merge replication between one publisher(sql server 2005 standard sp2) and a couple of subscribers(sql server 2005 express sp2). They are connected with each other through VPN tunnels(1.5M adsl connections). I'm using pull replication, every 20 minutes. The initial snapshot replication was finished properly for each subscriber, but after several hours, the subscribers keep getting such kind of error message "Another merge agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)". it looks like the vpn tunnel is not good sometimes, and the merge agent just sits there waiting. then another request comes in, it cannot get the handler of that agent which is held by the previous request. So how can i configure the server to release the agent when a new request comes in? or setup the timeout for each request?

Any idea would be appreciated.

Thank you very much!
Maybe you can consider -QueryTimeout parameter for the merge agent?|||thanks for reply.
but would u be able to tell me how to do that?
|||oh, sorry, i found it. the current timeout is 300, just 5 minutes. but i set the merge replication every 20 minutes. then how come this happens?
|||QueryTimeout is how long the merge agent will wait on a given query before timing out. The 20 minutes you specified sounds like the scheduled intervals at which the subscription will synchronize.

Merge Replication Problem

Hi all,

OS: Windows 2000 Server
SQL: SQL Server Standard 2000

I was searching the forums and the web but didn't find any information on solving this problem.

On the publisher when I look at the Replication Monitor -> Merge Agents section I see the following error to only one of the 4 subscribers. I've tried to removing and recreating the subscriber, unfortunately that didn't work so i recreated the merge replication all together with a new name and removed the original, this also didn't solve the problem.

I'm worried this is a problem on the subscriber, recently we renamed the windows computer name (which caused some data access and login errors), after doing a registry rebuild for SQL all of that seemed to be cleared up. NOt sure if this helps but I guess some background can't hurt.

So here's the error info that comes up.

Error Information

Agent, Merge Replication Provider, -2147200994
The process could not make a generation at the 'Subscriber'.

Data Source, SUBSCRIBER_DB_NAME, 515
Cannot insert the value NULL into column 'nicknames', table 'MyDB.dbo.MSmerge_genhistory'; column does not allow nulls. INSERT fails.

Thanks for any help...You have to do a no-sync subscription.
Check the following :
select * from sysmerge_subscriptions
go
Run sp_helpserver on the subscribing server and the srvid should be 0 for the subscriber.|||Hi Satya,

Thank you very much for the reply, at the very least it got me looking into nosync subscriptions...

Unfortunately I wasn't able to figure out how to set one up as such, and if simply changing the srvid at the subscriber is all that needs to be done.

I followed your instructions and ran the two queries you specificied at the SUBSCRIBER (not sure if both were supposed to be run at the subscriber...) and here are the results (in the attached zipped, csv file..) the srvid is not 0 for any of the rows.

If you get a chance to look at it and see the problem, or what needs to be changed (or how I should setup the nosync subscription), please do let me know...

once again thank you very much.|||Refer to books online about dynamic snapshot and applying them manually.

If any triggers are involved then execute sp_addsynctriggers, before that follow books online about this SP.

merge replication problem

I am working on merge replication.I am using sql server 2005 standard edition.Will things workout for me if i use this standard edition(just to know).

2. i have been working on merge replication on the same server but different databases.the way i do it is as follows

a.I create a database.

b.Create a publication(is has successfully been created)

c.Create a subscription(successfully created it)

d.Now when i look at the replication monitor when i click on my merge replication

under all subscription tab

status shows:uninitialized subscription and

Connection :it shows unknown

when i double click that it takes me to synchrinization history which has nothing going on with in it.

What would be the reason for the problem.

Please

Can you try the replication monitor and the sync history after you add some articles to the publication then run the snapshot agent on the publisher, and then run the merge agent to initialize the subscriber.|||

Thanks for your attention

after adding some articles.when i run the snapshot agent from

Local publications->my publication->view snap shot agent status->click start

it tries to start but then comes with a message saying "The agent has never been run."

and under local subscritpions->my subscription->view synchronization->

when i click start it says "No agent status information is available".

when i look at view history tab

The job failed. Unable to determine if the owner (xyz\myname) of job 1234511-testdb_merge-merge-27G6Y91-merge_sub- 0 has server access (reason: Could not obtain information about Windows NT group/user 'xyz\myname', error code 0x5. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621)).

What would be the casue if security permission.How should i set them.

please let me know.

sql

merge replication licensing

This will be the setup:
1 server with:
windows server 2003 standard
sql server 2005 standard
30+ clients with:
windows xp pro
sql server express
I will set up a merge replication (pull) and use web synchronization over
https. No active directory will be used.
The clients connect to the replisapi.dll hosted in the servers IIS and
supply a single predifined windows server user (Basic Authentication) to run
the dll. The dll uses a single predefined sql user to manage all sql traffic.
What licenses will be required?
As I understand I will need either 30 sql device cals or a per processor
license (the break of point in my case would be 35 cals).
I will probably also need an Internet Connector license for IIS or 30
windows cals.
Is this correct?Assuming the server has 1 physical processor (can be multi core) then you
will need 1 sql server 2005 processor licence - workgroup or standard.
Windows Server 2003 Standard - just the 5 cal one is fine; you don't need a
server cal for each user connecting because they aren't really users of the
server, they are using a service on the server...
Tony
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"mommi" <mommi@.discussions.microsoft.com> wrote in message
news:A7DDF8D6-3387-411F-BCF4-2169DF60F4F7@.microsoft.com...
> This will be the setup:
> 1 server with:
> windows server 2003 standard
> sql server 2005 standard
> 30+ clients with:
> windows xp pro
> sql server express
> I will set up a merge replication (pull) and use web synchronization over
> https. No active directory will be used.
> The clients connect to the replisapi.dll hosted in the servers IIS and
> supply a single predifined windows server user (Basic Authentication) to
> run
> the dll. The dll uses a single predefined sql user to manage all sql
> traffic.
> What licenses will be required?
> As I understand I will need either 30 sql device cals or a per processor
> license (the break of point in my case would be 35 cals).
> I will probably also need an Internet Connector license for IIS or 30
> windows cals.
> Is this correct?|||Thanks for the reply.
But doesn't IIS require some sort of a license? For instance if you put up a
webserver using IIS then you need either a processor license or an internet
connector license because you can't predict how many users are actually going
to visit you website at any given time...
I'm assuming my setup would require some sort of license for IIS because of
that.
"Tony Rogerson" wrote:
> Assuming the server has 1 physical processor (can be multi core) then you
> will need 1 sql server 2005 processor licence - workgroup or standard.
> Windows Server 2003 Standard - just the 5 cal one is fine; you don't need a
> server cal for each user connecting because they aren't really users of the
> server, they are using a service on the server...
> Tony
> --
> Tony Rogerson, SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson
> [Ramblings from the field from a SQL consultant]
> http://sqlserverfaq.com
> [UK SQL User Community]
>
> "mommi" <mommi@.discussions.microsoft.com> wrote in message
> news:A7DDF8D6-3387-411F-BCF4-2169DF60F4F7@.microsoft.com...
> > This will be the setup:
> > 1 server with:
> > windows server 2003 standard
> > sql server 2005 standard
> >
> > 30+ clients with:
> > windows xp pro
> > sql server express
> >
> > I will set up a merge replication (pull) and use web synchronization over
> > https. No active directory will be used.
> > The clients connect to the replisapi.dll hosted in the servers IIS and
> > supply a single predifined windows server user (Basic Authentication) to
> > run
> > the dll. The dll uses a single predefined sql user to manage all sql
> > traffic.
> >
> > What licenses will be required?
> >
> > As I understand I will need either 30 sql device cals or a per processor
> > license (the break of point in my case would be 35 cals).
> > I will probably also need an Internet Connector license for IIS or 30
> > windows cals.
> > Is this correct?
>|||Nope - that's part of the server price.
Anyway, the SQL bit is right; perhaps you should ask in the windows server
group and they'll confirm any fears you have.
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
"mommi" <mommi@.discussions.microsoft.com> wrote in message
news:1A5908E0-4554-4E59-97A0-233552B8F853@.microsoft.com...
> Thanks for the reply.
> But doesn't IIS require some sort of a license? For instance if you put up
> a
> webserver using IIS then you need either a processor license or an
> internet
> connector license because you can't predict how many users are actually
> going
> to visit you website at any given time...
> I'm assuming my setup would require some sort of license for IIS because
> of
> that.
>
> "Tony Rogerson" wrote:
>> Assuming the server has 1 physical processor (can be multi core) then you
>> will need 1 sql server 2005 processor licence - workgroup or standard.
>> Windows Server 2003 Standard - just the 5 cal one is fine; you don't need
>> a
>> server cal for each user connecting because they aren't really users of
>> the
>> server, they are using a service on the server...
>> Tony
>> --
>> Tony Rogerson, SQL Server MVP
>> http://sqlblogcasts.com/blogs/tonyrogerson
>> [Ramblings from the field from a SQL consultant]
>> http://sqlserverfaq.com
>> [UK SQL User Community]
>>
>> "mommi" <mommi@.discussions.microsoft.com> wrote in message
>> news:A7DDF8D6-3387-411F-BCF4-2169DF60F4F7@.microsoft.com...
>> > This will be the setup:
>> > 1 server with:
>> > windows server 2003 standard
>> > sql server 2005 standard
>> >
>> > 30+ clients with:
>> > windows xp pro
>> > sql server express
>> >
>> > I will set up a merge replication (pull) and use web synchronization
>> > over
>> > https. No active directory will be used.
>> > The clients connect to the replisapi.dll hosted in the servers IIS and
>> > supply a single predifined windows server user (Basic Authentication)
>> > to
>> > run
>> > the dll. The dll uses a single predefined sql user to manage all sql
>> > traffic.
>> >
>> > What licenses will be required?
>> >
>> > As I understand I will need either 30 sql device cals or a per
>> > processor
>> > license (the break of point in my case would be 35 cals).
>> > I will probably also need an Internet Connector license for IIS or 30
>> > windows cals.
>> > Is this correct?
>>

Merge Replication Issues between SQL Server 2005 (x64) and SQL Server Compact Edition

Hi ppl,

I have installed SQL Server 2005 x64 Enterprise edition with Service Pack 2 on a Windows Server 2003 x64 Standard Edition with Service Pack 2.

Now I have to configure Merge Replication that will work with SQL Server Compact Edition database on Windows Mobile devices.

Distributor and the Publisher are the same server.

IIS 6.0 is installed on the windows server. I have installed the SQL Server Compact Edition Server tools on the server. However the compact edition server tools are only available for 32bit servers and I have also found out from the article http://support.microsoft.com/default.aspx/kb/912430 that you cannot replicate data from SQL Server 2005 to SQL Server Compact Edition by using the 64-bit version of IIS.
So if this is true does that mean I can not use merge replication on 64 bit server? Does that mean I have to get another 32 bit server with 32 bit IIS on it to make this work or is there another work around. Am i missing something here?

Regards
NabeelI think this might solves the issue http://www.pluralsight.com/blogs/jimw/archive/2007/07/06/47932.aspx

Merge replication intermittent error.

Windows 2003 Server Standard, SQL Server 2000. Two identical machines.
Primary (P) and Secondary (S). I have set up merge replication with P as
the publisher and S as the distributor and an updating pull subscriber. The
machines are installed at a customer site and were successfully put into the
domain and all the replication is working. I have the merge and snapshot
agents impersonating the SQL Server Agent login.
They are still working on the power, networking and other parts of the new
building so the machines lose connectivity to the domain and login servers
peridically and sometimes power down. At times when I connect to the system
to check the replication monitor is showing an error. I had originally set
all agents to start with SQL Server and run continously. I saw that the
merge agent would encounter an error and stop. To avoid this I scheduled
the merge agent to run every day, once a minute to avoid intermittent
network errors causing it to stop and not restart. However, I still
periodically get an error that the merge agent logs every time it runs. I
can clear the error up by manually running the snapshot agent. I need to
figure out what is causing the error and have some way automatically restore
the replication.
The error reported by the merge agent is this:
The merge agent could retrieve article information for publication 'xxx'.
(Source:Merge Replication Provider (Agent); Error: -2147201017
SQL Server does not exist or access denied.
(Source: Primary (Data SOurce); Error Number: 17
I know it is not an access permission because it works most of the time. I
am wondering if it is a timing issue when a network error occurs or the
machines reboot and the primary cannot access the distributor in the
secondary. I am not sure why this would cause the merge agent to fail. It
seems to be that the snapshot agent may be failing during a reboot or during
a network failure and the subscription is marked as "bad" or something. I
am thinking of making the primary the distributor to see if that helps.
Any suggestions?
TIA
I think you need to set LoginTimeout to something large - like 60.
By default LoginTimeout is 15s.
"Mike M." <nospam@.someplace.com> wrote in message
news:%230FRneD6EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Windows 2003 Server Standard, SQL Server 2000. Two identical machines.
> Primary (P) and Secondary (S). I have set up merge replication with P as
> the publisher and S as the distributor and an updating pull subscriber.
The
> machines are installed at a customer site and were successfully put into
the
> domain and all the replication is working. I have the merge and snapshot
> agents impersonating the SQL Server Agent login.
> They are still working on the power, networking and other parts of the new
> building so the machines lose connectivity to the domain and login servers
> peridically and sometimes power down. At times when I connect to the
system
> to check the replication monitor is showing an error. I had originally
set
> all agents to start with SQL Server and run continously. I saw that the
> merge agent would encounter an error and stop. To avoid this I scheduled
> the merge agent to run every day, once a minute to avoid intermittent
> network errors causing it to stop and not restart. However, I still
> periodically get an error that the merge agent logs every time it runs. I
> can clear the error up by manually running the snapshot agent. I need to
> figure out what is causing the error and have some way automatically
restore
> the replication.
> The error reported by the merge agent is this:
> The merge agent could retrieve article information for publication 'xxx'.
> (Source:Merge Replication Provider (Agent); Error: -2147201017
> SQL Server does not exist or access denied.
> (Source: Primary (Data SOurce); Error Number: 17
> I know it is not an access permission because it works most of the time.
I
> am wondering if it is a timing issue when a network error occurs or the
> machines reboot and the primary cannot access the distributor in the
> secondary. I am not sure why this would cause the merge agent to fail.
It
> seems to be that the snapshot agent may be failing during a reboot or
during
> a network failure and the subscription is marked as "bad" or something. I
> am thinking of making the primary the distributor to see if that helps.
> Any suggestions?
>
> TIA
>
|||I read somewhere you can't do this from Enterprise Manager. Is that true?
I use EM to configure/maintain SQL Server.
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ux5BQDE6EHA.1596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I think you need to set LoginTimeout to something large - like 60.
> By default LoginTimeout is 15s.
> "Mike M." <nospam@.someplace.com> wrote in message
> news:%230FRneD6EHA.2192@.TK2MSFTNGP14.phx.gbl...
as[vbcol=seagreen]
> The
> the
snapshot[vbcol=seagreen]
new[vbcol=seagreen]
servers[vbcol=seagreen]
> system
> set
scheduled[vbcol=seagreen]
I[vbcol=seagreen]
to[vbcol=seagreen]
> restore
'xxx'.[vbcol=seagreen]
> I
> It
> during
I
>
|||Never mind. I did it using sp_configure.
Thanks.
"Mike M." <nospam@.someplace.com> wrote in message
news:OPJ9GcF6EHA.1564@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I read somewhere you can't do this from Enterprise Manager. Is that true?
> I use EM to configure/maintain SQL Server.
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ux5BQDE6EHA.1596@.tk2msftngp13.phx.gbl...
machines.[vbcol=seagreen]
> as
subscriber.[vbcol=seagreen]
into[vbcol=seagreen]
> snapshot
> new
> servers
originally[vbcol=seagreen]
the[vbcol=seagreen]
> scheduled
runs.[vbcol=seagreen]
> I
> to
> 'xxx'.
time.[vbcol=seagreen]
the[vbcol=seagreen]
fail.[vbcol=seagreen]
something.[vbcol=seagreen]
> I
helps.
>
|||Right click on your merge agent, select agent properties, steps, run agent,
click on the edit button, click in teh commands section, press the end key,
press the space bar, type -LoginTimeOut 120
Click Apply, ok, etc.
"Mike M." <nospam@.someplace.com> wrote in message
news:%235YnE2F6EHA.3708@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Never mind. I did it using sp_configure.
> Thanks.
> "Mike M." <nospam@.someplace.com> wrote in message
> news:OPJ9GcF6EHA.1564@.TK2MSFTNGP09.phx.gbl...
true?[vbcol=seagreen]
> machines.
P[vbcol=seagreen]
> subscriber.
> into
the[vbcol=seagreen]
> originally
> the
intermittent[vbcol=seagreen]
> runs.
need[vbcol=seagreen]
> time.
> the
> fail.
or
> something.
> helps.
>
|||Sorry about the time lapse but the holidays happened. I set the login
timeout but that did not help.
With a Merge replication, single updating pull subscription, I am wondering
what might cause the merge agent to get the following error and why does
manually running the snapshot agent allow the merge agent to succeed the
next run. Any thoughts?
The error reported by the merge agent is this:
The merge agent could retrieve article information for publication 'xxx'.
(Source:Merge Replication Provider (Agent); Error: -2147201017
SQL Server does not exist or access denied.
(Source: Primary (Data SOurce); Error Number: 17
TIA
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:en$hKHG6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Right click on your merge agent, select agent properties, steps, run
agent,
> click on the edit button, click in teh commands section, press the end
key,[vbcol=seagreen]
> press the space bar, type -LoginTimeOut 120
> Click Apply, ok, etc.
> "Mike M." <nospam@.someplace.com> wrote in message
> news:%235YnE2F6EHA.3708@.TK2MSFTNGP14.phx.gbl...
> true?
with[vbcol=seagreen]
> P
put[vbcol=seagreen]
> the
the[vbcol=seagreen]
that[vbcol=seagreen]
> intermittent
still[vbcol=seagreen]
> need
automatically[vbcol=seagreen]
or[vbcol=seagreen]
the
> or
>
|||what job owner is your merge agent running under? It should be running under
sa.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike M." <nospam@.someplace.com> wrote in message
news:OCiC1pz8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Sorry about the time lapse but the holidays happened. I set the login
> timeout but that did not help.
> With a Merge replication, single updating pull subscription, I am
wondering[vbcol=seagreen]
> what might cause the merge agent to get the following error and why does
> manually running the snapshot agent allow the merge agent to succeed the
> next run. Any thoughts?
> The error reported by the merge agent is this:
> The merge agent could retrieve article information for publication 'xxx'.
> (Source:Merge Replication Provider (Agent); Error: -2147201017
> SQL Server does not exist or access denied.
> (Source: Primary (Data SOurce); Error Number: 17
> TIA
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:en$hKHG6EHA.2568@.TK2MSFTNGP10.phx.gbl...
> agent,
> key,
> with
> put
of[vbcol=seagreen]
login[vbcol=seagreen]
> the
> that
> still
it[vbcol=seagreen]
I[vbcol=seagreen]
> automatically
publication[vbcol=seagreen]
the[vbcol=seagreen]
> or
> the
to[vbcol=seagreen]
reboot
>
|||I have it set to run under a domain user that is in the local
administrator's group. The replication runs fine for periods of time before
the intermittent error occurs. If the owner was incorrect I would suspect
that it wouldn't run at all.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OS1rGN18EHA.3596@.TK2MSFTNGP12.phx.gbl...
> what job owner is your merge agent running under? It should be running
under[vbcol=seagreen]
> sa.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Mike M." <nospam@.someplace.com> wrote in message
> news:OCiC1pz8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> wondering
'xxx'.[vbcol=seagreen]
that[vbcol=seagreen]
60.[vbcol=seagreen]
replication[vbcol=seagreen]
successfully[vbcol=seagreen]
and[vbcol=seagreen]
parts[vbcol=seagreen]
> of
> login
to[vbcol=seagreen]
saw[vbcol=seagreen]
I[vbcol=seagreen]
> it
> I
> publication
> the
occurs[vbcol=seagreen]
in[vbcol=seagreen]
> to
> reboot
that
>
|||That is true, it should be an all or nothing.
Let me see if I am understanding this correctly. Your merge agent is working
fine. It is running continuously. Suddenly it stops with the message SQL
Server does not exist or access denied. You run a new snapshot, it then
works?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Mike M." <nospam@.someplace.com> wrote in message
news:uBog2228EHA.1404@.TK2MSFTNGP11.phx.gbl...
> I have it set to run under a domain user that is in the local
> administrator's group. The replication runs fine for periods of time
before[vbcol=seagreen]
> the intermittent error occurs. If the owner was incorrect I would suspect
> that it wouldn't run at all.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OS1rGN18EHA.3596@.TK2MSFTNGP12.phx.gbl...
> under
does[vbcol=seagreen]
the[vbcol=seagreen]
> 'xxx'.
end[vbcol=seagreen]
> that
> 60.
identical[vbcol=seagreen]
> replication
> successfully
> and
> parts
[vbcol=seagreen]
connect[vbcol=seagreen]
> to
> saw
this[vbcol=seagreen]
> I
I[vbcol=seagreen]
time[vbcol=seagreen]
agent.[vbcol=seagreen]
Error: -2147201017[vbcol=seagreen]
of[vbcol=seagreen]
> occurs
distributor[vbcol=seagreen]
> in
agent
> that
>
|||Exactly! I know it sounds weird but that is what happens. I see network
and domain errors in the event log. Some of them indicate trouble finding
the login and/or the domain server and I have seen at least one SQL error
saying it could not authenticate users. I think one or more of these
network issues causes the state of the replication to get munged. Here are
some things I am thinking of trying to get the replication to recover
without intervention:
1. Make the distributor the smae machine as the publisher. Currently it is
on the backup (subscriber) machine. I am thinking it may help when there
are intermittent network errors.
2. Have the snapshot agent scheduled to run every 15 minutes or so. Just to
see if it restarts things.
Unfortunately the systems are half way around the world, in a live
environment and access is spotty.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eAwtceC9EHA.2676@.TK2MSFTNGP12.phx.gbl...
> That is true, it should be an all or nothing.
> Let me see if I am understanding this correctly. Your merge agent is
working[vbcol=seagreen]
> fine. It is running continuously. Suddenly it stops with the message SQL
> Server does not exist or access denied. You run a new snapshot, it then
> works?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Mike M." <nospam@.someplace.com> wrote in message
> news:uBog2228EHA.1404@.TK2MSFTNGP11.phx.gbl...
> before
suspect[vbcol=seagreen]
login[vbcol=seagreen]
> does
> the
run[vbcol=seagreen]
> end
Is[vbcol=seagreen]
like[vbcol=seagreen]
> identical
pull[vbcol=seagreen]
merge[vbcol=seagreen]
and[vbcol=seagreen]
> connect
had[vbcol=seagreen]
I[vbcol=seagreen]
> this
However,[vbcol=seagreen]
> I
> time
> agent.
> Error: -2147201017
most[vbcol=seagreen]
> of
> distributor
> agent
a[vbcol=seagreen]
or[vbcol=seagreen]
if
>

Merge replication in 2005... Version conflict?

I recently Upgraded both of my servers to SQL Server 2005 Standard.
Upon trying to set up Merge replication between the 2, I get this error:
For merge publications, the version of the Subscriber must not exceed the
version of the Publisher. (New Subscription Wizard)
They both have 2005 installed and updated (9.00.1399.00)
They both have the latest MDAC and .net versions
What could be causing this error?
Thanks!
what are the respective OSs? Could you post your publication script here?
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
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:CA2A8A38-E07F-4C16-9C05-F58FA92D61F2@.microsoft.com...
>I recently Upgraded both of my servers to SQL Server 2005 Standard.
> Upon trying to set up Merge replication between the 2, I get this error:
> For merge publications, the version of the Subscriber must not exceed the
> version of the Publisher. (New Subscription Wizard)
> They both have 2005 installed and updated (9.00.1399.00)
> They both have the latest MDAC and .net versions
> What could be causing this error?
> Thanks!
|||They are both Running Windows 2000 server.
Here is the script:
-- Enabling the replication database
use master
exec sp_replicationdboption @.dbname = N'twistpos', @.optname = N'merge
publish', @.value = N'true'
GO
-- Adding the merge publication
use [twistpos]
exec sp_addmergepublication @.publication = N'AccGC', @.description = N'Merge
publication of database ''twistpos'' from Publisher ''SERVER''.', @.sync_mode
= N'native', @.retention = 14, @.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', @.centralized_conflicts =
N'true', @.dynamic_filters = N'false', @.conflict_retention = 14,
@.keep_partition_changes = N'true', @.allow_synctoalternate = N'false',
@.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
GO
exec sp_addpublication_snapshot @.publication = N'AccGC', @.frequency_type =
4, @.frequency_interval = 14, @.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0, @.frequency_subday = 1,
@.frequency_subday_interval = 5, @.active_start_time_of_day = 500,
@.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date =
0, @.snapshot_job_name = N'SERVER-twistpos-AccGC-6'
exec sp_grant_publication_access @.publication = N'AccGC', @.login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'AccGC', @.login =
N'distributor_admin'
GO
exec sp_grant_publication_access @.publication = N'AccGC', @.login =
N'DOMAIN\Administrator'
GO
exec sp_grant_publication_access @.publication = N'AccGC', @.login = N'sa'
GO
-- Adding the merge articles
use [twistpos]
exec sp_addmergearticle @.publication = N'AccGC', @.article = N'GiftCards',
@.source_owner = N'dbo', @.source_object = N'GiftCards', @.type = N'table',
@.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop',
@.schema_option = 0x0000000000004FF1, @.auto_identity_range = N'true',
@.pub_identity_range = 100000, @.identity_range = 100000, @.threshold = 90,
@.destination_owner = N'dbo', @.column_tracking = N'false',
@.subset_filterclause = N'', @.vertical_partition = N'false',
@.verify_resolver_signature = 1, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
use [twistpos]
exec sp_addmergearticle @.publication = N'AccGC', @.article = N'Account',
@.source_owner = N'dbo', @.source_object = N'Account', @.type = N'table',
@.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop',
@.schema_option = 0x0000000000006FF1, @.auto_identity_range = N'false',
@.destination_owner = N'dbo', @.column_tracking = N'false',
@.subset_filterclause = N'', @.vertical_partition = N'false',
@.verify_resolver_signature = 1, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
"Hilary Cotter" wrote:

> what are the respective OSs? Could you post your publication script here?
> --
> 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
> "Ben" <Ben@.discussions.microsoft.com> wrote in message
> news:CA2A8A38-E07F-4C16-9C05-F58FA92D61F2@.microsoft.com...
>
>
|||see this command? delete it,
- it works then - publication_compatibility_level = N'80RTM',
unless your subscriber really is SQL 2000 RTM. I would also advise you to
drop the existing subscription database and recreate it.
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
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:38EC9C88-370F-49D2-9257-D434E8BC6C30@.microsoft.com...[vbcol=seagreen]
> They are both Running Windows 2000 server.
> Here is the script:
> -- Enabling the replication database
> use master
> exec sp_replicationdboption @.dbname = N'twistpos', @.optname = N'merge
> publish', @.value = N'true'
> GO
> -- Adding the merge publication
> use [twistpos]
> exec sp_addmergepublication @.publication = N'AccGC', @.description =
> N'Merge
> publication of database ''twistpos'' from Publisher ''SERVER''.',
> @.sync_mode
> = N'native', @.retention = 14, @.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', @.centralized_conflicts =
> N'true', @.dynamic_filters = N'false', @.conflict_retention = 14,
> @.keep_partition_changes = N'true', @.allow_synctoalternate = N'false',
> @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0
> GO
>
> exec sp_addpublication_snapshot @.publication = N'AccGC', @.frequency_type =
> 4, @.frequency_interval = 14, @.frequency_relative_interval = 1,
> @.frequency_recurrence_factor = 0, @.frequency_subday = 1,
> @.frequency_subday_interval = 5, @.active_start_time_of_day = 500,
> @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date
> =
> 0, @.snapshot_job_name = N'SERVER-twistpos-AccGC-6'
> exec sp_grant_publication_access @.publication = N'AccGC', @.login =
> N'BUILTIN\Administrators'
> GO
> exec sp_grant_publication_access @.publication = N'AccGC', @.login =
> N'distributor_admin'
> GO
> exec sp_grant_publication_access @.publication = N'AccGC', @.login =
> N'DOMAIN\Administrator'
> GO
> exec sp_grant_publication_access @.publication = N'AccGC', @.login = N'sa'
> GO
> -- Adding the merge articles
> use [twistpos]
> exec sp_addmergearticle @.publication = N'AccGC', @.article = N'GiftCards',
> @.source_owner = N'dbo', @.source_object = N'GiftCards', @.type = N'table',
> @.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop',
> @.schema_option = 0x0000000000004FF1, @.auto_identity_range = N'true',
> @.pub_identity_range = 100000, @.identity_range = 100000, @.threshold = 90,
> @.destination_owner = N'dbo', @.column_tracking = N'false',
> @.subset_filterclause = N'', @.vertical_partition = N'false',
> @.verify_resolver_signature = 1, @.allow_interactive_resolver = N'false',
> @.fast_multicol_updateproc = N'true', @.check_permissions = 0
> GO
> use [twistpos]
> exec sp_addmergearticle @.publication = N'AccGC', @.article = N'Account',
> @.source_owner = N'dbo', @.source_object = N'Account', @.type = N'table',
> @.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop',
> @.schema_option = 0x0000000000006FF1, @.auto_identity_range = N'false',
> @.destination_owner = N'dbo', @.column_tracking = N'false',
> @.subset_filterclause = N'', @.vertical_partition = N'false',
> @.verify_resolver_signature = 1, @.allow_interactive_resolver = N'false',
> @.fast_multicol_updateproc = N'true', @.check_permissions = 0
> GO
> "Hilary Cotter" wrote:
|||You still have it set in 80 compatibility mode.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:CA2A8A38-E07F-4C16-9C05-F58FA92D61F2@.microsoft.com...
>I recently Upgraded both of my servers to SQL Server 2005 Standard.
> Upon trying to set up Merge replication between the 2, I get this error:
> For merge publications, the version of the Subscriber must not exceed the
> version of the Publisher. (New Subscription Wizard)
> They both have 2005 installed and updated (9.00.1399.00)
> They both have the latest MDAC and .net versions
> What could be causing this error?
> Thanks!
|||OK... So here's what I figured out.
In Object Explorer, I see my subscription server as version 9.0.1399, but
the publication server is still marked 8.0.194.
How do I upgrade the publication server to 9.0.1399?
Do I still need to drop the old merge replication? How do I go about that?
Thanks for the help... still new to this 2005 thing.
"Hilary Cotter" wrote:

> see this command? delete it,
> - it works then - publication_compatibility_level = N'80RTM',
> unless your subscriber really is SQL 2000 RTM. I would also advise you to
> drop the existing subscription database and recreate it.
>
> --
> 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
> "Ben" <Ben@.discussions.microsoft.com> wrote in message
> news:38EC9C88-370F-49D2-9257-D434E8BC6C30@.microsoft.com...
>
>
|||OK, I'm really confused here. Can you issue a select @.@.version on your
publisher and post it here marked publisher, and do the same on your
subscriber and post it here marked subscriber.
You can't create SQL 2005 publications on SQL 2000 publishers - there is
something very wrong here. Besides I was able to repro your problem between
two SQL 2005 machines.
To drop the existing merge publications, right click on it in Enterprise
Manager or SQL Server Management Studio. and select Delete.
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
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:3B87E513-4C0E-4294-B45D-273485DDD94F@.microsoft.com...[vbcol=seagreen]
> OK... So here's what I figured out.
> In Object Explorer, I see my subscription server as version 9.0.1399, but
> the publication server is still marked 8.0.194.
> How do I upgrade the publication server to 9.0.1399?
> Do I still need to drop the old merge replication? How do I go about that?
> Thanks for the help... still new to this 2005 thing.
> "Hilary Cotter" wrote:
|||Just change the database compatibility level within the database properties.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Ben" <Ben@.discussions.microsoft.com> wrote in message
news:3B87E513-4C0E-4294-B45D-273485DDD94F@.microsoft.com...[vbcol=seagreen]
> OK... So here's what I figured out.
> In Object Explorer, I see my subscription server as version 9.0.1399, but
> the publication server is still marked 8.0.194.
> How do I upgrade the publication server to 9.0.1399?
> Do I still need to drop the old merge replication? How do I go about that?
> Thanks for the help... still new to this 2005 thing.
> "Hilary Cotter" wrote:
|||The instance had not been updated. Reattatched and everything was fine.
Thanks for the help.
Ben
"Michael Hotek" wrote:

> Just change the database compatibility level within the database properties.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Ben" <Ben@.discussions.microsoft.com> wrote in message
> news:3B87E513-4C0E-4294-B45D-273485DDD94F@.microsoft.com...
>
>

Merge Replication database can not truncate log

In my SQL Server 2000 Standard Edition.
I am trying to truncate the log for the database but i am getting the below
message.
The log was not truncated because records at the beginning of the log are
pending replication. Ensure the Log Reader Agent is running or use
sp_repldone to mark transactions as distributed.
Ponnu
This message relates to transactional replication rather than merge. Please
run your log-reader agent which will mark the log as read then it can be
truncated.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Nice to hear from you.
Sorry I have 1 snapshot replication and 6 merge replication on publication
on the database. I do not have any transaction replication.
Please let me know
regards
Ponnu
"Paul Ibison" wrote:

> This message relates to transactional replication rather than merge. Please
> run your log-reader agent which will mark the log as read then it can be
> truncated.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Presumably you once had a tranasactional replication setup and have removed
it without the log reader havong completed. In this case running sp_repldone
should suffice.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I never created Transactional replication on the this server.
It is looks lie very strange problem.
But Running the sp_repldone will cause any problem i mean need to
republished ?
EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time
= 0, @.reset = 1
Caution This procedure can be used in emergency situations to allow
truncation of the transaction log when transactions pending replication are
present. Using this procedure prevents Microsoft? SQL Server? 2000 from
replicating the database until the database is unpublished and republished.
Thanks
Ponnu
"Paul Ibison" wrote:

> Presumably you once had a tranasactional replication setup and have removed
> it without the log reader havong completed. In this case running sp_repldone
> should suffice.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Ponnu,
AFAIK this warning message relates exclusively to transactional replication.
If you are at all concerned, then you could open up a support case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
I have open the ticket with MS. i have checked with DBCC OpenTran, It looks
like some old transaction is there.
dbcc opentran
Transaction information for database 'ShipSure'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (178321:74147:1)
Regards
Ponnu
"Paul Ibison" wrote:

> Ponnu,
> AFAIK this warning message relates exclusively to transactional replication.
> If you are at all concerned, then you could open up a support case.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>