Enumerating publications on a server via RMO

[Anthony Paul]

Hello everyone,

I'm looking to get a list of publications on a given SQL Server
instance via RMO. I can't seem to find any information on how to do
this. What I need is, given a server name or existing connection, find
out if the machine is a publisher and if so, get a list of its
publications. Currently I am able to query the publication and
articles, but I have to specify the publication/article name in
advance. I need the ability to discover them at runtime.

Any help or nudge in the right direction would be appreciated,

Regards,

Anthony

[Anthony Paul]

Hello everyone,

I was able to find something in the Microsoft.SqlServer.Replication
namespace that does exactly what I want; however, it comes with the
following disclaimer from Microsoft : "This class supports the SQL
Server 2005 infrastructure and is not intended to be used directly
from your code." This leads me to believe that there's another way of
enumerating publications on a SQL Server 2005 instance via RMO, but
for now I'll post what I found because... well, it works for me. The
following is sample code to iterate through all the publications on a
given SQL Server (2005?) instance and enumerate through the articles
within, outputing to the richtextbox rtbFilter :

**referencing :
[C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
\Microsoft.SqlServer.ConnectionInfo.dllusing
Microsoft.SqlServer.Management.Common]
[C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
\Microsoft.SqlServer.Rmo.dll]

using Microsoft.SqlServer.Replication;

...

