none
Upgraded to SQL Server 2005. Updates no longer work. Error "Could not find server 'DEVDB' in sysservers. Execute sp_addlinkedse"

    Question

  • Hello!

    We just upgraded to SQL Server 2005 from SQL Server 2000. The DB was backed up using Enterprise Manager and restored with SQL Server Management Studio Express CTP. Everything went as expected (no errors, warnings, or any other indicator of problems).

    The DB resides in a DB Server (Server1) and the application we are running is a Client/Server system where the AppServer resides on Server2.

    During the application's operation all read, create, and delete transactions work fine but no update works. When viewing details in Trace Log I see this message after attempting any update:

    Could not find server 'Server1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. (7202)

    Any help is greatly appreciated,

    Lucio Gayosso

     

    Tuesday, February 28, 2006 10:42 PM

Answers

  • I took at look at the code and the trace you provided.

    The problem might be that the server name isn't set properly.

    run this from managment studio,

    select @@servername

    If it doesn't equal "ONE" then issue these commands

    sp_dropserver <whatever was returned by @@servername>

    go

    sp_addserver 'ONE', local

    then restart the server.

    Additionally you might consider changing this line:

    RS.Source = "TABLE1"

    to

    RS.Source = "Select * from Table1"

    as it saves several round trips to the server as the client figures out this is a table not a stored procedure.

     

    Saturday, April 22, 2006 11:45 PM

