Live Data Connector

bobheck

Live connector claims to provide "industry standard" connectivity to data sources. I have exhausted ways of trying to successfully use a UNION query in Access using linked tables. I would prefer not to use Access, but ODBC forces me to use Access or Excel because those are the only tools available enterprise wide for my end users.

(Sheet names have been sanitized from queries below)

I have taken away all but one column in the query to eliminate other errors. The query is now this:

----------------------

SELECT 

[On Hold]

FROM [Sheet1]

UNION

SELECT 

[On Hold]

FROM [Sheet2]

----------------------

and I still get the error in the attached image, which states

ODBC--call failed. There was an error executing an SQL query: 'near "(": syntax error' (1) (#20001)

I have turned off the workspace identifiers in the ODBC driver and there are no other parentheses in my query.

So, I turned on ODBC logging and this is what appeared in the log:

----------------------------

[PID_1404:TID_5780 18:55:46:535] SQLSetStmtAttr EXIT (SQLRETURN 0)

[PID_1404:TID_5780 18:55:46:535] SQLExecDirect ENTER (QUERY (SELECT "On Hold" FROM Sheet1" ) UNION (SELECT "On Hold" FROM "Sheet2" ))

[PID_1404:TID_5780 18:55:46:535] [dbtrace]

SELECT * FROM INTERNAL_CACHE;

[PID_1404:TID_5780 18:55:46:784] [dbtraceapi]

-- sqlite3_prepare_v2: (SELECT "On Hold" FROM "Sheet1" ) UNION (SELECT "On Hold" FROM "Sheet2" )

[PID_1404:TID_5780 18:55:46:784] [dbtracerc]

-- SQLITE ERROR CODE 1: near "(": syntax error

[PID_1404:TID_5780 18:55:46:784] [setstat] setting status S1000, (20001) There was an error executing an SQL query: 'near "(": syntax error' (1)

[PID_1404:TID_5780 18:55:46:784] SQLExecDirect EXIT (SQLRETURN -1)

[PID_1404:TID_5780 18:55:46:784] SQLFreeHandle (TYPE 3)

-----------------------------

Something in the driver is apparently adding parentheses to the query before executing it and this is causing an error.

Has anyone else experienced this? Any ideas appreciated.

odbc_error.jpg

Samuel Hollis

I have the same problem with excel. I was successful using the api and excel’s data from web option in power query. And power automate has some Smartsheet options too.

Nathan Lloyd

Hi there,

If you haven't already I would suggest raising a ticket with Smartsheet Support so this can be investigated. You can create a case here: https://help.smartsheet.com/contact

As many specifics as you can provide are helpful when writing in. I would suggest including the details from your post as well as any additional screenshots you can of the full query so they can try to reproduce this.

Best,

Nathan L.