Catching return values of a SP

I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.

Here is a piece of my SP inside SQL Server that shows the returned values:

…
SELECT @.Id = SCOPE_IDENTITY()
SELECT @.Id AS user_id
SELECT 1 AS Value
END
GO

In my aspx page I am trying to call the first value like this:

Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
 CmdInsert.Parameters("@.RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@.RETURN_VALUE").Value = nID

And to check if the right value is returned I use:

strConnection.open()
 cmdInsert.ExecuteNonQuery
 'Set the value of a textbox
 ident.text = nID
 strConnection.close()

But now no value appears in the textbox, How can I achieve it? What is wrong?You are sort of combining a few different approaches to solving this problem. Since only one ReturnValue can be returned from a stored procedure and you need 2 values, that approach won't work. And since you only have 2 values, I think that you should use OUTPUT parameters.

The stored procedure would look like this:

CREATE PROCEDURE
 myProcedure
AS
 @.myInput1 varchar(50),
 @.myInput2 varchar(50),
 @.myOutput1 bigint OUTPUT,
 @.myOutput2 bigint OUTPUT
INSERT <etc etc
SET @.myOutput1 = SCOPE_IDENTITY
SET @.myOutput2 = 2

Your aspx page code would look like this:

CmdInsert.Parameters.Add("@.myOutput1", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput1").Direction = ParameterDirection.Output
CmdInsert.Parameters.Add("@.myOutput2", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput2").Direction = ParameterDirection.Output
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = CmdInsert("@.myOutput1")
strConnection.close()

Terri|||I have followed all your steps, and now this error message appears:

BC30367: Class 'System.Data.SqlClient.SqlCommand' cannot be indexed because it has no default property.

What does it mean?|||Sorry, the line afected is this:

Line 105: ident.text = CmdInsert("@.Id")|||I'm the one who's sorry. The correct syntax for that line is:

ident.text = CmdInsert.Parameters("@.Id").Value

Terri|||i think its something like :

ident.text=convert.toint32(CmdInsert.Parameters("@.Id").Value)

hth|||Good! now runs fine

Thank you very much,
Cesar

Catching Primary Key Violation on insert error

I've read a few different articticles wrt how the handle this error gracefully.

I am thinking of wrapping my sql insert statement in a try catch and have the catch be something like

IF ( e.ToString() LIKE '%System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_PS_HR_Hrs'. Cannot insert duplicate key in object %')

{
lable1.text = "Sorry, you already have reported hours for that day, please select anothe rdate"

}

Is there a better way?

TIA

Dan

Find out the error number that your Exception is throwing, then trap for that particular error number. Your error might be number 2627 or 2601.

|||

Bummer-

I'm away from my asp.net enviormentt.

Um...

how do I do it?

int errorcode = ex.ToInt

if (errorcode = 123) then...

TIA

Dan

(Can't stop thinknkng about this stuff, I think I need professional help!)

|||

Catch the SqlException first, then display your message based on the returned number.

try{}catch (SqlException ex){if (ex.Number.Equals(2627)){// Display your error here}}

|||

Thanks Ed!

(Cute Kid!)

catching of output for RESTORE FILELISTONLY.

Hi Folks,
How can I catch the output(columns) of the following SQL
command in a table or each column in variables?
RESTORE FILELISTONLY
FROM DISK='c:\temp\FlexKIDS.bak'
I have tried the following construction, which did NOT
work:
CREATE TABLE #LIST_FILE
(LogicalName varchar(120),
PhysicalName varchar(500),
L_Type char(1),
L_FileGROUP varchar(60),
L_size int,
Max_size int)
INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
DISK='c:\temp\FlexKIDS.bak'INSERT #LIST_FILE EXEC ('RESTORE FILELISTONLY FROM
DISK=''c:\temp\FlexKIDS.bak''')
--
Jacco Schalkwijk
SQL Server MVP
"jack" <jbonapart@.dicon.nl> wrote in message
news:090801c3a834$312770e0$a401280a@.phx.gbl...
> Hi Folks,
> How can I catch the output(columns) of the following SQL
> command in a table or each column in variables?
> RESTORE FILELISTONLY
> FROM DISK='c:\temp\FlexKIDS.bak'
> I have tried the following construction, which did NOT
> work:
> CREATE TABLE #LIST_FILE
> (LogicalName varchar(120),
> PhysicalName varchar(500),
> L_Type char(1),
> L_FileGROUP varchar(60),
> L_size int,
> Max_size int)
>
> INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
> DISK='c:\temp\FlexKIDS.bak'|||Insert into #LIST_FILE
exec('RESTORE FILELISTONLY
FROM DISK = ''c:\temp\FlexKIDS.bak''')
This will help to capture the result set into a temp. table
Try this out .!
Regards,
Raghu
>--Original Message--
>Hi Folks,
>How can I catch the output(columns) of the following SQL
>command in a table or each column in variables?
>RESTORE FILELISTONLY
>FROM DISK='c:\temp\FlexKIDS.bak'
>I have tried the following construction, which did NOT
>work:
>CREATE TABLE #LIST_FILE
>(LogicalName varchar(120),
> PhysicalName varchar(500),
> L_Type char(1),
> L_FileGROUP varchar(60),
> L_size int,
> Max_size int)
>
>INSERT #LIST_FILE EXEC RESTORE FILELISTONLY FROM
>DISK='c:\temp\FlexKIDS.bak'
>.
>

Catching more then one Error

Hello all, not to sure if this can be done but I am attempting to catch a series of errors with in a single TRY CATCH statement.

For example, the following code triggers a single error ( which work fine)

SET NOCOUNT ON
declare @.errmsg nvarchar(4000)
begin try
select 0/0
end try
begin catch
SET @.errmsg = 'Msg ' +
cast(ERROR_NUMBER() as varchar(20)) + ', Level ' +
cast(ERROR_SEVERITY() as varchar(20)) + ', State ' +
cast(ERROR_STATE() as varchar(20)) + ', Line ' +
cast(ERROR_LINE() as varchar(20)) + ', ' + CHAR(13) + 'Procedure ' +
isnull(ERROR_PROCEDURE(),'') + CHAR(13) + isnull(ERROR_MESSAGE(),'')
end catch
select @.errmsg AS ERRMSG

RESULT:
Msg 8134, Level 16, State 1, Line 4, Procedure Divide by zero error encountered.

But when i execute the same TRY CATCH but use a backup routine that i purposly fail the query result shows the following ( which is what i want...)

Msg 4208, Level 16, State 0, Procedure usp_dbbackup2005_v1_5, Line 599
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Msg 3013, Level 16, State 1, Procedure usp_dbbackup2005_v1_5, Line 599
BACKUP LOG is terminating abnormally.

Instead i only get the last error in the series.

Msg 3013, Level 16, State 1, Procedure usp_dbbackup2005_v1_5, Line 599
BACKUP LOG is terminating abnormally.

Any Thoughts?

Thanks

Anybody have any thoughts on this? i can't see why i couldnt grab all the errors within a transaction.

Thanks

DM

|||

With my understanding about Try..Catch block (any language); it always cath one exception object and transform the control to Catch block from the try block (even C#.NET or VB.NET does the same..); I dont think it is logically possible ..

Folks what about your opinion here..

|||You wont get the stack are any further executed command errors in the error functions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Bummer, you would think you would be able to grab entire stack upon error. Obviously as far as a client is concerned the entire stack is pushed out.( which is what you would see if the statement was executed from a .net client or query analyzer.) so I guess that begs the question; how do you build a solid error handler if you are not provided all the tools to dissect the issue.

Thanks

DM

Catching Events

Hi,

I'm trying to catch an error and trigger a control flow to handle it. I introduce a control flow to catch "OnError" event, but , despite muy package has some errors it doesnt work...

Another issue, if i omit an error on a transformation object ( in order let the flow continue executing), can this error be managed by an event or record it in a log?

Thanks

Albertoim wrote:

Hi,

I'm trying to catch an error and trigger a control flow to handle it. I introduce a control flow to catch "OnError" event, but , despite muy package has some errors it doesnt work...

Thanks

How do you know it doesn't work?

|||

Because the flow i have added to execute when the error event happens does not execute.

|||I have added the event handler at the top level ( Package) for errors events and information events indeed and, as i said before, the flow to execute in this events does not execute. May i have to set some propertie to enable event catching?|||

Hi,

I have done several tests in a new blank project and error events work properly. I have notice that, in th project where i cant catch events, the txt log file is allways blank, so , i supose its something worng with the events, that for any reason arent raised.

In project properties, "DisableEventHandlers" is set to False.

Can anyone help me? Thanks.

|||

Are the errors immediate or near immediate? This scenario of "package level event level handler not firing and empty package logger file" sounds as if you're receiving errors on package load or on package level validation, very early in the attempted load/validate/execute execution sequence.

If particular, what kind of error events are being you receiving? Please attempt to run the package via dtexec (32 or 64 as appropriate) as well and then post the text of the first few errors.

|||The

package works fine... these errors are things like insert a row with a duplicate PK etc... i know that these kind of errors exists because the source table has dirty data. I would like to handle these errors catching an event.

The problem is that no event is raised when i open the log file is completely blank.... i think these two things can be related.

Catching Events

Hi,

I'm trying to catch an error and trigger a control flow to handle it. I introduce a control flow to catch "OnError" event, but , despite muy package has some errors it doesnt work...

Another issue, if i omit an error on a transformation object ( in order let the flow continue executing), can this error be managed by an event or record it in a log?

Thanks

Albertoim wrote:

Hi,

I'm trying to catch an error and trigger a control flow to handle it. I introduce a control flow to catch "OnError" event, but , despite muy package has some errors it doesnt work...

Thanks

How do you know it doesn't work?

|||

Because the flow i have added to execute when the error event happens does not execute.

|||I have added the event handler at the top level ( Package) for errors events and information events indeed and, as i said before, the flow to execute in this events does not execute. May i have to set some propertie to enable event catching?|||

Hi,

I have done several tests in a new blank project and error events work properly. I have notice that, in th project where i cant catch events, the txt log file is allways blank, so , i supose its something worng with the events, that for any reason arent raised.

In project properties, "DisableEventHandlers" is set to False.

Can anyone help me? Thanks.

|||

Are the errors immediate or near immediate? This scenario of "package level event level handler not firing and empty package logger file" sounds as if you're receiving errors on package load or on package level validation, very early in the attempted load/validate/execute execution sequence.

If particular, what kind of error events are being you receiving? Please attempt to run the package via dtexec (32 or 64 as appropriate) as well and then post the text of the first few errors.

|||The

package works fine... these errors are things like insert a row with a duplicate PK etc... i know that these kind of errors exists because the source table has dirty data. I would like to handle these errors catching an event.

The problem is that no event is raised when i open the log file is completely blank.... i think these two things can be related.

Catching Events

Hi,

I'm trying to catch an error and trigger a control flow to handle it. I introduce a control flow to catch "OnError" event, but , despite muy package has some errors it doesnt work...

Another issue, if i omit an error on a transformation object ( in order let the flow continue executing), can this error be managed by an event or record it in a log?

Thanks

Albertoim wrote:

Hi,

I'm trying to catch an error and trigger a control flow to handle it. I introduce a control flow to catch "OnError" event, but , despite muy package has some errors it doesnt work...

Thanks

How do you know it doesn't work?

|||

Because the flow i have added to execute when the error event happens does not execute.

|||I have added the event handler at the top level ( Package) for errors events and information events indeed and, as i said before, the flow to execute in this events does not execute. May i have to set some propertie to enable event catching?|||

Hi,

I have done several tests in a new blank project and error events work properly. I have notice that, in th project where i cant catch events, the txt log file is allways blank, so , i supose its something worng with the events, that for any reason arent raised.

In project properties, "DisableEventHandlers" is set to False.

Can anyone help me? Thanks.

|||

Are the errors immediate or near immediate? This scenario of "package level event level handler not firing and empty package logger file" sounds as if you're receiving errors on package load or on package level validation, very early in the attempted load/validate/execute execution sequence.

If particular, what kind of error events are being you receiving? Please attempt to run the package via dtexec (32 or 64 as appropriate) as well and then post the text of the first few errors.

|||The

package works fine... these errors are things like insert a row with a duplicate PK etc... i know that these kind of errors exists because the source table has dirty data. I would like to handle these errors catching an event.

The problem is that no event is raised when i open the log file is completely blank.... i think these two things can be related.

Catching errors and row cnt from SQLdataSource

I'm new to using SQL Data Source, so bare with me on the newbie question.

Is there a way to do a Try...Catch type scenario on the SDS? I have a grid and a SDS that is mapped together but previously I use to use a Try...Catch and show any errors. What can I do to display a message if there is an error with the SDS?

Try
'Call to DB

Catch
label1.txt = "Error: " & ex.Message.ToString

End Try

And is the best way to determine if there are any records to display is to use the SDS_Selected event?

Dim Rec as Integer = e.AffectedRows
If Rec = 0 Then
label1.text = "No Records Found."
End If

To catch errors on a SQLDataSource I use the 'ed' events (ie Selected, Inserted, Deleted and Updated), and check that e.Exception is not null. You can returne.ExceptionHandled = True once you have handled the exception.

e.AffectedRows looks like it would be the best way to count the number of affected records (I can't say I've ever tried to catch that, so there may be other ways I don't know about)

HTH

|||

Hi,
I agree with drktrnq. Below there is a code snippet. I hope it helps you.

1Protected Sub SqlDataSource1_Selected(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)Handles SqlDataSource1.Selected2If (e.Exception IsNotNothing)Then3 Me.Label1.Text = e.Exception.Message4 e.ExceptionHandled =True5 Return6 End If78 If (e.AffectedRows = 0)Then9 Me.Label1.Text ="No records found"10Else11 Me.Label1.Text = e.AffectedRows.ToString()12End If13 End Sub

Luis Ramirez.
www.sqlnetframework.com
The SQL framework for .NET.

catchin errors occured in sql server with DELPHI

hi
i want to know how can I catch an error occured in sql server with DELPHI
when an error occurs in sql server the number of error and description of th
e
error is sent to any programming language.
for example I want to change the message in my application when the error
occures?
but I don't knoe how?
is there any espesial EVENT HANDLER?Are you asking how to do this in Delphi? If so, you are probably better off
posting this in a Delphi forum.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"pooyan_pdm" <pooyanpdm@.discussions.microsoft.com> wrote in message
news:C64FB3F0-1327-4B22-AC22-7F367E9EEE33@.microsoft.com...
> hi
> i want to know how can I catch an error occured in sql server with DELPHI
> when an error occurs in sql server the number of error and description of
> the
> error is sent to any programming language.
> for example I want to change the message in my application when the error
> occures?
> but I don't knoe how?
> is there any espesial EVENT HANDLER?
>

Catch up Temp Table through Profiler

Hi,
How can i get the #Temp Table value after finished the SP ?
Is it possible through Sql Server profiler to hold the #Temp Table value ?
Thanks,Temptables will be dropped as soon as Stored Procedure finished the
task.
Madhivanan

catch the error on insert or update (was "need help")

hello!

im new to sql... what i'm trying to do is catch the error on insert or update statment of sql.. sound simple but please..

this is the sample table design...

tbl_Customer

CustomerID int(4) Primary AutoIncrement
CustomerCode nvarchar(25)
CustomerName nvarchar(25)
..
..
Deleted bit(1)

what i'm trying to do is when a record is deleted, it's not actually deleted
in the table but only marked 1 (true) the Deleted field.. because i don't want
to lose the relationship...

it's easy to do this on insert statement like this..

Create Procedure InsertCustomer(@.param1 ...) AS
IF NOT EXIST (SELECT * FROM tbl_Customer WHERE DELETED = 0) THEN
// do insert statement here
ELSE
// Do nothing

GO

this is also easy if i create a index constraints on the table.. but this will violate my design idea..

so anybody can help me to create the procedure in update statement and insert statementcatch what error?

can you explain what you want to do here?

catch SQL server down event

Hi,
How can I catch SQL Server unexpectedly shut down? I want to be the first
person who knows the server is down if it happens.
Thanks,
Julia"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:3499EB47-F17A-454E-98D0-5AE3CA792503@.microsoft.com...
> Hi,
> How can I catch SQL Server unexpectedly shut down? I want to be the first
> person who knows the server is down if it happens.
Generally it should put an event in the event log and programs like Servers
Alive should be able to monitor that.
However, I've got to say that if your SQL Server is unexpectedly shutting
down, you've got some major issues. It's normally extremely stable.
> Thanks,
> Julia

catch SQL server down event

Hi,
How can I catch SQL Server unexpectedly shut down? I want to be the first
person who knows the server is down if it happens.
Thanks,
Julia
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:3499EB47-F17A-454E-98D0-5AE3CA792503@.microsoft.com...
> Hi,
> How can I catch SQL Server unexpectedly shut down? I want to be the first
> person who knows the server is down if it happens.
Generally it should put an event in the event log and programs like Servers
Alive should be able to monitor that.
However, I've got to say that if your SQL Server is unexpectedly shutting
down, you've got some major issues. It's normally extremely stable.

> Thanks,
> Julia

catch SQL server down event

Hi,
How can I catch SQL Server unexpectedly shut down? I want to be the first
person who knows the server is down if it happens.
Thanks,
Julia"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:3499EB47-F17A-454E-98D0-5AE3CA792503@.microsoft.com...
> Hi,
> How can I catch SQL Server unexpectedly shut down? I want to be the first
> person who knows the server is down if it happens.
Generally it should put an event in the event log and programs like Servers
Alive should be able to monitor that.
However, I've got to say that if your SQL Server is unexpectedly shutting
down, you've got some major issues. It's normally extremely stable.

> Thanks,
> Julia

catch sql command if value doesnt exist

I have a sql command that is loaded on page load that collects information based on the query string. The query string is a random group of numbers and letters. How do I catch it and direct to an error page if the query can not be found in the database?

Thanks!

if you are trying to get some parameters from the querystring then you can use

dim queryvariable as string =request.querystring("variable")

try

dim sqlquery as string

sqlquery="SELECT column_Name from Table_name where variable= "& queryvariable

//use this sqlquery to check whether it returns some rows or not

catch

response.redirect("pageNotFound.aspx")

end try

|||

I'm pretty sure i did all that.

in page load i'm doing

getUserInfo(Request.QueryString["uid"]);

then the method

protected void getUserInfo(string userid) {string selectCmd ="SELECT * from users WHERE ID = @.id";string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection =new SqlConnection(strConnection); SqlCommand myCommand =new SqlCommand(selectCmd, myConnection); myCommand.Parameters.Add(new SqlParameter("@.id", SqlDbType.VarChar, 10)); myCommand.Parameters["@.id"].Value = userid;try { myConnection.Open(); SqlDataReader datareader = myCommand.ExecuteReader();while (datareader.Read()) { lblFirstName.Text = datareader["firstname"].ToString(); lblLastName.Text = datareader["lastname"].ToString(); lblTeam.Text = datareader["team"].ToString(); lblOffice.Text = datareader["office"].ToString(); } datareader.Close(); myConnection.Close(); }catch { Response.Redirect("~/error.aspx"); }

|||

any ideas?

|||

In your code you aren't checking if the datareader actually contains any data or not, so if no records are being returned nothing happens. 1 simple way to do it is:

1. Declare a boolean variable at the top initialized to False: boolean bolUserFound = False

2. Inside the while loop set the value to true: bolUserFound = True

3. After you close the connection evaluate the variable and if it's still false you know no records were found and you need to redirect to your error page:

if (bolUserFound = False) {

Response.Redirect("~/error.aspx");

}

|||

perfect!

Exactly what i needed...

Catch raiserror from ExecuteReader

Hi. I am executing a stored procedure. The stored procedure raises an error and all I need is to catch this error. Pretty simple, but it only works with an ExecuteNonQuery and not with an Executereader statement. Can anybody explain to me why this happens?

Here's the sp:

CREATE PROCEDURE dbo.rel_test
AS
select 1
raiserror ('My error.', 11, 2)
return
GO

Here's the ASP.Net page:

<% @.Page Language="VB" debug="True" %>
<% @.Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Public Function RunSP(ByVal strSP As String) As SqlDataReader
Dim o_conn as SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
AddHandler o_conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
o_conn.Open
Dim cmd As New SqlCommand(strSP, o_conn)
cmd.CommandType = System.Data.CommandType.StoredProcedure
Dim rdr as SqlDataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
rdr.Close()
cmd.Dispose()
Response.Write(o_conn.State)
End Function
Private Sub OnInfoMessage(sender as Object, args as SqlInfoMessageEventArgs)
Dim err As SqlError
For Each err In args.Errors
Response.Write(String.Format("The {0} has received a severity {1}, state {2} error number {3}\n" & _
 "on line {4} of procedure {5} on server {6}:\n{7}", _
 err.Source, err.Class, err.State, err.Number, err.LineNumber, _
 err.Procedure, err.Server, err.Message))
Next
End Sub
 Sub Page_Load(sender as Object, e as EventArgs)
RunSP("rel_test")
 End Sub
</script>

I thought InfoMessage captured messages with a severity level of 10 or less? If you change your sproc to raise a severity level 9 error will your code work?

Terri|||I was trying to get the severity level 11 til 18. I did receive the messages when they had severity level 1 til 10. That's what I found so weird about it.

I got it solved though. It seems like when using NextResult it will actually raise the error.

I really need to change my mind from ASP to ASP.Net :-/

Thnx!

Catch MSSQL triggers in VB6

Hi
Is there a way to run VB code when a trigger is executed ? Maybe to define a VB event that will occur when a trigger is executed ??
I've tried googling...
Thanks,
Inon.The simplest way to run VB code from a trigger is to create a character mode executable (one with no GUI component). The trigger can start this running on the server using master.dbo.xp_cmdshell calls.

While it is possible to contrive an example where the server launches an application on the client, it is neither easy nor practical. I personally wouldn't try it.

-PatP

catch error that stops query from processing

Is there anyway to catch and error that stops an query from processing?

what I am doing is sending emails with sp_send_dbmail and I am now getting an error saying my attachment is above my limit... I have tries putting the email in a SP to catch the error.. I have tried using try/catch but nothing...

all I want to do is if the attachment (which is the results of a query) is too big just send tan email stating it was too big so I can go check it manually.

Thanks for all the help... not sure if there is anything that can be done.

oh... SQL 2005 enterprise SP2 on Window 2003 SP2.

I presume that you are sending out some form of 'report' or query results.

Perhaps a more reliable and consistant approach would be to always drop the 'reports' into a common folder, and send out emails letting folks know that the report/data is ready to be picked. (Also helps to reduce the impact on the email system.)

|||

It is query results that I am trying to send... and it is only to myself. I am automating as much of my processes as possible. I guess it would be just as easy to have the results sent to file then email myself saying it was done with how many rows were effected...

Is there a way in 2005 to execute an sp with the results going to a file?

|||

William,

What i often will do is have the JOB step write the output to a file, and then in the step that sends the email, I attach the file to the email.

Catch Error message in Variable?

Greetings all,

When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?

When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...

Thanks

Yes there is a way. In fact, its done for you.

The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.

Please reply if any of that needs clarifying.

-Jamie

|||

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

|||Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.

-Jamie

|||

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

|||

Jamie Thomson wrote:

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

Nope, you misunderstood me.

Have a data flow which inserts 1000 rows into a table. One of the rows cannot

go because of a constraint on the column. The following is thrown by SQL /

SSIS:

>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code:

0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL

Native Client" Hresult: 0x80040E2F Description: "The

statement has been terminated.". An OLE DB record is available.

Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F

Description: "The INSERT statement conflicted with the CHECK constraint

"CK_cpxx". The conflict occurred in database "POS_ETL",

table "dbo.cpxx", column 'Col1'.".

>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: The "input "OLE

DB Destination Input" (32)" failed because error code 0xC020907B

occurred, and the error row disposition on "input "OLE DB Destination

Input" (32)" specifies failure on error. An error occurred on the

specified object of the specified component.

>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: The ProcessInput method on component

"OLE DB Destination" (19) failed with error code 0xC0209029. The

identified component returned an error from the ProcessInput method. The error

is specific to the component, but the error is fatal and will cause the Data

Flow task to stop running.

>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: Thread "WorkThread0"

has exited with error code 0xC0209029.

>>>>>>>>>>>>>>>>>>>>>>>>>>

The above is one error with 4 lines explaining what happened. (or not?)

This causes the onerror event to fire 4 times.

This type of behavior is useless for any error handling of this type as

anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work

than it's worth.

OnFail constraint is the simplest and totally ignoring the onerror event

handlers.|||

Hi Jamie/Crispin,

Thanks for your comments. I missed the point that u said.

As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

|||

Thiru_ wrote:

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).

-Jamie

|||

Crispin wrote:

This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.

Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.

Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )

-Jamie

|||

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.

-Jamie

|||

leo1 wrote:

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

I don't know. You have to tell me what your logic is.

Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306

-jamie

|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?

Catch Error message in Variable?

Greetings all,

When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?

When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...

Thanks

Yes there is a way. In fact, its done for you.

The OnError eventhandler has a system variable scoped to it called @.[System::ErrorDescription]. That variable contains the error message of the error that caused the eventhandler to fire.

Please reply if any of that needs clarifying.

-Jamie

|||

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

|||Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

Very simply...no. The OnPostExecute eventhandler gets raised when a container finishes execution. OnError gets raised when a container throws an error. hence, ErrorDescription is not relevant in OnPostExecute.

-Jamie

|||

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

|||

Jamie Thomson wrote:

Crispin wrote:

Thiru,

It would appear you can't. Simplest way to check (I know of) is open the expressions editor for any task and see the variable list.

Jamie:
Thanks. This also made the penny drop.
I remember a while back playing with the onError event and getting irritated because it was firing more than once. When you said about the error, in the progress, you see more than one line for errors. A quick test and the event does indeed fire once per line in the log yet this is actually a single error.

Know of a way to disable this other than maybe on error, disable the parents event handlers which seems rather dirty (If possible)

Something broke, I only want my error handler to fire once so I can fix the error once. Not 5 times (Last one tells me the thread stopped :))

I'm afraid you're rather at the whim of SSIS over this one. If 5 errors are thrown then the OnError will execute 5 times. You can put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though.

-Jamie

Nope, you misunderstood me.
Have a data flow which inserts 1000 rows into a table. One of the rows cannot go because of a constraint on the column. The following is thrown by SQL / SSIS:

>>>>>>>>>>>>>>>>>>>>>>>>>>
[OLE DB Destination [19]] Error: An OLE DB error has occurred. Error code: 0x80040E2F. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The INSERT statement conflicted with the CHECK constraint "CK_cpxx". The conflict occurred in database "POS_ETL", table "dbo.cpxx", column 'Col1'.".
>>>>>>>>>>>>>>>>>>>>>>>>>>

[OLE DB Destination [19]] Error: The "input "OLE DB Destination Input" (32)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (32)" specifies failure on error. An error occurred on the specified object of the specified component.
>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (19) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
>>>>>>>>>>>>>>>>>>>>>>>>>>

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
>>>>>>>>>>>>>>>>>>>>>>>>>>

The above is one error with 4 lines explaining what happened. (or not?)
This causes the onerror event to fire 4 times.
This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.
I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.|||

Hi Jamie/Crispin,

Thanks for your comments. I missed the point that u said.

As Crispin told, it fires the event 4 times for a single error and so I am getting a weird error msg instead of some useful information which is the fist line of the eror msg. So I am checking the variable first and if it is empty, then I am taking value from 'ErrorDescription'. It worked. (But as Crispin told, it is a dirty method, isn't it?)

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

|||

Thiru_ wrote:

Now Jamie, u mentioned that we can use a precedence constraint to handle this situation. Can u pls explain how can we do that?

Thanks.

I was thinking along the lines of using conditional precedence constraints to ensure that you only log the message if certain conditions are met (e.g. System::ErrorDescription has something in it).

-Jamie

|||

Crispin wrote:

This type of behavior is useless for any error handling of this type as anything you do in the handler will fire again and again and again.

I can think of many dirty ways to get around this but they create more work than it's worth.
OnFail constraint is the simplest and totally ignoring the onerror event handlers.

Hmmm...useless you say? Why is it useless that the eventhandler fires again and again? You still got all the information that you need for debugging. And more. Admittedly you may get information that isn't pertinent to you but look at it from this perspective - SSIS is giving you all the information that it can possibly give you in order to debug.

Not convinced? Fair enough...I can understand the frustration (although don't agree with it :) )

-Jamie

|||

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

|||

Thiru_ wrote:

Hi Jamie,

Thanks for your info. Actually I am also looking for this variable. In my case, instead of an 'onError' event handler, I have written 'OnPostExecute'. There I am writing a log file (using a script task) with all job stats like record read, record rejected, etc. I also have to write whether any error occured while executing the package.

When I pass the 'ErrorDescription' as readonly variable and used it in my script, I got the following error:

Error: Failed to lock variable "ErrorDescription" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Can't I use it in 'OnPostExecute'?

Thanks.

No. it is only scoped to the OnError eventhandler. Open up the Variables pane and you will see this for yourself.

-Jamie

|||

leo1 wrote:

JAmin,

How Can I

put conditional precedence constraints into the OnError eventhandler to make sure that everything in it only happens once though. Errordescription will always have some data in it during onError event

I don't know. You have to tell me what your logic is.

Go here for info about conditional precedence constraints: http://www.sqlis.com/default.aspx?306

-jamie

|||Error description is also available on the error output of data flow tasks, no?! Why not catch it there and use it later?