Error in subsequent formatting

DX_Frank

In My Query I've a left join.

LEFT JOIN	VRT.Rechtsvorm AS R
			ON R.sourceValue = B.LEGAL_ENTY
			OR	(
					R.sourceValue IS NULL
					AND B.LEGAL_ENTY IS NULL
				)

After the first time of formatting my query it formats the query like below.

LEFT JOIN	VRT.Rechtsvorm AS R
			ON R.sourceValue = B.LEGAL_ENTY
			OR	(
					R.	sourceValue IS NULL
					AND B.LEGAL_ENTY IS NULL
				)

NOTICE: The empty space between R. and sourceValue

After formatting my query again SQLPrompt removes the . Between R and source Value as seen below.

LEFT JOIN	VRT.Rechtsvorm AS R
			ON R.sourceValue = B.LEGAL_ENTY
			OR	(
					R	sourceValue IS NULL
					AND B.LEGAL_ENTY IS NULL
				)

At this time I also get an error:
Error laying out sql

This is the first I notice it.
Can this be investigated?

DX_Frank

Hi @Tianjiao_Li,

Today I've updated to version 9.1.1.4243 and it has been resolved.

Grant

Can you post what version you're running please? I've tried to replicate this behavior and I'm not seeing it.

DX_Frank

I'm using SQLPrompt version 9.0.0.3116

I've just updated to version 9.0.6.3580 but the error still exists.

DX_Frank

Here is my complete query with returns an error when formatting the query multiple times.

CREATE TABLE dbo.Table1
( 
	LEGAL_ENTY VARCHAR(2)
)
CREATE TABLE dbo.Table2
( 
	sourceValue VARCHAR(255)
)

SELECT		*
FROM		dbo.Table1 AS B
LEFT JOIN	dbo.Table2 AS R
			ON R.sourceValue = B.LEGAL_ENTY
			OR	(
					R.sourceValue IS NULL
					AND B.LEGAL_ENTY IS NULL
				)

DROP TABLE dbo.Table1
DROP TABLE dbo.Table2

Grant

Unfortunately, I'm not able to replicate the error. I've used your code and I'm formatting multiple times. I've even added additional code to try to force it to rewrite things, but it's not dropping the period like you're seeing.

What version of SSMS are you running? Are there any other settings, language, etc., that might be different between your system and mine that could help me replicate the problem?

DX_Frank

It is very strange, as a college of mine has the same as you're experiencing. The period isn't being dropped...
He is still on version 9.0.0.3116

I'm using SSMS SQL Server 2014 see attached image.


I'll compare my settings with his to fin out if I can find the problem...

DX_Frank

I've compared all settings with my college. Everything is the same and still my period is being removed...

khkiley

I have the same problem.

The first time during the format it looks like a tab is inserted between the alias and the column . Surprisingly SQL does not balk at this and the code still works.

The second time the source is formatted, the period "." after the alias is removed. The code no longer works after this.

Some added information:

Seems like this only happens if the alias is a single character, changing the alias to multiple characters as a work around prevents this from happening.

Turning off "Qualify Object Names" prevents this problem as well.

Hopefully this information assists in resolving this problem.

Thanks,

Kurt


I am running SQL Prompt 9.07.3692

SSMS Version
Microsoft SQL Server Management Studio 12.0.2569.0
Microsoft Analysis Services Client Tools 12.0.2569.0
Microsoft Data Access Components (MDAC) 10.0.16299.15
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.11.16299.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.16299

khkiley

A little more information.

When "Qualify Object Names" is turned off, the period is not removed, but the tab is still inserted after the period following the table alias and column name.

Kurt

DX_Frank

I've also tried the setting 'Qualify Object Names' set to off, but still the period is being removed. Renaming the alias to multiple characters is indeed a workarouns for this issues, but SQLprompt suggests a single character as alias.

Tianjiao_Li

Hi @DX_Frank

I'm afraid we still couldn't reproduce the issue given the information you have supplied.

Can you please format it using the default format style? Does the issue still occur?

So sorry for the inconvenience caused.

DX_Frank

Hi @Tianjiao_Li,

With the default format style I don't have the issue.
So it seems to be in one of the settings in our company style.

I'll attach our company style.

With these settings I have the problem.

Tianjiao_Li

Hi @DX_Frank ,

Thanks for the style file.

However I still couldn't reproduce the issue with it. Can you please upgrade to the latest version (9.0.8.3873 - January 31st 2018) and test again?

If problem persists, can you copy the style file from your co-worker or recreate a new one and try again?