August 12, 2004 at 2:42 pm
I'm a complete newbie when it comes to replication and my employer has asked that I replicate a smallish database from the head office onto all the user's laptops to allow offline access. The head office uses MS SQL Server 2000, the laptops have MSDE 2000. To further complicate matters the chosen solution is to publish a snapshot of this database with the settings to allow access via ftp, transfer the snapshot files to local offices (which has no database server of any kind), all as part of an overnight process ... then to transfer said files onto the laptops from relevant local office on an ad-hoc basis throughout the day. My part is to apply the snapshot to MSDE via anonymous subscriptions and from within a VB6 program. Here is the code I'm using:
Set mSQLDistribution = New SQLDistribution
With mSQLDistribution
'Set up the Publication
.Publisher = "TEST-ENTERPRISE"
.PublisherDatabase = "cws_intranet"
.Publication = "IntranetTest"
.PublisherSecurityMode = NT_AUTHENTICATION
'Set up the Distribution
.FileTransferType = FILETRANSFERFTP
.FTPAddress = "localhost"
.FTPPort = "21"
' .FTPLogin = "xxxx" '<-- default is anonymous
' .FTPPassword = "xxxx" '<-- default is <none>
'Set up the Subscription
.Subscriber = Environ("COMPUTERNAME")
.SubscriberDatabase = "cws_intranet"
.SubscriberDatasourceType = SQL_SERVER
.SubscriberSecurityMode = NT_AUTHENTICATION
.SubscriptionType = ANONYMOUS
.SynchronizationType = AUTOMATIC
'Synchronize the data
.Initialize
.Run
.Terminate
End With
I've set up an ftp server on the laptop, using localhost to point to itself, and this is where the snapshot files are. I've also set up an empty database under MSDE.
The first problem is that it seems to be looking for the snapshot files on the localhost ftp server of the publisher rather than the localhost ftp server of the laptop on which this code is running. The second problem is I'm getting some invalid column errors ... which makes me wonder if I've approached this all wrong.
As this is supposed to go live next week any help would be greatfully received.
Regards PLST
August 13, 2004 at 7:39 am
WOW PLST, you've stepped into a hornet's nest with replication. I know cause I'm in the middle of doing the same thing.
I must ask you; do you really have so much Incremental data changes that you need a full snap each time?
I am distributing my initial snap on a CD-ROM and then each time they connect, they just get the Merge incremental that is specific to them via very specific UDF driven filters. This really minimizes their time on line.
Just wondering.
Jim
August 13, 2004 at 9:01 am
Well its a very small database ... so data volume per subscriber is not an issue. Having ad-hoc replication sessions sometimes days apart in some cases, occasional schema changes, and a high degree of computer illiteracy on the part of the users are issues.
Actually, I've resolved the big problem: if you enter FTP properties in the publisher database it seems to override any FTP parameters you set programmatically at runtime! This is counter-intuitive.
Now my problem is: one stored procedure fails to load into the target database. I'm not running it, just trying to replicate it. I can open the downloaded file that contains it in notepad and cut-n-paste it manually into the target database without issue, but cannot automate its load.
As for jumping into a hornets nest on my first exposure to replication: what can I say? Its a new job, I'm in the initial 'probationary' period, and I'm a stress-bunny! 😮
Regards PLST
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply