PRB: Sp_addlinkedserver Does Not Validate the OLE DB Provider (200794)

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: