1

Have third-party procedure creation scripts to install on my databases. SQL Server 2019 CU21 databases are spread across several Windows 2019 servers. On some servers, the creation scripts run fine. On other servers, obscure syntax errors such as issues with the "WITH" clause come up. In addition, and related to the subject of the question, when the CREATE PROCEDURE statements do resolve, all comments and all white space are removed when the procedures' definitions are checked in SSMS and with object_definition(). This causes a logical error in some of the procedures because it is checking for the date in one of these comments to determine the version of the procedure that is running. I'm assuming this is indicative of some environmental differences between the servers, but I cannot for the life of me see what it is. All the options I can see in SSMS are identical.

Since the scripts themselves are big, here is an illustrative example of the behavior, first when working as expected and a few unexpected behaviors/errors I could recreate.

create or alter procedure test 

as 

begin
  ----------------------------------------------------------------------------------------------------
  -- TTTTTTTTestttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt --
  ----------------------------------------------------------------------------------------------------
    Print 'test'

END
GO

Expected result: Commands completed successfully. The procedure is created identical to the create procedure statment, character-for-character.

Unexpected result: Commands completed successfully. But now all white space and comments are missing. See below:

/****** Object:  StoredProcedure [dbo].[test]    Script Date: 6/27/2023 3:51:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER   PROCEDURE [dbo].[test]
AS
BEGIN
    PRINT 'test';
END

If I highlight just the comments and execute that, the servers producing expected behavior just say the normal "Commands completed successfully." But on the servers producing unexpected behavior, I receive "An error occurred while executing batch. Error message is: ExecuteReader: CommandText property has not been initialized"

Other issues with the third-party scripts on the abnormal servers include a cascade of "Must declare the scalar variable ..." errors for local variables utilizing procedure parameters when int local variables are declared and defined on a single line (DECLARE @x int = 0 had to be convereted to DECLARE @x int; SELECT @x = 0) and complaining that I need to put a semicolon after the last stement before using a WITH (CTE) clause, which they already were. Had to create a table using the WITH statement parameters that was then referenced in the subsequent quereies.

Obviously there are some sort of environmental differences here between the servers with the expected behavior vs. the unexpected behavior that are changing how the code is being compiled/processed, but I'm at a loss as to what they would be since the servers were just recently updated to CU 21 and the options I can readily see are all the same. Perhaps some dll or executable differences in things not covered by the CU patches? Or some weird hidden options I don't know about?

2

1 Answer 1

0

Per Martin Smith's comment, the issue was that some of the connections were made with Always On Encryption checked. Unchecked that in the Connection Options, then all servers responded with expected behavior.

Not the answer you're looking for? Browse other questions tagged or ask your own question.