Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Server does not exist or access denied

Posted on 2013-06-07
6
Medium Priority
?
686 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 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