Connect to Azure Synapse Analytics serverless SQL pool

[Jasper Palmaers]

Hi,

I would like to connect Tibco Spotfire to an external view (and/or table) in myserverless sql poolin Azure Synapse Analytics. Unfortunately, I receive the error message below when selecting the view. Please note that connecting to a dedicated sql pool in Azure Synapse analytics is working correctly but is not an option at the moment. Any help is welcome and much appreciated!

An exception was thrown by the data connection.

Exception text:

System.InvalidOperationException: Unable to build the 'Columns' collection because execution of the SQL query failed. See the inner exception for details. ---> System.Data.SqlClient.SqlException: PROCEDURE 'sp_columns_managed' is not supported.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

at System.Data.SqlClient.SqlDataReader.get_MetaData()

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)

at System.Data.ProviderBase.DbMetaDataFactory.ExecuteCommand(DataRow requestedCollectionRow, String[] restrictions, DbConnection connection)

--- End of inner exception stack trace ---

at Spotfire.Dxp.Data.Access.CancellationHelper.Execute(CancellationToken token, Action action)

at Spotfire.Dxp.Data.Access.CancellationHelper.Execute[T](CancellationToken token, Func`1 function)

at Spotfire.Dxp.Data.Adapters.SqlServer.SqlServerAdapterConnection.GetTableSchemasCore(CancellationToken cancellationToken, IEnumerable`1 tables, IReadOnlyList`1& tableSchemas)

at Spotfire.Dxp.Data.Access.Adapters.DataAdapterConnection.GetTableSchemas(IEnumerable`1 tables, CancellationToken cancellationToken, IReadOnlyList`1& tableSchemas)

at Spotfire.Dxp.Data.Access.Adapters.DataAdapter.GetTableSchemas(IDataAdapterExecutionContext executionContext, AdapterCredentialsValue credentials, CancellationToken cancellationToken, IEnumerable`1 tableNames, IReadOnlyList`1& tableSchemas)

at Spotfire.Dxp.Data.Access.Adapters.DataAccessDataSourceLink.c__DisplayClass57_0.b__0(CancellationToken token)

at Spotfire.Dxp.Data.Access.Adapters.DataAccessDataSourceLink.c__DisplayClass88_0.g__Execute|1()

 

Thank you in advance!

Kr,

Jasper

[Prashant Ajjannavar]

Hello Jasper,

You are right!

Currently, only the dedicated SQL pools support the required stored procedure, and therefore Spotfire is unable to load metadata.

Hence, the "serverless" Synapse SQL pools don't support the required stored procedure.

However, you can give it a try by creating a Custom query (using select *), as a workaround and see if it helps.

BR,

Prashant Ajjannavar