Error 7202: Linked Server Missing From sys.servers and Ad Hoc Query Blocks

Fix it now

Error 7202 means the first part of your four-part name is not registered in sys.servers on this instance, so nothing was ever sent to the network. Error 7415 is a different refusal: ad hoc access through OPENROWSET and OPENDATASOURCE is denied for that provider. Register the linked server, or enable ad hoc access deliberately.

SELECT name, product, provider, data_source, is_linked FROM sys.servers;
SELECT @@SERVERNAME AS Registered, SERVERPROPERTY('MachineName') AS Machine;
EXEC sp_addlinkedserver @server = N'SALESSRV', @srvproduct = N'', @provider = N'MSOLEDBSQL', @datasrc = N'sqlhost01\SALES';
EXEC sp_testlinkedserver N'SALESSRV';

1. Read the first result. If the name in your query is not in that list, that is the whole answer and the third statement creates it.
2. Check the second result before anything else. Two different values mean the machine was renamed and @@SERVERNAME is stale, which produces 7202 against your own instance.
3. Map the login the application will use with sp_addlinkedsrvlogin rather than leaving the default, then rerun the query as that account and not as yourself.
4. If you got 7415 instead, no linked server is missing. Either rewrite the statement against a linked server, or turn on the Ad Hoc Distributed Queries option knowing what it opens up.

sp_addlinkedserver has a short form for another SQL Server: EXECUTE sp_addlinkedserver N'SEATTLESales', N'SQL Server';. When @srvproduct is SQL Server, the provider, data source and catalogue arguments are not needed.

If the four-part name resolves now you can stop here. If not, the next section explains what the engine is looking up and why the network never enters into it.

Why it happens

A four-part name is server, database, schema, object. The engine resolves the first part against sys.servers, a catalogue table holding this instance’s own name plus every linked or remote server explicitly registered on it. If the name is not in that table, resolution stops. No DNS lookup happens and no connection is attempted, which is why 7202 comes back instantly and looks identical whether the remote machine is running, switched off or entirely fictional.

The published text says so directly: could not find the server in sys.servers, verify the server name, and if necessary run sp_addlinkedserver to add it. It is a registration error with a registration fix. It is also raised at severity 11 rather than the 16 most of the linked-server family use, which is worth knowing when you are scanning a log for something more dramatic.

sys.servers also holds the instance’s own name, written by sp_addserver at install time. Rename the machine afterwards and @@SERVERNAME keeps returning the old name, so any script that qualifies objects with the current machine name gets 7202 against its own instance. Comparing @@SERVERNAME with SERVERPROPERTY('MachineName') settles that in one query.

7415 guards something else entirely. OPENROWSET and OPENDATASOURCE let a statement name a provider and a connection string inline, with no registration, no login mapping and nothing in the catalogue to audit. Microsoft’s own wording is that enabling ad hoc names means any authenticated SQL Server account can reach the provider, and that these functions should be used only for data sources accessed infrequently. The option is off by default, and turning it on is a decision about the instance’s attack surface.

Full reference

Turning ad hoc access on, the way Microsoft publishes it

USE master;
GO
EXECUTE sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

Ad Hoc Distributed Queries is an advanced option, so show advanced options has to be 1 before sp_configure will even acknowledge the name. Without it you get error 15123, the configuration option does not exist or it may be an advanced option, which reads like a typo and is not one.

Which refusal you are looking at

Procedures worth knowing

Why 7311 pushes you towards OPENQUERY

A four-part name asks the provider for a schema rowset so the local optimiser can plan across both sides. 7311’s published text is precise about the failure mode: the provider supports the interface but returns a failure code when it is used. That is a provider limitation, not something you configure away. OPENQUERY avoids the requirement entirely because the remote side parses and executes the text itself and hands back a result, so it is the workable route for providers that cannot describe their own schema.

Choosing between a four-part name and OPENQUERY

• A four-part name lets the local optimiser plan across both instances, which is convenient and can drag whole tables back before filtering.
• OPENQUERY sends your statement to the remote server, so filtering and aggregation happen there and only the result crosses.
• Non-SQL Server providers frequently have dialects the local parser will not accept. OPENQUERY sidesteps that.
• OPENQUERY takes a literal string, so parameters have to be composed carefully. Prefer a stored procedure on the remote side over building the text locally.

When the query works for you and fails for the application

7314’s published text names permissions as one of its two causes, and that is the case people skip. The linked server login mapping decides which remote credentials are used, and a mapping that works for your Windows account can resolve to something with no rights for a service account. Test as the account that actually runs the workload, not as a sysadmin, and check the mapping with sp_helplinkedsrvlogin before concluding the remote object is missing.

Agent jobs that fail while SSMS succeeds

• Confirm which instance owns the job. A job on a second instance on the same machine has its own sys.servers.
• Confirm the job step’s database context. A step that runs in master resolves unqualified names differently from your query window.
• Confirm the step’s run-as account, then list sys.servers and test the linked server as that account.
• If the step uses OPENROWSET, remember the Ad Hoc Distributed Queries option is instance-wide, so it may be on where you tested and off where the job runs.

Every code this article covers

Confirm the fix worked

1. SELECT name FROM sys.servers; returns the exact name your query uses.
2. EXEC sp_testlinkedserver N'<name>'; completes without raising anything.
3. SELECT @@SERVERNAME, SERVERPROPERTY('MachineName'); returns two matching values.
4. The four-part-name query returns rows under the account the application uses, not only under yours.
5. If you enabled ad hoc access, EXEC sp_configure 'Ad Hoc Distributed Queries'; shows a run value of 1 and you have written down why.

Questions people ask about this