All of a sudden my layout is getting messed up

caderouxcaderoux Posts: 6
edited April 15, 2009 10:29AM in SQL Prompt Previous Versions
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

Comments

  • And all of a sudden it's re-formatting correctly again - no longer adding the extra spaces.
  • Now it's happening again. It's gradually adding extra spaces in.
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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!
  • emailed a version from last week which has the problem and the final version which doesn't exhibit the problem.

    Thanks,

    Cade
Sign In or Register to comment.