-
Notifications
You must be signed in to change notification settings - Fork 147
Description
Describe the bug
when trying to connect SQLProvider to a Synapse azure sql pool instance via MSSQL connector (synapse provides a compatible TSQL api / dialect) we currently get such errors .
e.g. MSSQL EF core provider supports both MSSQL server and synapse as well
here code in EF to support synapse behind the same interface
Microsoft.Data.SqlClient.SqlException (0x80131904): 'COLUMNPROPERTY' is not a recognized built-in function name.
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, SqlCommand command, 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 FSharp.Data.Sql.Providers.MSSqlServerProvider.FSharp.Data.Sql.Common.ISqlProvider.GetColumns(IDbConnection con, Table table) in zC:\git\SQLProvider\src\SQLProvider.Runtime\Providers.MsSqlServer.fs:line 622
at [FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.convertExpression@913.Invoke](mailto:FSharp.Data.Sql.QueryExpression.QueryExpressionTransformer.convertExpression@913.Invoke)(Table table)
Tried on azure synapse sql should be compatible with TSQL mssql but rows do not display property types correctly
To Reproduce
Steps to reproduce the behavior:
- Make a synapse sql pool instance on Azure cloud
- Connect the type provider using MSSQL SQLProvider with synapse connection string
- See error when running or trying to infer types
Try with azure synapse sql pool on azure
Expected behavior
Have an option for Azure Synapse or a way to use MSSQL provider to load az synapse db connections and types correctly
Screenshots
If applicable, add screenshots to help explain your problem.
Environment (please complete the following information):
- Database: [e.g. SQLite]
- Nuget Package and version: [e.g. SQLProvider.SQLite 1.5.0]
- OS: [e.g. iOS]
- Dotnet or .NET Framework and version [e.g. dotnet 8]
Additional context
Data warehous on azure, Synapse SQL is the current Azure Cloud solution for data warehouse / big data parallel distributed sql, so it would be great to have this when working with Azure