All replies

  • I'm moving this thread to the Database Engine forum because it is not a setup issue.
    Wednesday, March 01, 2006 5:48 PM
  • Capture a profiler trace of the update statement, and be sure to get stmt starting events.  It sounds as though your client app may be issuing a 4 part name in the update statement such as update server1.db.dbo.table. 

    Now this should work fine, providing that @@servername matches the name "server1".  If this is the case verify that @@servername does match the server identifier passed in the update statement.

    Saturday, March 11, 2006 7:10 AM
  • The following update statement causing the above error:

    RS.ActiveConnection = CONN
    RS.CursorType = 1
    RS.LockType = 3
    RS.Source = "TABLENAME"
    RS.Open
    RS.AddNew

    RS("AAA")= WHATEVER
    RS("BBB")= WHATEVER

    RS.Update   ' causing error if LockType = 3. Other LockTypes do not work...
       pID = RS("ID")

    RS.Close

    Any idea why?

    The INSERT statement works fine though but doesn't allow to retrieve the saved record ID (in red) which is critical for my application:

    SQL = "INSERT INTO TABLE (AAA,BBB) VALUES ('"&WHATEVER&"','"&WHATEVER&"') ;"
    Set RS = CONN.EXECUTE(SQL)

    I would appreciate SQL Team response very much. Thank you!

    Wednesday, April 19, 2006 10:44 PM
  • What client api are you using - Ado, DAO, RDO, SqlClient?

    Please run a profiler trace as outlined in my last post, and additionally script the object "TABLENAME" and post that as well.

     

     

    Thursday, April 20, 2006 4:45 AM
  • Thank you for addressing my post so quickly.

    We're using ADODB.Connection for connection and ADODB.Recordset for recordset.

    I ran the Profiler Trace on Update query as per your request. Here's the link:

    Trace1.trc in Winzip (otherwise it won't open)

    TABLENAME object is a simple table with the columns ID, AAA, BBB where ID is int, NOT NULL. Scripted:

    UPDATE [HFN].[dbo].[Table1]
    SET [AAA] = <AAA, nvarchar(50
    ),>
          ,[BBB] = <BBB, nvarchar(50
    ),>
    WHERE <Search Conditions,,>

    In other words, the Update query above is just a widely used [by me] way of inserting new record into the table, which worked in SQL2000. I love the power of 2005 and its tools. Awesome job guys!

    My question -- Is there a way NOT to replace all Update queries like in my previous post which we have in all our sites (probably in 300-400 scripts all together) with INSERT statements, which sure work in 2005?

     

     

    Thursday, April 20, 2006 2:25 PM
  • Peter, the trace doesn't include statment starting events.

    Could you run another trace with these events from profiler:

    Stored Procedure
    RPC:Completed
    RPC:Starting
    SP:StmtCompleted
    SP:StmtStarting

    TSQL
    SQL:BatchCompleted
    SQL:BatchStarting
    SQL:StmtCompleted
    SQL:StmtStarting

    Performance
    Showplan XML

    Errors and Warnings
    All

    Most of these are advanced events so you'll need to check the show all columns and show all events options in the new trace dialog.

    You shouldn't have any need to change your statements, it's just a matter of tracking down the problem. 

    The update statement above looks harmless enough, but I'm really interested in the actual table script.  Right-click on the table from Managment studio, choose script table as -> Create To... and post that output.

    Also please include the exact error text you are getting, complete with error number and state.

     

     

     

    Thursday, April 20, 2006 5:03 PM
  • Jerome,

    I'm not sure if you got my email. Here's the TRACE2.ZIP LINK with Update script, Trace as per your request above, error page (http://wt.webstudios.com/1SQL-Update_Statement.asp) and the scripted table.

    I hope you can figure it out. Thank you!

    Saturday, April 22, 2006 4:14 PM
  • I took at look at the code and the trace you provided.

    The problem might be that the server name isn't set properly.

    run this from managment studio,

    select @@servername

    If it doesn't equal "ONE" then issue these commands

    sp_dropserver <whatever was returned by @@servername>

    go

    sp_addserver 'ONE', local

    then restart the server.

    Additionally you might consider changing this line:

    RS.Source = "TABLE1"

    to

    RS.Source = "Select * from Table1"

    as it saves several round trips to the server as the client figures out this is a table not a stored procedure.

     

    Saturday, April 22, 2006 11:45 PM
  • YOU SOLVED IT!

    Thank you!

    select @@servername shown "WINSERVER2003", which was machine name and not SQL Server instance name ("ONE").

    I ran the procedures you recommended:

    sp_dropserver WINSERVER2003
    go
    sp_addserver 'ONE', local

    Worked like a charm.

    Thank you again for the incredibly fast turnaround on the issue. You are the man! I can only imagine what you guys are going through right now.

    Also, I wanted to thank you all in your team for such incredible job with functions, interfaces and performance optimization  and other tools in 2005. It takes a second to do some tasks which could take a hour before. Awesome job! You left Oracle and all others so far behind.  Here at Web Studios we will definately pass a few dozen clients to you for the Enterprize Licences in the nearest future.

    Well done!

      

     

    Sunday, April 23, 2006 5:45 AM
  • Glad to hear that worked.

    Thanks for the reply!

    -Jerome

    Sunday, April 23, 2006 4:28 PM
  • Thanks for the soltion!  That helped me a lot also.  Basically, I think it'll help anyone who installs SQL 2005 on a server and then renames the server.

    THANKS!

    ST

    Wednesday, May 31, 2006 7:07 PM
  •  

    What do I do if @@ServerName returns the correct name?

     I am connecting to the server via ADODB from MS Access using an explicit connection string. 

    Any help would be appreciated.

    - Jon Biavati

     

    Monday, March 26, 2007 6:00 PM
  • Follow the steps from the post above:

     

    Run a trace with these events from profiler:

    Stored Procedure
    RPC:Completed
    RPCTongue Tiedtarting
    SPTongue TiedtmtCompleted
    SPTongue TiedtmtStarting

    TSQL
    SQL:BatchCompleted
    SQL:BatchStarting
    SQLTongue TiedtmtCompleted
    SQLTongue TiedtmtStarting

    Performance
    Showplan XML

    Errors and Warnings
    All

     

    When you encounter the error stop the trace and determine the last statement run on the connection encountering the error.  Verify that the statement generated, and the server name used are correct.

    Tuesday, March 27, 2007 5:26 AM
  • I had a similar problem and was receiving the sp_addlinkedserver error, added the server. Now I get the following error

    [Microsoft][ODBC SQL Server Driver][SQL Server]The cursor does not include the table being modified or the table is not updatable through the cursor.

     

    Wednesday, September 05, 2007 2:55 PM
  • We have encountered the same issue after an upgrade from SQL Server 2000 to SQL Server 2005 SP1, in which the connection string is the machine name\instance name and when performing a cursor delete operation on a SELECT query on a local database, the SQL Server database engine was reporting this error (while a fetch and a cursor update work fine).

     

    This was fixed in the same manner as above by dropping the server with the current @@servername and then rebuilding a new server name using the SERVERPROPERTY('MACHINENAME') along with the suffix after the '\' from the instance name.

     

    It is bad policy explaining to administrators that such a simple operation as renaming the machine's name causes an identify SQL Server.

     

    I have no issue that @@servername can differ from the current machine name, in several cases where it makes sense to preserve the existing @@servername. What I find difficult is that SQL Server database engine either uses the connection string or the current machine name to resolve the server, when in fact it is the local server.

     

    I can only conclude that there is a bug in the SQL Server 2005 (SP1) database engine. Has this been fixed in one of the SQL Server SP2s or an existing hot-fix.

    Wednesday, October 31, 2007 7:52 PM
  • Thanks Jerome,

     

    This post helped. We virtualised the production servers to create a new test environment.

     

     

     

    Tuesday, August 12, 2008 1:11 AM
  • Thanks a lot. its a great help.

     

    Tuesday, November 25, 2008 6:12 AM
  • This was a lot of help to me. However, after I got things straightened out I could still see the old database engines connections in the connection drop-down menu.

    Here is a fix that I found for this issue:

    Quite often it happens that SQL Server Management Studio’s Dropdown box is cluttered with many different SQL Server’s name. Sometime it contains the name of the server which does not exist or developer does not have access to it. It is very easy to clean the list and start over.



    [img]http://www.pinaldave.com/bimg/servernamelist.jpg[/img]

    Delete mru.dat file from following location.

    For SQL Server 2005:
    C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

    If you can not find mru.dat at above location look for mru.dat in following folder.
    C:\Documents and Settings\[user]\Application Data\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\mru.dat

    For SQL Server 2008:
    C:\Documents and Settings\\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\mru.dat

    If you can not find mru.dat at above location look for mru.dat in following folder.
    C:\Documents and Settings\[user]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\ShellSEM\mru.dat

    If you are using VISTA OS instead of XP OS. Replace C:\Documents and Settings\[user]\Application Data\Microsoft\ with
    C:\Users\\AppData\Roaming\Microsoft\ and it should work.

    Make sure to take note down any IP address of SQL Server you may need in future.

    Reference : Pinal Dave (http://www.SQLAuthority.com)

    Original Post : http://blog.sqlauthority.com/2008/11/05/sql-server-clear-drop-down-list-of-recent-connection-from-sql-server-management-studio/

    Friday, November 06, 2009 3:59 AM