SQL server does not exist or access denied. 1

[RSX02]

Hi
I receive this error when I am trying to connect to my sql server and the odbc doesn't work. When this error occur it make my vb application crash.
I would like to know if I can put a validation in my vb application that display an error message that I created and allow my application to not crash. So make the user try again.
This is my code to allow me to connect to my odbc(when it's running).
Option Explicit
Public MyConn As Connection

Public Function OpenDb()
Dim strConnect As String
Set MyConn = New Connection
MyConn.Open "DSN=MyDSN;", "username", "password"
End Function
thanks in advance

 

[Glasgow]

You can use SQLDMO to do lots of useful stuff like:

Dim SrvOurs As New SQLDMO.SQLServer2, ErNo As Long, ErMsg As String
Dim DspMsg As String, i As Integer, Retries As Integer
SrvOurs.Name = GlbSqlServer
If SrvOurs.Status <> SQLDMOSvc_Running Then
GmsStatusMsg GlbSqlServer & &quot; database server is not running&quot; & vbCrLf & vbCrLf & _
&quot;Attempting to start...&quot;
On Error Resume Next
SrvOurs.Start False, GlbSqlServer 'NB this may return success but without start having completed
ErNo = Err.Number: ErMsg = Err.Description
If ErrTrap Then On Error GoTo GmnAccessSqlServerErr Else On Error GoTo 0
Select Case ErNo
Case 0, ErSqlSvrOn ' Server already started, OK
Case ErSqlSvrPerm: DspMsg = &quot;Cannot start database server when logged in as this user&quot;
Case Else: DspMsg = &quot;Error &quot; & ErNo & &quot;, cannot start &quot; & GlbSqlServer & _
&quot; database server - &quot; & ErMsg
End Select

Hopefully you can some ideas from this.

 

[RSX02]

Hi
Is it normal that I don't have the SQLDMO in the drop down list that allow me to choose one from the list? And it gives me an error 'Compile error: User-defined type not defined'
?

 

[Glasgow]

Under project references, look for

Microsoft SQLDMO Object Library

 

[RSX02]

Thanks Glasgow that solve the first error message that I had.
But now I get an error about this field &quot;GlbSqlServer&quot;. &quot;Variable not defined&quot;
Does it supposed to be the name of my sql server?

 

[Glasgow]

Er, yes, sorry - I just cut and pasted the most relevant chunk of my code rather than show all declarations, assignments etc that take place elsewhere.

Anyway, yes, GlbSqlServer is the name of the server - you can use a literal to experiment with at least. Also of interest:

Public Const ErSqlSvrOn = -2147023840
Public Const ErSqlSvrPerm = -2147024891
Public Const ErSqlBadLogin = -2147203048

Also, GmsStatusMsg just processes messages (use MsgBox if you want) and perhaps remove the If ErrTrap line.

Hope that clarifies.

 

[RSX02]

Glasgow
When I use the message box the program wait that I click on the ok button to continue. When I click on this button it goes on the rest of my code and try to make collection to my database without success because my server is closed. but after a little while it open my sql server. If I put the GmsStatusMsg do you think it's gonna solve my problem? How can I solve it?
Thanks in advance

 

[Glasgow]

No, GmsStatusMsg is just a routine of ours that displays a message without waiting for input.

I have hit this problem - you have requested that the service should start but it has not fully started by the time you are trying to connect. You could try adding something like:

If ErNo = 0 Then ' i.e. start request OK but not necessarily done yet!
Do Until SrvOurs.Status = SQLDMOSvc_Running Or Retries = 10
Sleep 1000 'wait 1 second (API call)
DoEvents
Retries = Retries + 1
Loop
End If
If ErNo <> ErSqlSvrOn And SrvOurs.Status <> SQLDMOSvc_Running Then
GmsMsg MsgCouldNotStartServer
Exit Function
End If

This tries 10 times to see if it is running yet, delaying 1 second between each attempt. If you don't want to include the Sleep API call, you could always experiment with for next loops to effect a similar delay, e.g.

Do Until SrvOurs.Status = SQLDMOSvc_Running Or Retries = 10
for i=1 to 10000: next i
Retries = Retries + 1
Loop

 

[RSX02]

Thanks glasgow for your time...
But it doesn't work yet. Now I get an error on the first recordset that I'm using in my program (for the log on).
&quot;Object variable or With block variable not set&quot;
This is the code that I have in my module. Maybe something is wrong. I used the second procedure that you send me for the sleep as I don't know how to use the sleep (it gave me an error)

Public Function OpenDb()
Dim strConnect As String
Dim SrvOurs As New SQLDMO.SQLServer2
Dim ErNo As Long
Dim ErMsg As String
Dim DspMsg As String, i As Integer, Retries As Integer
Dim GlbSqlServer As String
GlbSqlServer = &quot;.&quot;
SrvOurs.Name = GlbSqlServer
If SrvOurs.Status <> SQLDMOSvc_Running Then



MsgBox (GlbSqlServer & &quot; database server is not running&quot; & vbCrLf & vbCrLf & _
&quot;Attempting to start...&quot
On Error Resume Next
SrvOurs.Start False, GlbSqlServer 'NB this may return success but without start having completed
ErNo = Err.Number: ErMsg = Err.description

If ErNo = 0 Then ' i.e. start request OK but not necessarily done yet!
Do Until SrvOurs.Status = SQLDMOSvc_Running Or Retries = 10
For i = 1 To 10000: Next i
Retries = Retries + 1
Loop
End If
If ErNo <> ErSqlSvrOn And SrvOurs.Status <> SQLDMOSvc_Running Then
DspMsg = &quot;Error &quot; & ErNo & &quot;, cannot start &quot; & GlbSqlServer & _
&quot; database server - &quot; & ErMsg
Exit Function
End If

Else
Set MyConn = New Connection
MyConn.Open &quot;DSN=***;&quot;, &quot;**&quot;, &quot;****&quot;
End If
End Function

 

[Glasgow]

The Sleep isn't likely to be a factor if you are falling through the rest of the code with no further errors / messages. Have you verified that the MyConn.Open is actually executing? I suspect it may not be and so connection not initialised or opened

In the code:

Else
Set MyConn = New Connection
MyConn.Open &quot;DSN=***;&quot;, &quot;**&quot;, &quot;****&quot;
End

what 'if' is this else associated with? I don't think it should be there. Instead you need an End if I think. Sorry difficult to read it and probably not helped by fact I posted code in 2 chunks.

I assume Myconn is global and being used when you try to open the recordset (may be worth posting more code if still problems).

 

[RSX02]

Yeah I still have the problem when I replace :
Else
Set MyConn = New Connection
MyConn.Open &quot;DSN=***;&quot;, &quot;**&quot;, &quot;****&quot;
End if

By
End If
Set MyConn = New Connection
MyConn.Open &quot;DSN=frisco;&quot;, &quot;sa&quot;, &quot;frisco&quot;

Yes MyConn is global. My code with the recordset is working when the sqlserver is already started. It seems like it doesn't have enought time to start before I use the first recordset. I don't know if it could be possible to have a kind of message box without okay button and that display the number of second that I want. I think that my error is due to the fact that it doesn't have enought time to start the sqlserver. Because I see the sqlserver start a little bit after the error occur.
Do you have an idea how can I solve it?

 

[Glasgow]

But if that is the case you should get the cannot start database server message.

For safety, after the line

ErNo = Err.Number: ErMsg = Err.Description

insert
on error goto 0

to prevent errors going unnoticed then step through the code line by line (F8) to check that it gets as far as the connection open. Assuming that it does, you can either

a) adjust the loop limit of 10000 to increase delay

or

b) Move the MsgBox to after the SrvOurs.Start line then you can wait until server has started before acknowledging the message

If you want to use the sleep function, add

Public Declare Sub Sleep Lib &quot;kernel32&quot; (ByVal dwMilliseconds As Long)

to the head of the module - the parameter is in milliseconds so multiply required no of seconds by 1000.

See where that gets you.

 

[RSX02]

Good! That's seems a lot better now.
Now I have an error about the username and password when the user doesn't enter the right username and password. I tried to add this:

If ErNo = ErSqlSvrPerm Then
DspMsg = &quot;Cannot start database server when logged in as this user&quot;
End If

above
If ErNo <> ErSqlSvrOn And SrvOurs.Status <> SQLDMOSvc_Running Then

But that not seems to work.

 

[Glasgow]

Perhaps you should be testing for the ErSqlBadLogin error (that I posted earlier in the thread = -2147203048). I presume you are deliberately entering an incorrect user name and/or password for testing purposes? If so, I suggest you determine the actual value of ErNo that is being returned (i.e. when stepping through code - or simply from what is displayed when your application crashes) then set up that error number as a constant and trap for it. My error handling will not necessarily trap all the cases that you want to trap.

 

[RSX02]

Yeah seems to work now!
Thank you very much Glasgow!

 

[Glasgow]

Good, glad to hear it.

 

[Danialle]

Do you have any ideas why I would get the same error even though the user name, password, and host are all correct? I'm not familiar with SQLDMO, but it is part of the program I am trying to use:

Set dmoSrv = CreateObject(&quot;SQLDMO.SQLServer&quot

If MSSQL_SECURE_LOGIN Then
dmoSrv.LoginSecure = True
dmoSrv.Connect MSSQL_HOST
Else
dmoSrv.LoginSecure = False
MsgBox dmoSrv.VerifyConnection
dmoSrv.Connect MSSQL_HOST, MSSQL_LOGIN_NAME, MSQL_PASSWORD
End If

When I run this code i get the errors:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(connect()).

 

[Glasgow]

Are you sure the SQL server is actually running when you try to connect? You can check by comparing DmoSrv.Status with SQLDMOSvc_Running.

 

[Danialle]

Yes, the server is running. I did the test you recommended and that says it is running. Any ideas?

 

[Glasgow]

What operating system are you running and what is the value of MSSQL_SECURE_LOGIN ?