# Unable to use the ODBC driver as a SQL Server Linked Server **URL:** \ **Category:** Elasticsearch\ **Created:** [May 1, 2020, 2:58am UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660 "2020-05-01T02:58:01Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [May 1, 2020, 2:58am UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/1 "2020-05-01T02:58:01Z")
I've setup an ODBC 64-bit driver on a server that runs SQL Server 2017. I picked the ODBC version to match the ElasticSearch installation (6.0.6). The installation of the ODBC driver and creation of a Linked Server went fine and I am able to connect from the ODBC applet. When I expand the Linked Servers node in SQL Server Management Studio, I get the following: ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/9/89ab328096c7f11efd269b92c3980fa60ab662c8.png) So it sees the **es-oc2-q** , which is the name of the cluster. However, it doesn't list anything under Tables or Views. I've tried running the following commands, but they failed with _Invalid object name 'ESSTAGE.es-oc2-q'._: -- addresses is an alias SELECT \* FROM ESSTAGE.[es-oc2-q].addresses I then tried using the 2 dots: SELECT \* FROM ESSTAGE.[es-oc2-q]..addresses This did not return an error, but the query never comes back. What am I missing? ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [May 4, 2020, 9:41am UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/2 "2020-05-04T09:41:44Z")
Hi @rgelb, There are a few compatibility issues of this application with latest ODBC spec drivers (some details to be found here: [Elasticsearch ODBC driver with SQL Linked Server](https://discuss.elastic.co/t/elasticsearch-odbc-driver-with-sql-linked-server/221144)). The next stack release - 7.7.0, due very soon - should bring backwards support for the most important gaps. I'll update the thread once the release is out. Note however that the driver and server are, up to this release, in version lock-step; i.e. you'll need a server updated to 7.7. ---
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [May 4, 2020, 4:12pm UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/3 "2020-05-04T16:12:27Z")
@bogdan.pintea A release of the ODBC driver that is not version locked would be appreciated. We are on ES 6.6 and upgrading because the ODBC driver isn't working right will not get any traction with the powers that be in any organization. And the upgrade itself is not exactly a trivial matter either. ---
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [May 13, 2020, 8:17pm UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/4 "2020-05-13T20:17:51Z")
@bogdan.pintea I see ES 7.7 is released. Anything to report regarding the ODBC driver? Is it still locked to the ES version? ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [May 19, 2020, 11:30am UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/5 "2020-05-19T11:30:51Z")
Hello @rgelb, > Anything to report regarding the ODBC driver? Yes, but it might be a mixed news update for your needs. The `Linked Server` will now connect to Elasticsearch with a 7.7 driver (I've also updated the previously linked thread). > Is it still locked to the ES version? It is no longer bound in lock-step to the server **starting with this release** ; but: the general compatibility policy that Elasticsearch has towards the clients is still applicable; i.e. the server will support any client that's older than itself with up to one major release. Elasticsearch clients do not generally have a backwards compatibility policy themselves (i.e. support older servers), although it might sometimes work, depending on the APIs the client accesses. This is not specific to the SQL clients, but the SQL clients do require stricter integration to provide correctness and will complain if server's version is too old. And they do so because even if the version check would be edited out, connecting would still fail due to the changes that have occurred in previous releases. That's why I was suggesting in my first answer that a server upgrade would be needed too. I do understand that server upgrades are unfortunately much more delicate than a client update and generally not done just for one client support, but these are the given limitations, I'm afraid. ---
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [May 19, 2020, 4:40pm UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/6 "2020-05-19T16:40:09Z")
> [@bogdan.pintea](#): > > It is no longer bound in lock-step to the server @bogdan.pintea I am not sure that is true. This is what happened when i tried to connect: ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/6/a6515f61bfb1375feb973161c89dddab1ea74fec.png) Am I missing something simple? Like maybe a flag somewhere? ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [May 20, 2020, 5:38pm UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/7 "2020-05-20T17:38:34Z")
@rgelb, Let me give some concrete examples to explain how this works: - the 7.7 ODBC driver will work with a 7.7 server, 7.8 server, 7.x...; then also 8.0 server etc. - a 7.7 server will work with a 7.7 ODBC driver; - a 7.8 server will work with a 7.7 and 7.8 ODBC driver; - a 8.0 server will work with a 7.7, 7.8, 7.x... and 8.0 driver; a.s.o. Hopefully the compatibility policy of Elasticsearch I was trying to explain before makes more sense now, but let me know if I can clarify more. ---
**Author:** ![mattm](https://avatars.discourse-cdn.com/v4/letter/m/c89c15/32.png) [@mattm](https://discuss.elastic.co/u/mattm)\ **Post date:** [June 12, 2020, 6:19pm UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/8 "2020-06-12T18:19:30Z")
So I have server 6.4.2. I tried installing 7.7 ODBC and it errored out. I installed 6.5 ODBC and it didn't get an error but now I can't do any direct queries over the linked server of the elasticsearch database. What version of the ODBC driver can I install that will work for both the linked server query and the installation compatibility. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [June 12, 2020, 6:30pm UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/9 "2020-06-12T18:30:34Z")
Based on the discussion here I believe you need to upgrade to Elasticsearch 7.7 and then use version 7.7 of the ODBC driver. ---
**Author:** ![mattm](https://avatars.discourse-cdn.com/v4/letter/m/c89c15/32.png) [@mattm](https://discuss.elastic.co/u/mattm)\ **Post date:** [June 12, 2020, 6:33pm UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/10 "2020-06-12T18:33:06Z")
thanks for the response. Additionally, how can I get an example of using an openquery command against the elasticsearch linked server query? Currently trying to use select \* from linkedservername.clustername.indexname. Is this the correct format? I have also tried. select \* from openquery(linkedservername, 'select \* from clustername.indexname') and this doesn't work either. ---
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [June 13, 2020, 7:03am UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/11 "2020-06-13T07:03:35Z")
I am disappointed that for a version of ES that's still active, a bug is not fixed, but rather the customers are required to upgrade to the latest version, which is not a trivial thing. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [June 13, 2020, 7:14am UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/12 "2020-06-13T07:14:48Z")
As this is new functionality/capabilities, I believe it always goes into the latest version, which in this case was 7.7. Version 6.8 is still supported but not under active development. It is receiving fixes for security issues and serios bugs, but new functionality is not backported. Version 6.6 does not receive any updates or fixes at all. ---
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [June 15, 2020, 10:39pm UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/13 "2020-06-15T22:39:02Z")
Afaik, version 6.6 is an [active and supported](https://www.elastic.co/support/eol) version. The ODBC driver was supported in that version. Seeing how I paid for the entire product, I think ES should fix the driver. I am not asking ES to make the 7.7 version of the ODBC driver to be backwards compatible to 6.6 (though if they did, that would be fine). I am saying fix v6.6 of the ODBC driver that people paid for. Especially since the company has a fix. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [June 16, 2020, 5:19am UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/14 "2020-06-16T05:19:30Z")
I would recommend you contact Elastic Support in order to get an official response. ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [June 17, 2020, 10:13am UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/15 "2020-06-17T10:13:03Z")
Hi @mattm, > Currently trying to use select \* from linkedservername.clustername.indexname. Is this the correct format? I have also tried. > select \* from openquery(linkedservername, 'select \* from clustername.indexname') and this doesn't work either. The query provided in OPENQUERY ["is the query string executed in the linked server"](https://docs.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql) so it'll have to be formatted according to [Elasticsearch/SQL synthax](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-syntax-select.html). Try `SELECT field FROM openquery(linkedservername, 'SELECT field FROM indexname')` and expand from there. The catalog name is not required, but if you'd like to provided it, you'd need to use the ES/SQL catalog separator, which is `:`, like in `SELECT field FROM clustername:indexname`. ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [June 17, 2020, 10:39am UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/16 "2020-06-17T10:39:53Z")
Hello @rgelb, Very sorry you're having troubles with the upgrade required in this case. > I am saying fix v6.6 of the ODBC driver that people paid for. Supporting LinkedServer however required changes that applied both to the driver and the server and that are considered features, such as the [artificial varchar limitation required by LinkedServer](https://discuss.elastic.co/t/elasticsearch-odbc-driver-with-sql-linked-server/221144/14) or adapting to its incapacity to work with `BASE TABLE`s. No recent product Microsoft has rolled out requires these workarounds, LinkedServers is however not based on new code. I'd happily open a support request with Microsoft around these issues, but I think I know how it would end up. In any case, we're happy to meet our users where they are and doing efforts to accommodate their applications. And we are making fixing backports back into the supported versions. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 15, 2020, 10:39am UTC](https://discuss.elastic.co/t/unable-to-use-the-odbc-driver-as-a-sql-server-linked-server/230660/17 "2020-07-15T10:39:57Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.