Error Catch In SQL

Jul 20, 2005

Hi everyone, I am using an SQL extended stored procedure to send emails in a
DTS package using a cursor that goes through each row in a table.

Email sending code below
======================
exec master.dbo.xp_smtp_sendmail
@FROM = @sFrom,
@FROM_NAME = @sFrom,
@TO = @sRecepients,
@subject = @sSubject,
@message = @sBody,
@type = N'text/html',
@codepage = 0,
@server =N'MYMAILSERVER'
======================
Fetch Next From EmailCursor ...

Now the problem I have is that if an individual email address in invalid
then an error occurs and the whole DTS package falls over. What I would like
to be able to do is "catch the error", something like this (C# code used as
example)

try
{
exec master.dbo.xp_smtp_sendmail
@FROM = @sFrom,
@FROM_NAME = @sFrom,
@TO = @sRecepients,
@subject = @sSubject,
@message = @sBody,
@type = N'text/html',
@codepage = 0,
@server =N'MYMAILSERVER'
} catch {
exec master.dbo.xp_smtp_sendmail
@FROM = "arealaddress@mybusiness.com",
@FROM_NAME = @sFrom,
@TO = @sRecepients,
@subject = @sSubject,
@message = @sBody,
@type = N'text/html',
@codepage = 0,
@server =N'MYMAILSERVER'
}

Is this possible??? Normally I would do all the email validation before the
email is entered into the database but unfortunately, I do not have access
to the application code so I am stuck doing it this way.

Thanks in advance
Mark

View 2 Replies


ADVERTISEMENT

Try Catch Doesn't Catch Errors Inside A Data Flow Transformation Script Component

Feb 15, 2007

Hi,

I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:

a) Why doesn't my Catch block catch the exception?
b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?

Hope someone could help.

View 3 Replies View Related

Attempt To Catch Error Using @@ERROR In SQL 2000 Is Failing

May 2, 2007

We have a stored procedure that calculates the floor nr for users at our company campus using their office location. The calculation is done by a function that returns an integer. Unfortunately, not all users enter their information correctly so the function sometimes raises an error. Below is the code of that stored procedure.



UPDATE PERSONS

SET FLOORNR = dbo.FloorNR(OFFICELOCATION)

WHERE OFFICELOCATION IS NOT NULL



IF(@@ERROR <> 0 OR @@ROWCOUNT = 0)

BEGIN

RAISERROR ('Failed to calculate the floor number', 16, 1 ) with nowait

END



However, when the function dbo.FloorNR fails, it doesn't raise our error, but it seems to raise the error that comes from dbo.FloorNR.



How can we catch errors that come from dbo.FloorNr so that we can raise our own error? Our company still uses SQL 2000, so we cannot use the SQL 2005 try/catch option.

View 3 Replies View Related

SP Catch Error

Aug 21, 2007

Hi,

I have the following stored procedure which is added to the windows scheduler. When it is run I'm getting an "Invalid use of null error", therefore I need to capture and any errors but I'm not sure how to do this. Can someone help?
Thanks.


Code:

CREATE PROCEDURE [dbo].[usp_Reminders] AS

DECLARE @ReminderSent datetime
DECLARE @getRecords CURSOR
DECLARE @err int

SELECT E.[RL Staff No], E.Forename, E.Surname, C.CourseName, V.CourseDate , E.Email, V.ReminderSent
FROM empdetails.dbo.v_Employee E
INNER JOIN Validation V ON E.[RL Staff No] = V.[RL Staff No]
INNER JOIN empdetails.dbo.v_Course C ON V.CourseCode = C.CourseCode
WHERE V.Completed Is Null AND V.ReminderSent Is Null AND V.CourseDate <= dateadd(dd, -3, getdate())
order by e.[rl staff no]

SET @getRecords = CURSOR FOR

SELECT V.ReminderSent
FROM empdetails.dbo.v_Employee E
INNER JOIN Validation V ON E.[RL Staff No] = V.[RL Staff No]
INNER JOIN empdetails.dbo.v_Course C ON V.CourseCode = C.CourseCode
WHERE V.Completed IS NULL AND V.ReminderSent IS NULL AND V.CourseDate <= dateadd(dd, -3, getdate())
ORDER BY e.[rl staff no]

