February 23, 2010 at 10:32 pm
Hello all,
I have an Access 2000 adp application connected to an SQL Server 2000 back end. Yes, I know it's old technology, but it still works.
In my T-SQL procedures, I use the RAISERROR function to raise back end exceptions and then I retrieve the message for displaying to the user using the ADODB errors object. This generally works as expected both at the database engine and at the client.
I recently completed quite a large procedure which I split into a number of smaller procedures to make it easier to maintain and I execute them one after the other from the main procedure as follows.
alter procedure [dbo].[hrTimeSheet_CalculateSharedTimeAdjustments]
@TimeSheetID int
as
set nocount on
-- check all input data, if this is executed the raiserror
-- message is displayed at the client
if @TimeSheetID IS NULL
begin
raiserror('Bad input error message goes here.', 16, 1)
return 50000
end
-- sql statements for initial set up for the called procedures goes here
-- three procedures are executed here one after the other
exec dbo.hrTimeSheet_IdentifySharedGroups @TimeSheetID
-- more sql statements for additional processing goes here
-- if this raiserror is executed, the procedure is halted
-- correctly but the message is not displayed.
if (select min(AdjustedTimeInterval) from ##SharedProcesses) < 0
begin
raiserror('Appropriate error message goes here.', 16, 1)
return 50000
end
return
My problem is that any RAISERROR function called after the first or subsequent EXEC statement does not display the message at the client.
SQL server does raise the error and does cease execution with the return. I have verified this by checking the status of the table records in question and by adding 'WITH LOG' to the call and checking the server's application log.
Can you please offer any advice that may help solve this issue. Thanks in advance for taking the time to read this post.
Stu:)
Thanks for your assistance.
Stu:)
February 23, 2010 at 10:37 pm
What's the Client application? I'd suspect that's where the problem is.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 10:44 pm
FYI, I replaced the EXEC that you are calling (hrTimeSheet_IdentifySharedGroups), with a dummy procdure from my DB, and then ran this in Management Studio, and it worked fine. Error, error message, everything.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 11:03 pm
Thanks RBarryYoung,
The client app is mine. I'm the author. It's an in house app so therefore will not be known to you.
I will check out some ADODB issues and see if I can find a reason why it's not receiving the message at the client in this circumstance.
Stu:)
Thanks for your assistance.
Stu:)
February 23, 2010 at 11:15 pm
RBarryYoung,
Are you generally happy with my T-SQL syntax and usage?
Do you have any observation to make about that?
Stu:)
Thanks for your assistance.
Stu:)
February 23, 2010 at 11:34 pm
Well, I generally try not to comment on others code style, it's such a personal thing and all. One thing that I might suggest, is to upcase your SQL Keywords, that's pretty standard practice these days (for SQL, not other languages).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 11:36 pm
OK, taken on board.
Thanks for your assistance.
Stu:)
February 25, 2010 at 11:24 pm
I have an update to this issue for those that can benefit from it.
As I stated in my original post, the SQL procedure is indeed executing correctly. All RAISERROR throws are being executed at the database.
I have discovered that when the RAISERROR is thrown after a call to another procedure it is not passed to the VBA error collection. If it is thrown before any call to another procedure then it is passed as expected. I have not been able to find a reason for this but my testing reveals this behaviour.
My Solution:
As well as intercepting the ADODB errors via the VBA error collection in my client code, I explicitly loop through the ADODB error collection immediately after the execution of the procedure and my message is displayed on the client. If there are no ADODB errors, the message box does not fire.
Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate
'make any shared time adjustments
Dim CN As New ADODB.Connection
CN.CursorLocation = adUseClient
If CN.State = adStateClosed Then
CN.Open gstrConnectionString
End If
Dim cmd2 As New ADODB.Command
cmd2.ActiveConnection = CN
cmd2.CommandType = adCmdStoredProc
cmd2.CommandText = "hrTimeSheet_CalculateSharedTimeAdjustments"
cmd2.Parameters.Append cmd2.CreateParameter("@TimeSheetID", adInteger, adParamInput, 4, Me.TimeSheetID)
cmd2.Execute
'this error check is made here because when a sql procedure calls another procedure and then throws an exception,
'that exception does not seem to be passed to the vba error collection. If I loop through the ADODB errors explicitly
'the thrown error from ADODB procedure is displayed.
Dim cnErr As ADODB.Error
For Each cnErr In CN.Errors
MsgBox cnErr.Description
Next
Exit_Form_AfterUpdate:
CN.Close
Exit Sub
Err_Form_AfterUpdate:
'this check is still necessary because it captures any ADODB errors that DO get passed the VBA error collection.
If CN.Errors.Count > 0 Then
Call ProcessADOErrors(CN.Errors) 'this is my public sub that loops through ADODB errors.
Else
MsgBox Err.Description
End If
Resume Exit_Form_AfterUpdate
End Sub
Thanks for your assistance.
Stu:)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy