Replication errors - vicious circle

When I try to create a transactional publication, when I select the table I
want to replicate, I get the error "This table cannot be published because it
does not have a primary key column. Primary key columns are required for all
tables in transactional publications".
Then When I try to make the necessary changes to the table, such as adding a
column for the primary key, I get the error "Unable to modify table. Cannot
drop the table "tablename" because it is being used for replication."
I have never successfully replicated this table or any other. I can find no
evidence of any publications to remove.
I'm stuck!
Just answered my own question after reading thru a ton of posts on this forum.
"run sp_removedbreplication on each database..."
Thanks Paul Ibison!
"TahoePete" wrote:

> When I try to create a transactional publication, when I select the table I
> want to replicate, I get the error "This table cannot be published because it
> does not have a primary key column. Primary key columns are required for all
> tables in transactional publications".
> Then When I try to make the necessary changes to the table, such as adding a
> column for the primary key, I get the error "Unable to modify table. Cannot
> drop the table "tablename" because it is being used for replication."
> I have never successfully replicated this table or any other. I can find no
> evidence of any publications to remove.
> I'm stuck!
>
|||Issue the following statements
sp_MSunmarkreplinfo 'ProblemTableName'
Then you need to update the colstat column
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE syscolumns
SET colstat = colstat & ~4096
WHERE colstat & 4096 <>0
go
sp_configure 'allow updates', 2
go
reconfigure with override
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
"TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
news:346CE295-5031-45F3-90DA-9CA7A25424A5@.microsoft.com...
> When I try to create a transactional publication, when I select the table
> I
> want to replicate, I get the error "This table cannot be published because
> it
> does not have a primary key column. Primary key columns are required for
> all
> tables in transactional publications".
> Then When I try to make the necessary changes to the table, such as adding
> a
> column for the primary key, I get the error "Unable to modify table.
> Cannot
> drop the table "tablename" because it is being used for replication."
> I have never successfully replicated this table or any other. I can find
> no
> evidence of any publications to remove.
> I'm stuck!
>
|||I receive the error "ad hoc updates to system catalogues are not allowed"
from the update command.
"Hilary Cotter" wrote:

> Issue the following statements
> sp_MSunmarkreplinfo 'ProblemTableName'
> Then you need to update the colstat column
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> UPDATE syscolumns
> SET colstat = colstat & ~4096
> WHERE colstat & 4096 <>0
> go
> sp_configure 'allow updates', 2
> go
> reconfigure with override
> 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
> "TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
> news:346CE295-5031-45F3-90DA-9CA7A25424A5@.microsoft.com...
>
>
|||I'm not sure what is wrong here - this command should enable this
sp_configure 'allow updates', 1
go
reconfigure with override
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
"TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
news:793A5914-F3E9-4195-9F70-BA7C544E4427@.microsoft.com...[vbcol=seagreen]
>I receive the error "ad hoc updates to system catalogues are not allowed"
> from the update command.
> "Hilary Cotter" wrote:

replication error message in SQL Server 2005 (June CTP)

I upgraded from earlier version of SQL Server 2005 CTP to the June version.
When I try to create a new Publication for Replication (using the Management
Studio), I get the error message:
Invalid object name msdb.dbo.MSdistributiondbs
Yes, I do not have the above table and I don't see any old publications (the
older ones were removed via Publisher Properties). Why does SQL Server think
that I still have some old publications? Where does it store the info? I
don't find any tables like MSDdistribution, MSdistpublishers etc.http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jason Manfield" wrote:
> I upgraded from earlier version of SQL Server 2005 CTP to the June version.
> When I try to create a new Publication for Replication (using the Management
> Studio), I get the error message:
> Invalid object name msdb.dbo.MSdistributiondbs
> Yes, I do not have the above table and I don't see any old publications (the
> older ones were removed via Publisher Properties). Why does SQL Server think
> that I still have some old publications? Where does it store the info? I
> don't find any tables like MSDdistribution, MSdistpublishers etc.
>

replication error message in SQL Server 2005 (June CTP)

I upgraded from earlier version of SQL Server 2005 CTP to the June version.
When I try to create a new Publication for Replication (using the Management
Studio), I get the error message:
Invalid object name msdb.dbo.MSdistributiondbs
Yes, I do not have the above table and I don't see any old publications (the
older ones were removed via Publisher Properties). Why does SQL Server think
that I still have some old publications? Where does it store the info? I
don't find any tables like MSDdistribution, MSdistpublishers etc.
http://communities.microsoft.com/new...=sqlserver2005
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jason Manfield" wrote:

> I upgraded from earlier version of SQL Server 2005 CTP to the June version.
> When I try to create a new Publication for Replication (using the Management
> Studio), I get the error message:
> Invalid object name msdb.dbo.MSdistributiondbs
> Yes, I do not have the above table and I don't see any old publications (the
> older ones were removed via Publisher Properties). Why does SQL Server think
> that I still have some old publications? Where does it store the info? I
> don't find any tables like MSDdistribution, MSdistpublishers etc.
>

replication error message in SQL Server 2005 (June CTP)

I upgraded from earlier version of SQL Server 2005 CTP to the June version.
When I try to create a new Publication for Replication (using the Management
Studio), I get the error message:
Invalid object name msdb.dbo.MSdistributiondbs
Yes, I do not have the above table and I don't see any old publications (the
older ones were removed via Publisher Properties). Why does SQL Server think
that I still have some old publications? Where does it store the info? I
don't find any tables like MSDdistribution, MSdistpublishers etc.http://communities.microsoft.com/ne...p=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jason Manfield" wrote:

> I upgraded from earlier version of SQL Server 2005 CTP to the June version
.
> When I try to create a new Publication for Replication (using the Manageme
nt
> Studio), I get the error message:
> Invalid object name msdb.dbo.MSdistributiondbs
> Yes, I do not have the above table and I don't see any old publications (t
he
> older ones were removed via Publisher Properties). Why does SQL Server thi
nk
> that I still have some old publications? Where does it store the info? I
> don't find any tables like MSDdistribution, MSdistpublishers etc.
>sql

Replication error in Snapshot publication

Hi,
I built a database replication between 2 SQL 2000 servers in our domain
(both with SP3). I used snapshot publication and it worked for a few days.
But one day the Distribution Agent task failed with the following,
Error message:
Line 95: Incorrect syntax near 'DROP TABLE '.
Error details:
Line 95: Incorrect syntax near 'DROP TABLE '.
(Source: CAWLKW05 (Data source); Error number: 170)
------
Line 102: Incorrect syntax near 'SELECT * INTO '.
(Source: CAWLKW05 (Data source); Error number: 170)
------
Please suggest
Thanks
Johnny H.
can you script out the schema of the articles you are replicating and post
them here along with your publication script?
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
"Johnny Hu" <Johnny Hu@.discussions.microsoft.com> wrote in message
news:A5490840-428B-4025-B3DD-80536F81DE5F@.microsoft.com...
> Hi,
> I built a database replication between 2 SQL 2000 servers in our domain
> (both with SP3). I used snapshot publication and it worked for a few
days.
> But one day the Distribution Agent task failed with the following,
> Error message:
> Line 95: Incorrect syntax near 'DROP TABLE '.
> Error details:
> Line 95: Incorrect syntax near 'DROP TABLE '.
> (Source: CAWLKW05 (Data source); Error number: 170)
> ----
--
> Line 102: Incorrect syntax near 'SELECT * INTO '.
> (Source: CAWLKW05 (Data source); Error number: 170)
> ----
--
> Please suggest
> Thanks
> Johnny H.

Replication error after installing SP3a

After installation of SP3a on the publication server, clients are getting
errors during replication.
The error detail is : "Select permission denied on object
'MSDistributor_access', database 'tempdb', owner dbo
does this table exist in tempdb?
"Nol Thoelen" <noel.thoelen@.itomni.be> wrote in message
news:407bdf20$0$876$a0ced6e1@.news.skynet.be...
> After installation of SP3a on the publication server, clients are getting
> errors during replication.
> The error detail is : "Select permission denied on object
> 'MSDistributor_access', database 'tempdb', owner dbo
>
>
|||Yes, the table does exist in the tempdb database
"Hilary Cotter" <hilaryk@.att.net> schreef in bericht
news:erRWAPXIEHA.2596@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> does this table exist in tempdb?
> "Nol Thoelen" <noel.thoelen@.itomni.be> wrote in message
> news:407bdf20$0$876$a0ced6e1@.news.skynet.be...
getting
>

replication error 21258

Hi,

When I'm trying to subscribe at a publication with webservice method and RMO, i have the following error :

"You must first drop all existing merge publications to add an anonymous or local subscription to

database MyDB. Only members of the sysadmin fixed server role can perform this operation without specifying @.job_login or

@.job_password. Changed database context to MyDB"

Any help is welcome

Thanks

This usually happens when subscription database also has a merge publication. Could you verify if this is the case?|||

That's right.

Thanks.

Replication error

Hi

I am trying to replicate a database using the snapshot method. The publication worked fine, when the second server tried to pull the replication, it started, but then come up with the following error whilst creating the tables.

Insert Error: Column name or number of supplied values does not match table definition.

Any ideas?

cheers

SimonThe error usually occurs in transactional replication when someone accidentally changed the DDL in subscriber. You can reinitialize the subscription from the publication database and refresh your subscription.|||still no luck. Didn't help. Stil had the same error, any other ideas?

cheers

Replication distribution cleanup error

I used the SQL Server 2005 publication wizard to set up transactional replication. It scheduled a job called "Distribution clean up: distribution" that calls sp_MSdistribution_cleanup. The job fails every time it runs. The xp_cmdshell is enabled and SQL Server Agent runs under an account that has sufficient privileges to execute it. The account also has full rights to the repldata subdirectories. I have applied service pack 1.

I looked through the sp_MSdistribution_cleanup code and it looks like there are two stored procedures and one function missing from the SQL server databases. They are

master.dbo.sp_MSunc_to_drive sys.fn_escapecmdshellsymbolsremovequotes sys.sp_MSdrop_distribution_agentid

Where can I get a copy of these stored procedures? The expired distribution data is slowly consuming my remaining hard drive space. Thanks.

Joe

Hi, Joe,

There seems to be a bug in our code where we should not call master.dbo.sp_MSunc_to_drive, instead we should call sys.sp_MSunc_to_drive. In SQL Server 2005, we have move most of the system sps to mssqlsystemresource database, and some of them are hidden from end users as they're not supposed to be used directly by customers. sys.sp_MSunc_to_drive, sys.fn_escapecmdshellsymbolsremovequotes and sys.sp_MSdrop_distribution_agentid are hidden sps/function thus you don't see them but they do exist.

I will do more research and see if there is a workaround.

By the way, can you post the exact error message you're seeing from distribution cleanup job (by using replication monitor to view the job history)? Are you using dynamic snapshot merge replication? Any specific information about your replication setup would be helpful.

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks for answering me so quickly. The problem has been resolved. The account under which xp_cmdshell was executing did not have the proper access to the repldata share.

Joe

|||

Glad to hear that the problem is solved. The issue mentioned in my post is a minor issue and should not cause any functional problems. It would be helpful for other visitors of this thread if you can post the error message you're seeing before it's fixed (it should still be available in replication agent history).

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I'm really sorry, but the clean up agent already deleted the history. I think part of the message was "Could not clean up the distribution transaction tables."

Joe

sql

Replication Distribution Agent Does Not Start

Hi,
For some magical reason, my database replication has suddenly stopped
working. I modified the publication but the Distribution agent will
not run; the snapshot does not get applied to the subscriber, and
changes made at the publisher do not get replicated.
My configuration is: Transactional Replication with a
‘Pull’ Subscription. The Subscriber is Updateable with
Immediate Updates Only. All created via the SQL Server Enterprise
Manager. Both the Publisher and Subscriber are on the same server.
I’m running SQL Server 2000 (8.00.760 SP3), on Windows 2000
Server (5.0 (2195)).
I manually run the snapshop agent. It runs fine with the status
‘Succeeded’ and the Last Action is ‘A Snapshot of X
article(s) was generated’.
When I look at the distribution agent, things start to get a bit
confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
<SERVERNAME?>/ Publication/ Subscription), the status stays
constantly at ‘Never Started’. All other columns are
either blank or 0. When I right click over the subscription, both
Start Synchronising and Stop Synchronising are NOT greyed out. If I
click on either Start Synchronising or Stop Synchronising (absolutely)
nothing happens (except the mouse turns ‘busy’ for a
fraction of a second). There are no error messages.
I added -output c:\sqlout.txt -outputverboselevel 3 to the
distribution agent command. C:\sqlout.txt did not get created.
It worked fine up to a couple of days ago. I’m not sure exactly
what caused the problem, as I made a few changes at the same time. The
changes included: adding a new transactional publication, adding more
articles to an existing transaction publication, and adding a merge
publication. I’ve tried to go back to the set up before I made
the changes, even disabling replication and starting again from
scratch. Still the problem persists.
The owner of the distribution agent has dbo permissions in both the
publishing and subscribing database. Under ‘Publisher and
Distributor Properties’ I’ve set the ‘Replication
Agent Connection to Publisher’ and ‘Agent Connection to
the Subscriber’ both to Use SQL Server Authentication, both with
the same account that has dbo permissions in both publishing and
subscribing database.
Has anybody come across anything like this before? Can anybody make
any suggestions? Thanks in advance for your help.
Regards
Damian Helme
IS Manager
INTEC Engineering (UK) Ltd.
are you using an ActiveX control or Windows Synchronization Manager to
manage this subscription? If so you will not be able to manage your
distribution agent in EM.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Damian H" <damianhelme@.hotmail.com> wrote in message
news:2b893e38.0407220153.32af854e@.posting.google.c om...
> Hi,
> For some magical reason, my database replication has suddenly stopped
> working. I modified the publication but the Distribution agent will
> not run; the snapshot does not get applied to the subscriber, and
> changes made at the publisher do not get replicated.
> My configuration is: Transactional Replication with a
> ‘Pull’ Subscription. The Subscriber is Updateable with
> Immediate Updates Only. All created via the SQL Server Enterprise
> Manager. Both the Publisher and Subscriber are on the same server.
> I’m running SQL Server 2000 (8.00.760 SP3), on Windows 2000
> Server (5.0 (2195)).
> I manually run the snapshop agent. It runs fine with the status
> ‘Succeeded’ and the Last Action is ‘A Snapshot of X
> article(s) was generated’.
> When I look at the distribution agent, things start to get a bit
> confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
> <SERVERNAME?>/ Publication/ Subscription), the status stays
> constantly at ‘Never Started’. All other columns are
> either blank or 0. When I right click over the subscription, both
> Start Synchronising and Stop Synchronising are NOT greyed out. If I
> click on either Start Synchronising or Stop Synchronising (absolutely)
> nothing happens (except the mouse turns ‘busy’ for a
> fraction of a second). There are no error messages.
> I added -output c:\sqlout.txt -outputverboselevel 3 to the
> distribution agent command. C:\sqlout.txt did not get created.
> It worked fine up to a couple of days ago. I’m not sure exactly
> what caused the problem, as I made a few changes at the same time. The
> changes included: adding a new transactional publication, adding more
> articles to an existing transaction publication, and adding a merge
> publication. I’ve tried to go back to the set up before I made
> the changes, even disabling replication and starting again from
> scratch. Still the problem persists.
> The owner of the distribution agent has dbo permissions in both the
> publishing and subscribing database. Under ‘Publisher and
> Distributor Properties’ I’ve set the ‘Replication
> Agent Connection to Publisher’ and ‘Agent Connection to
> the Subscriber’ both to Use SQL Server Authentication, both with
> the same account that has dbo permissions in both publishing and
> subscribing database.
> Has anybody come across anything like this before? Can anybody make
> any suggestions? Thanks in advance for your help.
> Regards
> Damian Helme
> IS Manager
> INTEC Engineering (UK) Ltd.
|||Hillary
Thanks for your reply. No I am not using an ActiveX control or Windows
Synchronization Manager. It is all done through EM.
Here's the post again, but this time free of the garbled control
characters:
================================================== =============================
Hi,
For some magical reason, my database replication has suddenly stopped
working. I modified the publication but the Distribution agent will
not run; the snapshot does not get applied to the subscriber, and
changes made at the publisher do not get replicated.
My configuration is: Transactional Replication with a 'Pull'
Subscription. The Subscriber is Updateable with Immediate Updates
Only. All created via the SQL Server Enterprise Manager. Both the
Publisher and Subscriber are on the same server. I'm running SQL
Server 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).
I manually run the snapshop agent. It runs fine with the status
'Succeeded' and the Last Action is 'A Snapshot of X article(s) was
generated'.
When I look at the distribution agent, things start to get a bit
confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
<SERVERNAME?>/ Publication/ Subscription), the status stays
constantly at 'Never Started'. All other columns are either blank or
0. When I right click over the subscription, both Start Synchronising
and Stop Synchronising are NOT greyed out. If I click on either Start
Synchronising or Stop Synchronising (absolutely) nothing happens
(except the mouse turns 'busy' for a fraction of a second). There are
no error messages.
I added -output c:\sqlout.txt -outputverboselevel 3 to the
distribution agent command. C:\sqlout.txt did not get created.
It worked fine up to a couple of days ago. I'm not sure exactly what
caused the problem, as I made a few changes at the same time. The
changes included: adding a new transactional publication, adding more
articles to an existing transaction publication, and adding a merge
publication. I've tried to go back to the set up before I made the
changes, even disabling replication and starting again from scratch.
Still the problem persists.
The owner of the distribution agent has dbo permissions in both the
publishing and subscribing database. Under 'Publisher and Distributor
Properties' I've set the 'Replication Agent Connection to Publisher'
and 'Agent Connection to the Subscriber' both to Use SQL Server
Authentication, both with the same account that has dbo permissions in
both publishing and subscribing database.
Has anybody come across this problem before? Can anybody make any
suggestions? Thanks in advance for your help.
Regards
Damian Helme
IS Manager
INTEC Engineering (UK) Ltd.
|||I've encountered this error before and its driving me crazy trying to recall
exactly what is causing it. IIRC its something pretty simple.
Can you right click on your distribution agent, select agent properties
select steps, click on Run Agent, and click edit.
Then copy everything in the commands text box.
Open a command window and go to c:\program files\microsoft sql server\80\com
and then type
distrib and paste your commands here. To do this, go to the c:\ thingy in
the upper left hand corner of your command window and select edit - paste.
Make sure there is a space between distrib and the commands.
Then hit enter. You should get an error message this time.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Damian H" <damianhelme@.hotmail.com> wrote in message
news:2b893e38.0407222334.bc69d37@.posting.google.co m...
> Hillary
> Thanks for your reply. No I am not using an ActiveX control or Windows
> Synchronization Manager. It is all done through EM.
> Here's the post again, but this time free of the garbled control
> characters:
>
>
================================================== ==========================
===
> Hi,
> For some magical reason, my database replication has suddenly stopped
> working. I modified the publication but the Distribution agent will
> not run; the snapshot does not get applied to the subscriber, and
> changes made at the publisher do not get replicated.
>
> My configuration is: Transactional Replication with a 'Pull'
> Subscription. The Subscriber is Updateable with Immediate Updates
> Only. All created via the SQL Server Enterprise Manager. Both the
> Publisher and Subscriber are on the same server. I'm running SQL
> Server 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).
> I manually run the snapshop agent. It runs fine with the status
> 'Succeeded' and the Last Action is 'A Snapshot of X article(s) was
> generated'.
> When I look at the distribution agent, things start to get a bit
> confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
> <SERVERNAME?>/ Publication/ Subscription), the status stays
> constantly at 'Never Started'. All other columns are either blank or
> 0. When I right click over the subscription, both Start Synchronising
> and Stop Synchronising are NOT greyed out. If I click on either Start
> Synchronising or Stop Synchronising (absolutely) nothing happens
> (except the mouse turns 'busy' for a fraction of a second). There are
> no error messages.
> I added -output c:\sqlout.txt -outputverboselevel 3 to the
> distribution agent command. C:\sqlout.txt did not get created.
> It worked fine up to a couple of days ago. I'm not sure exactly what
> caused the problem, as I made a few changes at the same time. The
> changes included: adding a new transactional publication, adding more
> articles to an existing transaction publication, and adding a merge
> publication. I've tried to go back to the set up before I made the
> changes, even disabling replication and starting again from scratch.
> Still the problem persists.
> The owner of the distribution agent has dbo permissions in both the
> publishing and subscribing database. Under 'Publisher and Distributor
> Properties' I've set the 'Replication Agent Connection to Publisher'
> and 'Agent Connection to the Subscriber' both to Use SQL Server
> Authentication, both with the same account that has dbo permissions in
> both publishing and subscribing database.
> Has anybody come across this problem before? Can anybody make any
> suggestions? Thanks in advance for your help.
> Regards
> Damian Helme
> IS Manager
> INTEC Engineering (UK) Ltd.
|||Thanks for the suggestion, I didn't get chance to try your suggestion
as I had already reinstalled SQL Server, which solved the problem.
I'll keep a note of your suggestion for future use.
Thanks again.
Damian.

Replication Distribution Agent Does Not Start

Hi,

For some magical reason, my database replication has suddenly stopped
working. I modified the publication but the Distribution agent will
not run; the snapshot does not get applied to the subscriber, and
changes made at the publisher do not get replicated.

My configuration is: Transactional Replication with a 'Pull'
Subscription. The Subscriber is Updateable with Immediate Updates
Only. All created via the SQL Server Enterprise Manager. Both the
Publisher and Subscriber are on the same server. I'm running SQL
Server 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).

I manually run the snapshop agent. It runs fine with the status
'Succeeded' and the Last Action is 'A Snapshot of X article(s) was
generated'.

When I look at the distribution agent, things start to get a bit
confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
<SERVERNAME?>/ Publication/ Subscription), the status stays
constantly at 'Never Started'. All other columns are either blank or
0. When I right click over the subscription, both Start Synchronising
and Stop Synchronising are NOT greyed out. If I click on either Start
Synchronising or Stop Synchronising (absolutely) nothing happens
(except the mouse turns 'busy' for a fraction of a second). There are
no error messages.

I added -output c:\sqlout.txt -outputverboselevel 3 to the
distribution agent command. C:\sqlout.txt did not get created.

It worked fine up to a couple of days ago. I'm not sure exactly what
caused the problem, as I made a few changes at the same time. The
changes included: adding a new transactional publication, adding more
articles to an existing transaction publication, and adding a merge
publication. I've tried to go back to the set up before I made the
changes, even disabling replication and starting again from scratch.
Still the problem persists.

