Monday, January 14, 2008

Code Camp 2008.1 - t-Sql to CLR performance comparison testing and Cursor Avoidance Techniques

We had a great session at Code Camp. Thanks to Sebastian Meine who did most of the work for the session. I contributed by brainstorming with Sebastian, coding the CLR components of the project and walking through the code.
We concluded that there is no conclusion...CLR is faster for some things and T-SQL is faster for others. There are some very big limitations to the CLR integration in SQL server - the main one is the 8000 byte limit when serializing custom aggregates and the indifference to order in those functions.
We also played with some neat SQL-2005 enhancements like common table expressions and Cross apply which was new for some of the users.
I will try to get the code from Sebastian and post it here.

Thursday, January 10, 2008

Troubleshooting SQL server database mail

I am putting database mail on SQL server 2005 into use more and more these days with my consutling projects and it is making things much easier on me.

It is very easy to trigger emails based on transactions that happen on the database - like payments posted, account goes below a certain balance etc.

It is also nice to use some of the included views to see what is the status of your email messages.

If a message that I send did not reach the recipient I can check:
SELECT * FROM msdb.dbo.sysmail_faileditems
to look for that message.

Once I find it there I can can look at the mailitem_id in the msdb.dbo.sysmail_event_log view.

Happy mailing!