Catching SQL Exceptions for ConnStrings in Web.Config

Hi,

I have a connection string in my web.config - to which I then refer to in my code through all my controls, they're all databound to it.

Anyway - how do I catch any errors - such as when I want to view the website on a train, if I'm working on it.

I don't want it to crash and burn [the site, not the train] - if I dont have access to the sql server.

How can I wrap it in a try block!?- How do i then deal with controls which refer to the connection string?

One solution I thought of - is to programmatically set all the databinding - and not just with the GUI. As that way I can wrap everything in a try{}catch{} block.

Any other - site-wide way of doing this?

Thank you,

R

If it's a connstrings error, then one way would be to try them when the application starts up.

If you are looking for error handling during the select/insert/delete events of a sqldatasource, then check the errors in the selected/inserted/deleted events, and set e.errorhandled property to true (or not if you want the default error handling). Of course, that's not site wide.

|||

i want to capture an error at web.config stage.

so if you have something like this in your web.config:

<add name="DataStoreConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=mehDB;Integrated Security=True;uid=submit;pwd=" providerName="System.Data.SqlClient"/>

then have it produce an error on the site - and not just prevent the website from loading. as the database component althogh significant, only is necessary for logged in users - so not to affect users that are just browsing.

how do i catch the error at such an early stage?

thank you.

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 return codes of SP in ODBC way

Hi,
I use SQLPrepare and SQLExecute functions to execute a stored
procedure, which may return various codes. I dont know how to catch
these return codes in my VC function.
Is there any API defined in ODBC for retrieving the return code?
Thanks
KarthikI got it from another thread....
If you wanted to access the return value from the stored
procedure, you would execute a command string like the following:
SQLCHAR * szSQLStmt = (SQLCHAR*) "{? = call sp_test(?, ?)}";
And do a binding like this:
SQLINTEGER returnVal;
ret = SQLBindParameter(hstmt1, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG,
SQL_INTEGER, 4, 0,
&returnVal, 0, NULL);
After the call to the stored procedure, returnVal will contain the
return
value.

Catching report exceptions

Instead of using the Report Manager that comes with Reporting Services, I
have built a custom ASP.NET report interface that launches reports using URL
access. When there is a problem displaying a report, I get the usual
reporting services error, but I would like to redirect to a custom report
page. How do I do that ?
Thanks, Craig"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A040A3CA-2A55-4D2A-8410-75E2B353566D@.microsoft.com...
> Instead of using the Report Manager that comes with Reporting Services, I
> have built a custom ASP.NET report interface that launches reports using
> URL
> access. When there is a problem displaying a report, I get the usual
> reporting services error, but I would like to redirect to a custom report
> page. How do I do that ?
> Thanks, Craig
You can use web service Render method to catch an error, but if you want to
use URL approach, than
grab your's url output by using System.Net.HttpWebRequest ( or WebRequest)
class.
If the output is text based (HTML, CSV) - just convert returned by
WebResponse byte array to - char - string and check it for specific error
messages. If the output has to be binary (PDF, EXCEL, etc.) -just check
ContentType of your System.Net.WebResponse class and if it is "text/html" -
the error has occured ( you can always parse byte array to be sure as I
proposed above).

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 within a stored procedure

Hi,
Is it possible to set error handling locally to a stored procedure, what
I am trying to do is insert data using a stored procedure and if the
insert fails due to a constraint between the FK of two tables I want to
pass back a friendly message to an ASP page, but what seems to happen is
that SQL handles the error before I can catch it displays the following
message:
INSERT statement conflicted with TABLE FOREIGN KEY constraint
'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
table 'Employee'
So within my SP I am adding the following after my insert statement:
IF @.@.ERROR<>0
BEGIN
SELECT' WRONG SEC'
END
Then in my asp page I use the following after my execute command:
if objRS(0) = "WRONG SEC" Then
SEC_ERROR = 1
end if
I am no expert in SQL but my guess is SQL is taking control of the error
which is why this select doesn't work.
Any ideas?
May thanks
Peter
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi Peter.
You cannot suppress the errors being thrown from SQL Server. Although you
can sometimes handle them by checking @.@.error after statements, this doesn't
always work and the circumstances in which it does / doesn't is not clearly
covered in the SQL Server documentation.
TSQL error handling problems have been discussed extensively in these
newsgroups over the years but Erland Sommarskog took the time to write up a
good guide to this confusing topic a few months back. You can and should
read his article which you can find here:
http://www.sommarskog.se/
In short though - you'll need to catch and interpret the sql error in your
application layer. If you're using asp, how you code this will depend on
your scripting language. If Javascript, you'll use try / catch. If VBScript,
you'll use On Error Continue / Goto. If ASP.Net, try / catch in c#, vb, c.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uruFNv02DHA.2308@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi,
> Is it possible to set error handling locally to a stored procedure, what
> I am trying to do is insert data using a stored procedure and if the
> insert fails due to a constraint between the FK of two tables I want to
> pass back a friendly message to an ASP page, but what seems to happen is
> that SQL handles the error before I can catch it displays the following
> message:
> INSERT statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
> table 'Employee'
> So within my SP I am adding the following after my insert statement:
> IF @.@.ERROR<>0
> BEGIN
> SELECT' WRONG SEC'
> END
>
> Then in my asp page I use the following after my execute command:
> if objRS(0) = "WRONG SEC" Then
> SEC_ERROR = 1
> end if
> I am no expert in SQL but my guess is SQL is taking control of the error
> which is why this select doesn't work.
> Any ideas?
> May thanks
> Peter
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

---

Catching errors within a stored procedure

Hi,
Is it possible to set error handling locally to a stored procedure, what
I am trying to do is insert data using a stored procedure and if the
insert fails due to a constraint between the FK of two tables I want to
pass back a friendly message to an ASP page, but what seems to happen is
that SQL handles the error before I can catch it displays the following
message:
INSERT statement conflicted with TABLE FOREIGN KEY constraint
'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
table 'Employee'
So within my SP I am adding the following after my insert statement:
IF @.@.ERROR<>0
BEGIN
SELECT' WRONG SEC'
END
Then in my asp page I use the following after my execute command:
if objRS(0) = "WRONG SEC" Then
SEC_ERROR = 1
end if
I am no expert in SQL but my guess is SQL is taking control of the error
which is why this select doesn't work.
Any ideas?
May thanks
Peter
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi Peter.
You cannot suppress the errors being thrown from SQL Server. Although you
can sometimes handle them by checking @.@.error after statements, this doesn't
always work and the circumstances in which it does / doesn't is not clearly
covered in the SQL Server documentation.
TSQL error handling problems have been discussed extensively in these
newsgroups over the years but Erland Sommarskog took the time to write up a
good guide to this confusing topic a few months back. You can and should
read his article which you can find here:
http://www.sommarskog.se/
In short though - you'll need to catch and interpret the sql error in your
application layer. If you're using asp, how you code this will depend on
your scripting language. If Javascript, you'll use try / catch. If VBScript,
you'll use On Error Continue / Goto. If ASP.Net, try / catch in c#, vb, c.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:uruFNv02DHA.2308@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is it possible to set error handling locally to a stored procedure, what
> I am trying to do is insert data using a stored procedure and if the
> insert fails due to a constraint between the FK of two tables I want to
> pass back a friendly message to an ASP page, but what seems to happen is
> that SQL handles the error before I can catch it displays the following
> message:
> INSERT statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS',
> table 'Employee'
> So within my SP I am adding the following after my insert statement:
> IF @.@.ERROR<>0
> BEGIN
> SELECT' WRONG SEC'
> END
>
> Then in my asp page I use the following after my execute command:
> if objRS(0) = "WRONG SEC" Then
> SEC_ERROR = 1
> end if
> I am no expert in SQL but my guess is SQL is taking control of the error
> which is why this select doesn't work.
> Any ideas?
> May thanks
> Peter
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Catching errors in SSIS Backup Database Task

Hi,

In my SSIS package, I have a backup database task. When I run the package with DestinationAutoFolderPath set to a folder ("Network Service" account has full permission on this folder) and DestinationCreationType set to Auto, the task works just fine creating a backup with its own name. (similar to database_date<count>).

But what I want is in my front-end I am allowing the user to specify the name of the backup file. So I want the task to create the backup file in the name I supply. I set the DestinationCreationType to manual and in the application code added the DestinationManualList with the path from the UI.

Now the pacakge runs fine but does not take any backup. There is no errors as well. If I set the FailPackageOnFailure and FailParentOnFailure to true, then I am getting the DTSExecResult.Failure but I am not getting the actual error from the backup database task.

Am I missing anything here?

Thanks in advance,
Srikanth.

How are you executing the package?

What does the logfile (I assume that you have one) say?

-Jamie

|||

Thanks a lot for the reply.

I enabled the log in package and the backup database task. The following is the text appears in the log file:

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,(null)
PackageStart,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,Beginning of package execution.

Diagnostic,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,Based on the system configuration, the maximum concurrent executables are set to 3.

OnPreExecute,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:28 AM,8/29/2006 11:31:28 AM,0,0x,(null)
PackageEnd,SERVER211,SERVER211\Administrator,DICoreDataPackage,{8B470140-EB79-4A15-A2A7-10590065310C},{844D2D28-16D8-42C8-9E1F-3E1506544BE5},8/29/2006 11:31:29 AM,8/29/2006 11:31:29 AM,1,0x,End of package execution.

The thing is, I configured the DestinationCreationType to "manual" and added my backup file name through coding to DestinationManualList ArrayList:

