Activity overview
Latest activity by Chris
Why have you started linking to the Toolbelt in the SQL Prompt Release Notes, seems odd: https://documentation.red-gate.com/sp/release-notes-and-other-versions/sql-prompt-11-3-release-notes / comments
Why have you started linking to the Toolbelt in the SQL Prompt Release Notes, seems odd:https://documentation.red-gate.com/sp/release-notes-and-other-versions/sql-prompt-11-3-release-notes
Uninstalling SQL Prompt, rebooting and then reinstalling SQL Prompt fixed my issue. / comments
Uninstalling SQL Prompt, rebooting and then reinstalling SQL Prompt fixed my issue.
After upgrading to 22.6 I get the following error when trying to open SQL History: The semaphore timeout period has expired. / comments
After upgrading to 22.6 I get the following error when trying to open SQL History:The semaphore timeout period has expired.
All of these fail to format and error for me: Azure SQL Database, compatibility level 170 SQL Prompt 11.3.10.22862 SELECT
IIF(CURRENT_DATE=CURRENT_DATE AND is_ms_shipped=1,1,0) AS [is_ms_shipped]
FROM sys.objects AS [o]
SELECT
REGEXP_REPLACE ([o].[type], 'USER_|SYSTEM_', '') AS [regexp_replace]
FROM [sys].[objects] AS [o];
SELECT
REGEXP_SUBSTR(CONCAT([o].[schema_id], ':', [o].[name]), '\\d+', 1, 1) AS [regexp_substr]
FROM sys.objects AS [o];
SELECT
REGEXP_INSTR(CONCAT([o].[name], '_', [o].[object_id]), '\\d+', 1, 1) AS [regexp_instr]
FROM sys.objects AS [o];
SELECT
REGEXP_COUNT([o].[name], '_') AS [regexp_count]
FROM sys.objects AS [o];
SELECT
CASE WHEN REGEXP_LIKE([o].[name], '^[A-Za-z]') THEN 1 ELSE 0 END AS [regexp_like]
FROM sys.objects AS [o];
SELECT
[m].[match_value] AS [regexp_matches]
FROM sys.objects AS [o]
CROSS APPLY REGEXP_MATCHES(CONCAT('#', [o].[name], ' #AzureSQL'), '#\\w+') AS [m];
SELECT
[s].[Value] AS [regexp_split_to_table]
FROM sys.objects AS [o] CROSS APPLY REGEXP_SPLIT_TO_TABLE([o].[name], '[,;|]') AS [s]; / comments
All of these fail to format and error for me:Azure SQL Database, compatibility level 170SQL Prompt 11.3.10.22862 SELECT
IIF(CURRENT_DATE=CURRENT_DATE AND is_ms_shipped=1,1,0) AS [is_ms_shipped...
Also seeing the same behaviour with REGEXP_LIKE: SELECT
*
, REGEXP_REPLACE ([o].[type_desc], 'SYSTEM_|USER_', '') AS [type_desc]
FROM [sys].[objects] AS [o]; Using [type_desc2] formats the code without error, but then SQL Prompt puts REGEXP_REPLACE in square brackets, which is not valid SQL, so fails to execute. / comments
Also seeing the same behaviour with REGEXP_LIKE:SELECT
*
, REGEXP_REPLACE ([o].[type_desc], 'SYSTEM_|USER_', '') AS [type_desc]
FROM [sys].[objects] AS [o];Using [type_desc2] formats the code...
Seems to be CURRENT_DATE it doesn't like, this works fine: SELECT * , CAST (IIF([o].[is_ms_shipped] = 0 AND DATEDIFF (DAY, CAST (SYSUTCDATETIME () AS DATE), [o].[create_date]) BETWEEN 0 AND 7, 1, [o].[is_ms_shipped]) AS [BIT]) AS [is_ms_shipped] FROM [sys].[objects] AS [o]; I've also noticed that using CURRENT_DATE breaks intellisense, not sure if that SQL Prompt or SSMS? / comments
Seems to be CURRENT_DATE it doesn't like, this works fine:SELECT * , CAST (IIF([o].[is_ms_shipped] = 0 AND DATEDIFF (DAY, CAST (SYSUTCDATETIME () AS DATE), [o].[create_date]) BETWEEN 0 AND 7, 1, ...
The problem I have is that when I format the code above it changes . . .AS [is_ms_shipped] to …AS [o].[is_ms_shipped], which isn't valid SQL and causes SQL Prompt to fail with an incorrect syntax error. If I change my alias to something different to the name of the column used inside the IIF, e.g. [is_ms_shipped2], then SQL Prompt can format it just fine. I've only noticed it happening with the combination of code above. / comments
The problem I have is that when I format the code above it changes . . .AS [is_ms_shipped] to …AS [o].[is_ms_shipped], which isn't valid SQL and causes SQL Prompt to fail with an incorrect syntax e...
REGEXP and CURRENT_DATE fail to format
I have some SQL similar to the below example and it fails to format because it adds the table alias to the column alias when the column alias is the same as an existing column inside the IF, I need...
PIVOT
In the sample query below [object_id] and [type_desc] are underlined in green because they're unqualified. SQL Prompt does not automatically add the [p]. aliases and if I manually add them and then...
JSON_ARRAYAGG
I'm using SQL Prompt 11.0.8 on Azure SQL database, using an example from Microsoft's JSON_ARRAYAGG documentation:SELECT TOP(5) c.object_id, JSON_ARRAYAGG(c.name ORDER BY c.column_id) AS column_list...