CTE Error when installing sp_WhoIsActive

Question

I have several identical (near as I can tell) SQL Servers where I've recently added sp_WhoIsActive (showing some folks how much I like this tool) but one of them will not let me create the stored procedure. I get an error I'm quite familiar with about a CTE needing the previous statement to end with a semicolon. None of the other servers get this error.

I've boiled down the script to this to recreate the issue:

SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_WARNINGS ON;
SET NUMERIC_ROUNDABORT OFF;
SET ARITHABORT ON;
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_WhoIsActive')
    EXEC ('CREATE PROC dbo.sp_WhoIsActive AS SELECT ''stub version, to be replaced''')
GO
ALTER PROC dbo.sp_WhoIsActive
(
    @filter sysname = ''
)
AS
BEGIN;
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SET QUOTED_IDENTIFIER ON;
    SET ANSI_PADDING ON;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET ANSI_WARNINGS ON;
    SET NUMERIC_ROUNDABORT OFF;
    SET ARITHABORT ON;
IF @filter IS NULL
BEGIN;
    RAISERROR('Input parameters cannot be NULL', 16, 1);
    RETURN;
END;

--SELECT 'FIZZ' AS BUZZ;
WITH
a0 AS
(SELECT 'FOO' AS BAR)
SELECT * FROM a0;

END;
GO

IF you un-comment the SELECT 'FIZZ' AS BUZZ; line, it gets created.

Anyone have an idea what is different about this server?

Things I've checked:

database compatibility levels for all dbs are > 100 (master, model, etc are 140)
SQL Servers are all 14.0.344.5
No availability Groups
SSMS 18.12.1
I'm SA on all servers
version 12 of sp_whoIsActive https://github.com/amachanic/sp_whoisactive/releases/tag/v12.00

Full text of actual error below:

Msg 319, Level 15, State 1, Procedure sp_WhoIsActive, Line 206 [Batch Start Line 11]
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 209 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 212 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 215 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 218 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 221 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Procedure sp_WhoIsActive, Line 225 [Batch Start Line 11]
Incorrect syntax near the keyword 'ORDER'.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 230 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 319, Level 15, State 1, Procedure sp_WhoIsActive, Line 443 [Batch Start Line 11]
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 446 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 449 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 452 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 455 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 458 [Batch Start Line 11]
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Procedure sp_WhoIsActive, Line 462 [Batch Start Line 11]
Incorrect syntax near the keyword 'ORDER'.
Msg 102, Level 15, State 1, Procedure sp_WhoIsActive, Line 468 [Batch Start Line 11]
Incorrect syntax near ','.
Completion time: 2023-01-06T15:04:08.0850888-05:00

Martin Smith · Accepted Answer · 2025-03-15T17:21:32.097

I can repro this issue by going into connection properties and setting Column Encryption Setting=Enabled (or just ticking the Enable Always Encrypted box)

I suggest removing this to allow the procedure creation to succeed.

Apparently this option does some mangling of the source text including removing comments and some incorrect application of semi colons.

The comment removal will be why your line numbers are much lower than would be expected (first instance of ORDER in the code is much later than line 225).

When I execute

CREATE OR ALTER PROC #foo
AS
  BEGIN;
      X: /*This is a comment*/
  END;
WITH a0
     AS (SELECT 'FOO' AS BAR)
SELECT *
FROM   a0;

with that option enabled I see that what is actually executed is

CREATE OR ALTER PROCEDURE #foo
AS
BEGIN
    X:
END
WITH   a0
AS     (SELECT 'FOO' AS BAR)
SELECT *
FROM   a0;

The comments, text indentation, extra line breaks, and semi colons after BEGIN and (problematically here) the END have been removed.

It looks like the text transformation will actually add semi colons in most cases where they didn't exist in the original source so this definition will succeed with the option enabled rather than fail with "Incorrect syntax near the keyword 'with'" as expected.

CREATE OR ALTER PROC #foo
AS
PRINT ''
WITH a0
     AS (SELECT 'FOO' AS BAR)
SELECT *
FROM   a0;

One other symptom of this can occur when SSMS is unable to even parse the batch when attempting to perform its parameterization for always encrypted.

When a ParameterizationParsingException is thrown (e.g. as in the question here) the error message reported shows

Line n, column n instead of the more usual Msg n, Level n, State n, Line n.

In this eventuality selecting View -> Output and "Show output from:" as "Telemetry" will report something like

[2025-03-15T08:47:09.8521729+00:00] sql/ssms/command
                                    SQL.Command.CommandId = 1
                                    SQL.Command.GroupId = 52692960-56bc-4989-b5d3-94c47a513e8d
                                    SQL.Command.Name = Query.Execute
                                    VS.Core.Command.Name = Query.Execute
[2025-03-15T08:47:09.9012121+00:00] sql/ssms/autoparameterization/error/execution_error
                                    SQL.SSMS.AutoParameterization.EXCEPTION_TYPE = Microsoft.SqlServer.Management.UI.VSIntegration.Editors.AutoParameterization.Exceptions.ParameterizationParsingException
                                    SQL.SSMS.AutoParameterization.PARSE_SUCCESSFUL = False

CTE Error when installing sp_WhoIsActive

1 Answers1

Linked

Related