Error handlig in Query Analyzer

[Matthew Wells]

Hello

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.

Anyone know of a way?

Thansk.

Matthew Wells
MWells@FirstByt e.net

[Erland Sommarskog]

Re: Error handlig in Query Analyzer

Matthew Wells (MWells@FirstBy te.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_IDENTIFI ER ON in the
script, since OSQL by default runs with this setting off.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[Matthew Wells]

Re: Error handlig in Query Analyzer

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@sommars kog.sewrote in message
news:Xns9891AA2 DAAFB4Yazorman@ 127.0.0.1...

Matthew Wells (MWells@FirstBy te.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_IDENTIFI ER ON in the
script, since OSQL by default runs with this setting off.)
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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/prodinf...ons/books.mspx

[Erland Sommarskog]

Re: Error handlig in Query Analyzer

Matthew Wells (MWells@FirstBy te.net) writes:

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?

No, there isn't. As I said, each batch is sent to SQL Server
independently. You would need variables on the client side, but that
is not offered by Query Analyzer. Whence my recommendation to make
an install script in a language like Perl or VBscript, where you have
better control in Query Analyzer.

What you could do is to all batches this:

IF @@tracount 0
BEGIN
Statement1
Statement2
Statement3
END

Yet an alternative is to have all in one batch, and if there are
statement that will not compile because previous statements have
not yet been executed, you could put these in EXEC() or sp_executesql.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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