if (backupTask.DestinationManualList == null)
backupTask.DestinationManualList = new ArrayList();

backupTask.DestinationManualList.Add(m_SSISArgs.DatabaseBackupPath);

The objective is to create backup of the database with the file name I am giving. Do I have to set anyother property for this...

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.

Catching an exception from a Web service task

Hi!

I am quite new using SSIS and I have a problem with catching an (SOAP) exception from a Web service task. Some times my web service task can fail and when the web service is failing, it is throwing an exception. When the task succeeds the result is being put into a variable, That part is not a problem.

But catching an exception is. I have tried to use a script task and tried to get exception from the dts object model. I have not yet succeeded on that. But it might be a possible way to go. A different approach might be creating an OnError event on my web service task which I can create a task when triggered. But I have not found any solution yet and I hope some people out there have done this before or have a solution on this.

Regards

Geir F

I forget to tell that I want the error description text from the exception that is thrown from the web service task because I want to log the errors. So what I hope is to have a script task that executes when web service fails and to write some code to catch the error description.

Current status on this is that the script task executes when the WS fails (red arrow). Nice. The web service returns an object, say TestOutput variable. When the WS succeeds, I can convert the object into a integer datatype and catch the value. But when it fails, I don't know to catch the error description. When executing the package, I can see the error description on th progress tab, so there must be a way for me to get the error too :-)

Regards

GF

Catching a Return from SQL Stored Procedure

Hi All

Here is my SP

ALTER PROCEDUREdbo.InsertPagerDays

@.ReportEndDatedatetime,

@.PagerDaysint,

@.UserIDvarchar(25)

AS

IF EXISTS

(

-- you cannot add a pager days more than once per report date

SELECTReportEndDate, UserIdfromReportPagerDayswhereReportEndDate = @.ReportEndDateandUserId = @.UserID

)

Return1else

SET NOCOUNT OFF;

INSERT INTO[ReportPagerDays] ([ReportEndDate], [PagerDays], [UserID])VALUES(@.ReportEndDate, @.PagerDays, @.UserID)

RETURN

My Question is, this SP will not let you enter in a value more than once (which is what i want) but how do I write my code to inform the user? Here is my VB code becuase the SP does not error out (becuase it works it acts as if the record updates)

How can I catch the Return 1

'set parameters for SP

Dim cmdcommand =New SqlCommand("InsertPagerDays", conn)

cmdcommand.commandtype = CommandType.StoredProcedure

cmdcommand.parameters.add("@.ReportEndDate", rpEndDate)

cmdcommand.parameters.add("@.PagerDays", PagerDays)

cmdcommand.parameters.add("@.UserId", strUserName)

Try

'open connection here

conn.Open()

'Execute stored proc

cmdcommand.ExecuteNonQuery()

Catch exAs Exception

errstr =""

'An exception occured during processing.

'Print message to log file.

errstr ="Exception: " & ex.Message

lblstatus.ForeColor = Drawing.Color.Red

lblstatus.Text ="Exception: " & ex.Message

'MsgBox(errstr, MsgBoxStyle.Information, "Set User Report Dates")

Finally

If errstr =""Then

lblstatus.ForeColor = Drawing.Color.White

lblstatus.Text ="Pager Days Successfully Added!"

EndIf

'close the connection immediately

conn.Close()

EndTry

You need to add a parameter with ParameterDirection.ReturnValue - seehere for more info.

Catching a General Exception

I am trying to write a query that I only want to run on sql server 2005 databases. If a server isn't 2005, it will throw an exception. I would like to catch this general exception. Here is the query...

DECLARE @.Server [nchar] (100)
SET @.Server = (CONVERT(char(100), (SELECT SERVERPROPERTY('Servername'))))

INSERT INTO [tempdb].[dbo].[User_Auditing] (Server, UserName, WinAuth, SQL_Auth_UserName, PassPolicyOn)
SELECT @.Server, s.name, isntuser, q.name, is_policy_checked
FROM sys.syslogins s FULL OUTER JOIN sys.sql_logins q
ON (s.name = q.name)

The errors I would get are as follows...

Msg 208, Level 16, State 1, Line 4
Invalid object name 'sys.syslogins'.
Msg 208, Level 16, State 1, Line 4
Invalid object name 'sys.sql_logins'.

I know in Java, I would just put a try before the declare and a catch("Invalid object name") after the statement, however, I'm not sure if this is even possible in T-SQL. Thanks for any help.
-Kyle

Nope. You cannot. 2005 introduces the concept of try...catch, but it wouldn't catch this error.

You could do something like

if @.@.version like 'Microsoft SQL Server 2005%'

exec ('select ''this is 2005''')

else

exec ('select ''this is NOT 2005''')

I used @.@.version since it will work on any version of SQL Server. Using serverproperty() is another possibilty.