RaisError in a function? Any ideas

Writing a function and need to raise an error under certain conditions. This is a gross over-simplification of the function. But you get the idea....I need to use @.@.ROWCOUNT

Code Snippet

create Function GetTheValue

(@.Input INT)

returns Bigint

as

begin

declare @.Output bigint

select TheValue

from TheTable

where RowID = @.Input

IF @.@.ROWCOUNT = 0

RAISERROR('Oops',15,1)

return @.INput

END

Basically, the way this usually works is that you return an invalid value and check for it. Like in this case, you can never get a negative value for @.@.rowcount, just return -1 or something like this.

You can force an error to occur by doing something like 0/1, but I was never fond of that method since the error message doesn't correspond to the actual error.

|||

As you noticed, RAISERROR is NOT allowed in a FUNCTION.

A FUNCTION cannot have any output EXCEPT the FUNCTION value. Of course, it will 'crash' when unacceptable actions occur, such as divide by zero.

Louis indicated an acceptable method of handling a data anomaly.

|||

Thanks....

I'll probably use:

Code Snippet

if @.@.rowcount =0

select 1/0

RAISERROR does not cause SQL task to fail - why?

Greetings,

I have a stored procedure with a TRY / CATCH block. In the catch block I capture information about the error. I then use RAISERROR to "rethrow" the exception so that it will be available to SSIS.

I execute the stored procedure through a SQL task. I observe that SSIS reports the SQL task succeeds (the task box turns green) when RAISERROR is invoked. If I comment the catch block with RAISERROR then SSIS reports the task failed. (I created a simple procedure that does a divide by zero to force an error.) The expected error message is displayed when the sproc is run from the SQL Server Management Studio command line so I believe that the stored procedure is doing what I intended.

I would like to handle an error within my stored procedure without destroying SSIS's ability to detect a task failure. Is this possible? Is there an alternative to using RAISERROR?

Thanks,

BCB

But is it a message or an output? That is, which tab in SSMS does the results of the procedure end up on? "Results" or "Messages"?|||Can you please post code for both versions of the stored procedure?|||

Thanks for the replies.

Phil, to answer your question, the error message is displayed on the Messages tab in Management Studio.

I'm including two versions of the sproc as well as the invocation code I used to run it within Management Studio. I'm successfuly capturing the error info in the CATCH block and passing it back to SSIS. The point of having the RAISERROR at the end of the CATCH is to make SSIS fail the task.

This is the sproc with no CATCH:

Code Snippet

-- This is the stripped procedure with no CATCH block. The OUTPUT parms do nothing.
-- This sproc will cause an SQL task to fail.
ALTER PROCEDURE [dbo].[sp_ThrowException]
@.ERROR_NUMBER INT OUTPUT,
@.ERROR_MESSAGE NVARCHAR(4000) OUTPUT,
@.ERROR_SEVERITY INT OUTPUT,
@.ERROR_STATE INT OUTPUT,
@.ERROR_PROCEDURE NVARCHAR(126) OUTPUT,
@.ERROR_LINE INT OUTPUT,
@.FormattedMessage NVARCHAR(4000) OUTPUT
AS

BEGIN

SET NOCOUNT ON

-- this forces a "divide by zero" exception
SELECT 1 / 0 AS 'TRY' FROM view_OilRigs_REPORT R

END

This is the sproc as I really want it. It has the RAISERROR in the CATCH block.

Code Snippet

-- This is the desired procedure with the CATCH block. The error information is being returned
-- to the SQL task through the OUTPUT parms.
-- This sproc will allow an SQL task to succeed.

ALTER PROCEDURE [dbo].[sp_ThrowException]
@.ERROR_NUMBER INT OUTPUT,
@.ERROR_MESSAGE NVARCHAR(4000) OUTPUT,
@.ERROR_SEVERITY INT OUTPUT,
@.ERROR_STATE INT OUTPUT,
@.ERROR_PROCEDURE NVARCHAR(126) OUTPUT,
@.ERROR_LINE INT OUTPUT,
@.FormattedMessage NVARCHAR(4000) OUTPUT
AS

BEGIN TRY

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- this forces a "divide by zero" exception
SELECT 1 / 0 AS 'TRY' FROM view_OilRigs_REPORT R

END TRY

BEGIN CATCH

SELECT
@.ERROR_NUMBER = ERROR_NUMBER(),
@.ERROR_MESSAGE = ERROR_MESSAGE(),
@.ERROR_SEVERITY = ERROR_SEVERITY(),
@.ERROR_STATE = ERROR_STATE(),
@.ERROR_PROCEDURE = ERROR_PROCEDURE(),
@.ERROR_LINE = ERROR_LINE(),
-- this renders the error message in command line format
@.FormattedMessage = 'Msg ' + CAST(ERROR_NUMBER() AS NVARCHAR(20)) + ', ' +
'Level ' + CAST(ERROR_SEVERITY() AS NVARCHAR(20)) + ', ' +
'State ' + CAST(ERROR_STATE() AS NVARCHAR(20)) + ', ' +
'Procedure ' + ERROR_PROCEDURE() + ', ' +
'Line ' + CAST(ERROR_LINE() AS NVARCHAR(20)) + ', ' +
'Message: ' + ERROR_MESSAGE()

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR
(
@.ERROR_MESSAGE, -- Message text
@.ERROR_SEVERITY, -- Severity
@.ERROR_STATE -- State
)

END CATCH

I use this code to run the sproc in Management Studio.

Code Snippet

-- I use this to run the sproc within Management Studio.

SET NOCOUNT ON;

DECLARE @.local_ERROR_NUMBER INT
DECLARE @.local_ERROR_MESSAGE NVARCHAR(4000)
DECLARE @.local_ERROR_SEVERITY INT
DECLARE @.local_ERROR_STATE INT
DECLARE @.local_ERROR_PROCEDURE NVARCHAR(126)
DECLARE @.local_ERROR_LINE INT
DECLARE @.local_FormattedMessage NVARCHAR(4000)

EXEC [dbo].[sp_ThrowException]
@.ERROR_NUMBER = @.local_ERROR_NUMBER OUTPUT,
@.ERROR_MESSAGE = @.local_ERROR_MESSAGE OUTPUT,
@.ERROR_SEVERITY = @.local_ERROR_SEVERITY OUTPUT,
@.ERROR_STATE = @.local_ERROR_STATE OUTPUT,
@.ERROR_PROCEDURE = @.local_ERROR_PROCEDURE OUTPUT,
@.ERROR_LINE = @.local_ERROR_LINE OUTPUT,
@.FormattedMessage = @.local_FormattedMessage OUTPUT
SELECT
@.local_ERROR_NUMBER AS ERROR_NUMBER,
@.local_ERROR_MESSAGE AS ERROR_MESSAGE,
@.local_ERROR_SEVERITY AS ERROR_SEVERITY,
@.local_ERROR_STATE AS ERROR_STATE,
@.local_ERROR_PROCEDURE AS ERROR_PROCEDURE,
@.local_ERROR_LINE AS ERROR_LINE,
@.local_FormattedMessage AS FormattedMessage

Thanks for looking at this.

BCB

|||As long as it's posted to the messages tab, to my knowledge, you cannot capture that inside SSIS.|||

BlackCatBone wrote:

Thanks for the replies.

Phil, to answer your question, the error message is displayed on the Messages tab in Management Studio.

I'm including two versions of the sproc as well as the invocation code I used to run it within Management Studio. I'm successfuly capturing the error info in the CATCH block and passing it back to SSIS. The point of having the RAISERROR at the end of the CATCH is to make SSIS fail the task.

I don't think you can have it both ways. If the task fails, you're not going to be able to use it to capture the output parameters.

I'm personally surprised that SSIS does not fail the task when you use RAISERROR in your procedure code. I would have assumed that explicitly raising an error would cause the task to fail, but it does not. It's far too late for me to dig into this. Perhaps someone else can shed some light on the "why" of this behavior.

