Options

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
· Share on Twitter

Comments

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

    Thanks,

    Cade
    · Share on Twitter
Sign In or Register to comment.