Is there any way to exit a TSQL script completely? 
Author Message
 Is there any way to exit a TSQL script completely?

Hi.

I'm working on a database patching process to manage DB changes in multiple
environments (dev, test, production).  Ideally, I want to check that the
patch being applied is in the right order.  If not, I want the entire script
to abort.  I have already stumbled into the fact that all CREATE DEFAULT,
CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements
have to be in their own batches.  So, my problem is that even if I RETURN
from the first batch that checks the right patch sequencing, the next batch
will execute anyway.  I've combed SQL BOL, but I think I'm stuck.

I'm hoping that I don't have to resort to creating a wrapper application
(probably in VB) that will manage the patch execution.

Any suggestions?

Any good books or articles out there on managing the DB change process?

Thanks in advance,

-Peter



Tue, 24 Aug 2004 07:31:44 GMT
 Is there any way to exit a TSQL script completely?

Peter, read this post by Erland:

hl=en
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/


Hi.

I'm working on a database patching process to manage DB changes in multiple
environments (dev, test, production).  Ideally, I want to check that the
patch being applied is in the right order.  If not, I want the entire script
to abort.  I have already stumbled into the fact that all CREATE DEFAULT,
CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements
have to be in their own batches.  So, my problem is that even if I RETURN
from the first batch that checks the right patch sequencing, the next batch
will execute anyway.  I've combed SQL BOL, but I think I'm stuck.

I'm hoping that I don't have to resort to creating a wrapper application
(probably in VB) that will manage the patch execution.

Any suggestions?

Any good books or articles out there on managing the DB change process?

Thanks in advance,

-Peter



Tue, 24 Aug 2004 07:31:30 GMT
 Is there any way to exit a TSQL script completely?
I don't have any sample code on how you are comparing, recursive stored
procedures or what not so I will guess.

What I do is start the top with Begin Transaction

inside if a condition is not met i do the following


    Begin
        Rollback Transaction
        Return -99
    End

If you are doing something that calls other stored procedures do this.




    Begin
        Rollback Transaction

until you get to the end.
    End

The return parameter will come back from the sp.

If you only have one transaction on the beginning call, remove Rollback

Again I have no sample so I have no clue what you are trying to do.

Does this help at all?

Joe


Quote:
> Hi.

> I'm working on a database patching process to manage DB changes in
multiple
> environments (dev, test, production).  Ideally, I want to check that the
> patch being applied is in the right order.  If not, I want the entire
script
> to abort.  I have already stumbled into the fact that all CREATE DEFAULT,
> CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements
> have to be in their own batches.  So, my problem is that even if I RETURN
> from the first batch that checks the right patch sequencing, the next
batch
> will execute anyway.  I've combed SQL BOL, but I think I'm stuck.

> I'm hoping that I don't have to resort to creating a wrapper application
> (probably in VB) that will manage the patch execution.

> Any suggestions?

> Any good books or articles out there on managing the DB change process?

> Thanks in advance,

> -Peter



Tue, 24 Aug 2004 07:32:40 GMT
 Is there any way to exit a TSQL script completely?
Hi Peter..

The google link doesn't quite get it right....

You need to use RAISERROR(<message>, 20, 0) WITH LOG

(a) You MUST be logged on as a login with sysadmin rights
(b) You MUST use the WITH LOG
(c) The severity MUST be at least 20
(d) The State doesn't matter (although I think you could use it with a
specific severity to terminate WinProc on older versions)

The above works on all libraries on all recent versions of MS SQL Server.

Cheers,
Greg Linwood



Quote:
> Peter, read this post by Erland:


Quote:
> hl=en
> --
> HTH,
> Vyas, MVP (SQL Server)

> http://vyaskn.tripod.com/



> Hi.

> I'm working on a database patching process to manage DB changes in
multiple
> environments (dev, test, production).  Ideally, I want to check that the
> patch being applied is in the right order.  If not, I want the entire
script
> to abort.  I have already stumbled into the fact that all CREATE DEFAULT,
> CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements
> have to be in their own batches.  So, my problem is that even if I RETURN
> from the first batch that checks the right patch sequencing, the next
batch
> will execute anyway.  I've combed SQL BOL, but I think I'm stuck.

> I'm hoping that I don't have to resort to creating a wrapper application
> (probably in VB) that will manage the patch execution.

> Any suggestions?

> Any good books or articles out there on managing the DB change process?

> Thanks in advance,

> -Peter



Tue, 24 Aug 2004 15:01:49 GMT
 Is there any way to exit a TSQL script completely?
Thanks Greg.

Cool thing is I can start a transaction in the first batch, and if I throw
that severity 20 exception, the tran will roll back automatically, even if I
thorw it in a batch further down the line!  This allows me to wrap a bunch
of batches within one complete beautiful transaction.  Sweet.

I just wish that you could grant the permission to throw those high severity
errors to somebody that wasn't a member of sysadmin.  I want somedoby like a
db_ddladmin to be able to run a DB patch script and use this functionality.
In order to do that, the closest I have come is using CONTEXT_INFO to set a
flag if a statement fails.  Then the final batch can catch the flag and
rollback the tran explicitly.   The problem there is batches that include
CREATE TRIGGER or any of the other selfish DDL statements - I can't check
context_info or set it in those batches.

Oh well, I'm closer than I was before.  Thanks again to all of you that have
replied.


Quote:
> Hi Peter..

> The google link doesn't quite get it right....

> You need to use RAISERROR(<message>, 20, 0) WITH LOG

> (a) You MUST be logged on as a login with sysadmin rights
> (b) You MUST use the WITH LOG
> (c) The severity MUST be at least 20
> (d) The State doesn't matter (although I think you could use it with a
> specific severity to terminate WinProc on older versions)

> The above works on all libraries on all recent versions of MS SQL Server.

> Cheers,
> Greg Linwood



> > Peter, read this post by Erland:


- Show quoted text -

Quote:
> > hl=en
> > --
> > HTH,
> > Vyas, MVP (SQL Server)

> > http://vyaskn.tripod.com/



> > Hi.

> > I'm working on a database patching process to manage DB changes in
> multiple
> > environments (dev, test, production).  Ideally, I want to check that the
> > patch being applied is in the right order.  If not, I want the entire
> script
> > to abort.  I have already stumbled into the fact that all CREATE
DEFAULT,
> > CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW
statements
> > have to be in their own batches.  So, my problem is that even if I
RETURN
> > from the first batch that checks the right patch sequencing, the next
> batch
> > will execute anyway.  I've combed SQL BOL, but I think I'm stuck.

> > I'm hoping that I don't have to resort to creating a wrapper application
> > (probably in VB) that will manage the patch execution.

> > Any suggestions?

> > Any good books or articles out there on managing the DB change process?

> > Thanks in advance,

> > -Peter



Wed, 25 Aug 2004 09:56:59 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Completely exiting an application

2. How can I script db from tsql script?

3. TSQL question - what am I doing wrong?

4. Apple script to completely terminate crashed Filemaker

5. Ways to execute Script ?

6. Exit out of a script?

7. exit DTS from within active X script task

8. Exit from script when an error occurs

9. Exit .sql Script

10. Exiting a sql script

11. How to exit a .sql script?

12. Exit a sql script


 
Powered by phpBB® Forum Software