OPEN @getRecords
FETCH NEXT FROM @getRecords INTO @ReminderSent
WHILE @@FETCH_STATUS= 0
BEGIN
UPDATE Validation
SET ReminderSent = GetDate()
WHERE CURRENT OF @getRecords
FETCH NEXT FROM @getRecords INTO @ReminderSent
END

CLOSE @getRecords
DEALLOCATE @getRecords
GO

View 1 Replies View Related

Error Handling With Try/catch

Feb 6, 2008

Hey,Here's yet another question for you more knowledgeable than me Up to this point I have been using a try/catch statement when dealing with SQL, for exampleint result = 0;try{result = Int32.Parse(command.ExecuteScalar().ToString());}catch{result = 0;}But I read up one some error handling and I have no idea how to solve this anymore. Since I'll be using the catch block to catch exceptions, something like thiscatch (SqlException){throw;}So I was wondering what is the good, standard practice for dealing with this since I need the catch statement to set result to zero or I would end up with an error.Thanks in advance,Sixten 

View 2 Replies View Related

SP Error Handling And Try/Catch

May 9, 2006

I'm looking for a discussion of the pros and cons of using TRY/CATCH as an error handler in a standard fashion with Stored Procedures.  I've been using SQL Server for some time, and am accustomed to an approach of error handling in SPs that returns control from an SP via a common exit routine. 

Has anyone defined an approach they would consider sharing?  I realize that the reasons for standardizing on an approach includes requirements to your specific situation, so there are likely no perfect answers.  I understand the basics of TRY/CATCH in SQL Server. Specifically, I'm looking for an approach where a common "Catch Handler" is used, paying attention to the issues around COMMIT/ROLLBACK.

If there are any threads that discuss this, let me know; I've performed a seach and found nothing so far.

Thanks,

Chuck

View 4 Replies View Related

Catch The Error On Insert Or Update (was Need Help)

Jan 5, 2005

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 statement

View 1 Replies View Related

How To Catch Date Conversion Error

Mar 8, 2004

hi,

I try to write a function which includes a statement:

SELECT @dateReturn = CAST(@dateString As datetime)

to convert a string to datetime.

When it runs, sometime it will generate :
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
and all related are terminated.

Can I find a way to catch this error and don't let it terminate the whole thing? For
example, when this happens, I want to get datetime as NULL instead of just being
terminated.

Thanks.

View 1 Replies View Related

Catch Error Message In Variable?

May 24, 2006

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

View 14 Replies View Related

Check Referential Ingerity Or Catch Error

Oct 6, 2004

Hi all,

Just wondering what would be the normal or more efficient practice to insert/update a record.

1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling

My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @err = @@ERROR" after the insert statement.

so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.

Any advise would be appreciated..

Cyherus

View 2 Replies View Related

How To Catch Error And Retry AFTER Dts Script Step Has Ran

Jan 28, 2004

hello,

i am trying to figure out how to check for failure or success AFTER the script task has ran.

its a piece of cake to write script logic that runs before the task but how do i check things and decide to retry AFTER a script task has ran?

i want to check for an error after a large table replication and if it detects that there was an error i want to RETRY.

dts does not seem to have this one specific piece of functionality. am i overlooking something?

View 1 Replies View Related

Compile Error While Using Try Catch In SQL Server 2005

Mar 18, 2008



I am trying to use a simple BEGIN TRY and END TRY in my SP. It is giving a compile time error such as
Line 13: Incorrect syntax near 'try'.

Why is this, can somebody help me out. Yes i am sure it is SQL Server 2005 on my machine.

View 9 Replies View Related

Transact SQL :: Program To Catch Transaction Raised Error At The Very End

Aug 11, 2015

I am new to T-SQL programming. I need to write a main procedures to execute multiple transactions. How could I structure the program so that each transaction will not abort if failed. Instead, the next transaction will keep running. At the very end the procedure will output the error and report them back to the main program in the output parameters. Looking for pseudo code.

View 6 Replies View Related

Begin Catch Block Executed When Try Completes Without Error

Dec 10, 2007



I was just debugging a stored procedure visual studio and I was surprised with what I was watching.

I'm running SQL 2005 and visual studio 2005. I have a set of nested try/catch blocks.

