Connection problems to downed SQL Server

CJ_S, if you are here, in reply to your MSMQ suggestion:

That would be a possible workaround if MSMQ was stable enough, but my experiences with MSMQ suggests that this will not solve problems connecting to downed servers, but rather bring the servers down on a regular bases itself. I would rather not use this product in a production environment unless MS commits to some serious work on improving its stability.

My main wonder is why I don't get an "Unable to connect: SQL Server is unavailable or does not exist.
Specified SQL server not found." error. When connecting to a downed server, ADO should not hang but return this error. So why doesn't it do that?

Jeremy, you could always monitor the status of the connection:

   Set ADOcnMSSQL = New ADODB.Connection
   With ADOcnMSSQL
       .Provider = "sqloledb"
       .ConnectionString = "Server=MyServer;Database=MyDB;uid=Me;pwd=mypass"
       .Mode = adModeRead
       .CursorLocation = adUseClient
       .Open , , , adAsyncConnect
   End With
   On Error Resume Next
   Do
     DoEvents
   Loop Until ADOcnMSSQL.State <> adStateConnecting
   If ADOcnMSSQL.State <> adStateOpen Then
     MsgBox "There must have been a failure, check err object etc."
   End If
   On Error Goto 0

Your application can still process events but this way you will find out that the connection failed as the ConnectComplete event will never fire for this connection.

TimCottee, thanks for the suggestion, but...
1. I'm connecting this way for a reason. Waiting in a loop for the connection to open kind of negates the pros of connecting asynchronously :)
2. Even then, the State hangs on adStateConnecting forever - or at least until the client hangs :(. If the state would change to adStateOpen at some point I would expect to get the ConnectComplete event.

TimCottee:
ad 2: "I would expect" is an unhappy choice of words. I obviously used exactly your code (or something very much like it) to test any changes in the State property.

Public WithEvents c As ADODB.Connection

Private Sub c_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
  If pConnection.State = adStateClosed Then
    MsgBox "OK"
  Else
    MsgBox "Failed"
  End If
End Sub

Private Sub Form_Load()
  Set c = New ADODB.Connection
  c.Open "provder=sqloledb;"Server=MyServer;Database=MyDB;uid=Me;pwd=mypass"
     
End Sub


Cheers

oops...
If pConnection.State = adStateClosed
should read
If pConnection.State = adStateOpen

Cheers

angelIII of course that works, I didn't test it properly. However trying it out I find that I need an error handler in the ConnectComplete event as the test for .State here generates an error anyway!

Angel: maybe my wording is a bit unclear. When I said behind the "1." that the Open call didn't return, I meant that the ConnectComplete event wasn't raised. I'll edit the question to be more clear on that matter.

>The ConnectComplete wasn't raised...

I read that, but i wonder about that. I actually tried my code with Several ADO versions, and using as well Valid & Invalid Connection, connecting to SQL and Oracle Servers...

In all the cases, the ConnectComplete event was raised...

angel, I have just tried this out again, the thing that I find is when attempting to connect to a non-existent server the first attempt at a call to ADOcnMSSQL.State returns the actual error description. This appears to cause an error which makes it appear that the connectcomplete event doesn't fire.

Private WithEvents ADOcnMSSQL As ADODB.Connection

Private Sub OpenConnection()
  Set ADOcnMSSQL = New ADODB.Connection
  With ADOcnMSSQL
      .Provider = "sqloledb"
      .ConnectionString = "Server=MyServer;Database=MyDB;uid=Me;pwd=mypass"
      .Mode = adModeRead
      .CursorLocation = adUseClient
      .ConnectionTimeout = 5
      .Open , , , adAsyncConnect
  End With
'  On Error Resume Next
'  Do
'    DoEvents
'  Loop Until ADOcnMSSQL.State <> adStateConnecting
'  SetStatus ADOcnMSSQL.State
'  On Error GoTo 0
End Sub

Private Sub ADOcnMSSQL_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
    On Error Resume Next
    Debug.Print ADOcnMSSQL.State
   SetStatus ADOcnMSSQL.State
End Sub

Private Sub Command1_Click()
    OpenConnection
End Sub

Private Sub SetStatus(State As Integer)
    lblStatus = IIf(State = 1, "Connected To SQL Server", "Not Connected To SQL Server")
End Sub

If I run this with ADO 2.5 as above it sets the label correctly. However if I remove the debug.print line then it never does anything, a subsqequent test of the state property shows 0 (adStateClosed).

> In all the cases, the ConnectComplete event was raised...

Well, what can I say? Here it doesn't. Not on my development system, and not on any of the clients that have that particular app installed... Besides that, a guy from WebDevelopment mentioned he had the same problem when connecting from his IIS ASP pages.

Have you tried to reproduce the environment of an existing system that's down? I'm not the biggest NT guru, but I think it's a bit more then just 'mistyping' the servername, since the NT Domain account of a downed server will still exist on the PDC. If you just use a non-existent servername, it will not 'live' on the PDC of course.

note: i have a SQL box on my local PC which i can stop and start easily, but maybe the fact that i connect to a local PC makes the difference...
i will try to do this on a remote server...
Also i have SQL7 and SQL2000 installed (and this ADO2.6 dlls)...

Just tried it on a "downed" server with exactly the same results as described before. I get the connect complete event but only after an error occurs. If I don't attempt to trap the error it appears that the event doesn't get fired (though I think this is not actually true).

TimCottee: I tried the exact example code you posted above, with the same result - the client app hangs, and no events are raised before that. I get the distinct impression that the client app hangs directly after the Timeout has passed, IOW just when it _should_ be giving an error.

Angel: when testing, don't just try to connect to the server while the SQL service is not running, but actually switch the system off. That's the only way to accurately emulate a (hardware-) failing system without using a big hammer :)

