Solved

Server does not exist or access denied

Posted on 2013-06-07
6
644 Views
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!

Kai
0
Comment
Question by:Lupo09
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:wrmichael
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.
0
 

Author Comment

by:Lupo09
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")

    rs.MoveNext
    Wend

    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!

Kai
0
 
LVL 11

Accepted Solution

by:
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
    oConnection.open myconnectionstring
    End If
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39231097
0
 

Author Comment

by:Lupo09
ID: 39232912
@PatelAlpesh:
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...
0
 

Author Closing Comment

by:Lupo09
ID: 39232922
@wrmichael:

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!

Kai
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ssms - object execution statistics 12 37
SQL 2012 Syntax Error 5 24
t-sql month question 8 42
Scheduling Jobs for Execution: 4 14
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

708 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

20 Experts available now in Live!

Get 1:1 Help Now