THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Summer SQL Teaser #6 Non Existing Database

Here is a simple teaser, BTW I assume you don't have a database named WasabiDb or do you?

USE WasabiDb
IF @@Error <> 0
PRINT 'db doesn''t exist'

USE WasabiDb
IF @@Error <> 0
PRINT 'db doesn''t exist'

If you would run this in one shot (hit F5) how many of the error messages below will you see?

Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'WasabiDb'. No entry found with that name. Make sure that the name is entered correctly.

And how many 'db doesn't exist' messages will you see
Published Friday, July 6, 2007 10:10 AM by Denis Gobo
Filed under:



Adam Machanic said:

Nice one!  This is a great demonstration of how errors are handled in batch termination scenarios.

July 6, 2007 10:01 AM

Anz said:

Thats great.. Only now I understood the use of "Go" keyword :)

July 9, 2007 12:33 AM

Old_DBA said:

Hmm, what is this supposed to show?

Yes, I get 1 'Could not locate...' message and I also get 1 'db doesn't exist' but which one is being printed?  Your script has the same message being printed in both places.  I amended it slightly to include a '1' and '2' so I could tell.

Now it proves what a complete hash error trapping in SQL Server is (certainly on 2k and below).

I say this because:

1. Even though the error occurred in batch 1, I can't trap it and take some action in that bacth, the batch is dead.

2. Bizarrely, it seems that the second batch IS able to detect the failure.

If every proof were needed that error checking in SQL Server is pants, this is it.

As always, I am willing to be proved wrong.

July 16, 2007 10:39 AM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement