try/catch workaround for SQL Server 2000

In SQL Server 2005 Microsoft introduced try/catch functionality. It’s a necessity when you are working with resources that are outside the sql servers control. This might be linked databases where the connection and/or schema of the linked server and database might fall out or be of the wrong version so perfectly legal statements all of a sudden becomes illegal. With try/catch you are able to catch and handle those situations like notify an operator that a database on a remote server is not available.

I had this problem in SQL Server 2000 and I finally found a way to work around those batch-breaking errors and allow me to log that there was an error and continue with the batch.

Here is an example how errors break the batch. You will never see “Done!” printed as the select causes an error that breaks batch.

print ‘Invalid select that breaks batch (Done! will never be printer)’;
select * from atablethatdoesnotexist;
print ‘Done!’;

The workaround is to execute the statements that might fails as a separate batch and one way to do it is by using sp_executesql. This example does exactly the same as above but with a possibility to catch the error and without breaking the batch.

print ‘Invalid select that does not break the batch’;
declare @rc int;
declare @sql nvarchar(500);
set @sql = N‘select * from atablethatdoesnotexist;’;
exec @rc = sp_executesql @sql;
if @rc <> 0 print ‘An error occured but the batch continued!’;
print ‘Done!’;

This is just a workaround, if you can I would recommend to upgrade to SQL Server 2005 instead.

Subversion

After a few attempts I have managed to install Subversion and it looks to be really solid. I am using TortoiseSVN to access the repository.

What gave me trouble was the fact that Subversion 1.4 was not compatible with Apache 2.2 but 2.0. After installing 2.0 instead everything seemed to work but I managed to screw up the ACL for it so I got permissions errors. I first thought it to be because my firewall settings. Now I only miss Visual Studio integration.

Source control have saved me many times or let me know who fiddled with my code. Try to use some kind of source control. You will appreciate it when things gets messed up. Remember to back it up too!