It fails on the first try and drops into the catch block just as it should. Within the catch block a dynamic sql statement is created. A try block begins and attempts to exec(@sql). It then drops to the catch block. However, I copy the statement into SQL Management Studio and it runs without error everytime.

Why is it dropping into the catch block? Logically it should just complete executing the statement continue without going into the catch block.

Do I have to reset the raiserror values between try blocks or something?



--Thanks--



View 1 Replies View Related

Query Timeout Expired.... Message 7412. How To Catch This Error??

Oct 26, 2007

Hi All,

I have the same question and error that Chopaka is getting:

"I have a SQL 2005 job that calls a stored proc. The job step returns the message "Query timeout expired....Message 7412...The step succeeded." The proc never actually ever did anything due to the query timeout, and the job continued on to other steps. I'm going to address the timeout issue eventually, but first I'd like to trap the timeout problem and force the job to end.

It appears that the query timeout isn't really an error, just a message. I've tried TRY-CATCH in the SP but the situation isn't caught, again probably due to the interpretation that it isn't an error."


I have reduced the "Remote Query time out" to 1 sec, in order to catch the error and to prevent the job from running, but the error is not caught.


Is there a way to catch this in the SQL or in the job step to prevent the job from continuing?

This is the script that I'm using without any luck
BEGIN TRY
BEGIN Transaction
Create table #tmpSummaryTable
(
)
insert into #tmpSummaryTable
select * from CDRServer01.iXtemp.dbo.gx_tbFTRSummary_test

COMMIT Transaction
END TRY

BEGIN CATCH
DECLARE @err int
SELECT @err = @@error
PRINT '@@error: ' + ltrim(str(@err))

SELECT ERROR_NUMBER() ERNumber,


ERROR_MESSAGE() Error_Message
ROLLBACK
Return

END CATCH

View 3 Replies View Related

How To Stop Execution Of Stored Procedure If Error Occurs In Try/catch Block

Mar 3, 2008



Hello, I have stored procedure that when executed it will check to see if a given name is found in the database, if the name is found, I would like to have it continue on to do its work, however if the name is not found, I would like it to raise an error and then stop execution at that point, however, the way it is currently working is, if the name is not found, it catches the error, raises it and then continues on and tries to do its work, which then it bombs out because it can't. I wasn't sure if there was a way to stop the execution of the procedure in the catch statement. I don't think I want to raise the error level to 20-25 because I don't want to drop the connection to the database per say, I just want to halt execution.

Here is a simple example of what I have:




Code Snippet
begin try

if not exists (select * from sys.database_principals where [name] = 'flea')

raiserror('flea not found', 16, 1)
end try
begin catch

declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
select

@ErrorMessage = error_message(),
@ErrorSeverity = error_severity();
raiserror(@ErrorMessage, @ErrorSeverity, 1);
end catch
go

begin

print 'hello world'
end






At this point, if the user name (flea) is not found, I don't want it ever to get to the point of 'Hello World', I would like the error raised and the procedure to exit at this point. Any advice would be appreciated on how to best handle my situation!

Thanks,
Flea

View 5 Replies View Related

How To Catch An Error Raised In The Forach Loop Container And Perform Cleanup Jobs Accordingly?

Nov 29, 2007

Hello everyone,

I have a package that should accomplish the following task:
- Loop on all files in a given directory.
- Before proccessing the current file in the Enumerator, it should be moved to a folder called "importing"
- Load the content of the file into a destination DB.
- After a successfull load, the file is moved from the "importing" to the "success" folder.
- If anything went wrong during the load process, the file should be moved from the "importing" to the "error" folder and an e-mail should be sent out to a certain admin account.

What I have done so far:
My control flow looks like this:
Foreach Loop containing the following tasks:
- File System Task: moving the current file from its original path to the "importing" folder
- Data Flow Task1: performing some transformations and insertions into the DB and raw files.
- Data Flow Task2: performing some transformations and insertions into the DB and raw files.
- Data Flow Task3: performing some transformations and final insertions into the DB.
- File System Task: moving the current file from the "importing" to the "success" folder

Question:
I do not know how to catch the event that one of my three Data Flow Tasks has failed and in this case perform two simple tasks, namely...
- File System Task: moving the current file from the "importing" to the "error" folder
- Send Mail Task: sending a configured e-mail message to a ceratin administrator.

