February 9, 2005 at 10:57 am
When using Linked Servers to connect two SQL 2000 servers, there are two different options in "sp_addlinkedserver" to do it. You can use a provider of "SQLOLEDB" or "SQL Server". Do these two providers use the same SQLOLEDB drivers behind the scene? If not, does the "SQL Server" version perform better? It does not allow you to give it an alias name whereas the "SQLOLEDB" version does. We want to use alias names but want to make sure we're not giving up any performance.
I can't find any docs stating a difference, so at this point I'm assuming that they are the same.
February 10, 2005 at 1:59 am
BOL does actually tell you:
...
[ @srvproduct = ] 'product_name'
Is the product name of the OLE DB data source to add as a linked server. product_name is nvarchar(128), with a default of NULL. If SQL Server, provider_name, data_source, location, provider_string, and catalog do not need to be specified.
[ @provider = ] 'provider_name'
Is the unique programmatic identifier (PROGID) of the OLE DB provider corresponding to this data source. provider_name must be unique for the specified OLE DB provider installed on the current computer. provider_name is nvarchar(128), with a default of NULL. The OLE DB provider is expected to be registered with the given PROGID in the registry.
...
The table below (top line) this shows "product_name" as being "SQL Server"
The second line of the table is kinda misleading because if you use "SQL Server" as your 2nd parameter then the othe parameters produc an error thus:
Server: Msg 15428, Level 16, State 1, Procedure sp_addlinkedserver, Line 67
You cannot specify a provider or any properties for product 'sql server'.
To set the actual NETBIOS or DNS name of the linked server if different to teh linked server name use sp_setnetname. This is handy to remove a dependency on an actual servername.
Cheers
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply