SQL Prompt column qualifications dropped when a table alias is used

adam1100 · April 27, 2020 2:23PM

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;

Eddie D · May 6, 2020 11:19AM

Hi adam1100,

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

Image: https://us.v-cdn.net/6029854/uploads/editor/bp/7bgt459y4d53.png

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 D · April 28, 2020 5:32PM

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

adam1100 · April 28, 2020 5:39PM

Hi Eddie, I'm on SQL Prompt version 10.1.5.14730, and SQL Server Management Studio 15.0.18206.0 (SSMS v18.4)

Eddie D · May 1, 2020 2:51PM

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

adam1100 · May 1, 2020 3:12PM

Thanks Eddie. Appreciate it

adam1100 · May 6, 2020 12:53PM

Eddie, that worked, thank you very much.

Eddie D · May 7, 2020 7:53AM

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

SQL Prompt column qualifications dropped when a table alias is used

Best Answer

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk