?
Solved

ADO Recordset Delete Error SQL 2005 DB

Posted on 2008-10-12
5
Medium Priority
?
859 Views
Last Modified: 2013-11-16
Hi,

Any help on the following would be appreciated:

We have a VB6 application which uses ADO (MDAC 2.7) to connect to a SQL database. Recently we have been testing it using a SQL 2005 database and have come across a problem.

It would seem that if you try and issue a delete on a server side recordset you get the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'WINXP-TEST-V\SQLEXPRESS' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

If you change the recordset to client side it works fine. Unfortunately changing the recordsets to client side is not really an option as it is a huge program and to change all the cursors would be a massive task. An example of the code which creates this problem is shown below.

Has anyone got an idea why this does not work with a server side recordset?

Many Thanks
Dim cConnstr As String
    Dim oConn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cSql As String
    
    cConnstr = "Driver=SQL Server;Server=WINXP-TEST-V\SQLEXPRESS;UID=sa;PWD=sa1234;database=aa_jobdata"
    Set oConn = New ADODB.Connection
    With oConn
        .CursorLocation = adUseServer
        .Mode = adModeShareDenyNone
        .Open cConnstr
    End With
    
    cSql = "Select * From Costtype WHere Code = 'ZZZZ'"
    
    With rs
        .CursorLocation = adUseServer
        
        .Open cSql, oConn, adOpenKeyset, adLockOptimistic, adCmdText
        
        .Delete
        
        .Close
    End With

Open in new window

0
Comment
Question by:StevebData
[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
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22696810
actually, deleting rows with recordset is not recommended at all...

it should rather be done like this:
oConn.Execute "delete From Costtype WHere Code = 'ZZZZ' "

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22697985
I will go out on a limb and state that you are not getting the error in the Delete method, but rather in the Open method.

But I agree with angelIII you should not be using a recordset in the first place.
0
 

Author Comment

by:StevebData
ID: 22698506
The open method works fine, its just the delete which fails.

I know its not the most elegant solution or best practice but its what I inherited, and to change it would be a massive task as our code base is huge.

I should have pointed out that this application (which is about 10 years) old has been running on SQL 2000 for a number of years without any problems and its only when we tested it on a SQL 2005 database that we got these problems.

If anyone could explain why this method no longer works that would be a great help and would allow me to make an informed decision on what I do next.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22698546
this is a named instance, which needs, for being connected with ODBC, to be aliased in the client computer's sql network utility...
or use another connection string, using the sql oledb provider instead, for example.
0
 

Accepted Solution

by:
StevebData earned 0 total points
ID: 22701766
In case anyone gets the problem above, please see the following link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=329107&SiteID=1

It was caused because the computer which the SQL server resides on had been renamed in the past.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

719 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