September 26, 2003 at 1:19 am
I'm using ExecuteWithResultsAndMessages2() in a VB program and successfully executing an SQL batch that returns no results.
When I create a problem in the SQL by deleting a table that does not exist, I get no messages back in the strMessages output parameter. There is therefore no indication that an error has occurred.
Since my code works most of the time, I will only include below what I believe to be relevant:
'Code starts here
Dim oFResults As SQLDMO.QueryResults
Dim sFileQuery As String
Dim sMessages As String
Dim oDatabase
Dim oServer
Set oServer = New SQLDMO.SQLServer2
oServer.Connect "sa", "password"
sFileQuery = "DROP Table results_tbl"
For Each oDatabase In oServer.Databases
oFResults = oDatabase.ExecuteWithResultsAndMessages2(sFileQuery, sMessages)
Next
'Code ends here
Does anyone have any clue as to how to get those error messages to show up in the sMessages string?
September 26, 2003 at 6:28 am
When I run the code I get a trappable error (from .Net that is), do you get the error?
Andy
September 26, 2003 at 9:56 am
I do get the [Run-time error '208'] (Invalid Object name) error in the usual fatal error dialog when I don't have my [On Error Resume Next] set, but once I set it, I get nothing.
Is there some setup thing that I'm missing here? I expected that even if the error was passed by, I would still get message text in my strMessages output parm.
I'm using VB6 myself - still existing in the past.
September 26, 2003 at 11:08 am
I think you're going to have to trap the error and decide what to do. Maybe tell the user, maybe just keep going, depends on the task I guess. I don't see that as a flaw exactly.
Andy
September 26, 2003 at 11:27 am
You're probably correct. I guess that as a C++ guy, I expect to always get output back from any function that I call regardless of whatever global error setup is in place.
Guess I was wrong. THANKS for your help.
October 3, 2003 at 11:22 am
You probably already solved this but just in case, I believe that an error with a severity greater than 10 are handled via the calling app. Maybe add a function like this
private function executeStatement(byval v_sSQL as string
, byref r_oDb as Object
, byref r_sMessage as string) as boolean
on error goto errBlock
Dim oQuery as SQLDMO.QueryResults
set oQuery = r_oDb.ExecuteWithResultsAndMessages2(v_sSQL, r_sMessage)
set oQuery = nothing
executeStatement = true
exit function
errBlock:
executeStatement = false
sMessage = CStr(Err.Number) & "-" & Err.Description
end function
call it within your loop.
-Jason
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply