Generated script with IDENTITY INSERT fails

rerezzrerezz Posts: 5
edited March 10, 2017 4:29AM in ReadyRoll
I'm changing a table schema (with IDENTITY PK) so that the migration has to drop and create the table.

The generated migration script in the ready roll project is: 
CREATE TABLE [dbo].[RG_Recovery_1_XXX]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
...
)
GO
SET IDENTITY_INSERT [dbo].[RG_Recovery_1_XXX] ON
GO
INSERT INTO [dbo].[RG_Recovery_1_XXX]([ID], ...) SELECT [ID], ... FROM [dbo].[XXX]
GO
SET IDENTITY_INSERT [dbo].[RG_Recovery_1_XXX] OFF
GO

the build generates the following in {_Package.sql}:
IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('a412b234-714e-4f72-bd85-d885427b1020' AS UNIQUEIDENTIFIER))
  EXECUTE ('CREATE TABLE [dbo].[RG_Recovery_1_XXX]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
...
)
');

GO
IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('a412b234-714e-4f72-bd85-d885427b1020' AS UNIQUEIDENTIFIER))
  EXECUTE ('SET IDENTITY_INSERT [dbo].[RG_Recovery_1_XXX] ON
');

GO
IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('a412b234-714e-4f72-bd85-d885427b1020' AS UNIQUEIDENTIFIER))
  EXECUTE ('INSERT INTO [dbo].[RG_Recovery_1_XXX]([ID], ...) SELECT [ID], ... FROM [dbo].[XXX]
');

GO
IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('a412b234-714e-4f72-bd85-d885427b1020' AS UNIQUEIDENTIFIER))
  EXECUTE ('SET IDENTITY_INSERT [dbo].[RG_Recovery_1_XXX] OFF
');

Executing this with vsts on azure database gives me the following error:

Msg 544, Level 16, State 1, Server XYZ, Line 1
Cannot insert explicit value for identity column in table 'RG_Recovery_1_XXX' when IDENTITY_INSERT is set to OFF.

I removed the GO statements in the migration script:
CREATE TABLE [dbo].[RG_Recovery_1_XXX]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
...
)
GO
SET IDENTITY_INSERT [dbo].[RG_Recovery_1_XXX] ON
INSERT INTO [dbo].[RG_Recovery_1_XXX]([ID], ...) SELECT [ID], ... FROM [dbo].[XXX]
SET IDENTITY_INSERT [dbo].[RG_Recovery_1_XXX] OFF
GO

The build generates the script as following:
IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('a412b234-714e-4f72-bd85-d885427b1020' AS UNIQUEIDENTIFIER))
  EXECUTE ('EXECUTE ('SET IDENTITY_INSERT [dbo].[RG_Recovery_1_XXX] ON
  INSERT INTO [dbo].[RG_Recovery_1_XXX]([ID], ...) SELECT [ID], ... FROM [dbo].[XXX]
  EXECUTE ('SET IDENTITY_INSERT [dbo].[RG_Recovery_1_XXX] OFF
');

Which can be executed successfully.
Tagged:
· Share on Twitter

Comments

  • dnlnlndnlnln Posts: 234 Gold 2
    Thanks for taking the time to detail the issue (plus the workaround) so thoroughly for us.

    I've confirmed that this is a defect in the current version of ReadyRoll (1.13.21). Unfortunately, I do not have a timeline for it's resolution at timeline at this stage.

    I will update this thread once I have more information/resolution for the problem
    Daniel Nolan
    Product Manager
    Redgate Software
    · Share on Twitter
  • rbigbierbigbie Posts: 16 Bronze 2
    Did this ever get fixed? I am having a very similar problem. I have a generated migration script created by ReadyRoll version 1.14.6.4220 inside Visual Studio Enterprise 2017 version 15.2 (26430.16). Here is my generated script:

    -- <Migration ID="02805588-769b-4448-8b22-4ce3445b9c0a" />
    GO

    PRINT N'Rebuilding [dbo].[Products]'
    GO
    CREATE TABLE [dbo].[RG_Recovery_1_Products]
    (
    [Id] [int] NOT NULL IDENTITY(1, 1),
    [ProductCode] [nchar] (10) NOT NULL,
    [ProductName] [nchar] (10) NOT NULL
    )
    GO
    SET IDENTITY_INSERT [dbo].[RG_Recovery_1_Products] ON
    GO
    INSERT INTO [dbo].[RG_Recovery_1_Products]([Id], [ProductCode], [ProductName]) SELECT [Id], [ProductCode], [ProductName] FROM [dbo].[Products]
    GO
    SET IDENTITY_INSERT [dbo].[RG_Recovery_1_Products] OFF
    GO
    DROP TABLE [dbo].[Products]
    GO
    EXEC sp_rename N'[dbo].[RG_Recovery_1_Products]', N'Products', N'OBJECT'
    GO
    PRINT N'Creating primary key [PK_Products] on [dbo].[Products]'
    GO
    ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([Id])
    GO

    and I received the following error message in the log during my VSTS release execution:

    2017-07-24T22:10:49.4711906Z ***** EXECUTING MIGRATION "Migrations\0007_Add_UnitPrice_Products.sql", ID: {f337cc1b-48e7-4d23-b671-6bd7deb814f6} *****
    2017-07-24T22:10:49.5301889Z Dropping constraints from [dbo].[Products]
    2017-07-24T22:10:49.6488548Z Rebuilding [dbo].[Products]
    2017-07-24T22:10:49.8333832Z Msg 544, Level 16, State 1, Server SQL01DEV, Line 1
    2017-07-24T22:10:49.8333832Z Cannot insert explicit value for identity column in table 'RG_Recovery_1_Products' when IDENTITY_INSERT is set to OFF.
    2017-07-24T22:10:49.8919681Z ##[error]A deployment error occurred: sqlcmd.exe exited with a non-zero exit code.
    2017-07-24T22:10:49.9019653Z ##[error]System.Management.Automation.RuntimeException: sqlcmd.exe exited with a non-zero exit code. ---> System.Management.Automation.RuntimeException: sqlcmd.exe exited with a non-zero exit code.
    2017-07-24T22:10:49.9019653Z --- End of inner exception stack trace ---
    2017-07-24T22:10:49.9019653Z at System.Management.Automation.Runspaces.PipelineBase.Invoke(IEnumerable input)
    2017-07-24T22:10:49.9019653Z at System.Management.Automation.PowerShell.Worker.ConstructPipelineAndDoWork(Runspace rs, Boolean performSyncInvoke)
    2017-07-24T22:10:49.9019653Z at System.Management.Automation.PowerShell.Worker.CreateRunspaceIfNeededAndDoWork(Runspace rsToUse, Boolean isSync)
    2017-07-24T22:10:49.9019653Z at System.Management.Automation.PowerShell.CoreInvokeHelper[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)
    2017-07-24T22:10:49.9019653Z at System.Management.Automation.PowerShell.CoreInvoke[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)
    2017-07-24T22:10:49.9019653Z at Microsoft.TeamFoundation.DistributedTask.Handlers.LegacyVSTSPowerShellHost.VSTSPowerShellHost.Main(String[] args)
    2017-07-24T22:10:51.4350652Z ##[error]LegacyVSTSPowerShellHost.exe completed with return code: -1.

    I removed the GO statements as in the previous post but it did not solve my problem. This is very frustrating as it is preventing our release from executing.
    · Share on Twitter
  • rbigbierbigbie Posts: 16 Bronze 2
    It seems like this is only an issue when the ReadyRoll is executed as part of the VSTS Release. Almost as if sqlcmd.exe handles it differently during the release. I took all our migration scripts and ran them in order manually using SSMS against the database and it all executed correctly, but during the VSTS Release using ReadyRoll it failed thinking the IDENTITY_INSERT is set to OFF when there was a command to set IDENTITY_INSERT is set to ON. My only option I can think of at this point to try to resolve this would be to remove my migration scripts around the Products table changes and perform all the changes over again and try to get ReadyRoll to generate a script with all the changes at once. Haven't done this yet, because I didn't want to lose anything that we would need to investigate solving this problem the normal way. Recommendations?
    · Share on Twitter
  • rbigbierbigbie Posts: 16 Bronze 2
    I have more information about this. This is the SQL that is inside the [DatabaseName]_Package.sql script:

    ***** EXECUTING MIGRATION "Migrations\0006_Add_Identity_And_PK_To_Products.sql", ID: {02805588-769b-4448-8b22-4ce3445b9c0a} *****';

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('
    PRINT N''Rebuilding [dbo].[Products]''
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('CREATE TABLE [dbo].[RG_Recovery_1_Products]
    (
    [Id] [int] NOT NULL IDENTITY(1, 1),
    [ProductCode] [nchar] (10) NOT NULL,
    [ProductName] [nchar] (10) NOT NULL
    )
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('SET IDENTITY_INSERT [dbo].[RG_Recovery_1_Products] ON
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('INSERT INTO [dbo].[RG_Recovery_1_Products]([Id], [ProductCode], [ProductName]) SELECT [Id], [ProductCode], [ProductName] FROM [dbo].[Products]
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('SET IDENTITY_INSERT [dbo].[RG_Recovery_1_Products] OFF
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('DROP TABLE [dbo].[Products]
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('EXEC sp_rename N''[dbo].[RG_Recovery_1_Products]'', N''Products'', N''OBJECT''
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('PRINT N''Creating primary key [PK_Products] on [dbo].[Products]''
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([Id])
    ');

    This is the modification I had to make to the [DatabaseName]_Package.sql script in order to get it to work, I had to put the IDENTITY_INSERT statements inside the EXECUTE of the INSERT...SELECT statement:

    ***** EXECUTING MIGRATION "Migrations\0006_Add_Identity_And_PK_To_Products.sql", ID: {02805588-769b-4448-8b22-4ce3445b9c0a} *****';

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('
    PRINT N''Rebuilding [dbo].[Products]''
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('CREATE TABLE [dbo].[RG_Recovery_1_Products]
    (
    [Id] [int] NOT NULL IDENTITY(1, 1),
    [ProductCode] [nchar] (10) NOT NULL,
    [ProductName] [nchar] (10) NOT NULL
    )
    ');

    GO
    --IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    --EXECUTE ('SET IDENTITY_INSERT [dbo].[RG_Recovery_1_Products] ON');

    GO
    --IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('SET IDENTITY_INSERT [dbo].[RG_Recovery_1_Products] ON; INSERT INTO [dbo].[RG_Recovery_1_Products]([Id], [ProductCode], [ProductName]) SELECT [Id], [ProductCode], [ProductName] FROM [dbo].[Products]; SET IDENTITY_INSERT [dbo].[RG_Recovery_1_Products] OFF');

    GO
    --IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    --EXECUTE ('SET IDENTITY_INSERT [dbo].[RG_Recovery_1_Products] OFF');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('DROP TABLE [dbo].[Products]
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('EXEC sp_rename N''[dbo].[RG_Recovery_1_Products]'', N''Products'', N''OBJECT''
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('PRINT N''Creating primary key [PK_Products] on [dbo].[Products]''
    ');

    GO
    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('ALTER TABLE [dbo].[Products] ADD CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([Id])
    ');

    This raises some questions...Why is EXECUTE being used? Why does it appear that the IDENTITY_INSERT command seem to be out of session of the INSERT...SELECT statement when using the EXECUTE commands that ReadyRoll generated as part of the build? Is there a way to tell ReadyRoll during the build to not use EXECUTE statements in the [DatabaseName]_Package.sql script?

    You can see how this is a problem, having us change everywhere in the script where IDENTITY_INSERT command is being used defeats the purpose of us using ReadyRoll to generate the [DatabaseName]_Package.sql script. Any word from your developers as to how this can be solved? Thank you.
    · Share on Twitter
  • rbigbierbigbie Posts: 16 Bronze 2
    It appears we need to do it all as one EXECUTE command (for the IDENTITY_INSERT ON...INSERT...SELECT...IDENTITY_INSERT OFF). Every EXECUTE is its own batch, so the setting gets reset as soon as the batch ends. Can someone at RedGate confirm this? Doesn't this mean that by using EXECUTE in the [DatabaseName]_Package.sql script which is built during build will fail for us anytime IDENTITY_INSERT is being used? Is this happening to others and if so what are people doing to resolve it without having to modify the [DatabaseName]_Package.sql script every time is is regenerated during build? Thank you for looking into this.
    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi Rbigbie,

    This is an issue we're aware of - as stated above post Daniel Nolan created a bug for it in ReadyRoll and there is ongoing work on fixing the issue within ReadyRoll itself but there hasn't been a release to fix it as of yet.

    The workaround at this point is to go in to the Visual Studio project folder and edit the migration script directly (e.g. \\Projects\DatabaseProj1\DatabaseProj1\Migrations\002_20170728-1122_alex.bartley.sql) to remove the two GO statements - one after the initial SET IDENTITY INSERT ON​ and one before the final SET IDENTITY INSERT OFF​ .

    Doing this means the change only needs to occur once and will then be deployed properly each time in the packages.

    The deployment run in VS against the Shadow DB uses a different mechanism than the deployments - that's why this works initially for the developer but fails afterwards on deployment.

    I hope this helps you get up and running and I'll post here when there is a fix available as well!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • rbigbierbigbie Posts: 16 Bronze 2
    Thank you for the work around. The work around works and I am glad that the work around is a one time change after ReadyRoll generates the script. By removing the two GO statements you pointed out the resulting generated SQL (see below) during build looks like this now, which is what I would have expected. And it doesn't error out now. Thank you so much!

    IF NOT EXISTS (SELECT 1 FROM [$(DatabaseName)].[dbo].[__MigrationLogCurrent] WHERE [migration_id] = CAST ('02805588-769b-4448-8b22-4ce3445b9c0a' AS UNIQUEIDENTIFIER))
    EXECUTE ('SET IDENTITY_INSERT [dbo].[RG_Recovery_1_Products] ON
    INSERT INTO [dbo].[RG_Recovery_1_Products]([Id], [ProductCode], [ProductName]) SELECT [Id], [ProductCode], [ProductName] FROM [dbo].[Products]
    SET IDENTITY_INSERT [dbo].[RG_Recovery_1_Products] OFF
    ');

    GO

    And yes, we are very anxious to find out our hear about the announcement when this gets fixed. Thanks again!
    · Share on Twitter
  • dnlnlndnlnln Posts: 234 Gold 2
    Hi,

    A quick update on this issue: we just released a new version of ReadyRoll that should resolve the SET IDENTITY_INSERT issue permanently.

    However, for the moment, any existing projects will need to "opt in" to receive the fix. After updating your ReadyRoll client and build server (including the ReadyRoll.MSBuild NuGet package, if that applies to your project), make the following addition to your <ProjectName>.sqlproj file:
    <PropertyGroup>  
 <PackageScriptBatchExecutionStrategy>WrapInExecuteWithSingleSetNoExec</PackageScriptBatchExecutionStrategy>
</PropertyGroup>
    Please note that any projects you create after installing ReadyRoll 1.14.10 will automatically have this change in place, so no manual edits for new projects will be required from that point onwards.
    Daniel Nolan
    Product Manager
    Redgate Software
    · Share on Twitter
  • rbigbierbigbie Posts: 16 Bronze 2
    Awesome work guys! Great support!
    · Share on Twitter
Sign In or Register to comment.