Legacy OUTER JOINs (*=, =*)

Maybe you've seen the *= and =* LEFT and RIGHT OUTER JOIN operators floating around third party data access code or even (gasp!) Microsoft system generated code. I've never used them but, I have seen them lurking around, on occasion. Although these operators have been valid and supported in SQL Server, they are going away for SQL 2005.

For backwards compatibility, they will still work in databases with a compatibility level = 80 but, for databases with compatibility level = 90, they no longer work. Not that this is very exciting but, to play around with these JOIN operators, we'll need a couple tables:

CREATE TABLE tbl_tst1 (
tst1_pk int NOT NULL identity(1, 1),
tst1_data varchar(50));

CREATE TABLE tbl_tst2 (
tst2_pk int not null identity(1, 1),
tst1_pk int,
tst2_data varchar(50));

We'll also need to load them up with a bit of data:

INSERT tbl_tst1 VALUES ('tst1_data01');
INSERT tbl_tst1 VALUES ('tst1_data02');
INSERT tbl_tst1 VALUES ('tst1_data03');
INSERT tbl_tst1 VALUES ('tst1_data04');
INSERT tbl_tst1 VALUES ('tst1_data05');

INSERT tbl_tst2 VALUES (1, 'tst2_data01');
INSERT tbl_tst2 VALUES (1, 'tst2_data05');
INSERT tbl_tst2 VALUES (2, 'tst2_data01');
INSERT tbl_tst2 VALUES (2, 'tst2_data05');

Now, that we have a bit of useless data, we'll want to select all rows from tbl_tst1 and, any rows from tbl_tst2 that have tst2_data = 'tst2_data05' AND that match our rows from tbl_tst1:

SELECT * FROM tbl_tst1 AS one, tbl_tst2 AS two
WHERE one.tst1_pk *= two.tst1_pk
AND two.tst2_data = 'tst2_data05';

Assuming your compatibility level = 90, the first thing you'll probably notice when you run this query is that you get an error:

Msg 4147, Level 15, State 1, Line 28

The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Since that is highly inconvenient for our sample code (and for that 3^rd party monitoring application you just installed), we'll need to put our database into compatibility level 80:

EXEC dbo.sp_dbcmptlevel @dbname=N'db_name', @new_cmptlevel=80;

Now, we get the five rows from tst_tbl1 we were expecting. I was thinking of beating this dead horse with a =* RIGHT OUTER JOIN but, we all get the point. Let's get rid of the carcass:

DROP TABLE tbl_tst1;
DROP TABLE tbl_tst2;

And, since we don't wan't our 3^rd party monitoring tool cluttering up our shiny new SQL Server 2005 database with legacy JOINs, let's shut the door on them:

EXEC dbo.sp_dbcmptlevel @dbname=N'db_name', @new_cmptlevel=90;

Since these OUTER JOIN operators have been deprecated, you can count on them to not work in some future version of SQL Server, regardless of whether you have set your compatibility level below 90. Now, if we could only get rid of the legacy INNER JOIN syntax...

Database Mail

I have a confession to make. Beyond sending a couple of test emails, I've never used xp_sendmail to send email from my database servers. Because I was never comfortable with the idea of a MAPI client on the server, and hated the bother of setting it up, I've always used sp_OA* to send email via CDO (hold the derision, please).

The new mail procedure, sp_send_dbmail, however, works a bit differently. Although we no longer need a MAPI profile on the server, we still have a bit of setup to do. Database mail is disabled by default. The first thing we'll need to do is to enable it. Since it's an advanced option, we'll need to turn on advanced options, first:

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE WITH OVERRIDE;

Even though sp_send_dbmail isn't an extended procedure like its predecessor, it still uses extended procedures to handle the mail. This is a bit of a case of “do as we say, not as we do,” since extended procedures are deprecated and Microsoft says we should no longer use them for new development (email might make a good CLR procedure candidate, assuming you don't have to run UNSAFE to access the email server). At any rate, let's enable mail extended procedures:

EXEC sp_configure 'Database Mail XPs', '1';
RECONFIGURE WITH OVERRIDE;

Despite being liberated from the tyranny of the MAPI profile, we still need a mail profile in the database. Even though we have to create a profile, it doesn't require any extra software; it's all in the database and is a convenient way to specify our email account settings as the sender of a message. Books-Online was kind enough to provide a code sample:

EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Database Mail Profile',
@description = 'Profile with which to send database mail';

Of course, with any legitimate email message, you need an mail account from which to send it. We get to specify several properties for our account. The account name, email address, and email server are some of the more pertinent properties. Don't forget to set your mail server to allow your database server to relay mail:

EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'Database Mail Account',
@description = 'EMail account for sending database mail',
@email_address = 'mymail@domain',
@replyto_address = 'myemail@domain',
@display_name = 'Inbox Assassin',
@mailserver_name = 'smtp.mymailserver.domain';

Now it's getting a bit tedious. Just one more step and we can actually send an email message. I mentioned before that the profile was a convenient way to specify our email setting for a message. This next procedure associates an email account with a profile:

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Database Mail Profile',
@account_name = 'Database Mail Account',
@sequence_number = 1;

Assuming we specified a valid email account and smtp server, we should be all set to go. All we have to do to send an email now is to provide our profile, a recipient, a subject, and a message:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database Mail Profile'
, @recipients = 'myemail@domain'
, @subject = 'My first database mail'
, @body = 'Look, ma, No hands!';

Okay... that was fun. Now we have database mail set up on our server, what's next? Well, we could send out thousands of email messages to our close personal friends whose email addresses we store on the corporate database server, but, let's cleanup, instead! To remove our database mail settings, we pretty much just do our setup steps in revers. First we need to disassociate our email account from our profile:

EXEC msdb.dbo.sysmail_delete_profileaccount_sp
@profile_name = 'Database Mail Profile',
@account_name = 'Database Mail Account';

Now, we'll get rid of the email account:

EXEC msdb.dbo.sysmail_delete_account_sp
@account_name = 'Database Mail Account';

and get rid of our profile:

EXEC msdb.dbo.sysmail_delete_profile_sp
@profile_name = 'Database Mail Profile';

Finally, since we want to leave our server just the way we found it, we'll disable the mail extended procedures and turn off advanced options:

EXEC sp_configure 'Database Mail XPs', '0';
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'show advanced options', '0';
RECONFIGURE WITH OVERRIDE;

With a relatively straightforward setup, and no messing with MAPI or sp_OA*, I like database mail enough that I'll be using it for all of my database email messaging needs (all two of them); despite its reliance on extended procedures.