OLE DB provider "MSDASQL" for linked server

Question

Hi,

I use a linked server to read data from an accounting application Acomba
First I use a ODBC driver from acomba and create a DSN 32 bits
I test the DSN and succeded
I test the ODBC driver with Excel and I can read the data from the Acomba database

Now on a my SQL Server I created a Linked server with this ODBC I did the test for the linked server and the test succeded
In the providers MSDASQL I activate the Allow Inprocess checkbox

When I do a simple select with my linked server like this

I received this message :

OLE DB provider "MSDASQL" for linked server "SIMA" returned message "[Acomba ODBC Driver]Exception dans la fonction ISAMNextRecord".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "SIMA".

If I do select * from Openquery(SIMA,'select count(*) as NbRec from Customer')
I received the numbers of customers coming out from the Customer table with no error

It is very hard to find information about this error message

This use to work for many year but now it does not work anymore

What can I verify to find out the cause of this problem and fix it

The sql server version is :
Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (Intel X86)
Nov 1 2020 00:46:51
Copyright (c) Microsoft Corporation
Express Edition on Windows NT 6.3 <X64> (Build 9600: ) (WOW64) (Hypervisor)

Thanks in advance

Answer 1

Hi @Gilles Carreau ,

When I do a simple select from Openquery(SIMA,'select from Customer') I received this message :

Please try the following SQL statement:

...Openquery(SIMA,'select * from Customer')   

Answer 2

Ok I realise that the site is taking out my caracter star and I do not know why

Answer 3

Some bugs with this site. Did you try to specify just a few columns instead of select * ?

Answer 4

Linked servers are very difficult to troubleshoot, because there are so many components involved. There is SQL Server that talks to the MSDASQL provider, which in its turn talks to the ODBC driver.

What we can tell from this error message is that error occurs in the ODBC driver, and the best advice I can give is to talk to the vendor of Acomba. It could be that SQL Server + MSDASQL is calling Acomba in the wrong way, but I find it a lot more likely is that Acomba is not supporting it should not support. Or simply is buggy.

Rather than SELECT *, you could try listing columns explicitly and see if that helps. You can also try to select only some columns to see if this helps, or add some sort of filter.

Answer 5

Yes I try with only one column still have the same message
If I do a select * from count(*) it works