clickhouse-odbc
clickhouse-odbc copied to clipboard
ClickHouse
ClickHouse as a Linked Server in MSSQL
I have installed a driver and created linked server in Microsoft SQL Server 2005.
I successfully connected to it and can get some data (please see attached data)
but when I try run any query then I receive following errors:
SELECT * FROM OPENQUERY([BIGDATA], 'SELECT CounterID FROM perf.CounterDetails')
Msg 7357, Level 16, State 2, Line 1 Cannot process the object "SELECT CounterID FROM perf.CounterDetails". The OLE DB provider "MSDASQL" for linked server "BIGDATA" indicates that either the object has no columns or the current user does not have permissions on that object.
select * from BIGDATA..perf.CounterData
Msg 7312, Level 16, State 1, Line 1 Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "BIGDATA". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
select * from [BIGDATA].perf..CounterData
Msg 7313, Level 16, State 1, Line 1 An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "BIGDATA".
Please collect odbc driver log - like in that instruction https://docs.oracle.com/cd/E17952_01/connector-odbc-en/connector-odbc-configuration-trace-windows.html
Start recording, do the steps leading to failure, stop recording, attach resulting log.
Please recheck with https://github.com/ClickHouse/clickhouse-odbc/releases/tag/v1.1.3.20200115
The same issue with ODBC 1.1.7
select TradeDate from [CLICKHOUSE_25].[default]..orders_history
Msg 7399, Level 16, State 1, Line 7 The OLE DB provider "MSDASQL" for linked server "CLICKHOUSE_25" reported an error. The provider did not give any information about the error. Msg 7312, Level 16, State 1, Line 7 Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "CLICKHOUSE_25". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
select TradeDate from [CLICKHOUSE_25].[default].[orders_history]
Msg 208, Level 16, State 1, Line 7
Invalid object name 'CLICKHOUSE_25.default.orders_history'.
SSIS DataFlow task, with the same dsn.
Finally, it works in that way, but it's not an option.
EXEC('select TradeDate
from default.orders_history limit 10;') at [CLICKHOUSE_25];
ODBC DSN settings
I am having problems too. I was unable to connect a Linked Server to CH on SQL Server 2016 & 2019.
This might help to isolate the issue:
And this is the ODBC SQL.LOG trace:
ControlService 30b8-3b08 ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x0803EE98
ControlService 30b8-3b08 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x0803EE98 ( 0x0550FAA8)
ControlService 30b8-3b08 ENTER SQLSetEnvAttr
SQLHENV 0x0550FAA8
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER -5
ControlService 30b8-3b08 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 0x0550FAA8
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER -5
ControlService 30b8-3b08 ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x0550FAA8
SQLHANDLE * 0x0803EE94
ControlService 30b8-3b08 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x0550FAA8
SQLHANDLE * 0x0803EE94 ( 0x0550FB28)
ControlService 30b8-25dc ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x0817EE68
ControlService 30b8-25dc EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x0817EE68 ( 0x055102B0)
ControlService 30b8-25dc ENTER SQLSetEnvAttr
SQLHENV 0x055102B0
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER -5
ControlService 30b8-25dc EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 0x055102B0
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER -5
ControlService 30b8-25dc ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x055102B0
SQLHANDLE * 0x0817EE64
ControlService 30b8-25dc EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x055102B0
SQLHANDLE * 0x0817EE64 ( 0x05510330)
ControlService 30b8-25dc ENTER SQLSetConnectAttrW
SQLHDBC 0x05510330
SQLINTEGER 1241 <unknown>
SQLPOINTER [Unknown attribute 1241]
SQLINTEGER -6
ControlService 30b8-25dc EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)
SQLHDBC 0x05510330
SQLINTEGER 1241 <unknown>
SQLPOINTER [Unknown attribute 1241]
SQLINTEGER -6
ControlService 30b8-25dc ENTER SQLSetConnectAttrW
SQLHDBC 0x05510330
SQLINTEGER 1242 <unknown>
SQLPOINTER [Unknown attribute 1242]
SQLINTEGER -3
ControlService 30b8-25dc EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)
SQLHDBC 0x05510330
SQLINTEGER 1242 <unknown>
SQLPOINTER [Unknown attribute 1242]
SQLINTEGER -3
ControlService 30b8-25dc ENTER SQLBrowseConnectW
HDBC 0x05510330
WCHAR * 0x790A2430 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x790A2430
SWORD -3
SWORD * 0x00000000
ControlService 30b8-25dc EXIT SQLBrowseConnectW with return code 99 (SQL_NEED_DATA)
HDBC 0x05510330
WCHAR * 0x790A2430 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x790A2430
SWORD -3
SWORD * 0x00000000
ControlService 30b8-25dc ENTER SQLGetDiagRecW
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x05510330
SQLSMALLINT 1
SQLWCHAR * 0x0817EE1C
SQLINTEGER * 0x00000000
SQLWCHAR * 0x05510678
SQLSMALLINT 256
SQLSMALLINT * 0x0817EE70
ControlService 30b8-25dc EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x05510330
SQLSMALLINT 1
SQLWCHAR * 0x0817EE1C
SQLINTEGER * 0x00000000
SQLWCHAR * 0x05510678
SQLSMALLINT 256
SQLSMALLINT * 0x0817EE70
ControlService 30b8-25dc ENTER SQLDisconnect
HDBC 0x05510330
ControlService 30b8-25dc EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)
HDBC 0x05510330
ControlService 30b8-25dc ENTER SQLFreeHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x05510330
ControlService 30b8-25dc EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x05510330
ControlService 30b8-25dc ENTER SQLFreeHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x055102B0
ControlService 30b8-25dc EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x055102B0
ControlService 30b8-25dc ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x0817EE0C
ControlService 30b8-25dc EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x0817EE0C ( 0x05510E60)
ControlService 30b8-25dc ENTER SQLSetEnvAttr
SQLHENV 0x05510E60
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER -5
ControlService 30b8-25dc EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 0x05510E60
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 3 <SQL_OV_ODBC3>
SQLINTEGER -5
ControlService 30b8-25dc ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x05510E60
SQLHANDLE * 0x0817EE08
ControlService 30b8-25dc EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x05510E60
SQLHANDLE * 0x0817EE08 ( 0x05510EE0)
ControlService 30b8-25dc ENTER SQLSetConnectAttrW
SQLHDBC 0x05510EE0
SQLINTEGER 1241 <unknown>
SQLPOINTER [Unknown attribute 1241]
SQLINTEGER -6
ControlService 30b8-25dc EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)
SQLHDBC 0x05510EE0
SQLINTEGER 1241 <unknown>
SQLPOINTER [Unknown attribute 1241]
SQLINTEGER -6
ControlService 30b8-25dc ENTER SQLSetConnectAttrW
SQLHDBC 0x05510EE0
SQLINTEGER 1242 <unknown>
SQLPOINTER [Unknown attribute 1242]
SQLINTEGER -3
ControlService 30b8-25dc EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)
SQLHDBC 0x05510EE0
SQLINTEGER 1242 <unknown>
SQLPOINTER [Unknown attribute 1242]
SQLINTEGER -3
ControlService 30b8-25dc ENTER SQLBrowseConnectW
HDBC 0x05510EE0
WCHAR * 0x790A2430 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x790A2430
SWORD -3
SWORD * 0x00000000
ControlService 30b8-25dc EXIT SQLBrowseConnectW with return code 99 (SQL_NEED_DATA)
HDBC 0x05510EE0
WCHAR * 0x790A2430 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x790A2430
SWORD -3
SWORD * 0x00000000
ControlService 30b8-25dc ENTER SQLGetDiagRecW
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x05510EE0
SQLSMALLINT 1
SQLWCHAR * 0x0817EDC0
SQLINTEGER * 0x00000000
SQLWCHAR * 0x05513ED8
SQLSMALLINT 256
SQLSMALLINT * 0x0817EE14
ControlService 30b8-25dc EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x05510EE0
SQLSMALLINT 1
SQLWCHAR * 0x0817EDC0
SQLINTEGER * 0x00000000
SQLWCHAR * 0x05513ED8
SQLSMALLINT 256
SQLSMALLINT * 0x0817EE14
ControlService 30b8-25dc ENTER SQLDisconnect
HDBC 0x05510EE0
ControlService 30b8-25dc EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)
HDBC 0x05510EE0
ControlService 30b8-25dc ENTER SQLFreeHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x05510EE0
ControlService 30b8-25dc EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 0x05510EE0
ControlService 30b8-25dc ENTER SQLFreeHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x05510E60
ControlService 30b8-25dc EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x05510E60
Looks like the SQLBrowseConnect doesn't succeed. Maybe the implementation of that call needs to be revisited...
Well, I finally found the issue, the problem is the driver name on the linked server tests on the repo is not correct (maybe has been changed recently)
Will submit PR tonight with the fixing
4 part name is still not working, making integration between SQL & CH quite difficult only EXEC() at linked_server & OpenQuery are working.
And opening the table from linked server does not show the fields.
Thanks
