Root Cause and Solution for Linked Server Error in SQL Server 2014: Server Not Found in sys.servers After Upgrade

During database system upgrades, migrating linked server configurations often presents technical challenges. When upgrading SQL Server from version 2005 to 2014, a common yet easily overlooked issue involves configuration consistency of server names in the sys.servers system table. This article provides a deep analysis of this problem's generation mechanism, diagnostic methods, and solutions based on actual failure cases.

Problem Phenomenon and Initial Investigation

After completing the upgrade, users reported error messages when executing specific stored procedures: Could not find server 'server name' in sys.servers. Verify that the correct server name was specified.. Initial investigation showed that linked server configurations appeared normal:

-- Verify linked server existence
SELECT name FROM sys.servers

-- Test basic query functionality
SELECT TOP 10 * FROM linkedserver.database.dbo.table

-- Test simple stored procedure execution
EXEC linkedserver.database.dbo.test_storedproc

However, specific stored procedure calls still failed:

EXEC linkedserver.database.dbo.failing_storedprocedure 
    @id, 'load ', 'v2', @file_name, @list_id = @listid OUTPUT

Root Cause Analysis

The core issue lies in configuration inconsistency of server names in the sys.servers system table. During SQL Server upgrades, although linked server functionality is migrated, the sys.servers table may retain server name records from the old version. This inconsistency prevents correct server identification during certain stored procedure executions.

Specifically, when executing stored procedures, SQL Server queries sys.servers to validate server name effectiveness. If the actually used server name doesn't match the registered name in the system table, even basic connection functions work normally, specific operations will fail. This problem is particularly common in cross-version upgrades because different versions may have subtle differences in server name handling mechanisms.

Solution Implementation

Resolving this issue requires reconfiguring server names in sys.servers. The following are complete solution steps:

-- Step 1: Remove old server name configuration
EXEC sp_dropserver 'Server_A'
GO

-- Step 2: Add correct server name
EXEC sp_addserver 'Server', 'local'
GO

After implementing these operations, verify configuration correctness:

-- Verify server name has been updated
SELECT name, is_linked FROM sys.servers
WHERE name = 'Server'

-- Retest the failing stored procedure
EXEC linkedserver.database.dbo.failing_storedprocedure 
    @id, 'load ', 'v2', @file_name, @list_id = @listid OUTPUT

Supplementary Verification and Configuration Recommendations

In addition to the main solution, the following supplementary verifications should be performed:

1. RPC Configuration Verification: Ensure the linked server's RPC (Remote Procedure Call) option is enabled, which is a prerequisite for executing remote stored procedures.
2. Permission Configuration Check: Verify that the execution account has sufficient EXECUTE permissions for the target stored procedure.
3. Connection Testing: Use the sp_testlinkedserver stored procedure for comprehensive connection testing.

Preventive recommendations include:

• Back up sys.servers configuration before upgrading
• Immediately verify consistency of all linked server configurations after upgrading
• Establish standardized linked server naming conventions

Technical Deep Analysis

From an architectural perspective, the sys.servers system table stores configuration information for all servers known to the SQL Server instance. Each entry contains critical metadata such as server name, whether it's a linked server, and provider name. When executing cross-server operations, the query optimizer references this metadata to build execution plans.

In upgrade scenarios, if server name configurations are inconsistent, it may cause:

1. Query parsers to fail in correctly identifying server identifiers
2. Security context transmission failures
3. Transaction management exceptions

Understanding this mechanism helps prevent similar problems and provides debugging ideas for more complex distributed query scenarios.