Knowledge Base

PRB: Sp_addlinkedserver Does Not Validate the OLE DB Provider

Article ID: 200794

Article Last Modified on 10/29/2003

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.

REFERENCES

For additional information about xp_enum_oledb_providers, click the article number below to view the article in the Microsoft Knowledge Base:

216575 INF: xp_enum_oledb_providers Enumerates the OLE DB Providers

Additional query words: kbDSupport OLEDB

Keywords: kbprb KB200794