angel, same here I have tried it with remote and local servers with the same effect

... currently setting up VMWare with a SQL Server to simulate that ...
I don't have a hammer here, otherwise i would have done that :-)

> I don't have a hammer here, otherwise i would have done that

I hugely appreciate your dedication <grin>

Maybe you can catch InfoMessage event, well, it won't say anything useful, but ... it can be some 'sign'
http://support.microsoft.com/support/kb/articles/Q231/9/85.asp

ameba: I checked your suggestion, this is what I found:
1. The InfoMessage event is called with this information:
   a) adStatus = adStatusOK (!)
   b) pError contains error number 265946, description "Multiple-step operation completed with one or more errors. Check each status value."
   c) The error collection of the connection object contains the same error, with NativeError number the same (265946) and an empty SQLState.
2. If I call Cancel on the connection in this event, the client app hangs indefinately on the call to Cancel, but the system remains responsive (I can max/min the form, use toolbar buttons, Ctrl-Break the execution in the IDE, etc.)

So, some extra info, but it really doesn't tell me anything useful or helps solving the problem. I've searched the ADO documentation and the MSDN for this error but couldn't find any further info about it.

More suggestions?

OOPS, sorry, just noticed your comment. Ignore my comment  :-)
>Note: I'm not looking for some timer-oriented workaround here, or the advice to use a synchronous connection, but rather for a way to make this work as it should.

> Maybe you can reduce the frequency of the problem by checking server before connecting the async. way

Well, obviously that is a possible workaround, but frankly, I don't need you guys to figure that one out, hence the comment :)  I'm one of those people who notoriously wants things to work as they should, and gets irritated if they don't (I know, that kinda puts me in the wrong line of business ;-)  I know ADO has some pretty strange quirks, but most of the time I get it to do what I want, so that's the option I'm looking for here too.

Something Like:
Dim adoServant As New ADODB.DoItForMe
adoServant.WorkItOut

:)

Jeremy_D,

You seem to be experiencing some difficulty with this site.

Our experts are willing to spend some of their precious time to help you solve your problem. The only thing we're asking in return is some feedback. Did the suggestion help? If it did, grade the question. If it did not, tell the expert so they understand and are able to try again.

Experts-Exchange is not a news group, in which you can post a question, get the answer, and walk away. Or if you get a suggestion that doesn't work, simply ignore it. Instead, the way the site is set up encourages dialogue.

You can read more about how the site works in our member agreement: https://www.experts-exchange.com/jsp/infoMemberAgreement.jsp

Other participants, let me know if poster ignores my request.

modder
Community Support

hi modder!
i guess Jeremy_D is not working wrongly here... But it's request is very difficult, and he followed up his question (the last post is from him). Don't blame him because VB+ADO doesn't work as it "should"...

Cheers

Hi angelIII,

I'm sorry. This was one question in a huge list of questions posted by emoreau in the CS topic area. I accept that in certain occasions my comments may not be entirely the asker, in which case I apologise, and I hope Jeremy will understand that this is nothing personal.

Cheers

modder

Oh... the words "fair to" should have sat between "entirely" and "the asker".

Don't worry modder, I understand that such a blatantly obvious standard mailmerge message is 'nothing personal' <grin>.

I am quite stumped as to what to do with this Q though. I did respond with all possible information I could think of, but AngelIII, TimCottee and ameba seem to be out of ideas - for which I can't blame them since I have the same problem myself :)  I left the Q open in the hope that someone else would stop by who knew the magical hack needed here, but to no avail it seems.

So, what do you all think I should do? Delete the message because no answer was found, or accept a comment to file it under the PAQs, since the suggestions given might solve other questioner's problems?

Note that the 200 points are no problem at all, I have more of them then I'll ever need here.

Jeremy

Thanks Jeremy_D.... hm...... well, it depends. Maybe I'll wait for the others to comment too, but do participants feel that something genuinely useful was said here that someone who bought this question could use. I could reduce the points to something more reasonable, of course....

Force accepted by moderator.  In the event Asker returns with additional needs related to this question, please respond and continue the collaboration process.
Thank you,
Moondancer
Community Support Moderator @ Experts Exchange

Greetings, all.

Apologies for my action above, if it does not serve the intent.  Please let me know if an adjustment is desired.  I worked from a large listing and from prior date screens so they hadn't refreshed, I didn't see the previous comment.

Listening further if an adjustment is in order.

Thank you,
Community Support Moderator @ Experts Exchange

Moondancer/Moderator: Please refund my points. If I do accept one of the commands as an answer to this question, it will not be that of ameba.

Jeremy

I am not sure why the comment of ameba could not be accepted, but i agree that the "force" was not ok by itself, as Jeremy_D was continuing on this q after the "reminder".
Cheers

Thank you one and all.  I have refunded points for this question (question value changed to 0, points refunded).  Please post a new one for the intended expert, include this link to award the intended contributor so they have the crossfoot to this question.  Lesson learned.

https://www.experts-exchange.com/jsp/qShow.jsp?ta=vbdatabases&qid=20118401

Thank you,
Moondancer
Community Support Moderator @ Experts Exchange