All of a sudden my layout is getting messed up

caderoux

Lot of extra spaces which don't appear to have any pattern. Repeated reformatting only makes it worse.

I've reset SQLPrompt (3.9.0.43) to defaults and rebooted my machine and nothing is helping.

Have I accidentally set something wrong in SSMS (2005)?

What should I look for?

Here's a sample:

    TRUNCATE                 TABLE MISWork.BFL_PRE
    TRUNCATE        TABLE MISWork.BFL

    -- Insert
    ;
    WITH    P  AS (
                   SELECT   YYMM_ID
                           ,ALOCN_SRC_CD
                           ,PROD_LINE_NUM
                           ,GL_ACCT_NUM
                           ,LINE_ITEM_NUM
                           ,ISNULL([125],            0)          
                             + ISNULL([130],            0) AS                [131]
                           ,ISNULL([313],            0)          
                             + ISNULL([314],             0) 
                                       + ISNULL(    [321],             0)    
                                    + ISNULL(    [322],               0) 
                                       + ISNULL([677],             0)        
                                + ISNULL(
                                                 [678],             0) AS             [133]
                           ,ISNULL([676],           0)          
                            + ISNULL([677],           0) 
                                     + ISNULL([678],           0) AS           [132]
                           ,ISNULL([280],          0)         + ISNULL([281],           0) 
                                   + ISNULL([282],            0)      
                              + ISNULL([283],           0) 
                                   + ISNULL([284],         0)       
                             + ISNULL([285],        0) 
                                   + ISNULL([286],           0)      
                              + ISNULL([287],            0) 
                                   + ISNULL([288],            0)      
                              + ISNULL([289],          0) 
                                   + ISNULL([290],         0)       
                             + ISNULL([291],         0) AS         [279]
                   FROM     PUASFIN.FocusResults.BTS PIVOT (                SUM(ACCT_STATS_CNT)  FOR  ALOCN_BASE_CD  IN  ([125],                [130],              [313],                 [314],                 [321],                 [322],             [676],
                                                           [677], 
                                                             [678],         [280],                 [281],                 [282],     
                                                         [283],   
                                                           [284], 
                                                             [285],
                                                              [286],                 [287],       
                                                       [288],    
                                                          [289],  
                                                            [290], 
                                                             [291]) ) AS                pvt
                  )
        INSERT  INTO MISWork.BFL_PRE 
                (
                 YYMM_ID
                ,ALOCN_SRC_CD
                ,PROD_LINE_NUM
                ,GL_ACCT_NUM
                ,LINE_ITEM_NUM
                ,ALOCN_BASE_CD
                ,ACCT_STATS_CNT
                )
                SELECT  YYMM_ID
                       ,ALOCN_SRC_CD
                       ,PROD_LINE_NUM
                       ,GL_ACCT_NUM
                       ,LINE_ITEM_NUM
                       ,ALOCN_BASE_CD
                       ,ACCT_STATS_CNT
                FROM    P UNPIVOT (               ACCT_STATS_CNT  FOR  ALOCN_BASE_CD  IN  ([131],           [132],             [133],         [279]) ) AS                unpvt
                WHERE   ACCT_STATS_CNT               <> 0.0

caderoux

And all of a sudden it's re-formatting correctly again - no longer adding the extra spaces.

caderoux

Now it's happening again. It's gradually adding extra spaces in.

caderoux

It must be something to do with square brackets and single-line comments, because I'm doing a lot of refactoring as I'm building this process and the behavior comes and goes as I make major changes.

Brian Donahue

Hi Cade,

As you probably would guess, I cannot reproduce the issue. SQL Prompt will lay out the SQL code again and again and still put it in a consistent format.

Maybe if we had an exact representation of the query, we could reproduce the problem. Can you save your query to a file in SSMS, attach it to an email (don't copy/paste the text in) and send it to support (at) red-gate.com?

Thanks!

caderoux

emailed a version from last week which has the problem and the final version which doesn't exhibit the problem.

Thanks,

Cade