Thanks in advance...

Regards,
Samar

View 8 Replies View Related

Try Catch Does Not Catch Exception

May 15, 2007

hi all,



All of a sudden my application started crashing when trying execute dml statements on sql server mobile database (sdf file). Frustating thing is that whole application crashes without any error message. this happens for all kinds for DML statement but not all the time. Sometimes it would fail for delete statement such as delete from table; or for insert into statement



my problem catch does not catch the error. There is no way to find out teh what is causing this error



SqlCeConnection sqlcon = new SqlCeConnection("

Data Source = '\Program Files\HISSymbol\HISSymboldb.sdf';"

);

SqlCeCommand sqlcmd = new SqlCeCommand();

sqlcmd.CommandText = Insert into company('AA', 'Lower Plenty Hotel');

sqlcmd.Connection = sqlcon;

SqlCeDataReader sqldr = null;

try

{

sqlcon.Open();

//use nonquery if result is not needed

sqlcmd.ExecuteNonQuery(); // application crashes here



}

catch (Exception e)

{

base.myErrorMsg = e.ToString();



}

finally

{

if (sqlcon != null)

{

if (sqlcon.State != System.Data.ConnectionState.Closed)

sqlcon.Close();

sqlcon.Dispose();

}

}//end of finlally

View 4 Replies View Related

Try Catch

Jan 15, 2007

Hi

for MS SQL 2000
how can I do ?:


INSERT INTO [Users] (Name)
SELECT Names FROM OtherUsers


I am having an UNIQUE INDEX on [Users].Name

how can I avoid an error ?
if the [Users].Name allready exists I want to jump over the error and keep on inserting

thank you for helping

View 14 Replies View Related

TRY CATCH

Apr 22, 2008

Is it any better than using @@ERROR?

It doesn't seem so

Any opinions?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

View 4 Replies View Related

Try Catch

May 13, 2008

Hi!

I'm trying to use Try Catch together with transaction handling but it seems like it doesn't do Rollback although I produce an error.

It looks something like this:


DECLARE @soID INT
BEGIN TRY
BEGIN TRANSACTION
UPDATE Serviceobjekt
SET ServiceobjektstypID = 13
WHERE ServiceobjektID = 26555

UPDATE .... 2 more tables

INSERT INTO Serviceobjekt (
Namn...)
VALUES ('XXXX')

SET @soID = @@IDENTITY
INSERT INTO Atgard (
Namn, ServiceobjektID)
VALUES ('sssss',@soID)

COMMIT TRANSACTION
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
END CATCH


I get after changing some id's in the where clause which I know is wrong I get a result like this:

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

Shouldn't it be 0 rows affected on all if just one update or insert statement is wrong?

/Magnus

View 4 Replies View Related

Try And Catch

May 22, 2008

Hi I use Try catch and transactions in one of my stored procedures.

Inside I make a call to another stored procedure.

Is it possible to roll back the outer sp?

Please advise.

View 1 Replies View Related

Catch Me If You Can / Bug In The Bug?

Oct 7, 2007

