odbc error "Restricted data type attribute violation"

[Henrik]

Hello,

in Power BI Desktop (Version: 2.36.4434.381 32-bit) I want to connect to an Oracle data base via odbc.

I do have an ODBC data source set up, which so far worked fine in any application (Access, Excel, R, SPSS), . In Power BI, however, I get an error.

At first the connection seems to wok fine, I get a list of all tables in the data base as expected. But when I try to load a table, the following error message comes up:

"DataSource.Error: ODBC: ERROR [07006] [Oracle][ODBC]Restricted data type attribute violation."

Is this a bug, or is there something I can do about it?

Regards

Henrik

[javitodepinypon]

Same error here...

any ideas?

will MS solve this?

Regards

Javi

[bogomda]

I still didn't find solution to the problem yet and Power BI has little use for me until this issue is resolved. I so much love all features of Power Bi and they all have little use unless i can pull my data.

[jb7]

I was also having the same issue, which was odd since I was able to make the same ODBC connection last fall.  I managed to get it working w/ the following tweak to the query steps:

Upon seeing the failure, I would click Edit in order to access the failed steps in the query editor.  I rewrote the "APPLIED STEPS" statements.

For the Source I rewrote w/ the following:

= Oracle.Database("<dsn name>", [HierarchicalNavigation=false])

After applying this, I could then see the various tables listed along with the associated Schema and Items (which I used for the followign step).

For Navigation I rewrote with the following:

= Source{[Schema="<schema name>",Item="<table name>"]}[Data]

Not sure if this helps, but it got me up and running w/ our various data sets.

JB

[javitodepinypon]

Same error here...

Any suggestion Microsoft?

[bogomda]

I get same issue. I can open table from MS Acces but not from Power BI Desktop or Power Query (Excel 2016). Just sent a frown.

I also tried to connect to ODBC direct from Power Pivot and got following error message. I did provide password and user name correctly as you can see from below screen and it confimered succesfull connection. Again, no problem connecting from MS Access

[barry]

I get the same error.  Also I can't (or don't know how to) send a frown.  😞

[barry]

I get the same error.  Also, I can't (or don't know how to) send a frown!

[VRENEE]

Hello,

I have got same message with Oracle database thus it is OK in EXCEL 2010. Strange it does not work with POWERQUERY so (EXCEL addin and POWERBI DESKTOP). What is to say ?

Thanks in advance.

[arify]

Hi VRENEE,

Have you used the latest version of PBI Desktop? Can you please send us a frown with the trace files like I've described above?

Thanks

[VRENEE]

Hello,

Of course yes ...even older versions.

I have tried to use ODBC with POWERQUERY 2010 EXCEL addin and an error occured something similar to powerbi.

Therefore I noticed ODBC connexion, in 2010 EXCEL version, activated by datatab assistant works fine.

I copied the properties and parameters generated with this connexion and also the SQL : select * from mytable and it works fine in POWERQUERY. Tried into POWERBI … also fine.

I read a lot of things and blogs about this problem around POWERQUERY and ODBC and came to the conclusion that to work with ODBC driver into POWERBI I have to use my tnsname (of course) but give also a select clause in option tab and now it is OK.

When I have expanded the M language generated, it shew an odbc.query instruction while it was an odbc.datasource at first without SQL clause.

What I have read tend to explain that it was a “normal ODBC driver dysfunction” as there were some restrictions. The advice was to use ORACLE driver as far as possible. But as the workaround above it is OK for me. 

Hope it helps.

Regards.

[arify]

Hi @VRENEE,

It is completely up to you, if you're happy with the work around 🙂 But if we could get the traces about the details of this error, we can give you a much better experience in the future for this ODBC driver.

Thanks

[BananaLee]

Hi @arify,

I've just gotten into PowerBI Desktop and am trying to connect on an ODBC driver. I'm having the same problems and looking at the logs, there doesn't appear to be a "restricted data" line in the logs at all. 

