ODBC connection to Informix - no tables

pthawkes · ‎03-06-2020

I have a DSN set up on the machine and I am able to use the DSN to connect via SQL server...the connection allows me to see the tables of the informix DB

With power BI I am using the ODBC option and I connect (using the DSN ) to the server but I am not able to list any tables.

I do not have access to the remote server hosting the informix DB so I need to get this ODBC connection operational

I have run a trace and I have found the following error in one of the "Microsoft.Mashup.Container..."log files

DataMashup.Trace Error: 24579 :

{"Start":"2020-03-06T18:02:50.1575769Z","Action":"Engine/IO/Odbc/Command/GetTables",

"ResourceKind":"Odbc","ResourcePath":"dsn=MYDSNNAME","HostProcessId":"7352",

"CatalogName":"%","SchemaName":"","TableName":"",

"TableType":"","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcException,

Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage:

ERROR [IM001] [Informix][Informix ODBC Driver]Driver does not support this function.\r\nStackTrace:\n

at Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcUtils.HandleError(OdbcHandle hrHandle, RetCode retcode)\r\n

at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.GetMetadataDataReader(Func`2 execute)\r\n

at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.<>c__DisplayClass11_0.<GetTables>b__0(IHostTrace trace)\r\n

at Microsoft.Mashup.Engine1.Library.Common.Tracer.Trace[T](String method, Func`2 func)\r\n\r\n\r\n","ProductVersion":"2.78.5740.1101 (20.02)"

,"ActivityId":"59049136-3c2d-45d6-9e06-19cf362bb277","Process":"Microsoft.Mashup.Container.NetFX45","Pid":7588,"Tid":1,"Duration":"00:00:00.0005065"}

v-deddai1-msft · ‎03-08-2020

Hi pthawkes,

Are you trying to connect to sql server data source via odbc driver? If anything is misunderstood, please tell me.

>>ERROR [IM001] [Informix][Informix ODBC Driver]Driver does not support this function.\r\nStackTrace:\n

What is the version of odbc driver and desktop are you using? Would you please try to update it to the latest version? You can check it in the screenshot below:

Use win+R and type ODBCAD32 in it

Where did you create DSN? Please create DSN in System DSN. Here is the similar thread for your reference: https://community.powerbi.com/t5/Power-Query/Personal-Gateway-X-On-Premises-Gateway/m-p/70945#M5526

Best Regards,

Dedmon Dai

pthawkes · ‎03-09-2020

Hi Dedmon

Thanks for your response:

No, I am trying to connect to a remote informix database. What I was saying was that I have successully created a linked server in our sql enviroment using a SYSTEM DSN created earlier. I have now downloaded power Bi desktop to the SQL environment and I am trying to use the same SYSTEM DSN to connect power bi to this remote informix instance. The connection is successful - but I do not get a table listing so that I am able to start querying the tables.

Below is the ODBC Driver I am using to make the connection:

IBM Informix ODBC Driver (64 bit) Version 4.50.00.17629

v-deddai1-msft · ‎03-09-2020

Hi pthawkes,

I recommend you update the power bi desktop and the ODBC driver to the latest version. You can get the latest version of ODBC driver from https://www.ciscounitytools.com/Applications/CxN/InformixODBC/InformixODBC.html.

Would you please confirm if the user you map to in the linked server is the same as you used when you connect power bi desktop to informix.

As a workaround, would you please try to use datasource connection provided by power bi desktop:

Best Regards,

Dedmon Dai

pthawkes · ‎03-10-2020

Thanks Dedmon

I was trying to avoid using the power bi connector and just use the existing DSN connectivity I already had in place. I have given up trying to make it work and have decided to use the SQL Server Linked server via SSRS to generate the reports I need.

Otherwise thanks for taking the time to help out.