Error connecting from SQL Server 2016 to Filemaker odbc

Question

I created a linkedserver from SQL Server 2016 to Filemaker database on another server. ODBC driver version in 19.xx.

Using the ODBC driver and DSN, I can see the data from the Filemaker database using Excel or PowerBI.

However I am unable to query the Filemaker from SQL Server using linkedserver.

1. I installed Filemaker odbc 64 bit. Tried versions 17.0.1 to 19.5
2. Create a system DSN
3. Created a linked server

But whenever I try to run a query I get the error shown here. I tried many things and still can't figure out.

Has anybody else encountered this error?

I tried different versions of odbc drivers and tried to query using different syntax, such as openquery, openrowset etc, without any success

Answer 1

Finally I found the solution to the problem, using the following steps.

1. opened the port from sql server to file maker server
2. installed odbc drivers and created system dsn in sql server
3. created a linked server using the system dsn
4. queried the filemaker db from sql server via linked server using openquery like this

...

select * from openquery([linkedserver],' select col1, col2, col3 from tablename')

This worked. please make sure to specify the column names inside the openquery. hope it helps