Hello!
Try this (-:

BEGIN TRY

SELECT *

FROM NonExistentTable ;

END TRY

BEGIN CATCH

PRINT 'catch me if you can' ;

END CATCH ;

/*

results in:

Msg 208, Level 16, State 1, Line 2

Invalid object name 'NonExistentTable'.

statement not printet */

BEGIN TRY

BEGIN TRY

SELECT *

FROM NonExistentTable ;

END TRY

BEGIN CATCH

PRINT 'catch me if you can' ;

END CATCH ;

END TRY

BEGIN CATCH

PRINT 'catched?'

END CATCH ;

-- same behaviour

CREATE PROC catchme

AS

BEGIN

BEGIN TRY

SELECT *

FROM nonexistingtable ;

END TRY

BEGIN CATCH

PRINT 'catch me if you can'

END CATCH ;

END ;

BEGIN TRY

EXEC catchme ;

END TRY

BEGIN CATCH

PRINT 'catched' ;

END CATCH ;

-- prints "catched"

BEGIN TRY

EXEC catchnonexistingproc ;

END TRY

BEGIN CATCH

PRINT 'catched again' ;

SELECT error_state()

END CATCH ;

-- prints "catched again"





BEGIN TRY

RAISERROR ('catch this' , 10 , 1) ;

END TRY

BEGIN CATCH

PRINT 'hello' ;

END CATCH ;

-- no hello

BEGIN TRY

RAISERROR ('catch this' , 11 , 1) ;

END TRY

BEGIN CATCH

PRINT 'hello' ;

END CATCH ;

-- prints hello



CREATE PROC catchme2

AS

BEGIN

BEGIN TRY

RAISERROR ('catch this' , 10 , 1) ;

END TRY

BEGIN CATCH

PRINT 'CATCH ME IF YOU CAN' ;

END CATCH ;

END ;



BEGIN TRY

EXEC catchme2 ;

END TRY

BEGIN CATCH

PRINT 'gotcha?' ;

END CATCH ;

-- prints only "catch this"





CREATE PROC catchme3

AS

BEGIN

BEGIN TRY

RAISERROR ('catch this' , 20 , 1) ;

END TRY

BEGIN CATCH

PRINT 'CATCH ME IF YOU CAN' ;

END CATCH ;

END ;

BEGIN TRY

EXEC catchme3 ;

END TRY

BEGIN CATCH

PRINT 'gotcha?' ;

END CATCH ;

-- prints "CATCH ME IF YOU CAN" ;-)

See also
-- ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm

The above behaviour is as described in BOL, but
comparing to other languages such as Java, C#, PL/SQL
I would expect that catch always catch unless you take the power from the machine, special
in the case for "catchnonexistingproc". Remark the different behaviour compared to "nonexistingtable"
Documentation shows not example for non existing proc!
That catch doesn't catch "is as bug". So is the catch of non existing proc a bug in the bug? ;^)
I hope not, because it is the behaviour we want...

Greetings




View 7 Replies View Related

Try And Catch Problem

Feb 26, 2007

I want to use a try/catch to catch any error with this datareader so if no data then exit sub, eventually I will create custom error pagebut for now I just want to exit sub so following sql statements are not executed. I get the following problem  when I try to close connection.this was not a problem before using try and catch, do I have in right place? Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load        'declare vars        Dim iPreqid As String = CStr(Session("iPreqid"))                Dim sql As String = "select  *  from Vrequest where req_id = '" & iPreqid & "'"        'Connection String  value        Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString        'Create a SqlConnection instance        Using myConnection As New SqlConnection(conn)            Dim myCommand As New SqlCommand(sql, myConnection)            'declare sqldatareader            Dim dbReader As SqlDataReader            Try                myConnection.Open()                myCommand.ExecuteNonQuery()                dbReader = myCommand.ExecuteReader                'Initialize reader                dbReader.Read()                'assign reader index values using column names to controls                Label8.Text = CStr(dbReader("User_Name"))                Label11.Text = CStr(dbReader("NHI"))                Label9.Text = CStr(dbReader("Event_Number"))                Label10.Text = CStr(dbReader("ACC_Number"))                Label7.Text = CStr(dbReader("Required_Date"))                Label1.Text = CStr(dbReader("Title"))                Label3.Text = CStr(dbReader("address1"))                Label4.Text = CStr(dbReader("Birth_date"))                Label2.Text = CStr(dbReader("surname"))                Label5.Text = CStr(dbReader("home_phone"))                Label21.Text = CStr(dbReader("firstname"))                Label20.Text = CStr(dbReader("delivery"))                Label22.Text = CStr(dbReader("ward"))                Label23.Text = CStr(dbReader("request_date"))            Catch ex As Exception                Session("exception") = ex                exit sub            End Try        End Using        'if connection not closed then close        If (Not conn Is Nothing) Then            myConnection.Close()<---------------------------------------->Name 'myconnection is not declared'            myConnection.Dispose()<---------------------------------------->Name 'myconnection is not declared'        End If

View 4 Replies View Related

Try,Catch And Response

Jul 2, 2007

Hi,
Here is the code:Try
If DS.Tables(0).Rows.Count = 0 ThenResponse.Write("Cannot delete an unexist row!")
Response.End()
End If
DS.Tables(0).Rows(0).Delete()
Catch BUG as Exception
Response.Write(Bug.Message)
End Try
I removed all the connection stuff..
What I don't understand is why if ds.table(0).rows.count is zero I still get Bug.Message? I did Response.End ...
Thank you.

