Microsoft KB Archive/200794

= PRB: Sp_addlinkedserver Does Not Validate the OLE DB Provider =

Article ID: 200794

Article Last Modified on 10/29/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q200794



BUG#: 41658 (Shiloh)



SYMPTOMS
When you set up a Microsoft SQL Server 7.0 distributed query, the sp_addlinkedserver stored procedure does not check to verify that the OLE DB provider name is valid or installed on the computer.



WORKAROUND
To set up a linked server, right-click Linked Servers under the Security folder in the SQL Server Enterprise Manager and then click New Linked Server on the shortcut menu. Only the OLE DB providers installed on the computer are shown.

Additionally, you can use the xp_enum_oledb_providers extended stored procedure to enumerate all available providers on the computer where SQL Server is installed. The following is an example of how to use this extended stored procedure:

use master go exec xp_enum_oledb_providers go



MORE INFORMATION
SQL Server 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked server name.

The sp_addlinkedserver stored procedure is used to set up a linked server. However, sp_addlinkedserver does not check to verify that the OLE DB provider name is valid or if it is installed on the computer.

Thus, the following call will succeed:

sp_addlinkedserver 'myServer', ' ', 'XYZ', 'XYZ', 'XYZ','Data Source=ABC;'

However, queries will fail because XYZ is not a valid provider. If you try to use this linked server, you will receive the following message:

Server: Msg 7403, Level 16, State 1, Line 1

Could not locate registry entry for OLE DB provider 'XYZ'.

For more details on setting up and using distributed queries, refer to the sp_addlinkedserver, OpenQuery, and OpenRowset topics in the SQL Server 7.0 Books Online.