Server does not exist or access denied

Posted on 2013-06-07
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 500 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 myconnectionstring
    End If
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

863 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now