View 11 Replies View Related

Help Using Try Catch Exception Please

Mar 25, 2008

  Protected Sub detailsview1_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs)
Dim label2 As Label = CType(detailsview1.FindControl("label2"), Label)
Try

Catch sqlEx As SqlClient.SqlException
If sqlEx.Message.Contains("DELETE statement conflicted with COLUMN REFERENCE") Then
label2.Visible = True
label2.Text = "You cannot delete this Agent Type as it has a call weighting assigned to it, remove the weightings before you try to delete it"
e.Cancel = True
End If
End Try
End Sub Hi, Im using vb.net sql2005 and visual studio 2005
I have 2 tables which have a foregin key relationship.  When i try to delete information from within one of my aspx pages it rightly comes up with an application errror, something along the lines of
 DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_callScore_agentType'. The conflict occurred in database 'Merlin_####', table 'callScores', column 'typeID'.The statement has been terminated.
I have looked around and can see people talking about using a try catch excpetion however i need to know how id implement this using the detailsview1_itemdeleting event. Ive never used this before and havent found a decent tutorial to help.So far i have this code but im stuck as im not sure that this is correct but more importantly what i put in the try method. Protected Sub detailsview1_ItemDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewDeleteEventArgs)
Dim label2 As Label = CType(detailsview1.FindControl("label2"), Label)
Try
(WHAT GOES HERE)
Catch sqlEx As SqlClient.SqlException
If sqlEx.Message.Contains("DELETE statement conflicted with COLUMN REFERENCE") Then
label2.Visible = True
label2.Text = "You cannot delete this Agent Type as it has a call weighting assigned to it, remove the weightings before you try to delete it"
e.Cancel = True
End If
End Try
End Sub Your help would be greatly appreciated

View 12 Replies View Related

Try.. Catch Block

Dec 11, 2006

HI,
i'm trying to execute some sql using the Try.. Catch blocks.

Following code does not execute in Catch Block

Begin
begin try
insert into dbo.Test values (1,'aaa')
-- here we are inserting int value in identity field...
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO


Whereas the following block works fine and the Catch block executes.

Begin
begin try
Select 1/0
--This causes an error.
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO





Any idea why is it so?

View 5 Replies View Related

Try..catch Statement

Apr 13, 2008

can someone help me how to corect the query,


BEGIN TRY
INSERT INTO ORDERS VALUES( 1122,'BAC123' );
INSERT INTO ORDLINE VALUES( 1122,991,1 );
PRINT 'VERY GOOD'
END TRY
BEGIN CATCH
PRINT 'FALSE'
INSERT INTO LOGTAB ('BAC123',1122,991,1, ERROR_NUMBER,ERROR_MESSAGE);
END CATCH


i am getting error:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'ERROR_NUMBER'.

View 2 Replies View Related

Try Catch Syntax

Mar 14, 2007

hi there i have 2 questions about the TRY/CATCH statement that i have written below. any advice from more experienced persons would be highly appreciated. thankyou.

1. re the @ErrorMessage variable, i have seen this declared with a datatype of varchar(MAX), nvarchar(2048) and nvarchar(4000). which is the correct one for me to use?

2. do i need the statement 'RETURN @ErrorCode' at the end or is this redundant?

BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

DECLARE
@ErrorCode int,
@ErrorMessage nvarchar(4000),
@ErrorSeverity int

SELECT
@ErrorCode = @@ERROR,
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY()

IF EXISTS (SELECT *FROM Users WHERE Username = @Username)
RAISERROR('The username already exists.', 15, 1)
ELSE IF EXISTS (SELECT * FROM Users WHERE Email = @Email)
RAISERROR('The email already exists.', 15, 1)
ELSE
RAISERROR(@ErrorMessage, @ErrorSeverity, 1)

RETURN @ErrorCode

END CATCH

END

View 2 Replies View Related

How To Try-catch 'sp_addrolemember'?

Dec 27, 2006

how to try-catch sp_addrolemember?

View 4 Replies View Related

How To Catch An Exception?

Jul 20, 2005

