August 20, 2008 at 6:49 am
I want to abort a sql script, so that it doesn't continue further code.
For hard abort I use:
if @nsqlver < 9.00304200
begin
print 'Wrong Server Edition'
print ''
print 'Current Serverversion: '
print @@version
raiserror ('Script abort', 25, -1) with log
end
The behaviour of Sql2005 is in this case not deterministic.
Sometimes I get the print-messages on screen, sometimes not.
If I use another error-level for example 19, I always get the messages, but the script continues further sql-code below.
Can anybody help me?
Thanks.
August 20, 2008 at 7:20 am
RaisError does not end processing of a batch. All you need to do is put a Return after the RaisError and the batch will stop there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 7:54 am
Errors with a severity of 20 or higher stop the transaction and cause an immediate disconnect. That disconnect may be preventing the PRINT statement from occuring.
A cleaner way to do this would be to use a lower severity and then have a RETURN statement after the RAISERROR. That should allow all the processing to complete, the error to be raised and the cessation of activity.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 20, 2008 at 2:15 pm
Using the RasiError (Error Level < 20) and Return pattern works fine for the "BATCH" but if your script contains multiple batches separated by "Go" then the remaining parts of the script will be executed.
So if your script looks like this
--Some Code--
Error Test with conditional Return
Go
--Some More Code--
Some More Code will be executed unless you use an error level >= 20 to terminate the connection.
If you need the error data, you can add "With Log" to the RasiError which will put the error message text in the Windows Application Log.
August 20, 2008 at 7:23 pm
True. I was assuming a stored proc. Good catch.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 21, 2008 at 1:10 am
Thank you for your help.
But my problem isn't really solved.
I have forget to say that I use two batches in my scripts:
if @nsqlver < 9.00304200
begin
print 'Wrong Server Edition'
print ''
print 'Current Serverversion: '
print @@version
raiserror ('Script abort', 19, -1) with log
return
end
go
--further code
How can I detect in the second batch that something was wrong in the first batch and abort?
I use ""With Log" but how can I detect it in the second batch?
August 21, 2008 at 1:28 am
Use a variable. Something like:
Declare @success bit
Set @success = 0
If @nsqlver < 9.00304200
begin
print 'Wrong Server Edition'
print ''
print 'Current Serverversion: '
print @@version
raiserror ('Script abort', 19, -1) with log
Set @success = 1
return
end
go
If@success = 0
Begin
--further code
End
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 21, 2008 at 1:37 am
Sorry, but that doesn't work.
A variable is only guilty in a batch.
August 21, 2008 at 5:56 am
baumgaertner (8/21/2008)
Sorry, but that doesn't work.A variable is only guilty in a batch.
Right, that's what I get for posting at 3am my time!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 21, 2008 at 7:22 am
The easiest way to pass information between batches is with a Temp Table.
So at the beginning of the script you have something like
If Object_Id('TempDB..#ErrorTab) is not null Drop Table #ErrorTab
Create Table #ErrorTab(ErrorNum int)
Then when you detect an error just insert a value into the table. It does not really matter what value you use.
Then in the second batch you can use a number of approaches. One example is
if Exists (select top 1 * from #ErrorTab)
Begin -- error condition
end
else Begin -- no error condition
end
August 7, 2011 at 11:43 pm
Looks like you are on SQL 2005 but might you be seeing this documented bug in SQL 2000 that allows statements after a RAISERROR to be executed:
http://support.microsoft.com/kb/309802
Amar
August 8, 2011 at 3:06 am
You have two ways to terminate the group of batches:
1. Raise error with severity of 20:
raiserror('Error Here!', 20, -1) with log
But you will need to be logged in as user with sysadmin role and it will kill your connection as well
2. Use SET NOEXEC:
set noexec off
print 'batch 1'
go
print 'batch 2 start'
-- check for error condition and:
print 'Error Here!'
-- the following line will terminate execution
-- you will need to set noexec off, if want to use connection again
set noexec on
print 'batch 2 end'
go
print 'Batch 3'
go
August 8, 2011 at 5:41 am
i think you will also need to explicitly cast to a decimal; where are you getting the verison info from? how are you building the variable for the server version?
--10.0.4000.0
select SERVERPROPERTY('productversion') as ProductVersion into #tmp
/*
CREATE TABLE [dbo].[#tmp] (
[ProductVersion] sql_variant NULL)
*/
returns a sql_variant.
when you compare the two, they are compared as text, so '10.0.4000.0' < '9.00304200' (the 9~ gets implicitly cast to varchars)
Lowell
August 9, 2011 at 9:16 am
Do you really need to use separate batches for your script?
If not, remove the GOs and use Return and/or conditional Goto's to strategically placed labels.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply