PRB: Sp_addlinkedserver Does Not Validate the OLE DB Provider

ID: Q200794

The information in this article applies to:

Microsoft SQL Server version 7.0

SYMPTOMS

When setting up a 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 SQL Server Enterprise Manager and then click New Linked Server on the shortcut menu. Only the OLE DB providers installed on the computer will be 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.

Additional query words: kbDSupport OLEDB

Keywords : kbole SSrvStProc
Version : winnt:7.0
Platform : winnt
Issue type : kbprb