The owner of the distribution agent has dbo permissions in both the
publishing and subscribing database. Under 'Publisher and Distributor
Properties' I've set the 'Replication Agent Connection to Publisher'
and 'Agent Connection to the Subscriber' both to Use SQL Server
Authentication, both with the same account that has dbo permissions in
both publishing and subscribing database.

Has anybody come across this problem before? Can anybody make any
suggestions? Thanks in advance for your help.

Regards

Damian Helme
IS Manager
INTEC Engineering (UK) Ltd."Damian H" <damianhelme@.hotmail.com> wrote in message
news:2b893e38.0407222337.542a5fd1@.posting.google.c om...
> Hi,
> For some magical reason, my database replication has suddenly stopped
> working. I modified the publication but the Distribution agent will
> not run; the snapshot does not get applied to the subscriber, and
> changes made at the publisher do not get replicated.
>
> My configuration is: Transactional Replication with a 'Pull'
> Subscription. The Subscriber is Updateable with Immediate Updates
> Only. All created via the SQL Server Enterprise Manager. Both the
> Publisher and Subscriber are on the same server. I'm running SQL
> Server 2000 (8.00.760 SP3), on Windows 2000 Server (5.0 (2195)).
> I manually run the snapshop agent. It runs fine with the status
> 'Succeeded' and the Last Action is 'A Snapshot of X article(s) was
> generated'.
> When I look at the distribution agent, things start to get a bit
> confusing in ( <SERVERNAME>/Replication Monitor/ Publishers/
> <SERVERNAME?>/ Publication/ Subscription), the status stays
> constantly at 'Never Started'. All other columns are either blank or
> 0. When I right click over the subscription, both Start Synchronising
> and Stop Synchronising are NOT greyed out. If I click on either Start
> Synchronising or Stop Synchronising (absolutely) nothing happens
> (except the mouse turns 'busy' for a fraction of a second). There are
> no error messages.
> I added -output c:\sqlout.txt -outputverboselevel 3 to the
> distribution agent command. C:\sqlout.txt did not get created.
> It worked fine up to a couple of days ago. I'm not sure exactly what
> caused the problem, as I made a few changes at the same time. The
> changes included: adding a new transactional publication, adding more
> articles to an existing transaction publication, and adding a merge
> publication. I've tried to go back to the set up before I made the
> changes, even disabling replication and starting again from scratch.
> Still the problem persists.
> The owner of the distribution agent has dbo permissions in both the
> publishing and subscribing database. Under 'Publisher and Distributor
> Properties' I've set the 'Replication Agent Connection to Publisher'
> and 'Agent Connection to the Subscriber' both to Use SQL Server
> Authentication, both with the same account that has dbo permissions in
> both publishing and subscribing database.
> Has anybody come across this problem before? Can anybody make any
> suggestions? Thanks in advance for your help.
> Regards
> Damian Helme
> IS Manager
> INTEC Engineering (UK) Ltd.

You might want to post this in microsoft.public.sqlserver.replication, as
you may get a better answer over there.

Simon

Replication Details

Hi,

I used my DB and some tables for replication earlier, now i removed my all replication subscription and publication and my DB is out of replication.

Now i am altering my table for PK then its giving err as follows:

Cannot alter the table 'mstparty' because it is being published for replication.

From where i can find details, i mean in which sys table all replication obj are stored.

There is no Replication now on DB then why still table 'mstparty' has a replication err when i alter table.

Please advice as i am not able to alter my table or tables which earlier i used in replication and now i removed replication.Check this...
http://msdn2.microsoft.com/en-us/library/ms152757.aspx (http://msdn2.microsoft.com/en-us/library/ms152757.aspx)|||Run this query in your Distribution database. & see what data are you getting..?

Select * from MSpublications
Select * from MSarticles

If you are getting any data related your database, means your DB is still there for replication.

Use Publication Wizard from Enterprise Manager for remove publication.|||Not able to find out anything, as there is no subscription or publication exist at all.|||Can you post error along with error number..?|||here is the solutions below:

1. EXEC sp_removedbreplication 'your_database_name'

2. Execute the following procedure on the problematic database and pass the table name as the parameter:

EXEC sp_msunmarkreplinfo 'your_table_name'

3. You may not need this method, but take this as a last resort.

EXEC sp_configure 'allow',1
go
reconfigure with override
go
use your_database_name
go
update sysobjects set replinfo = 0 where name = 'your_table_name'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
gosql

Replication Deadlocks

Hello,
I am running multiple replication publication on a SQL 2k database, with
a separate server as distribution agent, then finally push subscriptions
to multiple databases across 5 servers.
All publications use row and column filtering in them.
My question is, can I use locking hints on the selection criteria for
the row filtering?
ie. Can I specify WITH (NOLOCK) on the row filtering so that it
eliminates the majority of the blocking / deadlocks?
Thanks
Dave
*** Sent via Developersdex http://www.codecomments.com ***
You could if it was a subquery. Do you have indexes on the join
condition/filter key in your filter?
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
"Dave" <Anonymous@.devdex.com> wrote in message
news:eqm8zv1THHA.5060@.TK2MSFTNGP02.phx.gbl...
>
> Hello,
> I am running multiple replication publication on a SQL 2k database, with
> a separate server as distribution agent, then finally push subscriptions
> to multiple databases across 5 servers.
> All publications use row and column filtering in them.
> My question is, can I use locking hints on the selection criteria for
> the row filtering?
> ie. Can I specify WITH (NOLOCK) on the row filtering so that it
> eliminates the majority of the blocking / deadlocks?
> Thanks
> Dave
> *** Sent via Developersdex http://www.codecomments.com ***
|||There is an index on the originating table/column, but the deadlocking
appears to be happening on the distribution database.
David
*** Sent via Developersdex http://www.codecomments.com ***

Replication creates new triggers?

I have just created a Publication and now most or all of my triggers have
been duplicated and given a name like ins_03FA9EAD39A54B18B6276581AA1F0C42.
Is this doe in replication setup? Thanks.
David
These are merge replication specific triggers. They are created in the
snapshot process on the publisher, and also when the snapshot is applied on
the subscriber.
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
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:uXbEBbRHGHA.528@.TK2MSFTNGP12.phx.gbl...
>I have just created a Publication and now most or all of my triggers have
>been duplicated and given a name like ins_03FA9EAD39A54B18B6276581AA1F0C42.
>Is this doe in replication setup? Thanks.
> David
>

Replication Comparison Chart for SQL2005

Anyone know of a chart on the Microsoft Site that compares all the
Replication types for SQL 2005?
Trans Shipping, Mirroring, Publication and Subscription and all that
stuff?
Here is a pretty good one on the Dell site.
www.dell.com/downloads/global/power/ps1q06-20050325-Sankaran.pdf
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
"jughead" <mike.aarset@.gmail.com> wrote in message
news:1174056604.344365.92450@.o5g2000hsb.googlegrou ps.com...
> Anyone know of a chart on the Microsoft Site that compares all the
> Replication types for SQL 2005?
> Trans Shipping, Mirroring, Publication and Subscription and all that
> stuff?
>

Replication between SQL2000 and SQL2005

I have a distributor setup on SQL Server 2005 (9.0.3042) and am trying to create a publication on SQL Server 2000 (8.0.2040) which receives the following error in my production environment:

Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802

Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.

The interesting part of this equation is that I was able to get this to work without error in my DEV (development) environment and well as my QAT (test) environment. This end result was that my distributor was SQL 2005, my publisher was SQL 2000 and my pull subscriber was SQL 2005. I have been diligently comparing our production environment to my other environment and have yet to find differences.

Has anyone else seen an error similiar to this? Any insight would be appreciated.

Thanks in advance,

Max

http://support.microsoft.com/kb/295027

HTH

Vishal

|||I'm having the same problem, and it's persisting even after setting the 8599 trace on. Were you able to get yours to work? If so, how?
sql

Replication Between Different Versions of SQL Server causes access denied error.

