Synapse Serverless SQL Support
TckFahimKanji
Posts: 10 New member
in SQL Prompt
Hi There,
I'm having issues getting SQL Prompt to provide functionality on Synapse Serverless databases. I'm an admin on the synapse resource and have full administrative rights inside the workspace.
I'm using SSMS 18.1.1 and SQL Prompt 10.11.9.27382. The reported version of synapse-serverless endpoint SQL is 12.0.2000.8.
The error suggests that SQL Prompt is trying to query a database (sysdatabases) that doesn't exist - shouldn't it be sys.databases instead?
Here's a verbose log of me connecting to my synapse serverless endpoint and trying to refresh the cache
I'm having issues getting SQL Prompt to provide functionality on Synapse Serverless databases. I'm an admin on the synapse resource and have full administrative rights inside the workspace.
I'm using SSMS 18.1.1 and SQL Prompt 10.11.9.27382. The reported version of synapse-serverless endpoint SQL is 12.0.2000.8.
The error suggests that SQL Prompt is trying to query a database (sysdatabases) that doesn't exist - shouldn't it be sys.databases instead?
Here's a verbose log of me connecting to my synapse serverless endpoint and trying to refresh the cache
2022-04-28 16:13:06.746 -07:00 [Debug] Checking is contained or restricted
2022-04-28 16:13:09.055 -07:00 [Debug] Contained or restricted: >2012
2022-04-28 16:13:10.466 -07:00 [Debug] Loading server capabilities
2022-04-28 16:13:10.539 -07:00 [Information] Chosen server capabilities "Sql2014"
2022-04-28 16:13:11.052 -07:00 [Warning] Failed to load server [mysynapseserverless]-ondemand.sql.azuresynapse.net
System.Data.SqlClient.SqlException (0x80131904): 'sysdatabases' is not supported.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.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.SqlClient.SqlCommand.ExecuteReader()
at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.PopulateDatabases(SqlConnection masterConnection, Boolean wasSkippedDueToFilter)
at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.LoadServer()
ClientConnectionId:f2069021-1a53-4f0d-ac90-b945e647092a
Error Number:15871,State:10,Class:16
2022-04-28 16:13:14.584 -07:00 [Debug] Loading server capabilities
2022-04-28 16:13:14.656 -07:00 [Information] Chosen server capabilities "Sql2014"
2022-04-28 16:13:16.049 -07:00 [Warning] Failed to load server [mysynapseserverless]-ondemand.sql.azuresynapse.net
System.Data.SqlClient.SqlException (0x80131904): 'sysdatabases' is not supported.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.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.SqlClient.SqlCommand.ExecuteReader()
at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.PopulateDatabases(SqlConnection masterConnection, Boolean wasSkippedDueToFilter)
at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.LoadServer()
ClientConnectionId:6ebf40cf-a0d6-4aaf-bfea-8d9f1203d261
Error Number:15871,State:10,Class:16
2022-04-28 16:13:18.273 -07:00 [Debug] Checking is contained or restricted
2022-04-28 16:13:19.899 -07:00 [Debug] Contained or restricted: >2012
2022-04-28 16:13:21.424 -07:00 [Debug] Loading server capabilities
2022-04-28 16:13:21.500 -07:00 [Information] Chosen server capabilities "Sql2014"
2022-04-28 16:13:22.060 -07:00 [Warning] Failed to load server [mysynapseserverless]-ondemand.sql.azuresynapse.net
System.Data.SqlClient.SqlException (0x80131904): 'sysdatabases' is not supported.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.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.SqlClient.SqlCommand.ExecuteReader()
at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.PopulateDatabases(SqlConnection masterConnection, Boolean wasSkippedDueToFilter)
at RedGate.SqlPrompt.Cache.Candidates.ServerCandidate.LoadServer()
ClientConnectionId:cb0fbe5b-a421-42d9-bd09-aca2197900d1
Error Number:15871,State:10,Class:16
Tagged:
Answers
https://documentation.red-gate.com/sp/getting-started/requirements
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?
Thanks for the update. That's unfortunate
Do you know if and when you may be providing support for Synapse with SQL Prompt?
Cheers,
Fahim.
I'm not sure, my advice would be to keep an eye on the patch notes for updates
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?
can you take another closer look at this please? I submitted this same inquiry ealrier this year and the support person was able to SHOW ME that they could crawl synapse serverless databases with SQL Prompt ...
Please help me figure this out:
https://forum.red-gate.com/discussion/comment/168118#Comment_168118
Unfortunately I'm unable to investigate this issue due to not supporting Azure Synapse with SQL Prompt.
If you wish this to be supported, I would post the request on the UserVoice here: https://redgate.uservoice.com/forums/94413-sql-prompt
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?