I'm also struggling a little with sending a frown e-mail since I can't seem to find the place to click in order to do so but I do have some logs which would be useful.

EDIT: Just realised you were referring to the PowerBI interface - frown e-mail sent.

The SQL workaround works but I'd be quite keen to be able to use the functionality without having to type up SQL (it's a very large database with many links) 

[arify]

Hi Henrik,

Can you send us a frown with the traces with this error? (Options > Diagnostics > Enable tracing)

Also, does this happen only one of the tables, or all of the tables? What are the column types?

Thanks

[Henrik]

Hi,

the error occurs with all tables, and as it seems independently of the data type (I tried a very simple table with just 2 character fields).

The log-files are pretty long, here are 3 of the error lines:

DataMashup.Trace Error: 24579 : {"Start":"2016-08-01T06:54:36.0505799Z","Action":"Engine/IO/Odbc/Connection/GetInfo32","InfoType":"SQL_AGGREGATE_FUNCTIONS","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 [HYC00] [Oracle][ODBC]Optional feature not implemented.\r\nStackTrace:\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.OdbcFunctionCaching.GetResult[Output](UInt16 input, Func`3 function)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.OdbcCachingSqlGetInfo.GetInfoInt32(SQL_INFO infoType)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.GetInfoInt32(SQL_INFO infoType)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.<>c__DisplayClass7.<GetInfoInt32>b__6(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.36.4434.381 (PBIDesktop)","ActivityId":"d334675a-9956-42dd-a4ae-b3d2c2c078a3","Process":"Microsoft.Mashup.Container.NetFX40","Pid":4468,"Tid":1,"Duration":"00:00:00.0019135"}

DataMashup.Trace Error: 24579 : {"Start":"2016-08-01T06:54:36.0532620Z","Action":"Engine/IO/Odbc/Connection/GetInfo32","InfoType":"SQL_CONVERT_GUID","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 [HYC00] [Oracle][ODBC]Optional feature not implemented.\r\nStackTrace:\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.OdbcFunctionCaching.GetResult[Output](UInt16 input, Func`3 function)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.OdbcCachingSqlGetInfo.GetInfoInt32(SQL_INFO infoType)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.GetInfoInt32(SQL_INFO infoType)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.<>c__DisplayClass7.<GetInfoInt32>b__6(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.36.4434.381 (PBIDesktop)","ActivityId":"d334675a-9956-42dd-a4ae-b3d2c2c078a3","Process":"Microsoft.Mashup.Container.NetFX40","Pid":4468,"Tid":1,"Duration":"00:00:00.0001716"}

DataMashup.Trace Error: 24579 : {"Start":"2016-08-01T06:54:34.7610881Z","Action":"Engine/IO/Odbc/Connection/GetInfo32","InfoType":"SQL_AGGREGATE_FUNCTIONS","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 [HYC00] [Oracle][ODBC]Optional feature not implemented.\r\nStackTrace:\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.OdbcFunctionCaching.GetResult[Output](UInt16 input, Func`3 function)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.OdbcCachingSqlGetInfo.GetInfoInt32(SQL_INFO infoType)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.GetInfoInt32(SQL_INFO infoType)\r\n   at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.<>c__DisplayClass7.<GetInfoInt32>b__6(IHostTrace trace)\r\n   at Microsoft.Mashup

[arify]

Hi Henrik,

These are "Optional feature not implemented" errors, it's just the steps we figure out the capabilities of the driver, these exceptions don't matter. Are you sure there wasn't a line with "Restricte​d data type attribute violation" error?

There might be a timing issue with the logs, can you make sure the first thing you do is enabling the traces, and then reproduce the issue, and then close PBI Desktop? There must be a line that says "Restricte​d data type attribute violation".

Also, instead of scanning the trace files, you can just send a frown email to us (click the little down-arrow next to the Smiley on top-left corner, there's a Send A Frown button there), we'll do the scanning for you 🙂

Thank you very much for helping us!

Oguz