Catch MSSQL Triggers In VB6
Jul 29, 2004Hi
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.
ADVERTISEMENT
T-sql Try Catch - Using Triggers And Nested Stored Procedures
Jan 7, 2008For every trigger and stored procedure I have a try-catch that writes to an error_log table.
The problem is the inner error is not preserved, always get:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
As seen below - though commented out:
I tried commiting any transactions - though I didn't create one.
I played with the
XACT_STATE though that was 0
My test case was last procedure has 1/0
Thanks,
Russ
-----------------------------------------------------------
Below is what I have
Step 1)
ALTER Trigger [trg_ActivityLogEntryReportsError] ON [dbo].[ActivityLog]
FOR INSERT AS
DECLARE @ActivityLogID int
,@AlertMessageTypeID int
,@comment nvarchar(max)
,@Error_Source nvarchar(max)
--- etc.
SELECT
@ActivityLogID = ActivityLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = Comment
FROM INSERTED
BEGIN TRY
if @AlertMessageTypeID = 2 -- activity reported an error
begin
exec proc_CreateAlertLogEntry_forError
@ActivityLogID
,@Comment
update ActivityLog
set flgActivityChecked = 1
where @activityLogId = activityLogID
end
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_ActivityLogEntryReportsError '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
--- etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.
,@Error_Comment )
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
Step 2)
/*
This will be called by a Trigger
*/
ALTER Procedure [dbo].[proc_CreateAlertLogEntry_forError]
(@ActivityLogID int
,@Comment nvarchar(max))
AS
Declare
@ProcessScheduleID int
,@ProcessID int
--,@comment nvarchar(max)
,@Error_Source nvarchar(max)
---etc
BEGIN TRY
insert into AlertLog
(
AlertMessageTypeID
,comment
,ActivityLogID
)
values
(
2 -- error
,@comment
,@ActivityLogID
)
end
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = 'proc_CreateAlertLogEntry_forError '
---etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
---etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
update ActivityLog
set
flgActivityChecked = 1
,UpdatedDate = getdate()
,UpdatedBy = suser_sname()
where
ActivityLogID = @ActivityLogID
STEP 3
ALTER Trigger [trg_AlertLogEntry_SendsOnInsert] ON [dbo].[AlertLog]
FOR INSERT AS
declare
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max)
,@Error_Source nvarchar(max)
,@Error_Procedure nvarchar(max)
,@Error_Message nvarchar(max)
--- etc.
SELECT
@AlertLogID = AlertLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = isnull(Comment,'')
,@ActivityLogID = isnull(ActivityLogID,-1)
FROM INSERTED
BEGIN TRY
PRINT 'trg_AlertLogEntry_SendsOnInsert'
PRINT @COMMENT
exec proc_SendEmail
@AlertLogID
,@AlertMessageTypeID
,@comment
,@ActivityLogID
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_AlertLogEntry_SendsOnInsert '
,@Error_Procedure = ERROR_Procedure()
,@Error_Message = ERROR_MESSAGE()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
-- etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
STEP 4
ALTER Procedure [dbo].[proc_SendEmail]
(
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max) = ''
,@ActivityLogID int = -1
)
AS
declare @AlertSubject nvarchar(512)
,@AlertBody nvarchar(max)
,@myQuery nvarchar(512)
,@profile_name1 nvarchar(128)
,@return_value int
,@mymailitem int
,@Error_Source nvarchar(max)
---etc.
,@Error_Comment nvarchar(max)
,@Test int
/*
@return_value int -- not using at this point but 0 is OK 1 is failure
@mymailitem int -- not using now could store mailitem_id which is on msdb.dbo.sysmail_mailitems
sysmail_mailitems.sent_status could be either 0 new, not sent, 1 sent, 2 failure or 3 retry.
*/
select top 1 @profile_name1 = [name] from msdb.dbo.sysmail_profile
order by profile_id
set @profile_name1 = rtrim(ltrim(@profile_name1))
print '@profile_name1: ' + @profile_name1
print '@comment: ' + @comment
Declare @CrsrRecipient Cursor
BEGIN TRY
PRINT 'proc_SendEmail'
--set @test = 1/0 'test crashing
select
@AlertSubject = 'AlertSubject'
,@AlertBody = 'AlertBody'
,@recipients = 'russ@test.com'
print 'sending email ' + CAST(getdate() as nvarchar(100))
EXEC @return_value = msdb.dbo.sp_send_dbmail
@profile_name = @profile_name1
,@recipients = @EMAILID
,@body = @AlertBody
,@subject = @AlertSubject
,@mailitem_id = @mymailitem OUTPUT
print 'Done ' + CAST(getdate() as nvarchar(100))
print cast(@return_value as nvarchar(100))
update alertlog
set AlertSendStatusID = 1 --sent
where
@AlertLogID = AlertLogID
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = ' proc_SendEmail '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
---etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
update alertlog
set AlertSendStatusID = 2 --error
where
@AlertLogID = AlertLogID
--if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
Try Catch Doesn't Catch Errors Inside A Data Flow Transformation Script Component
Feb 15, 2007Hi,
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.
Try Catch Does Not Catch Exception
May 15, 2007hi 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
Multiple Triggers On A Table Or Encapsulated Triggers
May 12, 2008This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
www.handleysonline.com
How Many Result-rows Does Mssql Return Should Be Used Asynchronous Method To Use Mssql Cursor?
Aug 11, 2004How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
Database Migration Plan - (mssql/msde To -&> Pgsql/mssql)
Feb 10, 2008Hi,
i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
hoping for your kind help guys. thanks :)
br
Frozenice
Server Configuration For MSSQL 2000 And MSSQL 2005
Sep 6, 2006Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies View RelatedCreating Index In MSSQL 2000 From MSSQL 2005
Mar 24, 2008Hi,
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)
INCLUDE
(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
Any help in creating such indexes in 2000 version is welcome.
Thanks,
Suresh.
Generating Script For MSSQL 2000 From MSSQl 2005?
May 3, 2008Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
Could you please give me the step to do?
Thanks,
Sankar R
Run Mssql 6.5 Client And Mssql 7.0 Server On Same Nt Workstaton?
Oct 26, 1999Hello:
I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.
However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?
Thanks.
David Spaisman
MSSQL Express 2005 Vs. MSSQL 2000
Jun 15, 2006Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
Thanks,
Ben"
Migration MSSQL 2000 32-bit To MSSQL 2005 64-bit
Nov 17, 2007I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.
Try Catch
Jan 15, 2007Hi
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
TRY CATCH
Apr 22, 2008Is 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
Try Catch
May 13, 2008Hi!
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
Try And Catch
May 22, 2008Hi 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.
Catch Me If You Can / Bug In The Bug?
Oct 7, 2007Hello!
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
Try And Catch Problem
Feb 26, 2007I 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 RelatedTry,Catch And Response
Jul 2, 2007Hi,
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.
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
SP Catch Error
Aug 21, 2007Hi,
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
Try.. Catch Block
Dec 11, 2006HI,
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?
Try..catch Statement
Apr 13, 2008can 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'.
Try Catch Syntax
Mar 14, 2007hi 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
How To Try-catch 'sp_addrolemember'?
Dec 27, 2006how to try-catch sp_addrolemember?
View 4 Replies View RelatedHow To Catch An Exception?
Jul 20, 2005Is 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 RelatedError Catch In SQL
Jul 20, 2005Hi everyone, I am using an SQL extended stored procedure to send emails in aDTS 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 invalidthen an error occurs and the whole DTS package falls over. What I would liketo be able to do is "catch the error", something like this (C# code used asexample)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 theemail is entered into the database but unfortunately, I do not have accessto the application code so I am stuck doing it this way.Thanks in advanceMark
View 2 Replies View RelatedUse Of TRY/CATCH: Architecture
Oct 20, 2007Hi 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.
Try...catch With Transactions
Apr 28, 2008I 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
On Update Catch 22
Aug 15, 2006How do you make an update cascade opperation if you want to set the PK to an excisting one?
I have a table where to post are the same only the PK differs, now I want to delete one but transfer it's dependecies. i.e redirect its references to the other post.
How can I do that using on update cascade/delete. I see a catch 22 coming up!
/Jonneponne
Try Catch Block
Apr 12, 2006Hi:
one of our study group members noticed a strange behavior and has the following question. Any thoughts are appreciated.
I am unable to understand as to why the CATCH block is not executed when an INSERT is made
On table T3 which is dropped after the first transaction.
The severity of Insert into t3 values (3) is Msg 208, Level 16, State 1, Line 2
Invalid object name 't3'.
BOL says TRY€¦CATCH constructs do not trap the following conditions:
Warnings or informational messages with a severity of 10 or lower.
Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY€¦CATCH will handle the error.
Here is the script.
use tempdb
go
create table t1 (a int)
create table t2 (b int)
create table t3 (c int)
Begin tran
Insert into t1 values (1)
Insert into t2 values (2)
Insert into t3 values (3)
IF @@error <> 0
Rollback tran
else
commit tran
-------------------------------------------------------------
Select * from t1
Select * from t2
Select * from t3
-------------------------------------------------------------
Drop table t3
-------------------------------------------------------------
Set xact_abort on
Begin try
Begin tran insertNow
Insert into t1 values (1)
Insert into t2 values (2)
save tran insertNow
Insert into t3 values (3)
commit tran insertNow
End try
Begin Catch
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION insertNow
END;
-- Test if the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION insertNow
END
End Catch
Using TRY...CATCH With RAISERROR
Jun 1, 2006
Hi, I am trying to write a trigger in SQL 2005 and am running into a lot of issues. Here is my code
BEGIN
BEGIN TRY
DECLARE @count int
--check for timeon which is greater than timeoff
SET @count=0
SELECT @count=count(*)
FROM IVRCONFIG.dbo.C_GROUP_HOURS a WITH (NOLOCK),inserted i
WHERE a.XFER_GROUP = i.XFER_GROUP
AND a.DAY_OF_WEEK = i.DAY_OF_WEEK
AND a.TIMEON = i.TIMEON
AND a.TIMEOFF < a.TIMEON
IF @count<>0 RAISERROR (50001,16,1);
END TRY
BEGIN CATCH
RAISERROR
END CATCH
END
The error I specify is one I added to sys.messages. Can anyone tell me where I am going wrong??