Posted by Matthew Wells on 12/06/06 17:03

That's not exactly true...
I can fire

Begin Tran

Statement 1
Statement 2
Statement 3
GO

-- I want to test here if the previos block was rolled back, but
--all variables declared earler lose scope
Statement 1
Statement 2
Statement 3
GO

Statement 1
Statement 2
Statement 3
GO

Rollback Tran

and all statements are rolled back. I want to test after each block to see
if an error occurred so I can skip the rest of the script. Is there a way
to declare a variable whose scope is the entire script?


"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9891AA2DAAFB4Yazorman@127.0.0.1...
> Matthew Wells (MWells@FirstByte.net) writes:
> > Is there a way to use Goto or something like it in QA? I have a script
> > with a few GO statements, as I'm sure you know, declared variables,
> > labels, and goto's only have scope within each GO block. If i have an
> > error in the second or third, of 5 GO blocks, I want to trap the error
> > number and goto only ONE error handler label.
>
> No, each batch is sent to SQL Server independly.
>
> I don't really know what your script does, bu my experience is that it's
> better to have a client to handle install scripts, as the client is better
> on error handling and the like.
>
> In OSQL there is actually a brute way out: you can do a RAISERROR with
> state 127. This causes OSQL to exit. However, QA does not act that way.
> (If you use OSQL, you need to include SET QUOTED_IDENTIFIER ON in the
> script, since OSQL by default runs with this setting off.)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]