Solved

catch sql error

Posted on 2013-06-04
15
314 Views
Last Modified: 2013-06-14
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

0
Comment
Question by:rivkamak
  • 4
  • 4
  • 4
  • +2
15 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 39221001
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
 

Author Comment

by:rivkamak
ID: 39221012
How can i catch a sql problem?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39221288
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39221431
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39221433
Can you tell us more about the statement you are submitting at each iteration ?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39221435
Sorry I did not realize DCPKing had already suggested you look over the SQL error logs.
0
 

Author Comment

by:rivkamak
ID: 39222297
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39222310
currently you are only testing if connection state is 1
(or is my VB that bad? that's quite possible)
0
 

Author Comment

by:rivkamak
ID: 39222333
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39222424
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
 

Author Comment

by:rivkamak
ID: 39222666
I would like to catch all errors.
How can I change my script to catch it?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39222704
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39222747
<<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
 
LVL 16

Expert Comment

by:DcpKing
ID: 39222837
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
 
LVL 82

Accepted Solution

by:
hielo earned 500 total points
ID: 39225915
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now