I am getting the following error:
SQL Server does not exist or access denied. Error number 17.
The publication and distributor is SQL Server 7.0 from internal lan.
The subscriber is SQL Server 2000 from external wan outside of the firewall.
Using SQL Server Enterprise Manager 7.0 on the publication server will not
allow me to register the subscriber as it says "You must upgrade your SQL
Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000(SQLDMO) to
connecti to this server". But if I use a non participanting SQL Server
Enterprise Manager 2000 within the internal lan it will allow me to register
and connect to the browser and I am able to view the database and its
tables.
In addition I can connect to the subscriber using Query Analyzer in version
7.0.
According to the MSDN under the section Replication Between Different
Versions of SQL Server, I fit the same criteria as Combination 2 for
snapshot replication ie. Publisher SQL Server 7.0/Distributor SQL Server
7.0/Subscriber SQL Server 2000.
So why does the push subscription fail?
Regards
Peter
Hello Peter,
This is expected behavior when trying to access SQL Server 2000 from SQL
7.0 version of SQL DMO. You will be able to connect to SQL Server 2000 from
SQL 7.0 version of Query Analyzer, but not Enterprise Manager. The
resolution is to upgrade the client tools to SQL 2000.
Also, as you have mentioned, you could use a SQL 2000 Eneterprise Manager
on a different server to work around this issue.
Combination 2 you referred to does not indicate that you could configure
this in SQL 7 Enterprise Manager. Since it is from SQL 2000 BOL, actually
you could only configure this from a SQL 2000 Enterprise Manager.
Hope this helps.
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default...national.aspx.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
>Subject: Replication Between Different Versions of SQL Server causes
access denied error.
>Date: Mon, 31 Oct 2005 16:30:49 +1000
>Lines: 32
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 61.88.17.230
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17479
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>I am getting the following error:
>SQL Server does not exist or access denied. Error number 17.
>The publication and distributor is SQL Server 7.0 from internal lan.
>The subscriber is SQL Server 2000 from external wan outside of the
firewall.
>Using SQL Server Enterprise Manager 7.0 on the publication server will not
>allow me to register the subscriber as it says "You must upgrade your SQL
>Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000(SQLDMO) to
>connecti to this server". But if I use a non participanting SQL Server
>Enterprise Manager 2000 within the internal lan it will allow me to
register
>and connect to the browser and I am able to view the database and its
>tables.
>In addition I can connect to the subscriber using Query Analyzer in
version
>7.0.
>According to the MSDN under the section Replication Between Different
>Versions of SQL Server, I fit the same criteria as Combination 2 for
>snapshot replication ie. Publisher SQL Server 7.0/Distributor SQL Server
>7.0/Subscriber SQL Server 2000.
>So why does the push subscription fail?
>Regards
>Peter
>
>
>
>
|||I have created the publication on SQL Server 7.0 server via SQL Server 2000
Enterprise and created a push subscription. As soon as the snapshoot was
ready to send I get the same error "The process could not connect to
Subscriber" (SQL Server does not exist or access denied. Error number: 17)
even though I could setup the subscriber and select the destination
database.
Regards
Peter
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl...
> Hello Peter,
> This is expected behavior when trying to access SQL Server 2000 from SQL
> 7.0 version of SQL DMO. You will be able to connect to SQL Server 2000
> from
> SQL 7.0 version of Query Analyzer, but not Enterprise Manager. The
> resolution is to upgrade the client tools to SQL 2000.
> Also, as you have mentioned, you could use a SQL 2000 Eneterprise Manager
> on a different server to work around this issue.
> Combination 2 you referred to does not indicate that you could configure
> this in SQL 7 Enterprise Manager. Since it is from SQL 2000 BOL, actually
> you could only configure this from a SQL 2000 Enterprise Manager.
> Hope this helps.
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default...national.aspx.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> access denied error.
> firewall.
> register
> version
>
|||Hello Peter,
This seems to be a MDAC issue. I suggest that you install latest MDAC 2.8
SP1 if it is on Win2000 server.
899456 Release manifest for MDAC 2.8 Service Pack 1 (2.81.1117.6)
http://support.microsoft.com/?id=899456
Also, please use SQL client network utility to make sure TCPIP and named
pipes are enabled. Also, you may want to create an Alais for SQL 2000
(subscriber) to test the situation.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
>References: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
<exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
>Subject: Re: Replication Between Different Versions of SQL Server causes
access denied error.
>Date: Tue, 1 Nov 2005 10:09:26 +1000
>Lines: 117
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <uaV8Pin3FHA.700@.TK2MSFTNGP15.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 61.88.17.230
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17507
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>I have created the publication on SQL Server 7.0 server via SQL Server
2000[vbcol=seagreen]
>Enterprise and created a push subscription. As soon as the snapshoot was
>ready to send I get the same error "The process could not connect to
>Subscriber" (SQL Server does not exist or access denied. Error number: 17)
>even though I could setup the subscriber and select the destination
>database.
>Regards
>Peter
>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
>news:exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl...
all[vbcol=seagreen]
https://partner.microsoft.com/US/tec...rview/40010469[vbcol=seagreen]
https://partner.microsoft.com/US/tec...pportoverview/[vbcol=seagreen]
not
>
>
|||The penny dropped!
Whilst I did configure SQL client network on my development pc to browse the
external database I didn't realise I had to configure it again on the
distributor server. The replication wizard sort of misled me as part of it
process what to nonimate the subscriber which it found on my development pc
whereas it should have failed it the distributor couldn't recognise it.
Regards
Peter
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:bCGcbYo3FHA.2984@.TK2MSFTNGXA01.phx.gbl...
> Hello Peter,
> This seems to be a MDAC issue. I suggest that you install latest MDAC 2.8
> SP1 if it is on Win2000 server.
> 899456 Release manifest for MDAC 2.8 Service Pack 1 (2.81.1117.6)
> http://support.microsoft.com/?id=899456
> Also, please use SQL client network utility to make sure TCPIP and named
> pipes are enabled. Also, you may want to create an Alais for SQL 2000
> (subscriber) to test the situation.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> <exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
> access denied error.
> 2000
> all
> https://partner.microsoft.com/US/tec...rview/40010469
> https://partner.microsoft.com/US/tec...pportoverview/
> not
>
|||Hello Peter,
Glad to hear the issue is resolved. :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
| References: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
<exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
<uaV8Pin3FHA.700@.TK2MSFTNGP15.phx.gbl>
<bCGcbYo3FHA.2984@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: Replication Between Different Versions of SQL Server causes
access denied error.
| Date: Thu, 3 Nov 2005 10:00:07 +1000
| Lines: 190
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <Ow70RmA4FHA.3540@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 61.88.17.230
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17574
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| The penny dropped!
| Whilst I did configure SQL client network on my development pc to browse
the
| external database I didn't realise I had to configure it again on the
| distributor server. The replication wizard sort of misled me as part of
it
| process what to nonimate the subscriber which it found on my development
pc
| whereas it should have failed it the distributor couldn't recognise it.
|
| Regards
| Peter
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:bCGcbYo3FHA.2984@.TK2MSFTNGXA01.phx.gbl...
| > Hello Peter,
| >
| > This seems to be a MDAC issue. I suggest that you install latest MDAC
2.8
| > SP1 if it is on Win2000 server.
| >
| > 899456 Release manifest for MDAC 2.8 Service Pack 1 (2.81.1117.6)
| > http://support.microsoft.com/?id=899456
| >
| > Also, please use SQL client network utility to make sure TCPIP and named
| > pipes are enabled. Also, you may want to create an Alais for SQL 2000
| > (subscriber) to test the situation.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > ================================================== ===
| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| > --
| >>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
| >>References: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
| > <exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
| >>Subject: Re: Replication Between Different Versions of SQL Server causes
| > access denied error.
| >>Date: Tue, 1 Nov 2005 10:09:26 +1000
| >>Lines: 117
| >>X-Priority: 3
| >>X-MSMail-Priority: Normal
| >>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| >>X-RFC2646: Format=Flowed; Original
| >>Message-ID: <uaV8Pin3FHA.700@.TK2MSFTNGP15.phx.gbl>
| >>Newsgroups: microsoft.public.sqlserver.replication
| >>NNTP-Posting-Host: 61.88.17.230
| >>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| >>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17507
| >>X-Tomcat-NG: microsoft.public.sqlserver.replication
| >>
| >>I have created the publication on SQL Server 7.0 server via SQL Server
| > 2000
| >>Enterprise and created a push subscription. As soon as the snapshoot was
| >>ready to send I get the same error "The process could not connect to
| >>Subscriber" (SQL Server does not exist or access denied. Error number:
17)
| >>even though I could setup the subscriber and select the destination
| >>database.
| >>
| >>Regards
| >>Peter
| >>
| >>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| >>news:exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl...
| >>> Hello Peter,
| >>>
| >>> This is expected behavior when trying to access SQL Server 2000 from
SQL
| >>> 7.0 version of SQL DMO. You will be able to connect to SQL Server 2000
| >>> from
| >>> SQL 7.0 version of Query Analyzer, but not Enterprise Manager. The
| >>> resolution is to upgrade the client tools to SQL 2000.
| >>>
| >>> Also, as you have mentioned, you could use a SQL 2000 Eneterprise
| >>> Manager
| >>> on a different server to work around this issue.
| >>>
| >>> Combination 2 you referred to does not indicate that you could
configure
| >>> this in SQL 7 Enterprise Manager. Since it is from SQL 2000 BOL,
| >>> actually
| >>> you could only configure this from a SQL 2000 Enterprise Manager.
| >>>
| >>> Hope this helps.
| >>>
| >>> Peter Yang
| >>> MCSE2000/2003, MCSA, MCDBA
| >>> Microsoft Online Partner Support
| >>>
| >>> When responding to posts, please "Reply to Group" via your newsreader
so
| >>> that others may learn and benefit from your issue.
| >>>
| >>> ================================================== ===
| >>>
| >>> Business-Critical Phone Support (BCPS) provides you with technical
phone
| >>> support at no charge during critical LAN outages or "business down"
| >>> situations. This benefit is available 24 hours a day, 7 days a week to
| > all
| >>> Microsoft technology partners in the United States and Canada.
| >>>
| >>> This and other support options are available here:
| >>>
| >>> BCPS:
| >>>
| >
https://partner.microsoft.com/US/tec...rview/40010469
| >>>
| >>> Others:
| > https://partner.microsoft.com/US/tec...pportoverview/
| >>>
| >>> If you are outside the United States, please visit our International
| >>> Support page:
| >>> http://support.microsoft.com/default...national.aspx.
| >>>
| >>> ================================================== ===
| >>>
| >>> This posting is provided "AS IS" with no warranties, and confers no
| >>> rights.
| >>>
| >>>
| >>> --
| >>>>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
| >>>>Subject: Replication Between Different Versions of SQL Server causes
| >>> access denied error.
| >>>>Date: Mon, 31 Oct 2005 16:30:49 +1000
| >>>>Lines: 32
| >>>>X-Priority: 3
| >>>>X-MSMail-Priority: Normal
| >>>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| >>>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| >>>>X-RFC2646: Format=Flowed; Original
| >>>>Message-ID: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
| >>>>Newsgroups: microsoft.public.sqlserver.replication
| >>>>NNTP-Posting-Host: 61.88.17.230
| >>>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| >>>>Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.replication:17479
| >>>>X-Tomcat-NG: microsoft.public.sqlserver.replication
| >>>>
| >>>>I am getting the following error:
| >>>>SQL Server does not exist or access denied. Error number 17.
| >>>>
| >>>>The publication and distributor is SQL Server 7.0 from internal lan.
| >>>>The subscriber is SQL Server 2000 from external wan outside of the
| >>> firewall.
| >>>>
| >>>>Using SQL Server Enterprise Manager 7.0 on the publication server will
| > not
| >>>>allow me to register the subscriber as it says "You must upgrade your
| >>>>SQL
| >>>>Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000(SQLDMO) to
| >>>>connecti to this server". But if I use a non participanting SQL Server
| >>>>Enterprise Manager 2000 within the internal lan it will allow me to
| >>> register
| >>>>and connect to the browser and I am able to view the database and its
| >>>>tables.
| >>>>
| >>>>In addition I can connect to the subscriber using Query Analyzer in
| >>> version
| >>>>7.0.
| >>>>
| >>>>According to the MSDN under the section Replication Between Different
| >>>>Versions of SQL Server, I fit the same criteria as Combination 2 for
| >>>>snapshot replication ie. Publisher SQL Server 7.0/Distributor SQL
Server
| >>>>7.0/Subscriber SQL Server 2000.
| >>>>
| >>>>So why does the push subscription fail?
| >>>>
| >>>>Regards
| >>>>Peter
| >>>>
| >>>>
| >>>>
| >>>>
| >>>>
| >>>>
| >>>>
| >>>
| >>
| >>
| >>
| >
|
|
|

Replication Automation...

My Scenarion is like this.

I have configured replication with a publication database, a distributor database and a few subscriber databases.
The replication model is snapshot + transactional replciation.

Now i want to check using system stored procedures as to whether the replication is configured and running properly.

What are the system stored procedures which can be used and how do use them??

Basically i wanted to automate the check of replciation. How can this be done?

--Sandu.Try MSdistribution_history table in he Distribution database. The "comments" and the "runstatus" columns will give you the status of your replication agents.|||Originally posted by pushistik
Try MSdistribution_history table in he Distribution database. The "comments" and the "runstatus" columns will give you the status of your replication agents.

Is there any other tables or system stored procedures which i can check to see the replciation details?

--Sandu.sql

Replication and Full Text Indexes.

SQL 2000 SP 3a running in an active passive cluster on Windows 2003.
This is the publisher and distributor of several publication in
transaction replication. Subscriptions are pulled anonymously. I plan
on setting up Full Text indexes/search on the cluster, on several of
the replicated tables, and was wondering if anyone has had any
experiences with this or can find any reason it would not work. Since
it's SQL 2000 and timestamp values can be replicated I can't see that
this would be an issue (as with SQL 7) as I will be using Change
Tracking to keep the indexes up to date.
I'm not necessarily concerned about replicating the full text indexes,
although it may be of use later. My major concern is can I replicate
the tables that have full text indexes with out issue? Also, since the
tables are already published can I just add the full text indexes
without worry?
Charlie
Hi Charlie,
Sorry for the late response.
[vbcol=seagreen]
Yes, you could make the replication for a Replicated database/table
successfully.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Replication 2000 - What if Publisher dead

What if a Publication and Distribution server machine fail to work. Do I need to reinitiliaze the whole snapshot on a new machine and retransmit the data to the subscriber. Or can I make use of replication script to recreate the publication and distribution and then replicate to existing subscriber using same machine name?

ThanksHow bad did it fail?|||Let say, if unable to load the SCSI hardisk, or the hardware failured.
Thanks

Originally posted by joejcheng
How bad did it fail?