Why does the Create script for a temp-table not show datatype for all columns?

gerthauan

If I am in a session in SSMS where I have created a temp-table using a select into query, then when I hower over this temp-table-name in the query after, and click on it to show the create script, the create script will often miss data types for some columns. Is this a bug? Would it be possible to fix?

An examle:

CREATE TABLE [#SomeTempTable]

(

[sektor] INT,

[Rapporteringsperiode] VARCHAR(11) NOT NULL,

[aar] SMALLINT,

[innDato] DATE,

[utDato] DATE,

[InnMnd],

[pas_id],

[pas_reg2] SMALLINT
)

In this example, two of the columns have no data type.

Jon_Kirkwood

Hi @gerthauan

 

Thank you for your patience whilst our development team worked on this issue.

Version 10.13.13 has been released, which includes a fix for this issue.

Are you able to patch at your earliest convenience and verify this has been resolved for you

 

Download of the latest version available here: https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.13.13.2147.exe

 

Patch notes available here: https://documentation.red-gate.com/sp/release-notes-and-other-versions/sql-prompt-10-13-release-notes

SQL Prompt 10.13 release notes

10.13.13.2147 - Released on 22 August 2023

Features

• Added a feature for saving SQL History advanced search settings.
• Added support for multiple syntaxes on Azure SQL database.
   

Fixes

• Fixed an issue with connecting via Azure Active Directory with MFA for SSMS19.1.
• Fixed AT TIME ZONE expressions recognition.
• Fixed formatting issue for TRIM in SQL Server 2022.
• Fixed "Could not load file or assembly System.Threading.Tasks.Extensions" error occurring when using Azure Active Directory authentication methods.
• Fixed an issue with columns not having data type when created by CAST function.

Dan_J

Hi @gerthauan

Thanks for reaching out to us regarding this, I'm sorry you are seeing this issue in SQL Prompt!

I've tried to reproduce the behavior you are seeing here but have so far been unsuccessful. 

Would you mind confirming what versions of SQL Prompt and SSMS you currently have installed? 

Also, have you noticed that specific data types are being omitted? Or does it happen with multiple data types?

gerthauan

Hi @Dan_J . 

I have been digging a bit more into this, and it seems to me that if the column was created from a cast of the original value, the data type will disappear in the SQLPRompt create script. 
Here is an example:

SELECT CAST(id AS varchar(10)) AS ID, bffs.ServerName, bffs.DatabaseID, CAST(DatabaseId AS varchar(6)) AS DatabaseIDConverted, bffs.TypeDesc

INTO #TempTableName

from dbo.BlitzFirst_FileStats bffs

SELECT * FROM  #TempTableName ttn


If i now hove over the #TempTableName and click the "tooltip", the following create statement is made:

CREATE TABLE [#TempTableName]

(

[ID],

[ServerName] NVARCHAR(128),

[DatabaseID] INT NOT NULL,

[DatabaseIDConverted],

[TypeDesc] NVARCHAR(60)

)

As you can see, the two columns that was created using a CAST, is missing the data type.

gerthauan

@Dan_J So, are you guys looking at this? Could you replicate the bug?

Dan_J

Hi @gerthauan

My sincere apology for the delay in coming back to you on this!

After many failed attempts, I am now finally seeing the same behavior you are reporting. Specifically, that there are no data types for columns created from a cast in the SQL Prompt create script.

This is now with our development team, I will come back to you again as soon as I have an update for you on this.

gerthauan

@Jon_Kirkwood Hi. I was a bit quick to accept your answer as the solution. Allthough the problem with the cast-columns has been fixed, there is still a problem if the column was created with a CASE-statement.

If I use the same example again:

DROP TABLE IF EXISTS #TempTableName;

SELECT  CAST(bffs.ID AS varchar(10))                                AS ID

      , bffs.ServerName

      , bffs.DatabaseID

      , CAST(bffs.DatabaseID AS varchar(6))                         AS DatabaseIDConverted

      , bffs.TypeDesc

      , CASE WHEN bffs.TypeDesc = 'ROWS' THEN 'ROWS' ELSE 'LOG' END AS TypeDescWithCast

INTO    #TempTableName

FROM    dbo.BlitzFirst_FileStats bffs;

SELECT  * FROM  #TempTableName ttn;

If I hover over the #TempTableName and collect the create table script that SQLPrompt is suggestion, it turns out like this:

CREATE TABLE [#TempTableName]

(

[ID] VARCHAR(10),

[ServerName] NVARCHAR(128),

[DatabaseID] INT NOT NULL,

[DatabaseIDConverted] VARCHAR(6),

[TypeDesc] NVARCHAR(60),

[TypeDescWithCast]

)

As you can see, the column created with CASE-expression, the one named TypeDescWithCast, has no data type in the create table-script.

Could you pass this along to the developers?

Thx