?
Solved

catch sql error

Posted on 2013-06-04
15
Medium Priority
?
327 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 49

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
LVL 49

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 49

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 49

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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

809 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