mssqltips logo

On demand creation and destruction of a SQL Server Linked Server

By:   |   Updated: 2009-09-25   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | More > 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


Last Updated: 2009-09-25


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, July 04, 2012 - 9:21:26 AM - Susheel Kumar Back To Top

Hello MsSqltips,..............

 

select * from linksserver...sheetname$ 

in the above sql suery

What is mean by [ ...  ]

 

Thnks and Regards

S.K.Verma

 


Monday, February 07, 2011 - 5:06:02 PM - ian Back To Top

Hi all, I know this is an old thread, but I'm trying to get some information - maybe if anyone's still seeing this thread they will have some advice for me?

For various reasons which are not particularly relevant here, I am interested in using this method to read excel files. Why not openrowset, you might ask? Well, I don't know the name of the worksheets, as they are user uploads from a web app. Anyway, I got this idea from a few posts and was able to make a stored proc that adds the linked server, queries the first worksheet, and then drops the linked server. It is fast and works great.

However, this may be hapening more than a hundred times per day; this just sounds like a bad idea to me, but I have no evidence of that whatsoever. Fragmentation? Excessive db growth? Other problems? (I'm taking your security concerns to heart, and checking for file exists, and so forth). I'd love to hear that  this solution is a viable one; it sounds bad on the face of it, but I don't really know, maybe it is just fine.

So, anyone with any knowledge and/or opinions on the matter care to offer some advice?

Thanks in advance,

Ian

 


Tuesday, October 06, 2009 - 8:36:53 PM - K. Brian Kelley Back To Top

[quote user="mharr"]

There are a couple of points that you do not mention that can catch users implementing your tip.

First, executing sp_addlinkedserver requires ALTER ANY LINKED SERVER permissions, which only the sysadmin or setupadmin server roles have by default. You will need to grant access to this permission for the user or application account that will be running the stored procedure (you don't run application accounts as sysadmin, do you?)

Second, by default, adding linked server adds permisisons for all logins on the server to access the linked server. Having a "temporary" like you plan certainly mitigates the security risk, but just as you check to make sure if the linked server is there in case it was not cleaned up last time, you probably should lock down access to just those logins that need it. You probably want to execute "sp_droplinkedsrvlogin , NULL" to drop all default logins, then explicit add your login with sp_addlinkedsrvlogin sproc (Of course, these sprocs require ALTER ANY LOGIN permission, which only securityadmin and sysadmin roles permit; will need to add this permission the user or application account.)

Third, the reader of this tip may want to do this for external file access, like linking to an Access database, Excel spreadsheet, or delimited text file. In these cases, it will be prudent to check if the desired file is there before attempting to create the link server. The undocumented extended procedure master.dbo.xp_fileexist (documented in previous tip http://www.mssqltips.com/tip.asp?tip=1272) is very handy for doing this.

Thanks for the handy tip.

[/quote]

 Yup, I didn't cover the detailed security side of things, because that's probably better suited for another tip. The idea was to give a quick solution like when you're running a job and need a temporary connection to another SQL Server.

 


Tuesday, October 06, 2009 - 8:35:22 PM - K. Brian Kelley Back To Top

[quote user="dooberry"]

So the answer is use a job to do it and run it when required?

[/quote]

 This provides a solution to use with a job, yes, if you don't want a linked server around all the time.


Friday, September 25, 2009 - 6:23:10 AM - mharr Back To Top

There are a couple of points that you do not mention that can catch users implementing your tip.

First, executing sp_addlinkedserver requires ALTER ANY LINKED SERVER permissions, which only the sysadmin or setupadmin server roles have by default. You will need to grant access to this permission for the user or application account that will be running the stored procedure (you don't run application accounts as sysadmin, do you?)

Second, by default, adding linked server adds permisisons for all logins on the server to access the linked server. Having a "temporary" like you plan certainly mitigates the security risk, but just as you check to make sure if the linked server is there in case it was not cleaned up last time, you probably should lock down access to just those logins that need it. You probably want to execute "sp_droplinkedsrvlogin , NULL" to drop all default logins, then explicit add your login with sp_addlinkedsrvlogin sproc (Of course, these sprocs require ALTER ANY LOGIN permission, which only securityadmin and sysadmin roles permit; will need to add this permission the user or application account.)

Third, the reader of this tip may want to do this for external file access, like linking to an Access database, Excel spreadsheet, or delimited text file. In these cases, it will be prudent to check if the desired file is there before attempting to create the link server. The undocumented extended procedure master.dbo.xp_fileexist (documented in previous tip http://www.mssqltips.com/tip.asp?tip=1272) is very handy for doing this.

Thanks for the handy tip.


Friday, September 25, 2009 - 6:18:06 AM - mharr Back To Top
There are a couple of points that you do not mention that can catch users implementing your tip. First, executing sp_addlinkedserver requires ALTER ANY LINKED SERVER permissions, which only the sysadmin or setupadmin server roles have by default. You will need to grant access to this permission for the user or application account that will be running the stored procedure (you don't run application accounts as sysadmin, do you?) Second, by default, adding linked server adds permisisons for all logins on the server to access the linked server. Having a "temporary" like you plan certainly mitigates the security risk, but just as you check to make sure if the linked server is there in case it was not cleaned up last time, you probably should lock down access to just those logins that need it. You probably want to execute "sp_droplinkedsrvlogin , NULL" to drop all default logins, then explicit add your login with sp_addlinkedsrvlogin sproc (Of course, these sprocs require ALTER ANY LOGIN permission, which only securityadmin and sysadmin roles permit; will need to add this permission the user or application account.) Third, the reader of this tip may want to do this for external file access, like linking to an Access database, Excel spreadsheet, or delimited text file. In these cases, it will be prudent to check if the desired file is there before attempting to create the link server. The undocumented extended procedure master.dbo.xp_fileexist (documented in previous tip http://www.mssqltips.com/tip.asp?tip=1272) is very handy for doing this. Thanks for the handy tip.

Friday, September 25, 2009 - 4:59:59 AM - admin Back To Top

You can just follow the steps in the tip you do not need to do anything else.

Basically before you can CREATE the stored procedure you just need to have the linked server in place so the stored procedure can be created.

Once the stored procedure has been created the linked server will be created and dropped when the stored procedure runs.

 


Friday, September 25, 2009 - 4:28:45 AM - dooberry Back To Top

So the answer is use a job to do it and run it when required?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools