Error in subsequent formatting

DX_FrankDX_Frank Posts: 8 New member
edited January 15, 2018 10:49AM in SQL Prompt
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?
Tagged:

Best Answer

Answers

  • Can you post what version you're running please? I've tried to replicate this behavior and I'm not seeing it.
  • DX_FrankDX_Frank Posts: 8 New member
    edited January 16, 2018 9:26AM
    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_FrankDX_Frank Posts: 8 New member
    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
    
  • 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_FrankDX_Frank Posts: 8 New member
    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.
    61hkjmns50bp.png

    I'll compare my settings with his to fin out if I can find the problem...
  • DX_FrankDX_Frank Posts: 8 New member
    I've compared all settings with my college. Everything is the same and still my period is being removed...
  • khkileykhkiley Posts: 4 Bronze 1
    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
  • khkileykhkiley Posts: 4 Bronze 1
    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_FrankDX_Frank Posts: 8 New member
    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.
  • 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.
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
  • DX_FrankDX_Frank Posts: 8 New member
    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.
  • 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?
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
Sign In or Register to comment.