By: K. Brian Kelley | Comments (8) | Related: 1 | 2 | 3 | 4 | > Linked Servers
Problem
I am trying to create a stored procedure which creates a linked server connection, performs a query against the linked server, and then tears down the linked server connection. I don't want the connection to be up at all times, hence the reason I want to create the connection in the stored procedure. However, when I try to create the stored procedure, I get the following error:
Msg 7202, Level 11, State 2, Procedure usp_GetDatabasesFromRemoteServer, Line 8> Could not find server 'LinkingServer' 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.
How do I get around this and create the stored procedure?
Solution
The trick to solving this problem is to create the linked server before executing the CREATE PROCEDURE statement. What's happening here is SQL Server is trying to validate the syntax of the stored procedure and cannot find the reference to "LinkingServer".
For instance, if you have this query within the body of the stored procedure this is what SQL Server is failing on when trying to create the procedure.
SELECT name FROM [LinkingServer].master.sys.databases;
SQL Server has to have the reference to "LinkingServer" defined or it can't validate the query. As a result, the stored procedure creation will fail.
To get around this issue, you'll need to manually execute sp_addlinkedserver to create the connection as shown below and then create the stored procedure.
EXEC sys.sp_addlinkedserver @server = 'LinkingServer', @srvproduct = N'SQL Server';
Once the linked server has been created, you can create the stored procedure. And once the stored procedure has been created you can drop the linked server, because the SP should create it the next time it runs.
One other step I take within the stored procedure is to check for the existence of the linked server. The reason for this is if the linked server already exists because it didn't get cleaned up properly or because someone else created it, the stored procedure will throw an error when it runs. This is easy to do using a query against sys.servers for the name of the server.
The following is an example of a stored procedure where such a check is being made along with the creation and destruction of the linked server.
CREATE PROC dbo.usp_GetDatabasesFromRemoteServer
AS
BEGIN
IF NOT EXISTS(SELECT name FROM sys.servers WHERE name = 'LinkingServer')
EXEC sys.sp_addlinkedserver @server = 'LinkingServer', @srvproduct = N'SQL Server';
SELECT name FROM [LinkingServer].master.sys.databases;
EXEC sys.sp_dropserver @server = 'LinkingServer', @droplogins = 'droplogins';
END;
GO
If the query against sys.servers finds the "LinkingServer" linked server connection already exists, it will not attempt to create the linked server connection. By the way, this also ensures the stored procedure runs properly the first time you execute it, even if you forgot to use sp_dropserver to remove the linked server connection you built when trying to create the stored procedure. Without this sort of logic, you might see the following error any time the query runs and that linked server is already present:
Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82 The server 'LinkingServer' already exists.
While the query (the SELECT name ...) will still execute properly, you'll generate the error as well. Better to anticipate that possibility and put in the extra line of code to handle it.
Next Steps
- Next time you have the need to create linked servers, but do not need to keep the linked server connection available at all times, try using this approach to help minimize any potential security issues between servers.
- Review these others tips about linked servers
- Review these other tips about SQL Server security
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
