Publishing a Database Programmatically

Hi -
I'm trying to set up merge replication between a SQLCE database on a Windows
Mobile PDA and a SQL2K database on the desktop. I can do this using
Enterprise Manager, but I need to do this programmatically (in my VB.NET
program) instead. I'm using SQL-DMO, and I'm running into an error that
seems to be telling me that my database isn't published. Well, that's what
I'm trying to do, so the error message isn't all that helpful to me. I'm
probably missing a fairly simple step.
What do I need to do to get this to work? (My relevant code and the error
message follow)
Also, after I succeed with publication, how can I programmatically (again
from within my VB.NET program) run the snapshot?
Thanks for your help.
- Jeff
Error message:
{System.Runtime.InteropServices.COMException}
[System.Runtime.InteropServices.COMException]:
{System.Runtime.InteropServices.COMException}
HelpLink: "SQLDMO80.hlp#101700"
InnerException: Nothing
Message: "[Microsoft][ODBC SQL Server Driver][SQL Server]The 'DemoDB02a'
database is not published for merge replication."
Source: "Microsoft SQL-DMO (ODBC SQLState: 42000)"
Program Code:
Dim objSqlServer As New SQLDMO.SQLServer
Dim oReplDB As SQLDMO.ReplicationDatabase
Dim oMergePub As New MergePublication2
objSqlServer.Connect(strServer, strUserID, strPassword)
oReplDB =
objSqlServer.Replication.ReplicationDatabases.Item (strDBName)
oMergePub.Name = strDBName & "Pub"
oMergePub.SnapshotMethod =
SQLDMO_INITIALSYNC_TYPE.SQLDMOInitSync_BCPNative
oMergePub.SnapshotSchedule.FrequencyType =
SQLDMO_FREQUENCY_TYPE.SQLDMOFreq_Daily
oMergePub.AltSnapshotFolder = strSnapFldr
oMergePub.AllowSyncToAlternate = True
oReplDB.MergePublications.Add(oMergePub)
' The above line throws the 'not published' exception
' Additional code follows to add articles (program never gets that
far)
Jeff,
to programatically run the snapshot, the easiest way is to use the activeX
controls from the samples folder (custom install of SQL Server). You could
run snapshot.exe with parameters, but the activeX control packages up the
whole thing in a OO style, so is nice to program against.
As for the error you are receiving, I'll have a look at it tomorrow on my
dev machine at work.
HTH,
Paul Ibison
|||Thanks, Paul -
I'll look for the ActiveX control (I won't be able to use it until I get
past this error, though).
I await your input tomorrow.
- Jeff
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eB0IEaOSEHA.2704@.TK2MSFTNGP10.phx.gbl...
> Jeff,
> to programatically run the snapshot, the easiest way is to use the activeX
> controls from the samples folder (custom install of SQL Server). You could
> run snapshot.exe with parameters, but the activeX control packages up the
> whole thing in a OO style, so is nice to program against.
> As for the error you are receiving, I'll have a look at it tomorrow on my
> dev machine at work.
> HTH,
> Paul Ibison
>
|||Jeff,
one last thought for today - can you run
EXEC sp_dboption 'DemoDB02a', 'merge publish', 'TRUE'
and see if this solves it.
Regards,
Paul Ibison
|||I can report some progress -
First, running the recommended sp_dboption got me past the error that I had
been experiencing; thank you very much. (Is there a comparable SQL-DMO
approach? Or would you recommend that I do/don't use SQL-DMO?)
I then created a virtual directory using the SQL Server CE Virtual Directory
Creation wizard and tested it successfully using Pocket IE on the PDA. In
Enterprise Manager (EM) on the desktop, I ran the agent for the publication.
Back on the device, synchronization failed. I got an error 29045:
Initializing SQL Server Reconciler has failed. That error was followed by
an error message: 'Publication 'DemoDB02aPub' does not allow anonymous
subscriptions.'
In an effort to get around the problem, I went back into EM, and, on the
Subscription Options tab for the publication, I selected 'Allow pull
subscriptions' and 'Allow anonymous subscriptions.' Note that, when I
created the publication entirely using EM, these options were automatically
selected (I think as a result of my specifying that Subscriber Types should
include 'Devices running SQL Server CE'). I'll need to figure out how to do
that programmatically, but for now I just wanted to see whether that would
let me successfully synchronize.
I ran the agent and then tried to sync from the PDA. This time, I got the
same error 29045, but followed by the message 'The subscription to
publication 'DemoDB02aPub' is invalid.' I tried reinitializing all
subscriptions (followed by rerunning the agent) to see whether that would
have any effect, and it didn't: same problem.
What should I try/do next? (My PDA sync code follows.)
Thanks again for your help.
- Jeff
PDA Sync Code:
Dim oRepl As New SqlCeReplication
oRepl.Publisher = "DESKTOP4600\ENP"
oRepl.PublisherDatabase = "DemoDB02a"
oRepl.Publication = "DemoDB02aPub"
oRepl.PublisherLogin = "sa"
oRepl.PublisherPassword = "MainPwd"
oRepl.SubscriberConnectionString =
"Provider=Microsoft.SQLServer.OLEDB.CE.2.0; Data Source=\My Documents\Demo
Data.sdf;"
oRepl.Subscriber = SyncSubscriber "CEDevice"
oRepl.InternetUrl = "http://DESKTOP4600/SCPShrB/sscesa20.dll"
oRepl.InternetLogin = "DESKTOP4600\jeffg22a"
oRepl.InternetPassword = "password01"
oRepl.ExchangeType = ExchangeType.BiDirectional
oRepl.Synchronize()
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uBT6sAPSEHA.3056@.TK2MSFTNGP11.phx.gbl...
> Jeff,
> one last thought for today - can you run
> EXEC sp_dboption 'DemoDB02a', 'merge publish', 'TRUE'
> and see if this solves it.
> Regards,
> Paul Ibison
>
|||Jeff,
the DMO equivalent of sp_dboption is in this link: http://msdn.microsoft.com/library/de..._p_e_2gah.asp.
Unfortunately I can't really debug your case as it is now quite specific to SQL Server CE and I don't have access to one. However, my general advice would be to get replication going manually using EM. Script it out then remove it. Next, modify your DMO c
ode until you can achieve identical results by scripting.
HTH,
Paul Ibison
|||Thanks, Paul -
Strangely, the sp and the DMO stopped working. I need to work with it a bit
more to see whether I can narrow down the problem. If I can't, I'll post
the question here.
I've also posted the E-specific issue on the CE forum. I've been following
your general advice all along.
Thanks for your help.
- Jeff
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:EA837568-1769-4B57-AA7E-27B1A05ACC33@.microsoft.com...
> Jeff,
> the DMO equivalent of sp_dboption is in this link:
http://msdn.microsoft.com/library/de..._p_e_2gah.asp.
> Unfortunately I can't really debug your case as it is now quite specific
to SQL Server CE and I don't have access to one. However, my general advice
would be to get replication going manually using EM. Script it out then
remove it. Next, modify your DMO code until you can achieve identical
results by scripting.
> HTH,
> Paul Ibison
|||Hi Jeff,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!