Is there something like exception handling in T-SQL?For example, how to catch an error of convertion at thissample:CREATE PROCEDURE SP@param VARCHAR(50)AS BEGINDELCARE @var INT-- try {SET @var = CONVERT( int, @param)-- } catch (error#245) {-- handle an error right here-- }ENDIt must be invisible for a caller of SP if something wrong inside SP.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 4 Replies View Related

Use Of TRY/CATCH: Architecture

Oct 20, 2007

Hi there,
My question is more "architectural" than technical.

One of our standard is to systematically enclose the code of our stored proc within a TRY/CATCH block.

What is your point of view?
Should a TRY/CATCH be included even for simple operations?
Are there any drawbacks?

Thanks!

EDIT: Thanks for your input.

View 4 Replies View Related

Try...catch With Transactions

Apr 28, 2008

I have 3 procedures where the first one calls the second one and the second one calls the third one. All procedures have try..catch blocks and transactions. In the third procedure I declare an INT variable and try to give it the value 'A', to create an error. This makes the code in the catch block to run. In the catch block I rollback the transaction and return 1. It then returns to the 2nd procedure, but here I get the exception "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 0. Is there I way to keep running the code in the second procedure without getting this error? I take care of the return value from the 3rd procedure which is 1 so I know that there was an error in the third procedure but I want to run the code below the procedure call to the 3rd procedure in the second procedure, is there I way to do this?

I tried to have a transaction only in the first procedure but then I get this error "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

What shoule I do to continue running the code in procedure 2 and procedure 1 after the procedure calls? In real life I have another scenario but the code below is just an easy example that creates the same error that my real procedures create.

Procedure 1:





Code Snippet
ALTER PROCEDURE [dbo].[ProcFirst]
AS
BEGIN

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

DECLARE @return int
BEGIN TRANSACTION




EXEC @return = [dbo].[ProcSecond]
IF @return <> 0
BEGIN

SELECT 'return value <> 0 i ProcFirst'
RETURN 1
END
IF (XACT_STATE()) = 1 COMMIT TRANSACTION
RETURN 0

END TRY
BEGIN CATCH -- Error-handling statements


-- End Transaction
IF @@TRANCOUNT > 0 BEGIN

IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION;
IF (XACT_STATE()) = 1 COMMIT TRANSACTION;
END


SELECT 'catch ProcFirst', @ErrorMessage


RETURN 1

END CATCH
END






Procedure 2:




Code Snippet
ALTER PROCEDURE [dbo].[ProcSecond]
AS
BEGIN

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

DECLARE @return int
BEGIN TRANSACTION




EXEC @return = [dbo].[ProcThird]




IF @return <> 0
BEGIN

-- End Transaction
IF @@TRANCOUNT > 0
BEGIN

IF (XACT_STATE()) = -1
BEGIN

SELECT 'RollBack transaction'
ROLLBACK TRANSACTION;
END
IF (XACT_STATE()) = 1
BEGIN

Select 'commit transaction'
COMMIT TRANSACTION;
END
END
SELECT 'return value <> 0 i ProcSecond'
RETURN 1
END



IF (XACT_STATE()) = 1 COMMIT TRANSACTION
RETURN 0
END TRY
BEGIN CATCH -- Error-handling statements

-- End Transaction
IF @@TRANCOUNT > 0
BEGIN

IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION;
IF (XACT_STATE()) = 1 COMMIT TRANSACTION;
END



SELECT 'catch ProcSecond', @ErrorMessage



RETURN 1
END CATCH
END






3rd procedure:




Code Snippet
ALTER PROCEDURE [dbo].[ProcThird]
AS
BEGIN

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

DECLARE @tal int
BEGIN TRANSACTION




SET @Tal = 'a'
IF (XACT_STATE()) = 1 COMMIT TRANSACTION;
RETURN 0
END TRY
BEGIN CATCH -- Error-handling statements
--End Transaction

IF @@TRANCOUNT > 0
BEGIN

IF (XACT_STATE()) = -1
BEGIN

SELECT 'RollBack transaction ProcThird'
ROLLBACK TRANSACTION;
END
IF (XACT_STATE()) = 1
BEGIN

Select 'commit transaction ProcThird'
COMMIT TRANSACTION;
END
END



RETURN 1
END CATCH
END






Please help me!

//Emelia

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved