Using USE when the database doesn''t exist.

Question

Post reply

Using USE when the database doesn''t exist.

Joe Doherty-260822

SSC Eights!

Points: 915
More actions
October 17, 2006 at 2:43 pm

#170111

Hi,
I'd like to know how people out there handle the following:

IF EXISTS(SELECT * FROM MASTER..SYSDATABASES WHERE NAME = 'myTESTdb')
USE myTESTdb
ELSE
GOTO QuitWithRollback

... some more tsql ...

QuitWithRollback:
ROLLBACK TRANSACTION

When I run the code above it seems to fail on parsing saying that the myTESTdb doesn't exist. But that's the whole point of testing for it.

How do you get round this problem???

Thanks,

Joe

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Make your "... some more tsql ..." a SP in myTESTdb and run dynamic SQL:

EXEC('USE myTESTdb

EXEC dbo.SomeMoreTsqlProcedure ')

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 2

But I would do just this:

IF EXISTS(SELECT * FROM MASTER..SYSDATABASES WHERE NAME = 'myTESTdb')

EXEC ('exec myTESTdb.dbo.SomeMoreTsqlProcedure')

_____________
Code for TallyGenerator

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

This compiles and executes just fine on my server :

IF EXISTS (SELECT * FROM MASTER..SYSDATABASES WHERE NAME = 'FakeDB')

begin

EXEC FakeDB.dbo.SomeProc

end

John McC Hall of Fame Points: 3410 More actions · Answer 4

Try

IF EXISTS(SELECT * FROM MASTER..SYSDATABASES WHERE NAME = N'myTESTdb')

USE myTESTdb

ELSE

GOTO QuitWithRollback

BEGIN TRANSACTION

... some more tsql ...

QuitWithRollback:

ROLLBACK TRANSACTION

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

Joe Doherty-260822 SSC Eights! Points: 915 More actions · Answer 5

But what happens if myTESTdb doesn't exist? Parsing fails.

I am looking for a solution that jumps to the QuitWithRollback if the database doesn't exist.

Thanks for your suggestion John.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Can you please tell me why my solution is not acceptable for you needs? As far as I know you have a working solution and I can't offer any more help without more details of what you need to do.

Joe Doherty-260822 SSC Eights! Points: 915 More actions · Answer 7

Your suggestion above does work however it doesn't quite fit into what I am trying to do, so if you can help me further I would very much appreciate it.

Basically I have a number of scripts that need to be applied to all our new servers. Depending on the server function (determined by the instance name) the script will either run as intended or it will simply drop out - I achieve this with a simple IF statement.

My problem comes when I try to run one of my scripts on a server that the script isn't intended for - because the USE statement contains a database name that doesn't exist within the instance so the script fails on parsing. If the database does exist then the script does not fail.

I need to be able to run all of our scripts against any sql instance and for the script to correctly determine if it should run the main contents against the instance. You should also presume the sql instance has no user databases at the time of execution to cover this situation. And also presume the current database focus is MASTER so that a USE has to be initiated so that focus changes to the database we intend to make changes to.

I hope this makes sense. Basically it is a requirement for all our scripts at work. People have got round this in different ways but I would like to know if it is possible to do it with the USE statement as I have mentioned above.

Thanks for your interest and help.

Joe

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Have you considered this one :

For each server

exec sp_msforeachdb 'IF ''?'' IN ('Pubs', 'DBA', 'Production') BEGIN use ? exec ?.dbo.USPDemo END'