I like to have the same procedure both
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.
Some interesting improvements would be:
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.
Dave Wentzel CONTENT