How can we help you today? How can we help you today?

Error Laying Out SQL on 9.1.4.4532

The bug fix in the latest release worked but caused a new issue: 
  • SP-6865 : 'Align data types and constraints' schema (DDL) formatting option could cause an argument null exception.

Now the "Format SQL" command fails if the script declares a variable with sysname.

USE master;
GO

/*
==============================================================================================================================
 Author: Person creating the Stored Procedure
 Create date: 03/22/2018
 Description:   Description of what procedure does
==============================================================================================================================
 Changes: 
 mm/dd/yyyy - Full Name - Short description of changes
==============================================================================================================================
 Indexes: 
 DatabaseName.Schema.IndexName
==============================================================================================================================
*/
CREATE PROC [Name of Stored Procedure]
(
   @pParameterName    VARCHAR(10)
 , @pXPM_UserName     VARCHAR(30) = NULL
 , @pXPM_TransactionID UNIQUEIDENTIFIER = NULL
 , @pXPM_Team_Info    VARCHAR(MAX) = NULL
)
AS
   BEGIN
      SET NOCOUNT ON;

      
      DECLARE @SP_ID  BIGINT
            , @PROCID)
   END;

You will receive the following error: Error refactoring unicode strings.

It looks like it doesn't even have to be within a stored procedure. This fails also:

USE master;
GO


      
      DECLARE @SP_ID  BIGINT
            , @PROCID)


This is a big issue considering our environment has logging constructs in our stored procedures that use sysname. This means I can't format any of our existing stored procedures.
Ngainer
0

Comments

4 comments

Sort by
  • Ngainer
    It actually looks like it is caused by the inclusion of this from the release notes:

    • Added refactoring option to prefix 'N' to literal strings when assigning to a unicode NCHAR/NVARCHAR variable. Found under Prompt -> Options -> Style.
    This was defaulted to checked after my upgrade and started causing Format SQL to fail. After unchecking this option, it is working again. It seems to be having trouble with sysname data type.
    Ngainer
    0
  • kmaughan
    Thanks for posting the workaround - works for me.
    kmaughan
    0
  • Jessica R
    So sorry for this issue in the latest release!

    I've logged a bug for this with internal reference, SP-6892, and we will post here once we have a fix.
    Jessica R
    0
  • Michael Clark
    Hi @Ngainer ,

    We have released SQL Prompt 9.1.5.4619 which fixes this issue. Thanks again for reporting!

    You can download it here:
    http://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_9.1.5.4619.exe

    Best,
    Michael
    Michael Clark
    0

Add comment

Please sign in to leave a comment.