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

Formatting bug in 6.4.0.659

jmeyerjmeyer Posts: 70 Bronze 2
edited October 23, 2014 10:39AM in SQL Prompt
Index formatting adds the table name prior to the index name, resulting in a syntax error in SQL Server 2008 R2

Repo:
-- execute first two statements
CREATE TABLE repo (id INT IDENTITY(1, 1)
                       NOT NULL
              , value CHAR(10) NOT NULL)

CREATE INDEX idx_repo_value ON dbo.repo(value)

-- press CTRL+B, CTRL+Q to format code
IF EXISTS ( SELECT  *
            FROM    sys.indexes
            WHERE   object_id = OBJECT_ID(N'[dbo].[repo]')
                    AND name = N'idx_repo_value' )
  DROP INDEX idx_repo_value ON dbo.repo  

-- DROP INDEX now has incorrect syntax of "DROP INDEX a.idx_a_value ON dbo.a"
-- this only happens if table dbo.repo already exists prior to reformatting the code

DROP TABLE dbo.repo

Comments

  • Options
    Hi Jens,

    Thanks for finding this!

    The latest stable build should fix this for you. Please let me know if this solves the issue.

    Best regards,

    David
  • Options
    jmeyerjmeyer Posts: 70 Bronze 2
    David:

    the private build fixed this particular issue. However, I've got a new one for you, exists both in the latest public release as well as this private build:
    USE tempdb;
    GO
    
    IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[repo]') AND type IN (N'U'))
      DROP TABLE [dbo].[repo];
    GO
    
    CREATE TABLE [dbo].[repo] ([whse_id] [INT] IDENTITY(1, 1) NOT NULL
                             , [effbeg_date] [DATE] NOT NULL)
    ON [PRIMARY];
    GO
    
    SELECT  r.*
    FROM    repo r
    INNER JOIN (SELECT TOP 100
                        whse_id
                      , MAX(effbeg_date) AS effbeg_date
                FROM    repo
                GROUP BY whse_id) ro
    ON      r.whse_id = ro.whse_id
            AND r.effbeg_date = ro.effbeg_date;
    GO
    
    The subquery inside the inner join will inherit the "r" alias from the outer table, resulting in a broken query.
  • Options
    Hi Jens,

    Thanks once again!

    I can reproduce this on my end, so I'll try and get this fixed as soon as possible.

    Best regards,

    David
  • Options
    Hi Jens,

    I've got a private build that should fix the Qualify Object Names issue.

    Unfortunately I made a mistake and the minor version number was updated (6.4.1) in the last release I sent you, sorry about this! This will mean that you'll have to uninstall SQL Prompt before you can install this new release (6.4.0).

    Please let me know if you have any issues, and once again sorry for any inconvenience.

    Best regards,

    David
  • Options
    jmeyerjmeyer Posts: 70 Bronze 2
    David:

    Thanks for the private build, this issue is fixed now.
Sign In or Register to comment.