July 2007 Blog Posts
This question pops up a lot everywhere and it's a common business requirement and until SQL Server 2008 doesn't come out with it's MERGE statement that will do that in one go we're stuck with 2 ways of achieving this. The biggest problem with every update/insert (upsert for those who haven't heard the term yet) is locking.
Let me first say that I like Service Broker. I think it's a great addition to SQL Server.
I've explained a bit about it here.
But if you're not carefull you can get weird things happening to you without apparent reason.
So what happened?
Well first i've changed my receiver stored procedure a bit. This resulted in an error when i tried sending new messages.
And from then on all hell broke loose. My error table grew by the second with new rows all containing the same error message:
The service queue "TargetQueue" is currently disabled
CPU was peaking at 100% , I/O was going...
Every now and then i see a question pop up that asks what is the max this or that in sql server?
Yes, me too. :)
I was tagged with this by Denis who has liven (is that even a word?) up to his nick "The SQL Meance". Bad Dennins, bad!
As lame as this thing might be and it does reminds us of the dreaded chain letters (anyone remeber getting those by normal mail. you know the one where people acctually deliver it... by vans even...can you believe it???), it's quite a nice excercise when you think about it. It kind of clears your path career wise.
So I have to find a few things, huh? Here goes:
1. Spending more time looking at art works.
Why? Because paintings, besides...
My article about Service Broker fundamentals and simple practical use has been posted on SQLTeam.com. Go check it out.
Service Broker is a new feature in SQL Server 2005. It is an integrated part of the database engine and it provides queuing and reliable direct asynchronous messaging between SQL Server 2005 instances only. In the future this is planned to be extended to non-SQL Server instances. This article shows how to use Service Broker and triggers to capture data changes.
No it's not some ultra misterious indexing feature that will make your database fly like it's on steroids.
Hypothetical indexes and database objects in general are simply objects created by DTA (Database Tuning Advisor)
Their names look like this:
These 2 lists are worth having nearby just in case:
Breaking Changes to Database Engine Features in SQL Server 2005
- Describes the changes made to the Database Engine in Microsoft SQL Server 2005
that could cause applications based on earlier versions of SQL Server to break.
Behavior Changes to Database Engine Features in SQL Server 2005
- Describes the changes in behavior of some Database Engine features in Microsoft SQL Server 2005
from their behavior in earlier versions of SQL Server.
Tests are a pretty much a must in today's world of programming. There's even a whole Test Driven Development methodology that deals with this.
There comes a time when we have to test how our app interacts with the database.
Database mail is a completly rewritten mailing system in SQL Server 2005 built on top of the service broker.
This means that it runs asynchrounously. The mails are put in a queue and are sent from there.
However it's not present in SQL Server Express. I wonder why not because Express supports Service Broker just fine.
Well i've found a great blog post that explains how to "enable" Database Mail in SQL Server Express.
But it's in German so i'll explain in English how to get it to work :)
SQL Express holds all necessary stored procedures, service broker queues, etc... for proper mail handling.