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

 

[Danialle]

I am running Windows NT and MSSQL_SECURE_LOGIN is False. The value is set in my constants section.

 

[Glasgow]

If you are running NT, you could try just testing with forcing MSSQL_SECURE_LOGIN = True if the current login should allow access to the SQL Server.

You could also experiment with setting dmoSrv.LoginTimeout to a value (e.g. 60) in case your application is just taking a while to find the server and is timing out before it actually finds it.

 

[Danialle]

Unfortunately the current login won't work on the Server (which is remote).

I tried using an obscenely long timeout (60, 120, 240) to see if the error came up before timeout, but it always shows up right at the timeout.

 

[Glasgow]

Can you access the server from, for example, Query Analyser or Enterprise Manager using this username & password? You could also try using the osql utility from the DOS prompt and see if you can login that way:

Osql -S ServerName -U Username -P password

If these present problems, the suggestion is that there is not necessarily anything wrong with the VB code but perhaps there is some problem with the server or your link to it.

If it is timing out, I guess it's not finding the server and so not even getting as far as validating the login.

 

[Danialle]

We know that we can connect to the server using an ODBC connection using the AS/400 software. Is there any way that you can think of to make that connection using VB code?

 

[Danialle]

By the way, I did try the query analyser and that returned the same error. At least we know it's not the code.

 

[Glasgow]

Now's the time to consider starting a thread on the SQL Server forum as VB is no longer relevant. I'm not sure I can be of any further help - sorry.

Good luck.

 

[Danialle]

Thanks very m,uch for all your help.

 

[suoirotciv]

How can I connect to a Database using SQLDMO?

I can only connect to a server with the given sample above. but how can i connect my recordset to the connection that was made by SQLDMO?

Please pardon the grammar.
Not good in english.

 

[Glasgow]

Strictly speaking, as I understand it, SQLDMO plays no part in the connection process - it just allows you to check if the SQL Server is accessible and mess with it if it is not.

I use ADO. Example as follows:

Dim DbConnect as New ADODB.Connection
Dim RsMine as New ADODB.Recordset

DbConnect.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDb;Data Source=MySQLServer"

RsMine.Open "SELECT * FROM MyTABLE WHERE Name='Smith'", DbConnect, adOpenStatic, adLockReadOnly, adCmdText
...
...
RsMine.Close
DbConnect.Close
Set RsMine = Nothing
Set DbConnect=Nothing

Hope that helps

 

[suoirotciv]

Thanks for the response.
Right now I'm already using the sample code above for connecting to a database and getting records.

But some problems we encounter right now is regarding network connections (I really dont know if it is network connection), but the main problem is that a the application lost its connection to the server.

Then I do some search on how to detect this problem. And it gives me the "Private Function object_ConnectionBroken( Message as String) as Boolean" of SQLDMO.

And it say something that it can be use to reconnect/refresh the connection to the server/database.

What can you suggest on my problem?



Please pardon the grammar.
Not good in english.

 

[Glasgow]

I confess that I have never used or had cause to use this event.

The suggestion is that, if you have code like:

Dim SrvOurs As New SQLDMO.SQLServer2
SrvOurs.AutoReconnect = False

then you can also have a function

Private Function SrvOurs_ConnectionBroken( Message as String ) as Boolean
If MsgBox("Server connection has been lost - do you want to try to reconnect",vbYesNo) = vbYes then
  SrvOurs_ConnectionBroken = True
Else
  SrvOurs_ConnectionBroken = False
End If

where the latter code is triggered on broken connection and will try to reconnect if requested. However, I am not sure how/whether this would work or whether you might have to use the WithEvents keyword when declaring the SrvOurs object.

 

[suoirotciv]

Just to follow up

"Can I just use the <Private Function object_ConnectionBroken( Message as String) as Boolean> to check the connection to server and if connection was detected as broken, can I just call my LocalConnection function that connects to the server/database?"

I ask this question because I do not know how to use the Private Function object_ConnectionBroken( Message as String) as Boolean

Please pardon the grammar.
Not good in english.

 

[suoirotciv]

Okay I will try that first and give you feedback.

Thanks!

Please pardon the grammar.
Not good in english.

 

[Glasgow]

With regard to

>"Can I just use the <Private Function object_ConnectionBroken( Message as String) as Boolean> to check the connection to server and if connection was detected as broken, can I just call my LocalConnection function that connects to the server/database?"

The suggestion from the documentation is that, provided you return True from the function, the attempt to reconnect will be automatic. However, it's the declaration of the object and triggering of the event that I am a little pessimistic about. I tried looking for an example but without success - if you search for ConnectionBroken in this forum you will see a couple of suggestions but no real detail.

I think you will probably have to declare SrvOurs at module level and, as I said, perhaps using WithEvents keyword.

 

[suoirotciv]

Thanks for the input . . .

Please pardon the grammar.
Not good in english.