I am having trouble getting the ODBC driver to work with MS SQL Linked Server. I have added a System DSN under the ODBC Control Panel with the Elastic 7.6 ODBC driver connecting to an active trial of Elastic 7.6 (also tried previous release with same result). I then used the System DSN to create a Linked server. When I open the linked server I do not see the tables that represent my indexes. I have been successful using the same driver with Excel and also using the JDBC driver with DB Visualizer with the same Elastic trial. Below is what I see in MS SQL Management Studio:
I am running Windows Server 2012 R2 and SQL Server 2016. I have also had the same result with Windows 10 and SQL Server 2019
Here are my config screens in ODBC and Linked Server
Hi @hwtearsdigi,
Thanks for the detailed report.
The first two errors suggest a deprecated 2.x API behaviour - and the driver should log a warning - but they might not be fatal to the application.
As the last error indicates, catalog support is currently inexistent in our SQL implementation, but we can - and actually should - simulate the support of that API call in one single case. To be sure thought: could you paste the log INFO message proceeding the ERROR one? It should indicate the value the application tries to set the catalog to and should start with setting current catalog to:.
Thanks.
The patch will be released part of Elastic's stack release cycle; a new installer will then be made available. A new 7.6 patch release should happen quite soon.
I was able to get 7.6.1 installed that was released recently. However, I am seeing the same errors/warnings. Can you confirm that this change made it into 7.6.1?
It unfortunately is in 7.6.1.
Just to make sure would you mind also enabling the driver manager logging and passing both driver's and driver manager's logs? Thank you.
As it turns out, the application fetches all tables (i.e. Elasticsearch indices) from the server, but won't list them because their type is not reported in a way expected by it (TABLE vs. BASE TABLE).
I've logged this into an issue that tracks the effort to better support apps exhibiting older ODBC API recommendations.
Until this gets addressed, one workaround could be defining aliases for those indices that are of interest and then accessing those from the application. And while this might not be ideal in all cases, oftentimes one refers to an alias anyways, such as when working with rolling indices.
So creating an alias made a view show up in linked server, but when I try to query it I get this:
select * from [ELASTIC].[a4eb75dedd5c4a05aba113779d7f97b5].[dbo].[michael1];
OLE DB provider "MSDASQL" for linked server "ELASTIC" returned message "The parameter is incorrect.".
OLE DB provider "MSDASQL" for linked server "ELASTIC" returned message "[Elastic][EsODBC 7.6.1(v7.6.0-4-g2459d3f+,u,r) Driver]schema filtering not supported".
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "MSDASQL" for linked server "ELASTIC" reported an error. One or more arguments were reported invalid by the provider.
Msg 7311, Level 16, State 2, Line 3
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "ELASTIC". The provider supports the interface, but returns a failure code when it is used.
There are indeed a couple of issues, but the outstanding one is the incompatibility of Elasticsearch/SQL's string types with Linked Servers/MSSQL's (namely, the lengths), even when using the query pass-through (OPENQUERY).
We've decided to add support for this application, but it requires dedicated work, so it'll unfortunately have to wait for one of the following non-patch releases.
This is btw an issue that Linked Servers/MSSQL has with other DBs/drivers out there (search phrase Requested conversion is not supported.)
Linked Servers will now connect to Elasticsearch, when using a 7.7 ODBC driver or older. This will require using the OPENQUERY on the Linked Servers' side and providing a specific setting on the driver side, the Varchar Limit; starting with 7.8 release, this setting is modifiable through driver's DSN editor:
The value provided here should be high enough to accommodate the longest string stored in Elasticsearch, but less than what Linked Servers supports (finding this latter limit might be a trial&error process).
Prior to 7.8 driver release, this setting needs to be provided as a connection string parameter; ex VarcharLimit=4000 (no spacing):
With a 7.7 Elasticsearch server, the indices will still not be listed under the Tables entry; this requires a 7.8 server or older. Aliases are listed correctly with either version however and one can define them to be visible under the Alias entry in the tree.
In any case, querying indices will work fine, irrespective of them showing up under the Tables entry or not.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.
