GetSchema("DataType") is not compatible with Microsoft Fabric Datawarehouse

[MaceWindu]

Describe the bug

This is basically #2086 but for another SQL Server-like database.

GetSchema('DataType") loads UDT types using CLR features of SQL Server, not supported on Microsoft Fabric Datawarehouse leading to exception from server from addUDTsToDataTypesTable method here.

Unhandled exception: FUNCTION 'ASSEMBLYPROPERTY' is not supported.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, 
TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean 
forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& 
task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 
timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader()
   at Microsoft.Data.SqlClient.SqlMetaDataFactory.addUDTsToDataTypesTable(DataTable dataTypesTable, SqlConnection connection, String ServerVersion)
   at Microsoft.Data.SqlClient.SqlMetaDataFactory.GetDataTypesTable(SqlConnection connection)
   at Microsoft.Data.SqlClient.SqlMetaDataFactory.PrepareCollection(String collectionName, String[] restrictions, DbConnection connection)
   at Microsoft.Data.ProviderBase.DbMetaDataFactory.GetSchema(DbConnection connection, String collectionName, String[] restrictions)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.GetSchema(DbConnectionFactory factory, DbConnectionPoolGroup poolGroup, DbConnection outerConnection, String 
collectionName, String[] restrictions)
   at Microsoft.Data.SqlClient.SqlConnection.GetSchema(String collectionName, String[] restrictionValues)
   at Microsoft.Data.SqlClient.SqlConnection.GetSchema(String collectionName)

To reproduce

connection.GetSchema("DataType")

Expected behavior

API doesn't try to access unsupported by server functionality.

Further technical details

Microsoft.Data.SqlClient version: 5.2.1

Additional context

Microsoft Fabric Datawarehouse unsupported features page doesn't list CLR support explicitly (or I cannot find it), but I wouldn't expect it to support CLR as effectively obsolete feature.

Original issue : linq2db/linq2db#4536

[ErikEJ]

@MaceWindu Looks like both EngineEdition 6 and 11 should be excluded in addition to 9?

https://learn.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver16

[arellegue]

@MaceWindu Thank you for reporting this issue. We will investigate and try to resolve this issue by trying ErikEJ's suggestion to also exclude EngineEdition 6 and 11 in addition to 9 implemented in a fix, PR #2099, for issue #2086.

[ErikEJ]

@MaceWindu any chance to check a select from sys.assemblies against a engineedition 9 and 11 server? The docs are quite ambigious

[ErikEJ]

@arellegue @MaceWindu I have created a PR

[MaceWindu]

@ErikEJ , sorry, I don't have access to this type of database. Original issue was reported by other user.

@Hoodah could you check it?

[ErikEJ]

@MaceWindu No worries, I found som docs that states that ASSEMBLYPROPERTY is only supported on "SQL Server"

[Hoodah]

I'm glad you have tracked down the issue. Here is the result of select * from sys.assemblies, in case it is helpful.

name                                                                                                                             principal_id assembly_id clr_name                                                                                                                                                                                                                                                         permission_set permission_set_desc                                          is_visible create_date             modify_date             is_user_defined
-------------------------------------------------------------------------------------------------------------------------------- ------------ ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------- ------------------------------------------------------------ ---------- ----------------------- ----------------------- ---------------
Microsoft.SqlServer.Types                                                                                                        4            1           microsoft.sqlserver.types, version=16.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil                                                                                                                                        3              UNSAFE_ACCESS                                                1          2024-04-14 00:43:55.473 2024-04-14 00:43:55.567 0

[ErikEJ]

@Hoodah Turned out it was the Assemblypropety function that was causing issues, and my fix adheres to the docs