Sometimes, you might need to get the SQL Server Name of the SQL Server where the BizTalkMgmtDB

The C# code below, uses the registry to get that server name, and also builds a connection screen to some database on that server (where you pass the Database Name as a parameter). MgmtDBServer (in the rk.GetValue below), referes to the BizTalkMgtmDB.

        public static string DynamicSQLConnectionStringOLEDB(string DBName)
        {
            try
            {
                Microsoft.Win32.RegistryKey rk = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\Microsoft\BizTalk Server\3.0\Administration", false);
                string ConnString = 
                  "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + 
                  DBName + ";Data Source=" + rk.GetValue("MgmtDBServer").ToString();
                return ConnString;
            }
            catch
            {
                return "";
            }
        }

Here’s one example how this could be used. Suppose you have a database that contains lookup tables, and that database is on the same SQL server as the BizTalkMgmtDB database. The Scripting Functoid below calls the C# to get the connection string, and becomes the second parameter to the Database Lookup Functoid.

The scripting functoid is setup as shown in the picture below. First, make sure you reference the .net assembly created from the C# code above. Then select that in “Script Assembly”, and pick the class in “Script Class”.

Related blogs:

Keith Lim

MSDN Restoring the BizTalk Configuration Database

Xref Database Routines