SQL Server 2005 Service Broker

So we've started doing some enterprise application integration and it was decided that we will use the Service Broker platform built into SQL Server 2005. Here is a good primer on the subject. Microsoft has continued with Service Broker in SQL Server 2008 as well.

Locating Special Characters in T-SQL

I ran into a situation recently where we were performing a data load from a SQL Server database to a third-party application and the load failed because of CrLf (carriage-return + line-feed) and Lf characters in large string field. The solution was to strip out the characters (and in this case replace them with HTML markup). Here's what I used to find the characters in the source database...

SELECT COUNT(*)
FROM
[TableName]
WHERE
[FieldName] LIKE '%' + char(13) + char(10) + '%'

Char(13) is a carriage-return and char(10) is a line-feed.

To swap the characters for HTML tags I just used the SQL Server Replace function...

REPLACE(@CharacterString, char(13) + char(10),'<BR/>')