Dynamic Linked Server Generator

It's handy to be able to setup and teardown a linked server at will.  Especially in dev envs.  You may need to refresh some data from a different environment and firing up SSIS is overkill when a simple INSERT INTO...SELECT is sufficient.  In this post I'll show you a utility I've been using for years that allows you to build a dynamic linked server quickly and easily.  

It's so easy to be able to change your Java app's connection string and point it to a different database.  Wouldn't it be nice if you could do that during your query development?  For instance, if you need to quickly refresh a dev table with data from another environment it's easy to do that with INSERT INTO...SELECT...FROM LinkedServer.db.schema.table.  But it's a hassle to build and teardown LinkedServer every time.  SSIS is cumbersome if this is a task you do frequently or want to automate.  In this post I'll show you a stored proc I've generated that will quickly build a "dynamic" linked server for you.  

You can get the procedure here.  

Let's take a look at some interesting parts of the routine.  First, let's take a look at the parameters: 

I like to have the same procedure both CREATE and DESTROY the linked server.  This makes LinkedServerGenerator easier to unit test and allows me to clean up my messes on shared dev servers.  Note that we have parameters for the "name" of the linked server you want to create.  This allows you to even write queries that JOIN data from multiple remote servers.  @DataSource is the server/instance name of the SQL Server.  Note that we assume SQL Server authentication...and yes...you have to (currently) pass in the user/password.  I know it's insecure.  

Here is the code for the DESTROY option.  I like my scripts to never fail and be idempotent (rerunnable without side-effects).  So, before attempting to drop a linked server I check first to see if it exists.  

Here is the basic pattern to dynamically build the linked server:  

Note specically Line 46 and 47.  If I need to call a stored proc remotely I do NOT want to enlist a distributed transaction and risk blocking.  This is my choice and YMMV based on your needs.  Again, this isn't meant to run production code.  

Finally, we create the logins and link them to the newly created linked server.  Again, I *do* understand the security risks inherent in this.  

Future Improvements

Some interesting improvements would be:  

• options to connect to other database managers (Oracle).  
• using NTLM authentication
• better handling of users/passwords

Summary

This is NOT code that I'd recommend running on a production server since there are a few GAPING security holes.  And of course you risk performance issues when you query ACROSS instance boundaries...for instance, where are the filter conditions applied...this is important to understand.  But this is useful to quickly query across servers in an ad hoc fashion.  I've been using this for years, I hope you find it valuable.  

You have just read "[[Dynamic Linked Server Generator]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.