I’m always running into situations where special characters need to be ignored when my applications communicate with SQL Server. I have library functions that I’ve written that parse out special characters, whether it be a specific set that are unique to the application, or simply all non-alphanumeric. While the functions work for simple cases, I often run into scenarios where I need to manage these filters at the View level. Having even a single function within your View can affect the performance of your queries. Instead, I leave the base View alone, and do the formatting locally within the stored procedure.

I came across an elegant solution, putting character codes into a static table (Utility.CharCode in this case) within your database. Then, you can update your variable or field with a simple UPDATE statement:

UPDATE Utility.CharCode
SET @txt_value = REPLACE(@txt_value, CHAR(CharCode), ”)

However, the two-letter combinations “oe” and “ae” are translated to specific character codes. Codes 140, 156, 198, and 230 must be eliminated from your table. Œ, and æ are ligatures in the Latin alphabet. Depending on your collation, SQL will translate the character code for these ligatures to the English letter equivalents, stripping any occurrences from your string.

Posted in 200 Words, SQL Server 2005 No Comments »

I came across an issue with SQL Database Mail in SQL Server 2005 today where an existing mail profile was not delivering messages. The error message returned was “profile name is not valid.”

If you have a Database Mail profile setup, and you execute a successful test using the “Send Test Email” function within SQL Server Management Studio, you will need to check for the relationship between the profile and the msdb database principal(s).

To check for the relationship, you’ll need to run the sysmail_help_principalprofile_sp stored procedure:

EXECUTE msdb.dbo.sysmail_help_principalprofile_sp
@principal_name = 'danw',
@profile_name = 'AdventureWorks Administrator' ;

If the results are empty, then the principal-to-profile default relationship does not exist. In order to correct this, execute the sysmail_add_principalprofile_sp stored procedure:

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@principal_name = 'ApplicationUser',
@profile_name = 'AdventureWorks Administrator Profile',
@is_default = 1 ;

This will add an entry for your database principal, and create a default SQL Database Mail profile on its behalf.

Posted in SQL Server 2005 2 Comments »