Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

Server does not exist or access denied

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
Lupo09
Asked:
Lupo09
  • 3
  • 2
1 Solution
 
Wayne MichaelSenior Software DeveloperCommented:
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
 
Lupo09Author Commented:
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
 
Wayne MichaelSenior Software DeveloperCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Lupo09Author Commented:
@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
 
Lupo09Author Commented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now