catch sql error

I have a script that I am connecting to a sql server.
I have an error catch script that I thought would skip over when it hits an error.
Today the script got stuck on the execute with this error
'Database  cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.'
instead of the script catching and passing over it, my page failed.

What did I put wrong in the script or is there another way to do this?

On Error Resume Next

set myConn = Server.CreateObject("ADODB.Connection")
myConn.open MM_TowerConn2_STRING

If err.Number = 0 Then

 if myConn.State = 1 then

  On Error Goto 0
'run script here 
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_TowerConn2_STRING
Command1.CommandText = "Csm1"
Command1.Prepared = true
set cars = Command1.Execute

If err.Number = 0 Then
connectionComplete = "true"

	  On Error Goto 0

cars_numRows = 0
Repeat1__numRows = -1
Repeat1__index = 0
cars_numRows = cars_numRows + Repeat1__numRows

end if
end if 

else
connectionComplete = "false"
 ' Error trapping - connection failed
end if

Open in new window

rivkamakAsked:
Who is Participating?
 
hieloConnect With a Mentor Commented:
try:
On Error Resume Next
Dim connectionComplete
'initialize it to true;  then change it to false if you encounter an error
connectionComplete = True

Set myConn = Server.CreateObject("ADODB.Connection")
myConn.open MM_TowerConn2_STRING

If Err.Number <> 0 Then
	connectionComplete = False
	Response.Write "Error(A." & Err.Number & "): " & Err.Description
	Err.Clear
Else
	If myConn.State = 1 Then
		
		'run script here 
		Set Command1 = Server.CreateObject("ADODB.Command")
		Command1.ActiveConnection = MM_TowerConn2_STRING
		Command1.CommandText = "Csm1"
		Command1.Prepared = true

		Set cars = Command1.Execute
		If Err.Number <> 0 Then
			connectionComplete = False
			Response.Write "Error(B." & Err.Number & "): " & Err.Description
			Err.Clear
		Else
			cars_numRows = 0
			Repeat1__numRows = -1
			Repeat1__index = 0
			cars_numRows = cars_numRows + Repeat1__numRows		
		End If
	End If
End If

Open in new window

0
 
DcpKingCommented:
Aside from the fact that this is all VB and not SQL ....

Command1.CommandText = "Csm1"

Whatever you put inside the quotes you should be able to just type in and run at the interface supplied with the database server (SSMS with SQL Server 2005 and greater). I rather doubt typing Csm1 and pressing F5 would get you anything other than an error.

I agree that On Error Goto 0 should disable error trapping (see here), but it is VB or VBA error trapping that you are working with. The error you are getting back is from the SQL system that VB has called out to. It isn't your page (and code) that failed - it was the connect to the database. The error isn't coming from the VB subsystem.

Do what it suggests - go look at the SQL Server logs for the time that it happened and see what went wrong.

hth

Mike
0
 
rivkamakAuthor Commented:
How can i catch a sql problem?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
PortletPaulfreelancerCommented:
about 1 century ago I did some VB..., but does it have something to do with how you are testing the connection state? (lines 28 & 29)
On Error Resume Next

set myConn = Server.CreateObject("ADODB.Connection")
myConn.open MM_TowerConn2_STRING

If err.Number = 0 Then

    if myConn.State = 1 then

        On Error Goto 0
        'run script here 
        set Command1 = Server.CreateObject("ADODB.Command")
        Command1.ActiveConnection = MM_TowerConn2_STRING
        Command1.CommandText = "Csm1"
        Command1.Prepared = true
        set cars = Command1.Execute

        If err.Number = 0 Then
            connectionComplete = "true"

            On Error Goto 0
            cars_numRows = 0
            Repeat1__numRows = -1
            Repeat1__index = 0
            cars_numRows = cars_numRows + Repeat1__numRows

        end if
    else
        'myConn.State <> 1 so do something here?
    end if 

else
    connectionComplete = "false"
     ' Error trapping - connection failed
end if

Open in new window

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Looks like a performance problem related to lack of server resources.  You should start by reading the SQL Server error logs.  Troubleshooting performance from a client side is difficult if not impossible.  How much RAM do you have on the server ?  Can you tell us more about the IO configuration of the server ? Where is TEMPDB hosted ?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Can you tell us more about the statement you are submitting at each iteration ?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Sorry I did not realize DCPKing had already suggested you look over the SQL error logs.
0
 
rivkamakAuthor Commented:
I know I can look at the sql log, but I need something that will catch any SQL error no matter what it is.
How can I program something into this that won't cause my whole page to fail becuase I can't connect.
0
 
PortletPaulfreelancerCommented:
currently you are only testing if connection state is 1
(or is my VB that bad? that's quite possible)
0
 
rivkamakAuthor Commented:
What can I do to test this line:
  set cars = Command1.Execute

the error I hit was this:
'Database  cannot be opened due to inaccessible files or insufficient memory or disk space.'
0
 
PortletPaulfreelancerCommented:
GoTo 0
Disables enabled error handler in the current procedure and resets it to Nothing.

http://msdn.microsoft.com/en-us/library/5hsw66as(v=vs.80).aspx

you are not trapping any errors?
0
 
rivkamakAuthor Commented:
I would like to catch all errors.
How can I change my script to catch it?
0
 
PortletPaulfreelancerCommented:
oh dear... my antique knowledge.. it goes something like this
On Error GoTo ErrorHandler

set myConn = Server.CreateObject("ADODB.Connection")
myConn.open MM_TowerConn2_STRING

If err.Number = 0 Then

    if myConn.State = 1 then

        'On Error Goto 0
        'run script here 
        set Command1 = Server.CreateObject("ADODB.Command")
        Command1.ActiveConnection = MM_TowerConn2_STRING
        Command1.CommandText = "Csm1"
        Command1.Prepared = true
        set cars = Command1.Execute

        If err.Number = 0 Then
            connectionComplete = "true"

            'On Error Goto 0
            cars_numRows = 0
            Repeat1__numRows = -1
            Repeat1__index = 0
            cars_numRows = cars_numRows + Repeat1__numRows

        end if
    else
        'myConn.State <> 1 so do something here?
    end if 

else
     ' Error trapping - connection failed
    connectionComplete = "false"
    GoTo ErrorHandler
end if

exit sub

ErrorHandler:
    ' do something - an alert?
    Resume Next
End Sub

Open in new window

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<I know I can look at the sql log, but I need something that will catch any SQL error no matter what it is.>>
That is recoding a part of what SSMS does: lots of work.  Just a few pointers on what kind of code you need to get all potential server side errors:

> DML errors: errors related to transactions.  You can use RAISE ERROR event in TSQL
> SQL application errors: You need to have access to SQL source code to do that
> SQL host system errors: WMI events, perfmon system counters...
> SQL performance counters

Again, you need to be aware you are embarking into a huge task here.  Perhaps you may want to look at existing SMO libraries allowing to interact with SQL Server as a start.  

Hope this helps.
0
 
DcpKingCommented:
Looking at it again .... get your DBA to do a DBCC CHECKDB on the database involved - it may be damaged. I hope I'm wrong!

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.