Accessing publication properties from Visual Basic (VB6)

[Gijs Beukenoot]

Hi all,

I have an existing application that checks a few things before it
launches another application. One thing that the application needs to
check is the subscription properties of both the local database and the
remote (LAN) server.

I've stumbled upon some properties but I am trying to fit in the last
ones and I can't seem to find the right code to accomplish this... Can
anyone shine a little light on this please ?

The following code queries the SQL Server (remote) to get (some of) the
properties of the replicated database. What I would like to fit in
after the 'state' are the properties that you can retrieve with the
EnumAllSubscrip tions function. However, I can't seem to find the right
object (sample code) to retrieve those (or I'm just overlooking the
obvious)...

Here is (part of) the code I'm using to query the publication on the
remote server :

Dim oMergePublicati on As SQLDMO.MergePub lication2
Dim oSubscription As SQLDMO.MergeSub scription2

Me.lstPublicati ons.Clear
For Each oReplicationDat abase In
objSQLSERVER.Re plication.Repli cationDatabases
If oReplicationDat abase.Name = Me.txtSQLServer Database.Text
Then
For Each oMergePublicati on In
oReplicationDat abase.MergePubl ications
bFoundReplicate d = True
Me.lstPublicati ons.AddItem "Database replicated as
'" & oMergePublicati on.Name & "'"
For Each oSubscription In
oMergePublicati on.MergeSubscri ptions
Me.lstPublicati ons.AddItem " - subscriber
:" & oSubscription.S ubscriber
Me.lstPublicati ons.AddItem " - local
DB :" & oSubscription.S ubscriptionDB
Me.lstPublicati ons.AddItem " -
state :" & GetReplStatus(o Subscription.St atus)
Next
Next
'For Each osubscriber In oReplicationDat abase.
'Next
End If
Next
Set oReplicationDat abase = Nothing
Set oMergePublicati on = Nothing

After this, I also want to check the local DB (=pulled subscription)
and show some of the properties (from EnumAllSubscrip tions). For
example to warn the user about the need to synchronise (last_updated
value)...

Any help appreciated,

Many thanks

GB

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>

[Simon Hayes]

I'm not sure what you're asking - do you mean that you know that
EnumAllScubscri ptions will return the information you need, but you
don't know how to manipulate a SQL-DMO QueryResults object? If so, then
you need to iterate over it, and use the GetColumn% methods to return
the data in the format you want - in this case, GetColumnString is
probably the best one to use.

This is a simple VBScript example of using a QueryResults object:

Option Explicit

Dim oSrv, res, c, r

Set oSrv = WScript.CreateO bject("SQLDMO.S qlServer2")
oSrv.LoginSecur e = True
oSrv.Name = "MyServer"
oSrv.Connect

Set res = oSrv.ExecuteWit hResults("selec t * from sysobjects")

For r = 1 To res.Rows
For c = 1 To res.Columns
If res.ColumnName( c) = "name" Then
WScript.Echo res.GetColumnSt ring(r, c)
End If
Next
Next
If this isn't helpful, you might get a better answer in
microsoft.publi c.sqlserver.rep lication.

Simon

[Gijs Beukenoot]

Sort of Simon,

I'm having difficulty to get the returnset from EnumAllSubscrip tions in
a workable object. It just returns nothing or won't compile.
In the code I've added, I managed to get the right call for the
EnumAllSubscrip tions but I'm not sure on the syntax it requires. Like :

Dim oSubscription as SQLDMO.Subscrib er2
Dim oSubscriptionDa ta as SQLDMO.<??>
For each oSubscriptionda ta in
oSubscription.E numAllSubscribe rs(SQLDMOSubscr iption_All)

OR do I need to make something like
Dim Result
Dim oSubscription as SQLDMO.Subscrib er2
Result = oSubscription.E numAllSubscribe rs(SQLDMOSubscr iption_All)
and then walk that Result for the correct data

Thanks anyway. I'll give this a try in the .repication group.

GB

Simon Hayes bracht volgend idée uit :

I'm not sure what you're asking - do you mean that you know that
EnumAllScubscri ptions will return the information you need, but you
don't know how to manipulate a SQL-DMO QueryResults object? If so, then
you need to iterate over it, and use the GetColumn% methods to return
the data in the format you want - in this case, GetColumnString is
probably the best one to use.

This is a simple VBScript example of using a QueryResults object:

Option Explicit

Dim oSrv, res, c, r

Set oSrv = WScript.CreateO bject("SQLDMO.S qlServer2")
oSrv.LoginSecur e = True
oSrv.Name = "MyServer"
oSrv.Connect

Set res = oSrv.ExecuteWit hResults("selec t * from sysobjects")

For r = 1 To res.Rows
For c = 1 To res.Columns
If res.ColumnName( c) = "name" Then
WScript.Echo res.GetColumnSt ring(r, c)
End If
Next
Next
If this isn't helpful, you might get a better answer in
microsoft.publi c.sqlserver.rep lication.

Simon

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap>