In MSSQL Server 2008 and later, any major differences between "sp_addlinkedserver" and using registered servers?

Question

I am looking to create a SQL view which uses data from multiple servers. All of these are MS SQL Server 2008 and later

From my understanding, there are two ways to go about in gaining access to the other servers:

1. Using the stored procedure "sp_addlinkedserver"

2. Using Management Studio UI to register new servers (more info here: http://msdn.microsoft.com/en-us/library/bb934126.aspx)

I was wondering if there are any substantial differences in either approach in terms of creating a view, or if both would work just fine? And as a follow up question, are there typically security settings in management studio I would need to alter that would allow me to publish views from different servers in the first place?

Answer 1

The sp_addlinkedserver just does a bare bones linkage, basically just creating the entry in sysservers. It won't allow you to do remote queries. It is enough to allow, for example, setting up the linked server as a replication subscriber.

Doing it via the UI does sp_addlinkedserver plus other calls. You can check it running the profiler. In the UI, there is a security tab that you need to put in credentials, plus the server options tab, which is what actually allows distributed queries. Eg options "data access" as well as "remote proc transaction promotion".

You can do all these manually, either running just the SP and then changing the options by right clicking the server.