How stop executing batches in Query Analyzer ??

Question

How stop executing batches in Query Analyzer ??

Sabir Khan

Valued Member

Points: 71
More actions
May 17, 2006 at 7:34 am

#168197

Hi All,
I believe, the best of the best of SQL communities are available on this forum. I want to ask you all who are dazzlingly skilled in SQL if there is no remedy of below problem. Is this simply not achievable ???
My kindest regards to all of you:
-Aarez
Hi folks,
Before telling you my requirement, pls try to understand below situation:
1) I open Query Analyzer and connect to my SQL Server (I have SQL 2000) using system admnistrator login id.
2) I load a script file which contains several batches of CREATE, DROP, UPDATE, INSERT statements.
3) I execute it.
It is very obvious that if script file is correct then it will execuate and create / update objects in "Master" db. Here comes my requirement/problem. Since, I did not notice the selected db and change it to desired one so some objects will be created in Master db. Now, I need a solution which should check if selected db is "Master" then none of the batches should be executed.
I have tried a solution by adding a batch on top in which first I checked DB_NAME() and if it was "Master" then tried several things to stop executing rest of the batches. But, none of them worked.
I have been working in SQL for last 7 many years but never felt short of solution(s) to any problem / requirement. Now, my reputation is at stake so pls help me fast !!
[Aarez]

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

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

You can check for the DB_ID (refer to the BOL) and that might help.

BUT why don't you just write your code to include the proper database? There are two ways to do it (I prefer the second).

USE myDB

SELECT *

FROM myTable

or

SELECT *

FROM myDB..myTable

-SQLBill

Marshall Smith SSCrazy Points: 2041 More actions · Answer 2

EDIT: SQLBill trumped me. Sorry for the double post.

Actually, check out the USE keyword. That tells SQL Server which database to apply the changes to.

E.g.,

USE pubs

SELECT *

FROM authors

All you have to is put that USE statement at the top of your script.

Sabir Khan Valued Member Points: 71 More actions · Answer 3

Hi SQLBill, Smith,

Thanks for your suggestion.

Yes, I can use USE statement. But, let me tell you that this is a very silly requirement of our client. She does not want to do this. Reason: She says that she has to run same script on 4-5 datatbases and she wants to stop exeution only if "Master" gets selected by any chance.

I tried to solve this problem by adding below code on top:

IF DB_NAME() = 'Master'

BEGIN

PRINT '**************************************************************************************'

PRINT '************** Master db is selected. Please select correct database *****************'

PRINT '**************************************************************************************'

RAISERROR('Cannot create/update objects', 22, 127) WITH LOG

END

If you see, I am raising error with severity level 22 and if you assing severity level above 18, then sql connection will be broken. It was a good idea but then there are some limitations if use severity above 18.

I request you to please discuss this in your groups and find a solution for me.

Thx,

[Aarez]

Marshall Smith SSCrazy Points: 2041 More actions · Answer 4

That is a terribly silly requirement.

OK, then how about this:

IF DB_NAME() = 'Master'

BEGIN

PRINT '**************************************************************************************'

PRINT '************** Master db is selected. Please select correct database *****************'

PRINT '**************************************************************************************'

END

ELSE

BEGIN

{do all the rest of your stuff}

END

That way, the rest of the script only gets executed if the database is NOT 'Master'.

Sabir Khan Valued Member Points: 71 More actions · Answer 5

Thanks Smith..

I tried that also. Since all the rest of my stuff includes multiple batches of CREATE, DROP, INSERT, etc..so i cannot put all these in ELSE part !!

Please think something which is not conventional. Only that can be the solution of this stupid requirement...

-Aarez

SQLBill SSC Guru Points: 51440 More actions · Answer 6

How about:

IF DB_NAME() 'Master'

That way, instead of testing if the database IS Master, you are testing to see if it is NOT Master.

-SQLBill

Pam Brisjar SSChampion Points: 12094 More actions · Answer 7

Why in the world not?

Wrapping things up in a begin..end statement is a perfectly acceptable, workable and logical solution.

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 8

Try:

IF DB_NAME() = 'Master'

GOTO Exit

-- REST of your script

GOTO Quit

Exit:

'**************************************************************************************'

PRINT '************** Master db is selected. Please select correct database *****************'

PRINT '**************************************************************************************'

Quit:

Andy