error -2147467259 sql server does not exist or access is denied

From: eli silverman (elisilverman_at_discussions.microsoft.com)
Date: 02/24/05

  • Next message: aek: "ADSI Script to check Computer name and Domain" 
    Date: Thu, 24 Feb 2005 11:31:08 -0800

    I have a number of applications that I have written in VB6 connecting to SQL
    2000 or MSDE 2000 servers and all at one point or another seem to exhibit the
    same issue. My problem used to only occur at a few locations where the
    application needed to run on a box other than the SQL server itself. however
    I recently got a new HP laptopand I am experiencing the issue when my local
    application attempts to connect to my local SQL server. Basically what
    happens is as follows. I have a billing template detail table that will
    potentially contain hundreds of thousands of detail records ( about 20,000
    per month of billing history). I need to read through that table and create
    invoices in our accounting software database. I need to read typically about
    200-1200 records depending on the size of the given client and then create
    records in about a dozen different tables in our accounting software to
    create the invoices. What happens is that the system will open my clietn side
    recordset then start processing the records. I use a variety of adodb
    recordsets and .execute statements to retrieve, assign and set the values on
    the records I am processing. The recordset contains billing details for a
    number of storesabout 2-7 records per store. The system processes through the
    stores at about 2-3 stores per second (i.e. about 15-25 records per second)
    but after a given number of records, varying from system to system where the
    process is running (currently agout 400 on my laptop), the system seems to
    hang when trying a process (either rst.open, rst.update or cnn.execute)
    If I wait about 20 seconds then re-issue the same command the system returns
    to processing throug the stores at the same speed as before. then after about
    the same 400 records it hangs again, I wait 20 seconds and re-issue the
    command and the system goes back to processing. The problem is not with the
    code or the data as I can restore the database to a different SQL server and
    run the code from my machine and the problem will not happen or it happens at
    a different interval. I have managed to get around the error message by
    setting the "connect timeout" property in my connection string to 300 and by
    setting the connectiontimeout and commandtimout propertyon my
    adodb.connection to 300. I am sure I only needed to do one or the other but
    not all three to achieve my goal but I was desparate with frustration. I no
    longer appear to get the error message but the system still stops about every
    400 records and takes about a minute to get past that one record then starts
    processing through the records at about 15-20 record/sec untill it has gone
    through another 400. The bottom line is that the system seems to spen about
    1/3 to 1/2 of it's processing time trying to recover from theis communication
    delay. It seems like there is some threshold or buffer, in the number of
    commands or bytes of data being sent to the SQL server, that I reach that the
    SQL server then needs a few seconds to recoup from.
    I have a second database that I use to copy data from an ODBC data source
    into matching SQL tables. It maintains two ADODB connections one to the
    Foreign ODBC source and the other to my SQL server. It opens a table on from
    the ODBC source then parses through the table copying record by record into a
    local table using creating an SQL insert into command that is executed with a
    cnn.execute statement. then it closes the first tables and opens then next
    and repeats the process. That program has about 10 tables that are coppied
    and some contain upward of 18,000 records. This code exhibits the same
    problem at about every 800-1400 records processed depending on the system it
    is running on. what is interesting is that the number of records before
    timeout are independant of the tables being processed. If my first table has
    200 recods, my seconf has 1300 and my third has 1000 the system will crash at
    records 400 and 1000 of table 2 and then again at records 300 and 900 of
    table 3. so even though I have closed my adodb.recordsets and opened a new
    set (even with rst = new adodb.recordset) if the system crashes every 600 and
    it made it through 200 on the last recorset since the last timeout it will
    only make it htrough 400 on the new recordset before the next timeout.

    The numbers I am stating are just rough examples I have seen it hang every
    411 or 1237 and it varies from program to program and system to system.

    Is there anything I can do to try to help identify where the drop in
    communication is coming from?

    Is there any known bug with SQL server 2000 and VB6 SP5 or 6 or ADO that
    causes SQL server to appear to pause.

    Much of my current code was hardcoded to the old SQL server name and there
    is to much code to change so I have either the SQL client or ODBC driver
    setting up an alias so that when the program looks for the old server name it
    goes straight to the new on. I don't believe this is the problem as this is
    not the case with the second application i mentioned and it also exhibits the
    problem.
    In all the cases SQL is set up to communicate using TCP/IP and that is the
    only network protocol installed on the workstations or servers.

    I used to be running XP Pro SP1 on a toshiba laptop with vb6 sp5 and SQL
    2000 sp3 I am now running XP Pro SP1 on an HP laptop with first vb6 sp5 then
    sp6 and SQL 2000 sp3

    Any help/guidance would be greatly appreciated.

  • Next message: aek: "ADSI Script to check Computer name and Domain"

    Relevant Pages

    • Re: DCOM Error - Event 10009
      ... using SQL Server Management Studio 2005. ... Whenever we first launch SQL ... when connecting to a different registered server, it will hang again for 5 - ... I have checked DCOM Config using dcomcnfg, ...
      (microsoft.public.sqlserver.clients)
    • RE: Fulltext failure on a 2 node cluster
      ... Server full-text search resource online: "SQL Cluster Resource 'Full Text' ...
      (microsoft.public.sqlserver.clustering)
    • Re: HELP PLEASE ~ ???
      ... You mentioned that it went ahead and added a SQL ... SQL Server 2000 database for all my data. ... find the connectionString in the newly recreated SQLExpress database. ... The connection string specifies a local Sql Server Express instance ...
      (microsoft.public.dotnet.framework.aspnet)
    • IBM INFORMIX ODBC DRIVER 3.00.00.13223 - ICLIT09B.DLL EXCEPTION_AC
      ... then use a link server – Microsoft OLE DB Provider for ODBC Driver. ... and Linked server test connection successfully and I can return most records ... A severe error occurred on the current command. ... Here's my SQL and ERRORS ...
      (microsoft.public.sqlserver.odbc)
    • EXCEPTION_ACCESS_VIOLATION Error: 17310, Severity: 20, State: 1.
      ... then use a link server – Microsoft OLE DB Provider for ODBC Driver. ... and Linked server test connection successfully and I can return most records ... A severe error occurred on the current command. ... Here's my SQL and ERRORS ...
      (microsoft.public.sqlserver.datamining)