Why Script Object as ALTER not working for SQL Pool on Azure

doniluga

Since last couple of updates F12 (script object as alter) stopped working for stored procedures in dedicated sql pool on Azure. Will this functionality come back?

Jon_Kirkwood

Hi @doniluga

 

Thank you for reaching out on the Redgate forums regarding your SQL Prompt inquiry.

I am not aware of any changes to this functionality and am I wondering if you are experiencing a bug.

 

Are you able to confirm your installed version of SQL Prompt

The most recent version is V10.14.8 and is available here:

https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.14.8.6563.exe

 

When you say that it's stopped working - do you still have the 'Script Object as ALTER' menu option when you right-click on your object in the SSMS editor? And does it run when you click on this shortcut?

If that is the case, is it just a situation where the F12 shortcut is no longer working?

In this case it may be a remapping of the key, can you check in Tools > Options > Keyboard > Keyboard for the command SQLPrompt.ScriptObjectasALTER and verify that its shortcut is mapped to F12

 

 

If this is all mapped as expected, but you are still not getting the functionality, can you confirm: 

• Is this only occurring for Dedicated SQL Pools on Azure, or other dB instance types?
• Does the functionality return if you downgrade to V10.14.7
  • https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.14.7.6275.exe

 

If you can confirm these details, it will help to replicate and escalate to our developers as a possible bug in the tool.

 

doniluga

Hi Jon,

I am not sure when it started as last couple of updates were quite close to each other. I am using the latest version of SQL Prompt 10.14.8.6563 and I am pretty sure that this is at least second update that the feature is not working. Tried to run it from the menu but still not working.
I have checked the keyboard settings and those are correctly set up. The feature is working with on prem servers, Azure managed instances, and Azure databases.
It does not work only for Dedicated SQL Pool.

Furthermore, when I point a cursor over a stored procedure and press CTRL + space, SQL Prompt can't fetch the script. It actually shows an error instead of a definition on script tab "Unable to display definition of this object. Reason: Catalog view 'triggers' is not supported in this version." Again, this only happens on Dedicated SQL Pool.

It is not so easy to roll back versions as I need to ask admin privileges from the IT security guys. but I will try.

Thanks,

PambosNik

Hello,

we have the same issue here with "catalog view 'triggers' is not supported in this version"

when we are trying to view the definition of a script (Procedure or View etc) and we have also Synapse Dedicated SQL Pool.
For sure this has to been fixed asap as its cost us on productivity level.

SQL Prompt version: 10.14.10.7538

doniluga said:

Hi Jon,

I am not sure when it started as last couple of updates were quite close to each other. I am using the latest version of SQL Prompt 10.14.8.6563 and I am pretty sure that this is at least second update that the feature is not working. Tried to run it from the menu but still not working.
I have checked the keyboard settings and those are correctly set up. The feature is working with on prem servers, Azure managed instances, and Azure databases.
It does not work only for Dedicated SQL Pool.

Furthermore, when I point a cursor over a stored procedure and press CTRL + space, SQL Prompt can't fetch the script. It actually shows an error instead of a definition on script tab "Unable to display definition of this object. Reason: Catalog view 'triggers' is not supported in this version." Again, this only happens on Dedicated SQL Pool.

It is not so easy to roll back versions as I need to ask admin privileges from the IT security guys. but I will try.

Thanks,

tk14ano

Hi,

We have the same issue on Azure SQL Data Warehouse. We cannot view the definition of Stored-Procedures.

<div>2024-04-03 11:28:56.172 +03:00 [Error] Unable to obtain CreateScript for sp_WhoIsActive</div><div>Microsoft.Data.SqlClient.SqlException (0x80131904): Catalog view 'triggers' is not supported in this version.</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.NextResult()</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Scripting.ObjectFactory.GetStoredProcedureFunction(DbConnection connection, ServerVersion serverVersion, String databaseName, String schemaName, String storedProcedureFunctionName, Func`1 objectDecoder)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Scripting.Scripter.GetStoredProcedureScripter(DbConnection connection, String schemaName, String spName, Func`1 objectDecoder)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Candidates.StoredProcedureCandidate.GetObjectScripter()</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Candidates.StoredProcedureCandidate.GetScript(ScriptType scriptType)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Candidates.AlterCandidateBase.GetCreateScript(ICreationScriptCache scriptCache)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.CommonUI.Forms.SchemaInformation.ScriptFetcher.<>c__DisplayClass1_0.<GetScriptFetcher>b__0()</div>

tk14ano

Hi,

We also have the same issue on our Azure SQL Data Warehouse. See attached screenshot
SQL Prompt version: 10.14.12.7945

Azure Synapse does not support triggers. This might be the roor cause of this issue.

This issue does not occur on SQL Server OnPremise. 

Thanks,
Chris

<div>2024-04-03 11:28:56.172 +03:00 [Error] Unable to obtain CreateScript for sp_WhoIsActive</div><div>Microsoft.Data.SqlClient.SqlException (0x80131904): Catalog view 'triggers' is not supported in this version.</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.NextResult()</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Scripting.ObjectFactory.GetStoredProcedureFunction(DbConnection connection, ServerVersion serverVersion, String databaseName, String schemaName, String storedProcedureFunctionName, Func`1 objectDecoder)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Scripting.Scripter.GetStoredProcedureScripter(DbConnection connection, String schemaName, String spName, Func`1 objectDecoder)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Candidates.StoredProcedureCandidate.GetObjectScripter()</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Candidates.StoredProcedureCandidate.GetScript(ScriptType scriptType)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Candidates.AlterCandidateBase.GetCreateScript(ICreationScriptCache scriptCache)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.CommonUI.Forms.SchemaInformation.ScriptFetcher.<>c__DisplayClass1_0.<GetScriptFetcher>b__0()</div>

tk14ano

Hi,

We also have the same issue on our Azure SQL Data Warehouse. See attached screenshot

SQL Prompt version: 10.14.12.7945

Azure Synapse does not support triggers. This might be the roor cause of this issue.

This issue does not occur on SQL Server OnPremise. 

Thanks,

Chris

<div>2024-04-03 11:28:56.172 +03:00 [Error] Unable to obtain CreateScript for sp_WhoIsActive</div><div>Microsoft.Data.SqlClient.SqlException (0x80131904): Catalog view 'triggers' is not supported in this version.</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more)</div><div>&nbsp; &nbsp;at Microsoft.Data.SqlClient.SqlDataReader.NextResult()</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Scripting.ObjectFactory.GetStoredProcedureFunction(DbConnection connection, ServerVersion serverVersion, String databaseName, String schemaName, String storedProcedureFunctionName, Func`1 objectDecoder)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Scripting.Scripter.GetStoredProcedureScripter(DbConnection connection, String schemaName, String spName, Func`1 objectDecoder)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Candidates.StoredProcedureCandidate.GetObjectScripter()</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Candidates.StoredProcedureCandidate.GetScript(ScriptType scriptType)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.Cache.Candidates.AlterCandidateBase.GetCreateScript(ICreationScriptCache scriptCache)</div><div>&nbsp; &nbsp;at RedGate.SqlPrompt.CommonUI.Forms.SchemaInformation.ScriptFetcher.<>c__DisplayClass1_0.<GetScriptFetcher>b__0()</div>