What are the challenges you face when working across database platforms? Take the survey
Options

Refactoring Error

LszokeLszoke Posts: 5 New member

I get this error:  

with the following script:

BEGIN

    DECLARE @Supplement NVARCHAR(30) = N'%' + N'Glucosamine Complex' + N'%';
    
    DECLARE @FromString NVARCHAR(30) = (SELECT TRIM( '%' FROM @Supplement) AS A);

    DECLARE @ToString NVARCHAR(30) = N'Joint Support';

    SELECT @Supplement AS 'Search', @FromString AS 'From' , @ToString AS 'To';

    DECLARE @Reason TABLE
    (
        Supplement NVARCHAR(30)
        , TableId  INT
        , ActionId INT
    );

    INSERT @Reason
           SELECT
                 @FromString
                 , T1.ActionTableId
                 , T1.ActionId
           FROM  glb.AssessmentReasons AS T1
           WHERE T1.[Language] = 1
                 AND T1.Reason LIKE @Supplement;

     -- 1) Update the asmt_Action Reasons column

    UPDATE TA SET TA.Reasons = REPLACE(TA.Reasons, @FromString, @ToString)
      FROM asmt_Action AS TA
      JOIN @Reason AS T2 ON T2.TableId = 1 AND TA.ID = T2.ActionId
      WHERE TA.ActionTableId = 1;

END;

Just upgraded to the latest v9 release.

Please note that part of the script had previously been formatted successfully with the earlier v8 product.

Win10
SQL Prompt 9.4.13.8478
SSMS v17.9.1

Best Regards,
L Szoke
Tagged:

Answers

  • Options
    Thanks for reporting it!

    I can reproduce it with the latest version 9.4.14. By any change do you remember which version was working?
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • Options
    We've logged this issue as SP-7517 in our internal bug tracking system!

    Apologies for the inconvenience caused.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • Options
    LszokeLszoke Posts: 5 New member
    Hi Tianjiao_Li,

    Glad you could reproduce it. The previous version was 8.2.5.2924

    Regards,

    Lajos Szoke
  • Options
    Apologies for the delay. This issue was fixed in 9.4.15.8960. Please upgrade.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • Options
    xapguh5xapguh5 Posts: 2 New member
    Similar problem is happening to me 

    With this code:
    UPDATE [MyTableName]
    SET OrgName = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM OrgName)
      , Address = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM ADDRESS)
      , City = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM City)
      , State = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM STATE)
      , Country = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM Country)
      , Zip = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM Zip)
      , FirstName = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM FirstName)
      , LastName = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM LastName)
      , Phone = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM Phone)
      , Email = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM Email)
      , JobTitle = TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM JobTitle)

    As soon as this is commented out the rest of the code formats fine.  I'm positive it's around the new Trim Function and it's parameters.  Any help appreciated.
    Sql Prompt Version 9.5.9.10716
    SSMS Version 15.0.18131.0
    SQL Server 2017, 14.0.3048.4 (x64)
    Windows Server 2012 R2 Standard 6.3
Sign In or Register to comment.