Windows Server

Marc Jennings · Guest

Hi there,

I am writing a web part that requires me to look up the content and
profile databases for the server that it will be installed on.

I am using

Wei-Dong XU [MSFT] · Guest

Hi Marc,

I'd suggest you can use the String.Split method to split the returned
string. The database name will be places the second entry of the
connection string.
//----------------------------------------------------------------------
using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Portal;
using Microsoft.SharePoint.Portal.Topology;

namespace ObtainDatabaseName
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
TopologyManager topology = new TopologyManager();
string con = topology.Databases[new
Guid("52aa2c60-757a-44fc-a750-c202767556cf")].SqlConnectionString;
string[] conStringPairs = con.Split(';');
char[] c = new char[]{'='};

//SQL server permits the database name containing '=', so I use the 2 as
the argument 2 to limit the returned string array size.
string[] databaseNamePair = conStringPairs[1].Split(c,2);
Console.WriteLine( databaseNamePair[1] );
}
}
}
//----------------------------------------------------------------------

Please feel free to let me know if you have any question.

Best Regards,
Wei-Dong XU (WD.XU)
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Marc Jennings · Guest

Thanks for the reply, but I seem to have missed something.

Where did you get you get the Guid for the database from? Also, is it
not easier to use

Wei-Dong XU [MSFT] · Guest

Hi Marc,

I know your requirement. When you pick up the database name, you still need
to know which database is the profile, which is stie and which is servers.

This information can be retrieved from the SPS configuration database (one
for each SPS installation with the default name sps01_config_db ). For
example if we use the sql command below to query the sps_config_db, we will
obtain the reseult similar to the content below.
---------------------------------------------------
use sps01_config_db
go

select a.VirtualServerId , a.Name
from Databases a , VirtualServers b
where a.VirtualServerId = b.VirtualServerId
go
---------------------------------------------------
-- query result at my box -------------------
VirtualServerId Name
0B5704E5-672F-4441-BC5A-4CF3A805A148 SPS31_PROF
0B5704E5-672F-4441-BC5A-4CF3A805A148 SPS31_SERV
0B5704E5-672F-4441-BC5A-4CF3A805A148 SPS31_SITE
ADDEDDB7-833B-443A-9631-C089C25E6120 STS_xman_86339470
AD479C1D-B545-4323-B94A-01DC08E078BE Xbox1_PROF
AD479C1D-B545-4323-B94A-01DC08E078BE Xbox1_SERV
AD479C1D-B545-4323-B94A-01DC08E078BE Xbox1_SITE
---------------------------------------------------

From the query result, we will obtain the database names for all the
SPS&WSS sites at this box. Since the Xbox1_PROF, Xbox1_SERV and Xbox1_SITE
are all the databases of one SPS portal site named XBOX (one virtual
server). They have the same VirtualServerId. SPS31_PROF etc is the same. So
we can obtain the dababase names of one portal by analysing the query
result.

I write one sample code for you to implment this, using Hashtable to store
the database name. The VirtualServerId is used as the key at the hashtable
and then use one ArrayList collection to store the database name. After the
processing, if the ArrayList count is less than 3, this database must be
WSS site; if the count is equal to 3, the arraylist contains the database
name for one portal site.

After obtianing the database name, we can check whether the database
contains one table which is only available at profile or site or server; so
that we can identify which role the database is, no mather what name they
are. You can use the SQL command below to query this.
-- at my database "XBox1_SERV" to query the table Sub_SiteInfo-----
-- if no such table in the database, return will be zero row
-- if got, 1 row returned
SELECT name
FROM sysobjects
WHERE (xtype = 'U') AND (name = 'sub_siteinfo')
----------------------------------------------------------------------
Another way, you can use the SQL DMO to check whether one specified SQL
database contains one specified table.
* in my sample code, I haven't implemented the two methods.

***
Please note: my suggestion introduces the method to identify the database
usage for the portal site; all operations are read-only. Please don't
manually/programmatically modify the data in the databases except from SPS
OM.
***

Since the web-based newsgroup reader is very convenient to use, I
copy-paste my sample code with comment at the end of this post so there is
no need for you to download one attachment from Outlook Express.

Please feel free to let me know if you have any question.

Best Regards,
Wei-Dong XU (WD.XU)
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
//--------------------------------------------------------------------------
--------
//
// console type application to obtain the database names
// of each SPS/WSS site in one SPS box
//
// then you can use the database name to identity its role
//
// please run this application at the SPS box with the SPS/System
// administrator account
//
// **note: sample code without the error handling code
//
// date: 2005-1-19
//
//--------------------------------------------------------------------------
--------
using System;
using System.Collections;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Portal;
using Microsoft.SharePoint.Portal.Topology;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace ObtainDatabaseName
{
class Class1
{
//global variable
private static Hashtable dbNameTable = null;

[STAThread]
static void Main(string[] args)
{
dbNameTable = new Hashtable();
ObtainDatabaseName();
}

static void ObtainDatabaseName()
{
TopologyManager topology = new TopologyManager();
ConfigurationDatabase cfgdb =
TopologyManager.DefaultConfigurationDatabase;
Console.WriteLine( cfgdb.SqlConnectionString );

string con = cfgdb.SqlConnectionString;
Console.WriteLine(
"The central admin DB name is: " + GetDataBaseName(con) );
CollectAllDBNames( con );
EnumerateDBNames( dbNameTable );
}

static string GetDataBaseName( string connectionString )
{
string con = connectionString;
string[] conStringPairs = con.Split(';');
char[] c = new char[]{'='};
string[] databaseNamePair = conStringPairs[1].Split(c,2);
return databaseNamePair[1];
}

static void CollectAllDBNames( string connectionString )
{
SqlConnection con = new SqlConnection(connectionString);
string cmdText = "select a.VirtualServerId , a.Name " +
"from Databases a , VirtualServers b " +
"where a.VirtualServerId = b.VirtualServerId";
SqlCommand sqlCommand = new SqlCommand(cmdText, con);
con.Open();
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(
CommandBehavior.CloseConnection);

while (sqlDataReader.Read())
{
Console.WriteLine(
sqlDataReader.GetGuid(0).ToString() + ", " +
sqlDataReader.GetString(1));
object virtualServerGUIDasKey = sqlDataReader.GetGuid(0);
object dbName = sqlDataReader.GetString(1);
if( dbNameTable.ContainsKey( virtualServerGUIDasKey ) )
{
//if the key exists, add the
//dbname into the ArrayList
ArrayList valueArray =
(ArrayList)dbNameTable[virtualServerGUIDasKey];
valueArray.Add( dbName );
}
else
{

//if in the HashTable, there is no such key;
//create one new ArrayList and assign the reference
//to the corresponding value to this key in hashtable
ArrayList arrayList = new ArrayList(3);
arrayList.Add( dbName );
dbNameTable.Add( virtualServerGUIDasKey, arrayList );
}
}
//Implicitly closes the connection because
CommandBehavior.CloseConnection was specified.
sqlDataReader.Close();
}

static void EnumerateDBNames(Hashtable hashTable )
{
if( hashTable == null )
return;

foreach( ICollection collection in hashTable.Values )
{
//if the ArrayList contains less than 3 database names
// it must be WSS site. go to the next one
if( collection.Count < 3 )
continue;

// enumerate the dbname of the portal site
foreach( object dbName in collection )
Console.WriteLine( dbName.ToString() );
Console.WriteLine( "---------------" );
}
}
}
}

	Windows Server Forum Index -> Portal Server Development	All times are GMT
Page 1 of 1