Live Situation #4: Explore long path folder with just one click

Working either with Biztalk or Mirth, you are required to remember the path for dropping a file to receive port(location) or viewing the output file to send port. You get frustrated every time to browse such a long path.
This utility will allow you to browse such folders using just single click.

Here is the damn shortcut using DOS command in a batch file.
START” command runs the service into a separate window.
In the code below, define all your receive folders under Option sections dedicating them to specific numbers.

Save this file in your desktop using .bat extension. Just double click on the batch file. Rest will guide you automatically.


echo.================== Menu ==================
echo. 1. App1 DropBox  2. App2 DropBox  0. Clear Screen
set myChoice=
echo.&set /p myChoice=Enter a choice number or press ENTER to quit: || GOTO:EOF

:Option_1   Open App1 DropBox
START C://MyApp1/

:Option_2   Open App2 DropBox
START C://MyApp2/

:Option_0   Clear Screen

Live Situation #1 : Using a metachar in SQL query

Problem – Fetching the metachar from a database table in MSSQL Server
Scenario – In one of the Biztalk project closely executed by my friend had one issue in generating and processing an EDI file.

1. Extract member information data from SQL Server via SQLAdapter in Biztalk
2. Have some stored procedure calls
3. Create EDI and sends it to the send port

There were 18000 records in sql table and is expected to have an EDI of 18000 claims. Entire process was moving smoothly so far.
Suddenly Biztalk threw an error and it was difficult to predict what exactly the error is”.

He made a small change in SP to fetch only 100 records and run the entire process. He succeeded into it and could generate a proper EDI.
He changed the SP to 1000 then 2000, 3000, 4000. Worked well. But it failed in case of SP fetching 5000 records.

My dear friend suspected that there is a data issue between 4000 to 5000. But what exactly? After little normalization we felt it must be an issue of some wild chars (., *. # as they may appear in member address field).

So how can we identify them?

We planned to put a plan SELECT query as below
SELECT top 9000 address
FROM dataMembers
where CHARINDEX(‘*’, address) > 0 //incorrect

This gives you a syntax error. We modified it by putting an escape char
SELECT top 9000 address
FROM dataMembers
where CHARINDEX(‘\*’, address) > 0 // incorrect

Modified to – escape the \ char as well
SELECT top 9000 address
FROM dataMembers
where CHARINDEX(‘\\*’, address) > 0 // incorrect

Then finally we stored the ASCII value of it in a variable and run it as below

Declare @findMetaChar nvarchar(5);
Set @findMetaChar = char(42)
SELECT top 9000 address FROM dataMembers
where CHARINDEX(@findMetaChar, address) > 0

This listed us 3 rows and observered as there were * chars in address field.
And in Biztalk, * we took as segment element separator. Hence this caused Biztalk to stop the processing.