Shulou(Shulou.com)06/01 Report--

Customer requirements: a Ubuntu server, install SQL Server 2017 database, as an intermediate server, through the SQL Server link server way, connect the relevant databases of various business systems.

Problem: there is a system whose database uses Oracle database. After creating a linked server, there is an error in referencing the Times "not yet registered OLD DB provider OraOLEDB.Oracle".

To test it yourself, first create a linked server through GUI. The target server type can only be selected as "SQL Server". The "other data sources" option is gray and unselectable.

Ask the customer how the linked server is created on the system, and tell them that it was created through the SQL command.

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver

@ server = unknown OracleData'

, @ srvproduct=N'ORACLE'

, @ provider=N'OraOLEDB.Oracle'

, @ datasrc=N'TNS_NAME'

EXEC master.dbo.sp_addlinkedsrvlogin

@ rmtsrvname= Oracle datasheet, @ useself=N'False', @ locallogin=NULL

, @ rmtuser=N'OraUser', @ rmtpassword='OraPassword'

GO

Select * from OracleData..OraUser.UserTable

Go

Message 7403, level 16, status 1, line 1

The OLEDB provider "OraOLEDB.Oracle" has not been registered.

According to the customary way of thinking under the Windows system, the problem can be solved by installing the corresponding driver of the Oracle database.

First install the Oracle client programs and tools, as described above:

Http://blog.itpub.net/81227/viewspace-2668066/

Through the sqlplus tool test, you can connect to the Oracle database.

Test the linked server, or report the original error.

If you can't solve the problem directly, can you solve the problem through ODBC? Although ODBC is less efficient and poor performance than the direct approach, it is always good to be able to solve problems.

To install the unixODBC method, see:

Http://blog.itpub.net/81227/viewspace-2668511/

Through the isql tool test, also connected to the Oracle database.

Test the linked server again, and the problem remains.

If you can't solve the problem, you must always give the customer an explanation, otherwise you will appear too unprofessional.

The answer can only be found in the official documentation of MicroSoft.

Finally, the answer was found in a MicroSoft document called "Editions and supported features of SQL Server 2017 on Linux" (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2017?view=sql-server-ver15), Linked Servers to data sources other than SQL Server (the Linux version of SQL Server 2017 does not support linked servers for data sources other than SQL Server).

I can finally give a confession to the client.

In fact, so far, the Linux version of SQL Server 2019 is also not supported. However, in SQL Server 2019, another technology called PolyBase is provided to solve the problem of accessing other types of data sources in SQL Server.

Pit 1: when creating a linked server through the SQL command, the validity of the data source type is not checked

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver

@ server = unknown OracleData1'

, @ srvproduct=N'ORACLE'

, @ provider=N'unixODBC'

, @ datasrc=N'DSN_NAME'

EXEC master.dbo.sp_addlinkedsrvlogin

@ rmtsrvname= Oracle datasheet, @ useself=N'False', @ locallogin=NULL

, @ rmtuser=N'OraUser', @ rmtpassword='OraPassword'

GO

USE [master]

GO

EXEC master.dbo.sp_addlinkedserver

@ server = unknown OracleData2'

, @ srvproduct=N'ORACLE'

, @ provider=N'MSDASQL'

, @ datasrc=N'TNS_NAME'

EXEC master.dbo.sp_addlinkedsrvlogin

@ rmtsrvname= Oracle datasheet, @ useself=N'False', @ locallogin=NULL

, @ rmtuser=N'OraUser', @ rmtpassword='OraPassword'

GO

No matter what type the data source is changed to, it will not report an error when it is created, and the linked server has been created successfully.

Pit 2: the system does not support other types of data sources, but the error message is "access interface is not registered", rather than explicitly indicating that the system does not support this type of data sources.

Select * from OracleData1..OraUser.UserTable

Go

Message 7403, level 16, status 1, line 1

The OLE DB provider "unixODBC" has not been registered.

Select * from OracleData2..OraUser.UserTable

Go

Message 7403, level 16, status 1, line 1

The OLE DB provider "MSDASQL" has not been registered.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.