catch sql error

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

How can i catch a sql problem?

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

Select allOpen in new window

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 ?

Can you tell us more about the statement you are submitting at each iteration ?

Sorry I did not realize DCPKing had already suggested you look over the SQL error logs.

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.

currently you are only testing if connection state is 1
(or is my VB that bad? that's quite possible)

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.'

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?

I would like to catch all errors.
How can I change my script to catch it?

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

Select allOpen in new window

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

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