locked
Incorrect casting of Data types while publishing database projects using VS2015 RRS feed

  • Question

  • Hi,

    When we are trying to generate a script after the schema compare using Visual Studio 2012/2015, we see an issue with the script generated whenever converting from either system defined data type of a column to user defined data type or from user defined data type to user defined data type. 

    The following code should help to reproduce the issue

    -- Create a test table
    CREATE TABLE [dbo].[TestB]
    (
    	[ColumnA] [INT] IDENTITY(1,1) NOT NULL,
    	[ColumnB] [NVARCHAR](200) NOT NULL,
    	[ColumnC] [VARCHAR](100) NOT NULL,
    	[ColumnD] [DECIMAL](10, 0) NOT NULL,
    	[CheckSum]  AS (HASHBYTES('SHA2_256',CONCAT([ColumnA],[ColumnB],[ColumnC],[ColumnD]))),
    PRIMARY KEY CLUSTERED 
    (	[ColumnA] ASC )
    ) ON [PRIMARY]
    
    
    -- User Defined Type
    CREATE TYPE [dbo].[UDT_AMT] FROM [DECIMAL](10, 3) NOT NULL
    
    -- amend the test table as below in TFS
    CREATE TABLE [dbo].[TestB]
    (
    	[ColumnA] [INT] IDENTITY(1,1) NOT NULL,
    	[ColumnB] [NVARCHAR](200) NOT NULL,
    	[ColumnD] [dbo].[UDT_AMT] NOT NULL,
    	[CheckSum]  AS (HASHBYTES('SHA2_256',CONCAT([ColumnA],[ColumnB],[ColumnD]))),
    PRIMARY KEY CLUSTERED 
    (	[ColumnA] ASC )
    ) ON [PRIMARY]
    
    
    -- Script generated after the schema compare is as below
    CREATE TABLE [dbo].[tmp_ms_xx_TestB] (
        [ColumnA]  INT             IDENTITY (1, 1) NOT NULL,
        [ColumnB]  NVARCHAR (200)  NOT NULL,
        [ColumnD]  [dbo].[UDT_AMT] NOT NULL,
        [CheckSum] AS              (hashbytes('SHA2_256', concat([ColumnA], [ColumnB], [ColumnD]))),
        PRIMARY KEY CLUSTERED ([ColumnA] ASC)
    );
    
    IF EXISTS (SELECT TOP 1 1 
               FROM   [dbo].[TestB])
        BEGIN
            SET IDENTITY_INSERT [dbo].[tmp_ms_xx_TestB] ON;
            INSERT INTO [dbo].[tmp_ms_xx_TestB] ([ColumnA], [ColumnB], [ColumnD])
            SELECT   [ColumnA],
                     [ColumnB],
                     CAST ([ColumnD] AS [dbo].[UDT_AMT]) -- This throws an error as it is not recognized as a system defined data type
            FROM     [dbo].[TestB]
            ORDER BY [ColumnA] ASC;
            SET IDENTITY_INSERT [dbo].[tmp_ms_xx_TestB] OFF;
        END
    
    DROP TABLE [dbo].[CheckSumTestB];
    
    EXECUTE sp_rename N'[dbo].[tmp_ms_xx_CheckSumTestB]', N'CheckSumTestB';

    In the generated script you would notice that it is trying to case the column using user defined data type (i.e., UDT_AMT) as opposed to the base data type (i.e., DECIMAL(10,3) ). This however has been observed only while converting numeric fields but not the strings which is strange.

    Could you please let me know if this is a bug? If so, is there any workaround to handle this until a permanent fix is applied?

    Thanks,
    Vamsi K


    VamsiK87


    • Edited by Vamsik87 Friday, April 22, 2016 9:21 AM typo
    Friday, April 22, 2016 6:57 AM

All replies

  • Hi,

    Thanks for posting here,

    we would like to know whether you are using Visual Studio Team Service or on-premises tfs and we would appreciate if you can explain your scenario bit more. This will help us to answer your query better.

    Regards,

    Friday, April 22, 2016 3:37 PM
  • We are not using Visual Studio Team Service or there are any plans in  migrating to it anytime soon.

    There is a requirement for us to automate the build and deployment of database projects. As part of which we are trying to generate the publish script in which above said issue has been noticed. Either we generate the publish script or do a schema compare and then generate the update script the issue persists. If you would like to know more details please do let me know what you are looking from me in specific.

    Thanks.


    VamsiK87

    Tuesday, April 26, 2016 2:24 AM
  • Any help in this regard is much appreciated...

    Thanks,


    VamsiK87

    Thursday, May 12, 2016 7:25 AM
  • @Vamsik87 - I can't offer a fix, but in cases where you are seeing auto-generated script errors you might want to consider a migrations-driven approach, which gives developers full control over how changes are deployed.

    There are open source solutions that use this approach, such as Flyway and Liquibase.

    Redgate provides ReadyRoll, implemented as a sub-type of the SSDT database project, so shares many characteristics, such as the deep Visual Studio integration. It also auto-generates the SQL but does so at development time, which means it can be customized (and therefore fixed) by the developer well before it reaches deployment. This makes it very well suited to automated deployments where reliability is crucial.

    I work as a product manager at Redgate and am happy to answer any questions you might have.


    Product Manager Red Gate Software

    Tuesday, May 24, 2016 3:41 PM
  • Thanks David. As a matter of fact, I had heard about the Ready Roll and we were given a presentation as well on how to use it and all. We may adopt it eventually.

    Thanks,

    Vamsi


    VamsiK87

    Saturday, July 2, 2016 3:45 AM