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

SQL Prompt column qualifications dropped when a table alias is used

adam1100adam1100 Posts: 4 New member
This bug has existed for as for as long as I have been using SQL Prompt:

CREATE TABLE dbo.Foo (FooId INT PRIMARY KEY);
CREATE TABLE dbo.Foo2 (FooId INT PRIMARY KEY);
CREATE TABLE dbo.Bar (BarId INT PRIMARY KEY);

-- without aliases, columns are still qualified
SELECT NULL 
   FROM dbo.Foo 
  INNER JOIN dbo.Bar ON Bar.BarId = Foo.FooId;

-- after formatting alias qualifiers in ON clause are dropped
SELECT NULL
   FROM dbo.Foo AS _Foo 
  INNER JOIN dbo.Bar AS _Bar ON BarId = _Foo.FooId
  INNER JOIN dbo.Foo2 AS _Foo2 ON _Foo2.FooId = _Foo.FooId;

DROP TABLE dbo.Foo;
DROP TABLE dbo.Foo2;
DROP TABLE dbo.Bar;

Best Answer

  • Options
    Eddie DEddie D Posts: 1,789 Rose Gold 5
    Answer ✓
    Hi adam1100,

    Can you please enable the following option (SQL Prompt menu -> Options) highlighted by the red rectangle in the following screen shot?


    With this option enabled, I can no longer replicate the reported fault symptoms.

    Also can you ensure that the Qualify column names with aliases" (SQL Prompt ->Options-> Inserted code ->Qualification) is checked as well.

    I hope turning on the above options resolves the problem for you.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com

Answers

  • Options
    Eddie DEddie D Posts: 1,789 Rose Gold 5
    Hi, thank you for your forum post.

    What version of SQL Prompt are you using? Also what version of SSMS are you using?
     I ask these questions as I am unable to replicate the reported problem using SSMS 18.4 and SQL Prompt V10.1.4.14671 installed on my test system. I know that I am version behind the current latest versions of SSMS and SQL Prompt.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    adam1100adam1100 Posts: 4 New member
    Hi Eddie, I'm on SQL Prompt version 10.1.5.14730, and SQL Server Management Studio 15.0.18206.0 (SSMS v18.4)

  • Options
    Eddie DEddie D Posts: 1,789 Rose Gold 5
    Hi adam1100,
    After upgrading my SQL Prompt installation to V10.1.5.14730, I am now able to replicate the reported problem.

    I have submitted a bug report.  I will update this post when I receive further news.

    Many Thanks
    Eddie 
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    adam1100adam1100 Posts: 4 New member
    Thanks Eddie.  Appreciate it
  • Options
    adam1100adam1100 Posts: 4 New member
    Eddie, that worked, thank you very much.
  • Options
    Eddie DEddie D Posts: 1,789 Rose Gold 5
    Hi adam1100,
    Excellent news that the option resolves the problem.
    The congratulations should go to my colleagues in development team, who made me aware of the option that I embarrassingly missed.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.