Refactor Bug - SQL Prompt 10.1.7.15015

howarthcd

SQL Prompt 10.1.7.15015

I've encountered a strange (possibly edge-case) bug when refactoring in either SSMS (v18.5) or VS 2017 Professional (15.9.21) , seemingly when a table belongs to a schema named 'Logging'.

The following statement (generated by VS when importing a database into a database project) raises an error when refactoring:

CREATE TABLE [Logging].[LogEntries] (

    [LogEntryId]             INT            IDENTITY (1, 1) NOT NULL,

    CONSTRAINT [PK_LogEntries] PRIMARY KEY CLUSTERED ([LogEntryId] ASC) WITH (FILLFACTOR = 90)

)

GO




...but the following does not:

CREATE TABLE [Loggings].[LogEntries] (

    [LogEntryId]             INT            IDENTITY (1, 1) NOT NULL,

    CONSTRAINT [PK_LogEntries] PRIMARY KEY CLUSTERED ([LogEntryId] ASC) WITH (FILLFACTOR = 90)

)

GO

However, reverting back to the original statement, adding in a CREATE SCHEMA statement causes an error not to be raised:

CREATE SCHEMA [Logging];

GO

CREATE TABLE [Logging].[LogEntries] (

    [LogEntryId]             INT            IDENTITY (1, 1) NOT NULL,

    CONSTRAINT [PK_LogEntries] PRIMARY KEY CLUSTERED ([LogEntryId] ASC) WITH (FILLFACTOR = 90)

)

GO

There seems to be something odd going on in relation to the word 'Logging'.

Thanks
Chris

Alex B

Hi @howarthcd,

There's something else going on here, but I'm not quite sure what it is.  I have another customer seeing something similar, but it's only in relation to two tables that have the same schema and table name.  There are some others with the same schema and table name that do format correctly, but only a specific two that do the same as you see here (and they do not have Logging in the name).

Now in both cases this works correctly for me and the only way I get the same error is to manually put a semicolon where it indicates there is an error with the semicolon.

It seems to be trying to add a semicolon before the WITH as if it were a CTE in your cases, but SQL Prompt no longer does this that I can see.

If you try formatting both of these, what is the result:

<div>WITH test AS
(SELECT 1 AS col1)
SELECT * FROM test</div>

and

SELECT 1

WITH test AS
(SELECT 1 AS clo1)
SELECT * FROM test

For me, the first one does not add a semicolon before the WITH and the second one will add a semicolon after the 1 as normal for insert semicolons, does that happen for you?

Kind regards,
Alex

howarthcd

Alex B said:

If you try formatting both of these, what is the result:

<div>WITH test AS
(SELECT 1 AS col1)
SELECT * FROM test</div>

and

SELECT 1

WITH test AS
(SELECT 1 AS clo1)
SELECT * FROM test

For me, the first one does not add a semicolon before the WITH and the second one will add a semicolon after the 1 as normal for insert semicolons, does that happen for you?

Kind regards,
Alex

Hi Alex, I get the same as you - no semicolon for the first (as I would expect), semi-colon after the SELECT 1 statement for the second.

The error I'm getting appears to be something relating to the word 'Logging' (the table's schema name in my example). If I add an 's' to the schema name (i.e. 'Loggings') then the semi-colon adding works just fine.

The error I reported only seems to happen if I attempt to refactor with just the table CREATE statement. As soon as I add in a 'CREATE SCHEMA Logging' statement then the refactor completes.

Thanks
Chris

Alex B

Hi @howarthcd,

The other person I have seeing this isn't seeing it in relation to a Logging schema, and for them if they change the name of the table (adding an x) it then works.

Are you using ER/Studio to develop things here?  Do you have any other addons in SSMS that could be having an impact?  And is it possible there are any non visible characters around the WITH statement?

When I have your script, it's not highlighting anything for me around the WITH statement:
​
even when I don't have the schema created.

If you type the statement out in a new query window by hand does it also show the red squiggles next to WITH?

And when you mouse over the red squiggles next to the WITH and the beginning of FILLFACTOR statement what does it indicate is the problem?

Kind regards,
Alex

howarthcd

Hi @Alex B, I think I've sussed out what's going on.

If the schema name in which the table is being created has the same name as a database attached to the instance of SQL Server, then the error occurs.

You should be able to replicate the behaviour if you try this on a SQL Server instance:

USE master
GO

CREATE DATABASE Logging

GO

...then in a new SSMS query window change context to the new Logging database then refresh the SQL Prompt cache.

Then attempt to add semicolons to the following in ANY database on the SQL Server instance (not just the one newly created):

CREATE TABLE [Logging].[LogEntries] (

    [LogEntryId]             INT            IDENTITY (1, 1) NOT NULL,

    CONSTRAINT [PK_LogEntries] PRIMARY KEY CLUSTERED ([LogEntryId] ASC) WITH (FILLFACTOR = 90)

)

GO

You should then experience the error.

It so happens that the database in which I am trying to create the above table is also named 'Logging', I didn't think that this was relevant at first but it now seems that it might be.

Thanks
Chris

howarthcd

@Alex B

To answer your question regarding development environment, this happens in SSMS but also in VS when working with database projects where the database name is the same as the schema name in which the table is being created.

This particular table script was auto-generated by VS when 'importing' the database via the native Database Project import task in VS.

Alex B

Hi @howarthcd,

That's done it!  Thank you for that, I'm not sure I would ever have been able to suss it given I don't see the databases you have and are running it on :-)

I was going into the IDE and such since it didn't seem there was another way it could be happening other than how the script was generated (I've not seen anything like this before).

I'll escalate this to the development team and let you know when I have an update from them!

Kind regards,
Alex

Alex B

Hi @howarthcd,

Righto, so it seems that when the schema name and the database match, the query needs to be fully qualified otherwise it is invalid SQL syntax as it doesn't know whether it is the database or the schema that is being used to qualify with.  When you put the CREATE SCHEMA statement in, it then uses that to say it's the schema and therefore why it works in that case.

The insert semicolons refactoring works correctly when you fully qualified the table (in this case using [Logging].[Logging].[LogEntries]).

Ultimately, creating a schema with the same name as the database is not considered a best practice and it is an edge-case so unfortunately the team is not going to be able to dedicate time to looking further into the issue and possible fixes, but thank you for taking the time to raise it with us so we are aware of it!

Kind regards,
Alex

howarthcd

Thanks @Alex B for researching into this and getting back to me.

I'm not totally convinced about the explanation though as I've subsequently found that the error only surfaces when there is a 'WITH' clause included with the constraint creation statement, even when the table creation statement is not qualified with the database name.

Splitting apart the table and constraint creation script gives the error in this scenario:

CREATE TABLE [Logging].[LogEntries] (

    [LogEntryId]             INT            IDENTITY (1, 1) NOT NULL

)

GO

ALTER TABLE  [Logging].[LogEntries] ADD

    CONSTRAINT [PK_LogEntries] PRIMARY KEY CLUSTERED ([LogEntryId] ASC) WITH (FILLFACTOR = 90)

GO

...but not in this one:

CREATE TABLE [Logging].[LogEntries] (

    [LogEntryId]             INT            IDENTITY (1, 1) NOT NULL

)

GO

ALTER TABLE  [Logging].[LogEntries] ADD

    CONSTRAINT [PK_LogEntries] PRIMARY KEY CLUSTERED ([LogEntryId] ASC)

GO

The only difference is the omission of the WITH clause in the second pair of statements.

I agree that this is evidently an edge case and so likely isn't worth spending the time on fixing.

Thanks again

Chris