search

ClickHouse / clickhouse-odbc

ODBC driver for ClickHouse
https://clickhouse.tech
Apache License 2.0
245 stars 86 forks source link

ClickHouse as a Linked Server in MSSQL #215

Open jdeshin opened 4 years ago

jdeshin commented 4 years ago

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) linked_server 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".

filimonov commented 4 years ago

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.

filimonov commented 4 years ago

Please recheck with https://github.com/ClickHouse/clickhouse-odbc/releases/tag/v1.1.3.20200115

Turum commented 4 years ago

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. image

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 image

mmauri commented 3 years ago

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: image

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
mmauri commented 3 years ago

Sorry, forgot to tell you the driver version: image

Thanks for your help .

traceon commented 3 years ago

Looks like the SQLBrowseConnect doesn't succeed. Maybe the implementation of that call needs to be revisited...

mmauri commented 3 years ago

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

turgaysargin commented 9 months ago

Hi,

Did you solve the problem? I have the same problem.

Thanks.