With that said, this is the approach that I would recommend if I needed to capture the error information from the stored procedure and have the package respond to the error as well:

Remove the RAISERROR from the procedure. Leave the TRY CATCH block in the procedure. Have the Execute SQL task call the procedure and capture the output parameters. Have precedence constraints below the Execute SQL task that follow a "success" path or an "error" path based on the values of the output parameters - likely the error number.

RAISERROR dilema

Hello,

I am tring to use the RAISERROR command to log an error when it happens and pass a parameter into it. This is what I am looking for in a nutshell. I know this syntax is incorrect.

RAISEERROR('The following process has encountered an error' +space(1) + @.var, 16,1, @.with_log='TRUE')

Any help would be appreciated...

Thanks,

Dave

To make this work, you need to create your message before calling RAISERROR.

DECLARE @.ErrMessage varchar(250)

SET @.ErrMessage = ( 'The following process has encountered an error' + ' ' + @.var )

And then call RAISERROR using the @.ErrMessage, e.g.,

RAISEERROR( @.ErrMessage, 16, 1, @.with_log='TRUE' )

Of course, if @.Var is numeric,it will have to be cast as varchar to concatenate.

sql

RAISERROR Behavior Question

I have a RAISERROR statement being displayed to the screen prior to a print statement, however the print statement was executed before the RAISERROR statement. Why would this happen? The print statement is not part of any conditional logic. It is executed sequentially followed by an IF statement that generates the RAISERROR.

Step 1. Loop through all Databases and dynamically run DBCC ShowContig
Step 2. End the loop and Print "SCANNING COMPLETED"
Step 3. Declare Cursor to read DBCC ShowContig results
Step 4. Check current time to see if it is ok to continue processing
If it is NOT ok, RAISERROR
Else begin DBReindex process
Step 5. Close and Deallocate Cursor

If I replace RAISERROR with a print statement, everything prints in order. What gives?

DaveIf you post the code it would be easier to find solution. However, If you are not saving the value of @.@.error in a variable it resets to Zero when you go back to check it again .

In other words You should

Declare @.err int
And while checking @.@.error use Select @.err = @.@.error and then check @.err value which will stay stored . I am not sure if this is what you needed to know .. Like I said , code posting might help|||The logic itself works fine so posting the code most likely won't help. The RAISERROR is generated after a conditional statement checking the length of a local variable. There is no need to check @.@.ERROR at that time. The only issue is why a RAISERROR gets sent to the screen before a print statement when the print statement executed first. I was told a few hours ago that Print statements are first sent to the buffer and its possible a RAISERROR does not hit the buffer. That may explain why it appears first.

Thanks, Dave|||what's the severity level and state values? i've experimented for a couple of minutes with different severity levels including 20 and up, at which point the print statement does not get displayed at all.|||Raiserror ("My error message", 16, 1) with log

Dave|||i put print 'test' before your raiserror and got this:

test
Server: Msg 50000, Level 16, State 1, Line 2
My error message|||I'll be testing all of the error routines again this week. Once I recreate the issue I'll see if the code is small enough to post.

Thanks, Dave

raiserror / terminate a users connection

