On the adventureworks database on SQL Server there are tables using the uniqueidentifier datatype, dremio is adding a collation under the covers which causes the data_read_error. According to SQL server “Expression type uniqueidentifier is invalid for COLLATE clause”. Can this be fixed?
Can you share the failed job profile please? https://www.dremio.com/tutorials/share-query-profile-dremio/
Thanks. There is a known issue with the UNIQUEIDENTIFIER data type. Is it possible for you to change the type and try again for testing purposes?
Yes, changing the datatype to binary and creating a virtual dataset for both tables allows you to perform the join between them. You might want to update the tutorial https://www.dremio.com/tutorials/microsoft-ssis-alternative-dremio-power-bi/ with the with this info.
Thanks for the suggestion. Sorry, it isn’t obvious to me what should change. Can you help?
The fact that you cannot join source tables that have a uniqueidentifier as a column in the table. You need to create a virtual dataset for every table and change the datatype to binary. Then the join between the tables will work. In the tutorial all the work is done on the source tables.
Ah, I see. Do all the tables have a column of type uniqueidentifier? I don’t have this database easily accessible.
Anthony, do you happen to have an update on this issue? I am having the same issue from my end and it would be a lot of work having to do manual casting…
Kelly, in my particular case I have over 700 tables in SQL Server using uniqueidentifier.
Can you try the workaround where you can disable generation of COLLATE clauses?
You can do this by setting the JVM property dremio.jdbc.mssql.push-collation.disable to true in the dremio-env file ( add -Ddremio.jdbc.mssql.push-collation.disable=true )
1 Like
Anthony, thanks this did the trick. I was wondering, when disabling this functionality (push collation) what feature/functionality am I missing out?
Did this issue resolved in Drmeio 3.0?