I got an amusing call from a DBA the other day saying that the BizTalk SQL adapter was failing whenever he sent through a message that had an apostrophe in it. The message being sent to the SQL adapter contained;
Mum’s Apple Pie
The message had suspended in Biztalk with a failure to due a failed MSDTC transaction on the SQL port. The DBA had opened the message in the BizTalk Adminstrators Console Group Hub and then copied the message into his SQL analyzer to test the SQL query. He had noticed that the apostrophe was not escaped and rightly deduced that the SQL query would fail if it was not escaped. See http://www.kamath.com/codelibrary/cl003_apostrophe.asp for example.
I told him to calm down because this was not the source of his problem. I got him to start SQL profile and set up a filter to trace the SQL query coming from the faulting SQL adapter. Sure enough the trace showed that when BizTalk sends the same message to a SQL adapter it magically escapes the apostrophe and the SQL request now contains;
Mum”s Apple Pie
The point here is that if you are troubleshooting a SQL adapter failure sometimes the suspended message does contain the exact SQL request that was submitted. I think that you need to trace the SQL transaction to see what is really being submitted.
Once he submitted the SQL from the trace to his stored procedure he could see that the error was due to a change he had made and it was not the fault of the BizTalk server solution. To finish with I can’t resist giving this link to an amusing cartoon about misuse of the apostrophe ( http://www.angryflower.com/bobsqu.gif ). Maybe we need a cartoon about misuse of the apostrophe in SQL queries. 🙂