ODBC Help - The given key was not present in the dictionary.

[PowerBS]

I am using the CData ODBC driver connecting to Sage Accounts.

The ODBC connection wizard does not return the table list into the Navigator so I have to enter it into the query editor with the following:

= Odbc.DataSource("dsn=CData Sage50UK Source Sys", [HierarchicalNavigation=false])

This returns a list of the tables I require but I need these to be selectable in the Fields Pane, not displayed as a datatable.

I can then create a new query from the source and use this Navigation Pane to select a table (The tables show up in this Nav Pane but not in the connection wizard) which gives me the following expression:

= Source{[Item="Receipts",Schema="Sage50UK",Catalog="CData"]}[Data]

I then get the error message:

DataSource.Error: ODBC: ERROR [HY000] ApplicationDiagnosis:GET operation failed - The given key was not present in the dictionary.

at System.ThrowHelper.ThrowKeyNotFoundException()
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Sage.Integration.Accounts50.SDOAdapter.SDOFeedDetails.GetTableColumnFromProperty(String property)
at Sage.Integration.Accounts50.SDOAdapter.SDORequestHandler.OnGetRequest(IRequest request, IFeed feed)
at Sage.Integration.Accounts50.SDOAdapter.RequestHandler`2.OnGetRequest(IRequest request)
at Sage.Integration.Accounts50.SDOAdapter.RequestHandler`2.GetRequest(IRequest request)
at Sage.Integration.Accounts50.SDOAdapter.SDORequestManager.OnGetResource(IRequest request)
at Sage.Integration.Accounts50.SDOAdapter.SDORequestManager.GetRequest(IRequest request) (-1)

The CData ODBC works perfectly fine in Excel (when MS Query does not break) but I cannot make all my Sage tables appear on the designer form for the end users to select tables/fields for their reports.

I have sent a few frowns with screenshots and logs but nothing back yet and the documentation is seriously lacking, can anyone on here help me out before I have give in and go back to Crystal?

[ankitpatira]

@PowerBS I have seen same issue when using ODBC connector for firebird database. This is because power bi does database scan and it runs a SP or commands to get list of all tables and views available which some ODBC driver don't support. I've successfully overcome this issue using DSN created first and then using that via ODBC connector. I would suggest do that instead of typing in ODBC string via ODBC connector to connect.

[HHassan]

Hi Ankit,

I have created a System/User DSN (using ODBC 32-bit window) against Data Source: Sage Line 50 V23. I am able to connect and view tables in Power BI but when I am selecting a table (load), it is throwing an error, looks like it is not able to load the table. I have checked the path has .DAT files with big Volume/size. Any help will be really appreciated (please give solution in step-by-step for clarity). I am using Windows Server 2012 with Power BI 32 Version and sucessfuly able to load Access DB, SQL, Excel.

DataSource.Error: ODBC: ERROR [42S02] Table not found
Details:
DataSourceKind=Odbc
DataSourcePath=dsn=Sage Line 50 v23
OdbcErrors=Table

[PowerBS]

I am trying to connect to Sage Accounts using the CData ODBC adapter but the tables do not appear in the Navigator for selection.

If I enter the connection manually in the Query windows it returns a list of the table names:

= Odbc.DataSource("dsn=CData Sage50UK Source Sys", [HierarchicalNavigation=false])

I am adding a new query from the source and using Navigation in the right pane to select a table (the table list now shows in this navigator) pane) and I get the following error:

DataSource.Error: ODBC: ERROR [HY000] ApplicationDiagnosis:GET operation failed - The given key was not present in the dictionary.

Also is there any better documentation for this application as my end users are really going to struggle if I roll this out.

   at System.ThrowHelper.ThrowKeyNotFoundException()
   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at Sage.Integration.Accounts50.SDOAdapter.SDOFeedDetails.GetTableColumnFromProperty(String property)
   at Sage.Integration.Accounts50.SDOAdapter.SDORequestHandler.OnGetRequest(IRequest request, IFeed feed)
   at Sage.Integration.Accounts50.SDOAdapter.RequestHandler`2.OnGetRequest(IRequest request)
   at Sage.Integration.Accounts50.SDOAdapter.RequestHandler`2.GetRequest(IRequest request)
   at Sage.Integration.Accounts50.SDOAdapter.SDORequestManager.OnGetResource(IRequest request)
   at Sage.Integration.Accounts50.SDOAdapter.SDORequestManager.GetRequest(IRequest request) (-1)
Details:
    DataSourceKind=Odbc
    DataSourcePath=dsn=CData Sage50UK Source Sys
    OdbcErrors=Table

The issue is not the ODBC driver as it works perfectly fine in Excel, I have send a few frowns but nothing back yet.