Gotcha: Host instance set up during BizTalk Server reinstallation

Recently we had an intance where one of VM's had crashed which has BizTalk server installed on it with its databases on SQL Server hosted on a different machine. We then reinstalled and set up BizTalk Server and associated components. Since the other machine which has SQL Server was not down, we could see that all our application configuration (like applications, bindings, hosts, host instances etc.)  remained intact. Although all host instances were listed,  status of all host instances was shown as "Status Unavailable" which is because physical windows process for each host instance is not there on the machine which is just re-configured. Fortunately, it is easy to get the processes back on the machine by doing the following,

1. Right click on host instance
2. Click on Configure and just enter password (account name is already populated)

 We can now see that respective windows processes get created and status is changed to Stopped.

Another important step needed is to install BizTalk application assemblies into GAC.

HTH.

SQL query for multi keyword search

I recently came across a scenario where user enters multiple search keywords separated by delimitor character and needs to retrieve all rows from SQL Server database when certain column contains at least one of the words in the given list. I thought, this is common scenario with search screens. Thus I am posting queries I used to do this, if anyone likes to use this approach.

User enters list of search words like, 'word1,word2,word3'.

Here is function that returns a table with list of words as single column record set,

CREATE FUNCTION SplitStrings

(@String varchar(MAX), @Delimiter char(1))
RETURNS @Results TABLE (Item varchar(200))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE varchar(8000)
SELECT @INDEX = 1
IF @String IS NULL RETURN
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Item) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END

Then use above function in your query as shown in the sample query below,
 
SELECT * FROM table1, SplitStrings(@SearchWords,',')
WHERE CHARINDEX(item,SearchColumn)>0
 
You could use RTRIM/LTRIM functions on item if you need to get rid of leading or trailing spaces.
 
PS: Although this does work to get what we need, this may not be the best way to do this. There could be some other better ways also to do the same. Please do look for other ways if this is not optimal solution for your needs. 
 
HTH.

Gotcha: BizTalk HTTP adapter on IIS 7.0 and above

Recently I needed to set up HTTP receive adapter(BizTalk 2009) on my Windows 7 development machine which has IIS 7.0. Source system is going to send a simple date value through query string parameter of HTTP site URL (like http://server/AppName/BTSHTTPReceive.dll?MyDate=datevalue). I followed instructions stated here on MSDN documentation and tested the set up by pointing to the site URL in a browser window(IE9). But I did not get the response as expected and observed some popup windows on my browser.

After closely looking at different steps explained in the link mentioned below, I realized that my request is really not POST, but GET . So I needed to input GET verb as well in step 8 of instructions for IIS 7.0 section. After making this change, I got the response that expected in the browser.

HTH.

Gotcha: Order of input parameter of BizTalk Map functoid

I often tend to oversee this and realize only after wasting few test cycles.

When a functoid has multiple input parameters and any input link other than last link is deleted and added, new link gets added as the last link instead of in its original position. This would adversely affect end result of functoids for which ordering is important (like Value Mapping, String Concat etc).

For eg. when we delete and add first input of value mapping functoid, then second parameter becomes first which may or may not result a boolean value and boolean expression becomes second parameter. Eventually, we will not get result as expected (if this is not realized soon enough, we may suspect end result of boolean expression and spend more time on tweaking it)

HTH.

Fix: "Error occurred while configuring the connection uri. Invalid URI: The hostname could not be parsed."

While generating schemas using WCF-SQL Adapter wizard, following error occurs if server name and instance name are not provided separately in Server Name and Instance Name fields under Config Adapter\Uri Properties tab,

"Error occurred while configuring the connection uri. Invalid URI: The hostname could not be parsed."

HTH.

Tip: To find last updated timestamp of SQL Server objects

Recently, I was having a need to quickly find the recently deployed stored procedure between two SQL Servers. But I found that, only Created date property is available when I right clicked and looked at the properties of Stored procedure. We can get this information by querying for modify_date column in Sys.Objects system view (or object specific views like Sys.Procedures/Sys.Tables/Sys.Views etc.)

HTH.

Tip: How to add shortcut/hot key to open Source Control explorer in Visual Studio

Although we currently have various ways to open Source Control Explorer in Vistual Studio IDE, I though it would be nice to have a shortcut or hot key to open Source Control Explorer (as we access it frequently for various needs while working in team environment). We can seet that, View->Other Windows-> Source Control Explorer does not have any shortcut key associated with it like other options do. But VS team has not left us without options to add/update these shortcut keys at our will. Here is how we can add our own shortcut key to open Source Control Explorer,

1. Navigate to Tools->Options->Environment->Keyboard
2. Type view.tfssource in "show commands containing" criteria and we can see respective command listed in the results list 
3. Place cursor in "Press shortcut keys" text box and type in the key that you like to use as shortcut (make sure that, you are providing a key which is not currently being used) and click on Assign and OK buttons.

Now we can directly open the Source control by hitting our custom shortcut key. We can follow similar steps to have our own custom shortcut keys for any feature that has an associated command.

HTH.