Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Replication / November 2005

Tip: Looking for answers? Try searching our database.

Forcing SQL Express to synchronize

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
RSH - 29 Nov 2005 17:50 GMT
I have a situation where I have SQL Server 2005 as my Publisher/Distributor
and I have it setup to run Push Merge Replication.  The subscriber is an
instance of SQL Server Express.  Obviously SQL Server Express doesn't have
the means to force synchronization.  I do have it setup at the moment to run
Continiously but this will definitely be resource intensive running Merge
Replication on a 1000 or so databases...so I would like to be able to set it
up as Run on Demand and force the synchronization from the application.  I
have seen BOL but that RMO code is all in .Net, I am programming in
VB6...and furthermore it looks like I have to use a Pull Subscription in
order to code for RMO...is that correct?

How should I go about setting this up for optimum performance?

Thanks!
RSH
Hilary Cotter - 29 Nov 2005 19:27 GMT
I would advise you to use a pull subscription and use the activeX controls
(you can use the SQL 2005 activeX control with VB6 - use set
test=CreateObject("SQLDistribution.90.SQLDistribution")
), or use WSM for that.

You want to use a pull as it has a lower impact on the publisher and you can
run it from the subscriber which is especially important if your publisher
is offline from time to time.

For WSM go to Start, All Programs, Accessories and select Synchronize. Click
on MS SQL Server and select Properties to select your publication and set
subscription properties.

Signature

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

>I have a situation where I have SQL Server 2005 as my Publisher/Distributor
>and I have it setup to run Push Merge Replication.  The subscriber is an
[quoted text clipped - 11 lines]
> Thanks!
> RSH
RSH - 29 Nov 2005 21:59 GMT
Hilary,

Thanks for your reply.

Question 1:

I set up a Pull subscription scenerio and on the SQLServer Express instance
when I run WSM I get the following error:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber."
"The process could not read the file 'C:\Program Files...' due to OS Error
3"

That directory does not exist from the "unc\" part of the path so I am
assuming that a snapshot hasn't been created.  If  I am correct how do I
generate a snapshot in SQL Express?  Or is it created on the SQL Server
instance and copied over?

Question 2:

The VB Code below is my quick attempt at using the Active X control.  The
code generates the following error: "The subscription to publication
'TestSub1' has expired or does not exist" which is not right...it does in
fact exist as illustrated in my screenshot...what am I missing here?

Option Explicit
--------------------------------------------------------------
' Add Reference Microsoft SQL Distribution Control 8.0

Private mobjDistr As SQLDISTXLib.SQLDistribution

Private Sub main()
Set mobjDistr = New SQLDISTXLib.SQLDistribution

On Error GoTo Errhandler

With mobjDistr
   'Set up the Publisher
   .Publisher = "VIRTUAL1"
   .PublisherDatabase = "00010101"
   .Publication = "TPTestSub1"
   .PublisherSecurityMode = NT_AUTHENTICATION

   'Set up the Subscriber.
   .Subscriber = "PARENTVM\TPSVCCTR01"
   .SubscriberDatabase = "00010101"
   .SubscriberDatasourceType = SQL_SERVER
   .SubscriberSecurityMode = DB_AUTHENTICATION
   .SubscriberLogin = "V1"
   .SubscriberPassword = "pw"

   'Set up the Subscription.
   .SubscriptionType = PULL
   .SynchronizationType = AUTOMATIC

   'Synchronize the data.
   .Initialize
   .Run
   .Terminate

   MsgBox "Complete"

End With

Exit Sub

Errhandler:
MsgBox Error

End Sub
--------------------------------------------------------------

Thanks a ton for your help!
RSH

>I would advise you to use a pull subscription and use the activeX controls
>(you can use the SQL 2005 activeX control with VB6 - use set
[quoted text clipped - 25 lines]
>> Thanks!
>> RSH
Hilary Cotter - 30 Nov 2005 02:49 GMT
This almost always means a permissions problem. From what it looks like you
are connecting from your subscriber to c:\Program files\... on your
publisher, or at least it should be your publisher. Make sure when you
configured your distributor that your snapshot location was defined as a
share. If not you can go back and fix it by right clicking on your
replication folder and selecting distributor properties.Select the
Publishers node, and for your publisher select the browse button to the
right of your publisher and in the default snapshot folder make it a share.

For your second question, the reason you most frequently get this message is
because the snapshot has not being generated for this publication.

Signature

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

> Hilary,
>
[quoted text clipped - 100 lines]
>>> Thanks!
>>> RSH
RSH - 30 Nov 2005 13:22 GMT
Thanks.

How do I generate the snapshot?

> This almost always means a permissions problem. From what it looks like
> you are connecting from your subscriber to c:\Program files\... on your
[quoted text clipped - 113 lines]
>>>> Thanks!
>>>> RSH
RSH - 30 Nov 2005 18:37 GMT
Hillary,

I set the Snapshot folder up as a Share as you described yesterday.  I am no
longer getting the error about:
"The Schema script 'Accrual_2.sch' could not be propogated to the
Subscriber." and I see the files being setup in the share.

I am still getting the pesky error: "The subscription to publication
'TestSub1' has expired or does not exist"

I assume that as part of the setup that somewhere the subscriber is aware of
where to look for the snapshot...I did not see any properties for this when
setting up the subscriber.  Also something that might be a bit odd is that I
am generating the snapshot from the Publisher side...is this correct?  Or
should I somehow generate it from the subscriber (if so how?) and if that is
the case how do I make the Publisher aware of the fact that it was created?

Thanks alot for your time!
Ron

> Thanks.
>
[quoted text clipped - 117 lines]
>>>>> Thanks!
>>>>> RSH
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.