Hello, I'm using ODBC driver from dynamics 365.
I'm experiencing some strange behavior.
For example, when I attach Dyn365 database as shared server on my SQL Server, in my queries the TOP 100 clause is ignored.
So for example, the query:
select top 100 * from crm_produzione...account
retrieve all the rows from the table "account" of the linked server "crm_produzione".
In addition, another issue is:
if I create a query with a subquery, I get a Devart error:
the query, for example, can be:
select top 100 * from crm_produzione...res_pianodeiconti where
res_pianodeicontiid in (select res_pianodeicontiid from crm_produzione...res_richiestaacquisto)
and I get the error:
Il provider OLE DB "MSDASQL" per il server collegato "crm_produzione" ha restituito il messaggio "[Devart][ODBC][Dynamics 365]Unknown column 'Tbl1005.res_richiestaacquistoid'".
Messaggio 7320, livello 16, stato 2, riga 1
Impossibile eseguire la query "SELECT "Tbl1002"."res_pianodeicontiid" "Col1008","Tbl1002"."createdon" "Col1009","Tbl1002"."createdby" "Col1010","Tbl1002"."modifiedon" "Col1011","Tbl1002"."modifiedby" "Col1012","Tbl1002"."createdonbehalfby" "Col1013","Tbl1002"."modifiedonbehalfby" "Col1014","Tbl1002"."ownerid" "Col1015","Tbl1002"."ownerid$type" "Col1016","Tbl1002"."owningbusinessunit" "Col1017","Tbl1002"."owninguser" "Col1018","Tbl1002"."owningteam" "Col1019","Tbl1002"."statecode" "Col1020","Tbl1002"."statuscode" "Col1021","Tbl1002"."versionnumber" "Col1022","Tbl1002"."importsequencenumber" "Col1023","Tbl1002"."overriddencreatedon" "Col1024","Tbl1002"."timezoneruleversionnumber" "Col1025","Tbl1002"."utcconversiontimezonecode" "Col1026","Tbl1002"."res_name" "Col1027","Tbl1002"."res_codificacompleta" "Col1006" FROM "res_pianodeiconti" "Tbl1002" WHERE "Tbl1002"."res_pianodeicontiid" = ANY (SELECT "Tbl1002"."res_pianodeicontiid" FROM (SELECT "Tbl1005"."res_richiestaacquistoid" "Col1029" FROM "res_richiestaacquisto" "Tbl1005") "Any_Source_Tbl" )" sul provider OLE DB "MSDASQL" per il server collegato "crm_produzione".
Ora di completamento: 2022-09-29T14:51:46.6496767+02:00
Of course, if I query Dynamics directly using SQL Server Management Studio connected to the remote Dyn365 database, the query works.
thanks