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

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

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

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.