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!

Publication changes not appearing at subscriber?

I have setup a merge replication publication (SQL server 2005 wth SQL Mobile) with dynamic filters based on host name and when i sync to the pda all data appears fine. When I make changes on the PDA and synchronise, the changes are pushed back to the server fine.

However when I change data on the server and synchronise at the PDA, no changes ever appear!

Surely I must have missed something obvious or done something really stupid.

If i check the replication monitor it just says no changes so any suggestions would be great.

Thanks,

Andy

Ok, Ive managed to prove that if there is no @.subset_filterclause specified, it works fine. As soon as the filter is turned on i cannot add any new rows.

The filter is quite complex and as far as I can see, it will always fail because of the order in which records are added to the database. Can anybody clarify?

I have an organisation table and account management tables. The filter says download organisations that you are the account manager of. THerefore the organisation record is saved before the account management due to dependencies and at the point of creating the org, the trigger will fail because the account management record will not exist.

Surely this must be a standard problem so what am i doing wrong?

|||

After using one of our gold partner support calls, Microsoft have diagnosed the problem as my use of precomputed publications. This option in 2005 means that each insert fires a trigger which in turn evaluates the filter conditions.

In my case when i added a new organisation, it checked if the organisation was inside the users account management roles. This fails because the account management row has not yet been inserted (and cannot be added until after the org due to FK's.

The simple solution was to turn off precomputed partitions which sorted the problem.

Apparently it should be possible to use precomputed partitions if I define a row filter based on the last row to be inserted with all other rows linked through join filters. However, the suggested filters didnt work so I will try a bit more when i get an hour spare (in 2027 )

Publication changes not appearing at subscriber?

I have setup a merge replication publication (SQL server 2005 wth SQL Mobile) with dynamic filters based on host name and when i sync to the pda all data appears fine. When I make changes on the PDA and synchronise, the changes are pushed back to the server fine.

However when I change data on the server and synchronise at the PDA, no changes ever appear!

Surely I must have missed something obvious or done something really stupid.

If i check the replication monitor it just says no changes so any suggestions would be great.

Thanks,

Andy

Ok, Ive managed to prove that if there is no @.subset_filterclause specified, it works fine. As soon as the filter is turned on i cannot add any new rows.

The filter is quite complex and as far as I can see, it will always fail because of the order in which records are added to the database. Can anybody clarify?

I have an organisation table and account management tables. The filter says download organisations that you are the account manager of. THerefore the organisation record is saved before the account management due to dependencies and at the point of creating the org, the trigger will fail because the account management record will not exist.

Surely this must be a standard problem so what am i doing wrong?

|||

After using one of our gold partner support calls, Microsoft have diagnosed the problem as my use of precomputed publications. This option in 2005 means that each insert fires a trigger which in turn evaluates the filter conditions.

In my case when i added a new organisation, it checked if the organisation was inside the users account management roles. This fails because the account management row has not yet been inserted (and cannot be added until after the org due to FK's.

The simple solution was to turn off precomputed partitions which sorted the problem.

Apparently it should be possible to use precomputed partitions if I define a row filter based on the last row to be inserted with all other rows linked through join filters. However, the suggested filters didnt work so I will try a bit more when i get an hour spare (in 2027 )