Server does not exist or access denied

Posted on 2013-06-07
Medium Priority
Last Modified: 2013-06-09
Hi experts,

I have a strange problem that I can't figure out...

I have a VB6 application that updates some SQL-Server 2008 tables.

Some updates are sent to the sql-server for each record in some table loops.

That works fine for some time and some 10000 records are updated or inserted.

But then - all of the sudden - the sql-server reports: server does not exist or access denied.

Our network folks tell me that it is not a network issue. Both SQL-Server and my machine are VMs hosted on the same Machine.

my connection string looks like:
"Provider='sqloledb';Data Source='gdedb001\business';Initial Catalog='myDatabase';user id=xxxxx; PWD=yyyyy;"
But i don't think that is a coonection string issue as thoudands of records are updated properly.

what  my update routine looks like:

1. select loop
         for each 1. record:
             Update or insert some records
             another. select loop
                   Update or insert some records
            end 2. loop
end 1. loop

has anybody an idea what the reason could be???

Thanks a lot!

Question by:Lupo09
  • 3
  • 2
LVL 11

Expert Comment

ID: 39228684
Are you properly closing your connections?

Do you SQL management studio?  Can you access it via that when this happens?

If you can check out the monitoring tools and see what is going on.

Author Comment

ID: 39229987
I'm not closing the connection.

In my script I open the connection in the beginning and use the same connection for all selects / updates / inserts.

I always do it that way and usually it workds well.

What I didn't say earlier:
my function (VB6 Function ) is recursive and loops a tree structure.:

Some Details may help, it's like that:
function myRecursiveUpdateFunction (ID)

    Dim nameConn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Dim strSQL as  String

    strSQL = "Select ID from myTable where Parent_ID= " & ID
    set rs = nameConn.execute(strSQL)
    While Not rs.EOF
        strSQL = "Update Table_2 set x='" & something &  "' where ID = " & rs("ID")
        set rs2 = nameConn.execute(strSQL)

        myRecursiveUpdateFunction rs("ID")


    set rs=nothing
    set rs2 = nothing
    set nameConn=Nothing

end Function

My function is quite more complex but I think, this outlines how it works...

Your second question:
Yes, I regularly work with SQL-MS and can access it without problems when that happens.  As I have that function in VB6, I can enter the debug mode, extract the SQL code and execute it in SQL-Server Management Studio sucessfully- In that debug-mode I can resume (continue) the function after a minute or so, but not after a second.
The connection seems to be "overloaded" somehow but recoveres after some time...

Thanks for further help!

LVL 11

Accepted Solution

wrmichael earned 2000 total points
ID: 39230050
Since you are doing recursive calling and updating many records you might be losing your connection in the middle of your code.

it has been a while since I've done this with VB6 but you may need to check your connection to make sure it is still valid and if not reconnect it.

Things out of your control can cause that connection to end.

I also thought you would need a rs.close before you setting it to nothing but... its has been a few years.

When it happens in "debug" mode can you still see the active connection to the server?

I know it can slow you down much but I usually open and close the connection each time I do something.

To see if this is the issue you could put the open and close in your routine and if it goes away you at least know how it is broken.

I think the code was something like this:

    If oConnection.State = adStateClose Then
    oConnection.open myconnectionstring
    End If
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 21

Expert Comment

by:Alpesh Patel
ID: 39231097

Author Comment

ID: 39232912
Thanks for that hint, but none of the proposed solutions applies, they all deal with the problem that a connection is not possible at all. In my case, I have a connection that works fine for a while, but than - after some hundred sql-commands it stops with that error message...

Author Closing Comment

ID: 39232922

Thanks for your answer!

I have tried to close the recordsets right after using - problem still remains.
Good idea to check the connection state when that problem appears: The connection state is 1 - adStateOpen, but when i try to continue the code (with F8 in VB6)  I get the same message: Server does not exists or access denied, wich obviosly is not the case...

But then I got it :-)
I followed your hint to close the connection and open it for ech time I need it and I eaven introduced several seperate connections for different parts in my code. That helped!!! The message nor longer pops up! And the performance is not affected negativly.

In all those years that I do ADO programming I never did it this way but alway reused the same connection for all commands and it (before now) alway went well.
But it is never too late to learn...

I'm working on translating these update routines to Dot.Net on C# - I will remember this here, too...

Thanks again!


Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

578 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