Connecting to Sphinx from sql server linked server

Question

I am trying to connect to Sphinx from SQL Server Management Studio as a Linked server. I tried the following query:

EXEC master.dbo.sp_addlinkedserver @server=N'SPHINX_SEARCH', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'Driver={MySQL ODBC 8.0 ANSI Driver};Server=127.0.0.1;Port=9306,charset=UTF8;User=;Password=;OPTION=3'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SPHINX', @useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

And i am getting the following exception:

Title: Microsoft SQL Server Management Studio

The test connection to the linked server failed.

Additional information

The OLE DB providcer "MSDASQL" for linked server "SPHINX_SEARCH" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SPHINX_SEARCH". (Microsoft SQL Server, Error: 7399).

While connecting to Sphinx from MySQL is working via the following command:

mysql -h 127.0.0.1 -P 9306

Any suggestions?

---

Sphinx version: 3.0.3, SQL server version: 2014, MySQL version: 5.6

Answer 1

Tested with Manticore, but should work with Sphinx too:

EXEC master.dbo.sp_addlinkedserver @server = N'Search',
@srvproduct=N'manticore', @provider=N'MSDASQL', @datasrc=N'manticore',
@provstr=N'Driver={MySQL ODBC 8.0 ANSI Driver};
Server=127.0.0.1;Port=9306,charset=UTF8;User=;Password=;'

manticore is System DSN name set in ODBC Administrator.

In sphinx.conf you need to set mysql_version_string to 5.1.1 (or anything greater than 4.1.1) to replace the actual daemon version. Otherwise the ODBC driver will refuse to work with the searchd daemon (because sees server version as being too old).

Update based on comments

The issue was solved after installing the latest Sphinx release 3.1.1. There was some bug related to MySQL client in release 3.0.3.

Answer 2

Did you check in SQL Server Management Studio under "Server Objects > Linked Servers > Providers" if your MSDASQL Provider is listed here and is installed properly?

Also you could try to open the properties for this provider and check the "allow inprocess" option there and check again if you can connect.

You also might want to check this link for further things you can try:

https://support.microsoft.com/en-us/help/2450479/you-get-7399-and-7300-error-messages-when-accessing-a-linked-server