MSSQL, ODBC, Invalid cursor state, не могу подключить MSSQL базу в КХ клиенте

[azadevlab]

Добрый день, помогите с подключение по odbc к базам MSSQL

настроили odbc на clickhouse сервере, подключение с таких клиентов как isql, tsql есть, но подключиться с КХ клиента не удаётся

1. /etc/odbcinst.ini

   [FreeTDS]
   Description     = FreeTDS
   Driver          = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
   Setup           = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
   FileUsage       = 1
   UsageCount      = 5

2. /etc/freetds/freetds.conf

   [MSSQLAzure]
   host = azalab.database.windows.net
   port = 1433
   tds version = 7.0
   #tds version = 5.0
   client charset = UTF-8

3. /etc/odbc.ini

   [MSSQLAzure]
   Driver = FreeTDS
   Servername = MSSQLAzure
   Port = 1433
   Database = analytics
   UID = clickhouseuser
   PWD = clickhousepassword

4. Проверка подключения к MSSQL базе по ODBC

   root@srv1:/# isql -v MSSQLAzure "clickhouseuser" "clickhousepassword"
   +---------------------------------------+
   | Connected! |
   | |
   | sql-statement |
   | help [tablename] |
   | quit |
   | |
   +---------------------------------------+
   SQL> SELECT * from analytics.costs
   ВЫВОД ДАННЫХ ...............

root@srv1:/# tsql -v -S MSSQLAzure -D analytics -U clickhouseuser
Password: 
locale is "C.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Setting analytics as default database in login packet
Changed database context to 'analytics'.
Changed language setting to us_english.
1> 

5. подключение с КХ клиента и сама ошибка = = = root@srv1:~# clickhouse client -u admin --password adminpassword

ClickHouse client version 21.11.10.1 (official build).
Connecting to localhost:9000 as user admin.
Connected to ClickHouse server version 21.11.10 revision 54450.
srv1.ftt.local :) 
srv1.ftt.local :) 
srv1.ftt.local :) select * from odbc('DSN=MSSQLAzure;Uid=clickhouseuser;Pwd=clickhousepassword;', 'analytics', 'costs')
SELECT *
FROM odbc('DSN=MSSQLAzure;Uid=clickhouseuser;Pwd=clickhousepassword;', 'analytics', 'costs')
Query id: 9b1c588d-12b5-4dec-a2aa-3ae49ff63159

0 rows in set. Elapsed: 0.390 sec.
Received exception from server (version 21.11.10):
Code: 86. DB::Exception: Received from localhost:9000. DB::Exception: Received error from remote server /columns_info?connection_string=DSN%3DMSSQLAzure%3BUid%3Dclickhouseuser%3BPwd%3Dclickhousepassword%3B&schema=analytics&table=costs&external_table_functions_use_nulls=true. HTTP status code: 500 Internal Server Error, body: Error getting columns from ODBC 'std::exception. Code: 1001, type: nanodbc::database_error, e.what() = ../contrib/nanodbc/nanodbc/nanodbc.cpp:4925: 2400: [FreeTDS][SQL Server]Invalid cursor state (version 21.11.10.1 (official build))'
. (RECEIVED_ERROR_FROM_REMOTE_IO_SERVER)
srv1.ftt.local :) 
srv1.ftt.local :)

[Slach]

я пробовал подключать через официальные ODBC драйвера, вместо FreeTDS посмотрите как у меня настроено, попробуйте воспроизвести https://gist.github.com/Slach/9f9449a722091a13a9069b79f8dc7da7

[sefabalica]

hi, I have the same problem, I can't connect to clickhouse odbc while connecting to isql. were you able to find a solution?

[tcerg]

Hello!

I have the same problem (

Test connection over isql & tsql - successfully.

BUT ClickHouse client: :) select top 10 V1 from odbc('DSN=TSQL_NE; Uid=user;Pwd=password;','asu','dbo.data')

Code: 86. DB::Exception: Received error from remote server /columns_info?connection_string=DSN%3DTSQL_NE%3B%20Uid%3Duser%3BPwd%3Dpassword%3B&schema=asu&table=dbo.data&external_table_functions_use_nulls=true. HTTP status code: 500 Internal Server Error, body: Error getting columns from ODBC 'Code: 36. DB::Exception: Table asu.dbo.data not found. (BAD_ARGUMENTS) (version 22.2.2.1)' . (RECEIVED_ERROR_FROM_REMOTE_IO_SERVER) (version 22.2.2.1) (in query: select top 10 V1 from odbc('DSN=TSQL_NE; Uid=user;Pwd=password;','asu','dbo.data'))...

Anybody find a solution?

[traceon]

This is related to clickhouse server and clickhouse-odbc-bridge, not clickhouse-odbc driver. I suggest moving the discussion to https://github.com/ClickHouse/ClickHouse/issues.