Hi Group,
I have an SQL Server with a database called support for which I have setup a
merge replication subscription called "support".
On my laptop, I have a support program using MSDE locally to access an exact
copy of the data as the main SQL server has.
Whenenver I try to synchronise using the SQLDistribution activeX control, I
get the following message:
"Cannot create transactional subscription to merge publication "support".
The publication type should either be transactional(0) or snapshot(1) for
this operation."
The trouble is, how I see it, I have to used merged because several clients
would have disconnected data sources and any or all could effectively change
the data.
Any help on this appreciated.
Jim
I think your script is wrong. Could you post it 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
|||Hi Hilary,
Thanks for the reply. I figured out I actually needed to use the "merge"
activeX control and not the distribution one. My code is below but I am
still receiving this error:
"The merge process was unable to add the pull subscription from
'JBHOME':'Support:'Support'."
JBHOME being my SQL server, support being the database name and also the
publication name. I have tried various variations on the below code but to
no avail.
-- CODE: --
With SQLMerge
.Publisher = "JBHOME"
.PublisherDatabase = "Support"
.Publication = "Support"
.PublisherSecurityMode = NT_AUTHENTICATION
.Distributor = "JBHOME"
.DistributorSecurityMode = NT_AUTHENTICATION
.Subscriber = "(Local)"
.SubscriberDatabase = "Support"
.SubscriberDatasourceType = SQL_SERVER
.SubscriberSecurityMode = NT_AUTHENTICATION
.SubscriberLogin = "sa"
.SubscriberPassword = "mypassword"
.SubscriptionType = ANONYMOUS
.SynchronizationType = AUTOMATIC
.AddSubscription EXISTING_DATABASE, NONE
.Initialize
.Run
.Terminate
End With
-- END CODE --
Kind Regards
Jim
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1113405033.118118.312480@.g14g2000cwa.googlegr oups.com...
>I think your script is wrong. Could you post it 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
>
|||FYI,
What I am attempting to do is have a server with a database on it and be
able for a vb written client program to "merge" update the remote server.
The clients will use MSDE as their disconnected database, and all this will
work over the internet (hopefully!!!)
Jim
"Reapes" <reapesuk-@.hotmail.com> wrote in message
news:xvb7e.85688$Nr5.22780@.fe2.news.blueyonder.co. uk...
> Hi Hilary,
> Thanks for the reply. I figured out I actually needed to use the "merge"
> activeX control and not the distribution one. My code is below but I am
> still receiving this error:
> "The merge process was unable to add the pull subscription from
> 'JBHOME':'Support:'Support'."
> JBHOME being my SQL server, support being the database name and also the
> publication name. I have tried various variations on the below code but to
> no avail.
> -- CODE: --
> With SQLMerge
> .Publisher = "JBHOME"
> .PublisherDatabase = "Support"
> .Publication = "Support"
> .PublisherSecurityMode = NT_AUTHENTICATION
> .Distributor = "JBHOME"
> .DistributorSecurityMode = NT_AUTHENTICATION
> .Subscriber = "(Local)"
> .SubscriberDatabase = "Support"
> .SubscriberDatasourceType = SQL_SERVER
> .SubscriberSecurityMode = NT_AUTHENTICATION
> .SubscriberLogin = "sa"
> .SubscriberPassword = "mypassword"
> .SubscriptionType = ANONYMOUS
> .SynchronizationType = AUTOMATIC
> .AddSubscription EXISTING_DATABASE, NONE
>
> .Initialize
> .Run
> .Terminate
> End With
>
> -- END CODE --
> Kind Regards
> Jim
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:1113405033.118118.312480@.g14g2000cwa.googlegr oups.com...
>
Showing posts with label laptop. Show all posts
Showing posts with label laptop. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Replication error - schema
I am getting the error below trying the 1st merge synch to my laptop. The
laptop has MSDE2000 SP3 on it. I tried this synch right after I setup the
database as a publisher. This is the 1st subscriber I have tried to create.
Can anyone help? Thanks.
David
The schema script
'\\LIFEDEV02\d$\sqldata\MSSQL\ReplData\unc\LIFEDEV 02_MCFIData_MCFIData\20051129091321\vw_BillingDeta ilBalance_1465.sch'
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: DONAFAYE (Agent); Error number: 0)
------
Invalid object name 'dbo.vw_BillingPaid'.
(Source: DONAFAYE (Data source); Error number: 208)
------
I think that I read somewhere that all objects such as functions, triggers,
procs, etc.. have to be at the subscriber if there is a dependency AND if
they are not in your publication. If the referenced object is not in your
publication then you'll have to add it or manually create it at the
subscriber.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"David" wrote:
> I am getting the error below trying the 1st merge synch to my laptop. The
> laptop has MSDE2000 SP3 on it. I tried this synch right after I setup the
> database as a publisher. This is the 1st subscriber I have tried to create.
> Can anyone help? Thanks.
> David
> The schema script
> '\\LIFEDEV02\d$\sqldata\MSSQL\ReplData\unc\LIFEDEV 02_MCFIData_MCFIData\20051129091321\vw_BillingDeta ilBalance_1465.sch'
> could not be propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> Unable to replicate a view or function because the referenced objects or
> columns are not present on the Subscriber.
> (Source: DONAFAYE (Agent); Error number: 0)
> ------
> Invalid object name 'dbo.vw_BillingPaid'.
> (Source: DONAFAYE (Data source); Error number: 208)
> ------
>
>
|||David,
stored procedures use deferred name resolution, so you can get away with
this sort of thing, but in the case of views it doesn't work - you'll need
to have vw_BillingPaid on the subscriber to be able to initialize. You could
drop the subscription, XXX the view into the publication then readd the
subscription. You could alternatively add vw_BillingPaid manually, but this
would have to be done for any other subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I'm confused. This is the FIRST subscription I am trying to create. The
view that is referred to DOES exist on the Publication. I do not understand
why I am getting this error.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23fvafr09FHA.912@.TK2MSFTNGP11.phx.gbl...
> David,
> stored procedures use deferred name resolution, so you can get away with
> this sort of thing, but in the case of views it doesn't work - you'll need
> to have vw_BillingPaid on the subscriber to be able to initialize. You
> could drop the subscription, XXX the view into the publication then readd
> the subscription. You could alternatively add vw_BillingPaid manually, but
> this would have to be done for any other subscribers.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
I also looked at the articles being published and ALL views are selected. I
tried dropping and re-creating the subscription, but it did the same thing
at the exact same place. I once had a similar error problem with DTS on a
different database but never was able to resolve it. I also tried your
suggestion to add the view vw_BillingPaid to the subscriber. I did it with
DTS and exported the view and it went over fine. Then I ran the synch
again, but got the exact same error. This is very frustrating. Any ideas
on what else I can do to make this work? I have 7 laptops to setup as
anonymous subscribers, but can't even get the very 1st one to work. Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23fvafr09FHA.912@.TK2MSFTNGP11.phx.gbl...
> David,
> stored procedures use deferred name resolution, so you can get away with
> this sort of thing, but in the case of views it doesn't work - you'll need
> to have vw_BillingPaid on the subscriber to be able to initialize. You
> could drop the subscription, XXX the view into the publication then readd
> the subscription. You could alternatively add vw_BillingPaid manually, but
> this would have to be done for any other subscribers.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
I think your dependency information is corrupted. If you add the view to the
subscriber manually and meanwhile drop it from the publication it should be
ok. The current problem is that it is still in the publication and is
therefore being dropped.
Alternatively, run sp_depends on the view that errors to check that the
dependency is not acknowledged by SQL Server. If this shows no dependencies
then refresh the view and run it again to check the dependency is
recognised, and once this is the case, the snapshot article order should be
correct.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You were right. I dropped the pub and subscriber and dropped and re-created
the 2 views and checked their dependencies. Both now appear ok. Then I
tried publication and the create subscriber again and got error on a
different view. Same error. I will remove and recreate everything again!
Is there any way to check all views at once for corrupt dependencies? (I'm
guessing no). Also, any idea how this could happen to so many views?
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O8FdSaQ%23FHA.3340@.TK2MSFTNGP12.phx.gbl...
> David,
> I think your dependency information is corrupted. If you add the view to
> the subscriber manually and meanwhile drop it from the publication it
> should be ok. The current problem is that it is still in the publication
> and is therefore being dropped.
> Alternatively, run sp_depends on the view that errors to check that the
> dependency is not acknowledged by SQL Server. If this shows no
> dependencies then refresh the view and run it again to check the
> dependency is recognised, and once this is the case, the snapshot article
> order should be correct.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||David,
these links show the sort of thing we're talking about:
http://support.microsoft.com/?id=115333
http://support.microsoft.com/?id=201846
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Transactional\Snapshot replication in SQL2005 (the SQL2005 snapshot agent
specifically) is much more resilient to inaccurate\missing information in
sysdepends or sys.sql_dependencies. For Merge replication users, they have
the capability to specify a custom ordering for the articles in SQL2005 to
overcome these kinds of problems.
-Raymond
"David" <dlchase@.lifetimeinc.com> wrote in message
news:OTAXiyR%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> You were right. I dropped the pub and subscriber and dropped and
> re-created the 2 views and checked their dependencies. Both now appear
> ok. Then I tried publication and the create subscriber again and got
> error on a different view. Same error. I will remove and recreate
> everything again! Is there any way to check all views at once for corrupt
> dependencies? (I'm guessing no). Also, any idea how this could happen to
> so many views?
> David
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:O8FdSaQ%23FHA.3340@.TK2MSFTNGP12.phx.gbl...
>
laptop has MSDE2000 SP3 on it. I tried this synch right after I setup the
database as a publisher. This is the 1st subscriber I have tried to create.
Can anyone help? Thanks.
David
The schema script
'\\LIFEDEV02\d$\sqldata\MSSQL\ReplData\unc\LIFEDEV 02_MCFIData_MCFIData\20051129091321\vw_BillingDeta ilBalance_1465.sch'
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: DONAFAYE (Agent); Error number: 0)
------
Invalid object name 'dbo.vw_BillingPaid'.
(Source: DONAFAYE (Data source); Error number: 208)
------
I think that I read somewhere that all objects such as functions, triggers,
procs, etc.. have to be at the subscriber if there is a dependency AND if
they are not in your publication. If the referenced object is not in your
publication then you'll have to add it or manually create it at the
subscriber.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"David" wrote:
> I am getting the error below trying the 1st merge synch to my laptop. The
> laptop has MSDE2000 SP3 on it. I tried this synch right after I setup the
> database as a publisher. This is the 1st subscriber I have tried to create.
> Can anyone help? Thanks.
> David
> The schema script
> '\\LIFEDEV02\d$\sqldata\MSSQL\ReplData\unc\LIFEDEV 02_MCFIData_MCFIData\20051129091321\vw_BillingDeta ilBalance_1465.sch'
> could not be propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> Unable to replicate a view or function because the referenced objects or
> columns are not present on the Subscriber.
> (Source: DONAFAYE (Agent); Error number: 0)
> ------
> Invalid object name 'dbo.vw_BillingPaid'.
> (Source: DONAFAYE (Data source); Error number: 208)
> ------
>
>
|||David,
stored procedures use deferred name resolution, so you can get away with
this sort of thing, but in the case of views it doesn't work - you'll need
to have vw_BillingPaid on the subscriber to be able to initialize. You could
drop the subscription, XXX the view into the publication then readd the
subscription. You could alternatively add vw_BillingPaid manually, but this
would have to be done for any other subscribers.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I'm confused. This is the FIRST subscription I am trying to create. The
view that is referred to DOES exist on the Publication. I do not understand
why I am getting this error.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23fvafr09FHA.912@.TK2MSFTNGP11.phx.gbl...
> David,
> stored procedures use deferred name resolution, so you can get away with
> this sort of thing, but in the case of views it doesn't work - you'll need
> to have vw_BillingPaid on the subscriber to be able to initialize. You
> could drop the subscription, XXX the view into the publication then readd
> the subscription. You could alternatively add vw_BillingPaid manually, but
> this would have to be done for any other subscribers.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
I also looked at the articles being published and ALL views are selected. I
tried dropping and re-creating the subscription, but it did the same thing
at the exact same place. I once had a similar error problem with DTS on a
different database but never was able to resolve it. I also tried your
suggestion to add the view vw_BillingPaid to the subscriber. I did it with
DTS and exported the view and it went over fine. Then I ran the synch
again, but got the exact same error. This is very frustrating. Any ideas
on what else I can do to make this work? I have 7 laptops to setup as
anonymous subscribers, but can't even get the very 1st one to work. Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23fvafr09FHA.912@.TK2MSFTNGP11.phx.gbl...
> David,
> stored procedures use deferred name resolution, so you can get away with
> this sort of thing, but in the case of views it doesn't work - you'll need
> to have vw_BillingPaid on the subscriber to be able to initialize. You
> could drop the subscription, XXX the view into the publication then readd
> the subscription. You could alternatively add vw_BillingPaid manually, but
> this would have to be done for any other subscribers.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
I think your dependency information is corrupted. If you add the view to the
subscriber manually and meanwhile drop it from the publication it should be
ok. The current problem is that it is still in the publication and is
therefore being dropped.
Alternatively, run sp_depends on the view that errors to check that the
dependency is not acknowledged by SQL Server. If this shows no dependencies
then refresh the view and run it again to check the dependency is
recognised, and once this is the case, the snapshot article order should be
correct.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||You were right. I dropped the pub and subscriber and dropped and re-created
the 2 views and checked their dependencies. Both now appear ok. Then I
tried publication and the create subscriber again and got error on a
different view. Same error. I will remove and recreate everything again!
Is there any way to check all views at once for corrupt dependencies? (I'm
guessing no). Also, any idea how this could happen to so many views?
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O8FdSaQ%23FHA.3340@.TK2MSFTNGP12.phx.gbl...
> David,
> I think your dependency information is corrupted. If you add the view to
> the subscriber manually and meanwhile drop it from the publication it
> should be ok. The current problem is that it is still in the publication
> and is therefore being dropped.
> Alternatively, run sp_depends on the view that errors to check that the
> dependency is not acknowledged by SQL Server. If this shows no
> dependencies then refresh the view and run it again to check the
> dependency is recognised, and once this is the case, the snapshot article
> order should be correct.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||David,
these links show the sort of thing we're talking about:
http://support.microsoft.com/?id=115333
http://support.microsoft.com/?id=201846
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Transactional\Snapshot replication in SQL2005 (the SQL2005 snapshot agent
specifically) is much more resilient to inaccurate\missing information in
sysdepends or sys.sql_dependencies. For Merge replication users, they have
the capability to specify a custom ordering for the articles in SQL2005 to
overcome these kinds of problems.
-Raymond
"David" <dlchase@.lifetimeinc.com> wrote in message
news:OTAXiyR%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> You were right. I dropped the pub and subscriber and dropped and
> re-created the 2 views and checked their dependencies. Both now appear
> ok. Then I tried publication and the create subscriber again and got
> error on a different view. Same error. I will remove and recreate
> everything again! Is there any way to check all views at once for corrupt
> dependencies? (I'm guessing no). Also, any idea how this could happen to
> so many views?
> David
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:O8FdSaQ%23FHA.3340@.TK2MSFTNGP12.phx.gbl...
>
Wednesday, March 21, 2012
Replication between MSDE and SQL Server 2000
I got a corporate machine has SQL Server 2000 installed. And a laptop has
MSDE installed. Is this complicated to setup a 2 way replication in SQL
Server 2000 Enterprise Manager?
Not at all. The MSDE can't be set up as a transactional publisher, and most
people set up the MSDE as a subscriber because of the topology they're using
anyway. Apart from that it's pretty straightforward. Configuration of MSDE
on a separate bix when you can't connect to it using EM can be more
difficult though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use merge replication for bi-directional replication. Merge also works with
MSDE.
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
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23decwk1FGHA.376@.TK2MSFTNGP12.phx.gbl...
>I got a corporate machine has SQL Server 2000 installed. And a laptop has
> MSDE installed. Is this complicated to setup a 2 way replication in SQL
> Server 2000 Enterprise Manager?
>
|||Hi Hilary,
Could you tell me where can I get the detailed steps to set up the
replication process between the SQL Server 2000 and the laptop MSDE ?
As I am investigating the database to do this project, MSDE is an option,
and Firebird is another but I can only find some trial version of components
which have some limitations.
The reason why I need to do the replication is the sales persons bring the
laptop on site and then back to office hook up to the corporate sever
database and the replication can be done on demand or by schedule. So it
depends on if I can experiment that SQL Server 2000 and MSDE is a good
combination, the project will use this combination, otherwise, Firebird or
MySQL.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uFv4DO2FGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Use merge replication for bi-directional replication. Merge also works
with
> MSDE.
> --
> 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
> "Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23decwk1FGHA.376@.TK2MSFTNGP12.phx.gbl...
>
MSDE installed. Is this complicated to setup a 2 way replication in SQL
Server 2000 Enterprise Manager?
Not at all. The MSDE can't be set up as a transactional publisher, and most
people set up the MSDE as a subscriber because of the topology they're using
anyway. Apart from that it's pretty straightforward. Configuration of MSDE
on a separate bix when you can't connect to it using EM can be more
difficult though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use merge replication for bi-directional replication. Merge also works with
MSDE.
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
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:%23decwk1FGHA.376@.TK2MSFTNGP12.phx.gbl...
>I got a corporate machine has SQL Server 2000 installed. And a laptop has
> MSDE installed. Is this complicated to setup a 2 way replication in SQL
> Server 2000 Enterprise Manager?
>
|||Hi Hilary,
Could you tell me where can I get the detailed steps to set up the
replication process between the SQL Server 2000 and the laptop MSDE ?
As I am investigating the database to do this project, MSDE is an option,
and Firebird is another but I can only find some trial version of components
which have some limitations.
The reason why I need to do the replication is the sales persons bring the
laptop on site and then back to office hook up to the corporate sever
database and the replication can be done on demand or by schedule. So it
depends on if I can experiment that SQL Server 2000 and MSDE is a good
combination, the project will use this combination, otherwise, Firebird or
MySQL.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uFv4DO2FGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Use merge replication for bi-directional replication. Merge also works
with
> MSDE.
> --
> 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
> "Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
> news:%23decwk1FGHA.376@.TK2MSFTNGP12.phx.gbl...
>
Subscribe to:
Posts (Atom)