How can we help you today? How can we help you today?

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

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.
gerthauan
0

Comments

6 comments

Sort by
  • 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?
    Dan_J
    0
  • 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
    0
  • gerthauan
    @Dan_J So, are you guys looking at this? Could you replicate the bug?
    gerthauan
    0
  • 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.
    Dan_J
    0
  • 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.
    Jon_Kirkwood
    0
  • 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
    gerthauan
    0

Add comment

Please sign in to leave a comment.