ServerConnection conn = new ServerConnection("sql server name", "user
name", "user password");

try
{
conn.Connect();

ReplicationServer rs = new ReplicationServer(conn);



ArrayList al = rs.EnumLightPublications("name of the database being
replicated", 3, true, true); // 1 = trans, 2 = merge, 3 = all. I
*presume* these are correct (tested through quick trial and error,
didn't find the proper enum equivalent)

foreach (LightPublication lp in al)
{
rtbFilter.AppendText("Name:" + lp.Name + "\r\n");
rtbFilter.AppendText("Description:" + lp.Description + "\r\n");
rtbFilter.AppendText("Type:" + lp.Type.ToString() + "\r\n");
rtbFilter.AppendText("PublisherName:" + lp.PublisherName + "\r\n");
rtbFilter.AppendText("PublisherType:" + lp.PublisherType + "\r\n");
rtbFilter.AppendText("PublicationDBName:" + lp.PublicationDBName +
"\r\n");
rtbFilter.AppendText("\r\n");

TransPublication tp = new TransPublication(lp.Name,
lp.PublicationDBName, conn);
foreach (TransArticle ta in tp.EnumArticles())
{
rtbFilter.AppendText("\tName:" + ta.Name + "\r\n");
rtbFilter.AppendText("\tDescription:" + ta.Description + "\r\n");
rtbFilter.AppendText("\tType:" + ta.Type.ToString() + "\r\n");
rtbFilter.AppendText("\tFilterProcOwner:" + ta.FilterProcOwner + "\r
\n");
rtbFilter.AppendText("\tFilterProcName:" + ta.FilterProcName + "\r
\n");
rtbFilter.AppendText("\tFilterClause:" + ta.FilterClause + "\r\n");
}
}
}
catch(Exception ex)
{
Console.Out.WriteLine(ex.Message);
}
finally
{
conn.Disconnect();
}


Hope this helps,

Anthony

[E. Rie.]

Hi everyone,

I put together the following sample to iterate over existing publications. It does not use the infrastructure method EnumLighPublictions.


erie.

//"c:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Rmo.dll"
using RMO = Microsoft.SqlServer.Replication;
//"c:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll"
using SQL = Microsoft.SqlServer.Management.Common;
using SqlClient = System.Data.SqlClient;

SqlClient.SqlConnection sqlConn = new SqlClient.SqlConnection("your_connectionstring_here");
SQL.ServerConnection serverConnection = new SQL.ServerConnection(sqlConn);

try
{
serverConnection.Connect();

RMO.ReplicationServer replicationServer = new RMO.ReplicationServer(serverConnection);

//is the server a Publisher?
if (replicationServer.IsPublisher == true)
{
//iterate over all databases with an enabled replication
foreach (RMO.ReplicationDatabase replicationDatabase in replicationServer.ReplicationDatabases)
{
//iterate over all transactional publications in the current database
foreach (RMO.TransPublication transPublication in replicationDatabase.TransPublications)
{
//output the publication's name and its articles
Console.WriteLine("{0} ({1})", transPublication.Name, transPublication.DatabaseName);
foreach (RMO.TransArticle transArticle in transPublication.TransArticles)
{
Console.WriteLine("\t{0}", transArticle.Name);
}
}
}
}
}
finally
{
if (serverConnection != null)
{
serverConnection.Disconnect();
serverConnection = null;
}

if (sqlConn != null)
{
sqlConn.Close();
sqlConn.Dispose();
sqlConn = null;
}
}

Post by Anthony Paul
Hello everyone,
I'm looking to get a list of publications on a given SQL Server
instance via RMO. I can't seem to find any information on how to do
this. What I need is, given a server name or existing connection, find
out if the machine is a publisher and if so, get a list of its
publications. Currently I am able to query the publication and
articles, but I have to specify the publication/article name in
advance. I need the ability to discover them at runtime.
Any help or nudge in the right direction would be appreciated,
Regards,
Anthony

Post by Anthony Paul
Hello everyone,
I was able to find something in the Microsoft.SqlServer.Replication
namespace that does exactly what I want; however, it comes with the
following disclaimer from Microsoft : "This class supports the SQL
Server 2005 infrastructure and is not intended to be used directly
from your code." This leads me to believe that there's another way of
enumerating publications on a SQL Server 2005 instance via RMO, but
for now I'll post what I found because... well, it works for me. The
following is sample code to iterate through all the publications on a
given SQL Server (2005?) instance and enumerate through the articles
[C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
\Microsoft.SqlServer.ConnectionInfo.dllusing
Microsoft.SqlServer.Management.Common]
[C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies
\Microsoft.SqlServer.Rmo.dll]
using Microsoft.SqlServer.Replication;
...
ServerConnection conn = new ServerConnection("sql server name", "user
name", "user password");
try
{
conn.Connect();
ReplicationServer rs = new ReplicationServer(conn);
ArrayList al = rs.EnumLightPublications("name of the database being
replicated", 3, true, true); // 1 = trans, 2 = merge, 3 = all. I
*presume* these are correct (tested through quick trial and error,
didn't find the proper enum equivalent)
foreach (LightPublication lp in al)
{
rtbFilter.AppendText("Name:" + lp.Name + "\r\n");
rtbFilter.AppendText("Description:" + lp.Description + "\r\n");
rtbFilter.AppendText("Type:" + lp.Type.ToString() + "\r\n");
rtbFilter.AppendText("PublisherName:" + lp.PublisherName + "\r\n");
rtbFilter.AppendText("PublisherType:" + lp.PublisherType + "\r\n");
rtbFilter.AppendText("PublicationDBName:" + lp.PublicationDBName +
"\r\n");
rtbFilter.AppendText("\r\n");
TransPublication tp = new TransPublication(lp.Name,
lp.PublicationDBName, conn);
foreach (TransArticle ta in tp.EnumArticles())
{
rtbFilter.AppendText("\tName:" + ta.Name + "\r\n");
rtbFilter.AppendText("\tDescription:" + ta.Description + "\r\n");
rtbFilter.AppendText("\tType:" + ta.Type.ToString() + "\r\n");
rtbFilter.AppendText("\tFilterProcOwner:" + ta.FilterProcOwner + "\r
\n");
rtbFilter.AppendText("\tFilterProcName:" + ta.FilterProcName + "\r
\n");
rtbFilter.AppendText("\tFilterClause:" + ta.FilterClause + "\r\n");
}
}
}
catch(Exception ex)
{
Console.Out.WriteLine(ex.Message);
}
finally
{
conn.Disconnect();
}
Hope this helps,
Anthony

[Gene Wirchenko]

On Wed, 04 May 2011 20:22:26 GMT, E. Rie. <***@hotmail.com>
^^^^^^^^^^^

Post by E. Rie.
Hi everyone,
I put together the following sample to iterate over existing publications. It does not use the infrastructure method EnumLighPublictions.

[snip]
^^^^^^^^^^^^^^
[snip]

Just over four years. I think we have a new record!

Sincerely,

Gene Wirchenko