Hi,
For slightly convoluted reasons I'd like to be able to terminate a users
connection from within a stored procedure, in a similar way as if I'd raised
an error with a severity of 20 or above. Is this possible?
The circumstances are that I have a number of security tests that I run at
various places, and there is one possible outcome which can only mean that a
user has been active for over 20 hours (practically impossible) or that the
system has been broken into in some way - I'd like to just completely kill
the connection immediately if this condition is detected, and wrap it in a
single stored procedure (i.e. take the key in question, test it, and then
raise an error and throw the user out if it fails).
Can it be done?
Thanks
NickYou can use KILL, but it won't be a neat error message for that connection..
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Nick Stansbury" <nick.stansbury@.sage-removepartners.com> wrote in message
news:cutjph$rm0$1@.pop-news.nl.colt.net...
> Hi,
> For slightly convoluted reasons I'd like to be able to terminate a user
s
> connection from within a stored procedure, in a similar way as if I'd rais
ed
> an error with a severity of 20 or above. Is this possible?
> The circumstances are that I have a number of security tests that I run at
> various places, and there is one possible outcome which can only mean that
a
> user has been active for over 20 hours (practically impossible) or that th
e
> system has been broken into in some way - I'd like to just completely kill
> the connection immediately if this condition is detected, and wrap it in a
> single stored procedure (i.e. take the key in question, test it, and then
> raise an error and throw the user out if it fails).
> Can it be done?
> Thanks
> Nick
>|||But won't "Kill" only work for other connections other than the current
connection? What I need to do is Kill the current connection
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23MC5e55EFHA.2564@.tk2msftngp13.phx.gbl...
> You can use KILL, but it won't be a neat error message for that
connection...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Nick Stansbury" <nick.stansbury@.sage-removepartners.com> wrote in message
> news:cutjph$rm0$1@.pop-news.nl.colt.net...
users
raised
at
that a
the
kill
a
then
>|||Sorry, I thought you want to execute this from the outside the connection, h
ence the reference to
KILL
I'm afraid that I can't see an easy way to accomplish this unless the user i
s symin. If so, you
can use RAISERROR with severity 20 or higher.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nick Stansbury" <nick.stansbury@.sage-removepartners.com> wrote in message
news:cuv15k$al6$1@.pop-news.nl.colt.net...
> But won't "Kill" only work for other connections other than the current
> connection? What I need to do is Kill the current connection
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23MC5e55EFHA.2564@.tk2msftngp13.phx.gbl...
> connection...
> users
> raised
> at
> that a
> the
> kill
> a
> then
>

Raiserror

Is there a way to raise an exception inside a user function in the Sqlserver2000?.Guess not

Did you check out BOL for specifics?

CREATE FUNCTION udf_myFunction99 (@.x varchar(8000))
RETURNS int
AS
BEGIN
RAISERROR 50001 'Error Raised'
RETURN -1
END
GO|||Originally posted by Brett Kaiser
Guess not

Did you check out BOL for specifics?

CREATE FUNCTION udf_myFunction99 (@.x varchar(8000))
RETURNS int
AS
BEGIN
RAISERROR 50001 'Error Raised'
RETURN -1
END
GO



I got the error

Server: Msg 443, Level 16, State 2, Procedure udf_myFunction99, Line 5
Invalid use of 'RAISEERROR' within a function.|||Yeah, I know...That's why I said:

"guess not"

Check out books online for a better description of Functions...

I know there a few limitations...

What are you trying to do?|||Originally posted by Brett Kaiser
Yeah, I know...That's why I said:

"guess not"

Check out books online for a better description of Functions...

I know there a few limitations...

What are you trying to do?

There are many limitations I know, but this one I didn't found.
I supose its not allowed to use RAISERROR in functions.
I am generating a calc module from a CASE system using only functions. So far I found a solution for every limitation. Otherwise I will have to rebuild the entire system using only stored procedure instead of functions.

That1s why I am looking for some "magic" to raise the exception.

Thank You.|||...or we could hope for a miracle...

Since you haven't been able to raise out in the other functions, why do you want to with this one?

And yeah, sprocs would have been the way to go (MOO)...how are you using the functions?

BOL

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.

Control-of-Flow statements.

DECLARE statements defining data variables and cursors that are local to the function.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

INSERT, UPDATE, and DELETE statements modifying table variables local to the function.

EXECUTE statements calling an extended stored procedures.sql

RAISERROR

Hello,

I am raising an error on my SQL 2005 procedure as follows:

RAISERROR(@.ErrorMessage, @.ErrorSeverity, 1)

How can I access it in my ASP.NET code?

Thanks,

Miguel

See the following post

http://forums.asp.net/p/639921/639921.aspx#639921