Can't stop execution of “ADD PERIOD”












2














I'm trying to conditionally execute an ALTER TABLE command (this one, to be precise). However, I can't get the ADD PERIOD to be conditional and I suspect this might be a bug.



Here's what I've tried to skip execution if the PERIOD already exists on the table. All of these raise the same error:




Msg 13597, Level 16, State 2, Line xx



Temporal SYSTEM_TIME period is already defined on table 'msdb.dbo.sysjobhistory'.






  1. Only add the PERIOD if it doesn't currently exist:



    if not exists(select 1 from sys.periods where object_id = OBJECT_ID('sysjobhistory'))
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)


    This raised the error.




  2. Since the above didn't work, I wanted to make sure my IF statement was evaluating correctly so I tried:



    if 1=2
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)


    But that raised the error as well.




  3. Use SET NOEXEC ON to stop execution of the code:



    set noexec ON
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
    set noexec OFF


    Didn't work. The ALTER TABLE still raised the error.




  4. Admit defeat and wrap it in a TRY/CATCH and just ignore the error:



    begin try
    ALTER TABLE [dbo].[sysjobhistory]
    ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
    end try
    begin catch
    end catch


    This raised the error too -- which I didn't expect since it was a level 16 and, according to the docs, a level 16 should go to the CATCH block.




Is this a bug in Azure SQL Server? This is running on a Managed Instance. The @@VERSION reports:




Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 2 2018 21:17:06 Copyright (C) 2018 Microsoft Corporation




UPDATE:



I can use a similar technique to skip adding columns that already exist without any issues:



if not exists(select 1 from sys.columns where name = 'StartTime' and object_id = OBJECT_ID('sysjobhistory'))
ALTER TABLE [dbo].[sysjobhistory]
ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000')
GO


The above code only executes if the sysjobhistory does not contain the column StartTime. If it already does, the code does nothing and continues executing. The ALTER TABLE with ADD PERIOD always causes an execution error even if the code is inside an IF condition that evaluates to false.










share|improve this question





























    2














    I'm trying to conditionally execute an ALTER TABLE command (this one, to be precise). However, I can't get the ADD PERIOD to be conditional and I suspect this might be a bug.



    Here's what I've tried to skip execution if the PERIOD already exists on the table. All of these raise the same error:




    Msg 13597, Level 16, State 2, Line xx



    Temporal SYSTEM_TIME period is already defined on table 'msdb.dbo.sysjobhistory'.






    1. Only add the PERIOD if it doesn't currently exist:



      if not exists(select 1 from sys.periods where object_id = OBJECT_ID('sysjobhistory'))
      ALTER TABLE [dbo].[sysjobhistory]
      ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)


      This raised the error.




    2. Since the above didn't work, I wanted to make sure my IF statement was evaluating correctly so I tried:



      if 1=2
      ALTER TABLE [dbo].[sysjobhistory]
      ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)


      But that raised the error as well.




    3. Use SET NOEXEC ON to stop execution of the code:



      set noexec ON
      ALTER TABLE [dbo].[sysjobhistory]
      ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
      set noexec OFF


      Didn't work. The ALTER TABLE still raised the error.




    4. Admit defeat and wrap it in a TRY/CATCH and just ignore the error:



      begin try
      ALTER TABLE [dbo].[sysjobhistory]
      ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
      end try
      begin catch
      end catch


      This raised the error too -- which I didn't expect since it was a level 16 and, according to the docs, a level 16 should go to the CATCH block.




    Is this a bug in Azure SQL Server? This is running on a Managed Instance. The @@VERSION reports:




    Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 2 2018 21:17:06 Copyright (C) 2018 Microsoft Corporation




    UPDATE:



    I can use a similar technique to skip adding columns that already exist without any issues:



    if not exists(select 1 from sys.columns where name = 'StartTime' and object_id = OBJECT_ID('sysjobhistory'))
    ALTER TABLE [dbo].[sysjobhistory]
    ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000')
    GO


    The above code only executes if the sysjobhistory does not contain the column StartTime. If it already does, the code does nothing and continues executing. The ALTER TABLE with ADD PERIOD always causes an execution error even if the code is inside an IF condition that evaluates to false.










    share|improve this question



























      2












      2








      2







      I'm trying to conditionally execute an ALTER TABLE command (this one, to be precise). However, I can't get the ADD PERIOD to be conditional and I suspect this might be a bug.



      Here's what I've tried to skip execution if the PERIOD already exists on the table. All of these raise the same error:




      Msg 13597, Level 16, State 2, Line xx



      Temporal SYSTEM_TIME period is already defined on table 'msdb.dbo.sysjobhistory'.






      1. Only add the PERIOD if it doesn't currently exist:



        if not exists(select 1 from sys.periods where object_id = OBJECT_ID('sysjobhistory'))
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)


        This raised the error.




      2. Since the above didn't work, I wanted to make sure my IF statement was evaluating correctly so I tried:



        if 1=2
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)


        But that raised the error as well.




      3. Use SET NOEXEC ON to stop execution of the code:



        set noexec ON
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
        set noexec OFF


        Didn't work. The ALTER TABLE still raised the error.




      4. Admit defeat and wrap it in a TRY/CATCH and just ignore the error:



        begin try
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
        end try
        begin catch
        end catch


        This raised the error too -- which I didn't expect since it was a level 16 and, according to the docs, a level 16 should go to the CATCH block.




      Is this a bug in Azure SQL Server? This is running on a Managed Instance. The @@VERSION reports:




      Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 2 2018 21:17:06 Copyright (C) 2018 Microsoft Corporation




      UPDATE:



      I can use a similar technique to skip adding columns that already exist without any issues:



      if not exists(select 1 from sys.columns where name = 'StartTime' and object_id = OBJECT_ID('sysjobhistory'))
      ALTER TABLE [dbo].[sysjobhistory]
      ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000')
      GO


      The above code only executes if the sysjobhistory does not contain the column StartTime. If it already does, the code does nothing and continues executing. The ALTER TABLE with ADD PERIOD always causes an execution error even if the code is inside an IF condition that evaluates to false.










      share|improve this question















      I'm trying to conditionally execute an ALTER TABLE command (this one, to be precise). However, I can't get the ADD PERIOD to be conditional and I suspect this might be a bug.



      Here's what I've tried to skip execution if the PERIOD already exists on the table. All of these raise the same error:




      Msg 13597, Level 16, State 2, Line xx



      Temporal SYSTEM_TIME period is already defined on table 'msdb.dbo.sysjobhistory'.






      1. Only add the PERIOD if it doesn't currently exist:



        if not exists(select 1 from sys.periods where object_id = OBJECT_ID('sysjobhistory'))
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)


        This raised the error.




      2. Since the above didn't work, I wanted to make sure my IF statement was evaluating correctly so I tried:



        if 1=2
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)


        But that raised the error as well.




      3. Use SET NOEXEC ON to stop execution of the code:



        set noexec ON
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
        set noexec OFF


        Didn't work. The ALTER TABLE still raised the error.




      4. Admit defeat and wrap it in a TRY/CATCH and just ignore the error:



        begin try
        ALTER TABLE [dbo].[sysjobhistory]
        ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
        end try
        begin catch
        end catch


        This raised the error too -- which I didn't expect since it was a level 16 and, according to the docs, a level 16 should go to the CATCH block.




      Is this a bug in Azure SQL Server? This is running on a Managed Instance. The @@VERSION reports:




      Microsoft SQL Azure (RTM) - 12.0.2000.8 Nov 2 2018 21:17:06 Copyright (C) 2018 Microsoft Corporation




      UPDATE:



      I can use a similar technique to skip adding columns that already exist without any issues:



      if not exists(select 1 from sys.columns where name = 'StartTime' and object_id = OBJECT_ID('sysjobhistory'))
      ALTER TABLE [dbo].[sysjobhistory]
      ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000')
      GO


      The above code only executes if the sysjobhistory does not contain the column StartTime. If it already does, the code does nothing and continues executing. The ALTER TABLE with ADD PERIOD always causes an execution error even if the code is inside an IF condition that evaluates to false.







      sql-server azure-sql-database






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 5 hours ago

























      asked 7 hours ago









      PatrickSteele

      1155




      1155






















          1 Answer
          1






          active

          oldest

          votes


















          3














          You need dynamic SQL for DDL like this.



          IF NOT EXISTS(SELECT 1 FROM sys.periods WHERE object_id = OBJECT_ID('sysjobhistory'))
          DECLARE @sql NVARCHAR(MAX) = N''
          SET @sql += N'
          ALTER TABLE dbo.sysjobhistory
          ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime);'

          EXEC sys.sp_executesql @sql





          share|improve this answer

















          • 1




            @PatrickSteele ADD COLUMN and ADD CONSTRAINT appear to get deferred compilation whereas ADD PERIOD doesn't. I don't find any documentation stating this behaviour though for any of them. I guess technically not a bug as it isnt behaving differently from how it is documented to work.
            – Martin Smith
            3 hours ago











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226186%2fcant-stop-execution-of-add-period%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          3














          You need dynamic SQL for DDL like this.



          IF NOT EXISTS(SELECT 1 FROM sys.periods WHERE object_id = OBJECT_ID('sysjobhistory'))
          DECLARE @sql NVARCHAR(MAX) = N''
          SET @sql += N'
          ALTER TABLE dbo.sysjobhistory
          ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime);'

          EXEC sys.sp_executesql @sql





          share|improve this answer

















          • 1




            @PatrickSteele ADD COLUMN and ADD CONSTRAINT appear to get deferred compilation whereas ADD PERIOD doesn't. I don't find any documentation stating this behaviour though for any of them. I guess technically not a bug as it isnt behaving differently from how it is documented to work.
            – Martin Smith
            3 hours ago
















          3














          You need dynamic SQL for DDL like this.



          IF NOT EXISTS(SELECT 1 FROM sys.periods WHERE object_id = OBJECT_ID('sysjobhistory'))
          DECLARE @sql NVARCHAR(MAX) = N''
          SET @sql += N'
          ALTER TABLE dbo.sysjobhistory
          ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime);'

          EXEC sys.sp_executesql @sql





          share|improve this answer

















          • 1




            @PatrickSteele ADD COLUMN and ADD CONSTRAINT appear to get deferred compilation whereas ADD PERIOD doesn't. I don't find any documentation stating this behaviour though for any of them. I guess technically not a bug as it isnt behaving differently from how it is documented to work.
            – Martin Smith
            3 hours ago














          3












          3








          3






          You need dynamic SQL for DDL like this.



          IF NOT EXISTS(SELECT 1 FROM sys.periods WHERE object_id = OBJECT_ID('sysjobhistory'))
          DECLARE @sql NVARCHAR(MAX) = N''
          SET @sql += N'
          ALTER TABLE dbo.sysjobhistory
          ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime);'

          EXEC sys.sp_executesql @sql





          share|improve this answer












          You need dynamic SQL for DDL like this.



          IF NOT EXISTS(SELECT 1 FROM sys.periods WHERE object_id = OBJECT_ID('sysjobhistory'))
          DECLARE @sql NVARCHAR(MAX) = N''
          SET @sql += N'
          ALTER TABLE dbo.sysjobhistory
          ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime);'

          EXEC sys.sp_executesql @sql






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered 7 hours ago









          Erik Darling

          21k1263103




          21k1263103








          • 1




            @PatrickSteele ADD COLUMN and ADD CONSTRAINT appear to get deferred compilation whereas ADD PERIOD doesn't. I don't find any documentation stating this behaviour though for any of them. I guess technically not a bug as it isnt behaving differently from how it is documented to work.
            – Martin Smith
            3 hours ago














          • 1




            @PatrickSteele ADD COLUMN and ADD CONSTRAINT appear to get deferred compilation whereas ADD PERIOD doesn't. I don't find any documentation stating this behaviour though for any of them. I guess technically not a bug as it isnt behaving differently from how it is documented to work.
            – Martin Smith
            3 hours ago








          1




          1




          @PatrickSteele ADD COLUMN and ADD CONSTRAINT appear to get deferred compilation whereas ADD PERIOD doesn't. I don't find any documentation stating this behaviour though for any of them. I guess technically not a bug as it isnt behaving differently from how it is documented to work.
          – Martin Smith
          3 hours ago




          @PatrickSteele ADD COLUMN and ADD CONSTRAINT appear to get deferred compilation whereas ADD PERIOD doesn't. I don't find any documentation stating this behaviour though for any of them. I guess technically not a bug as it isnt behaving differently from how it is documented to work.
          – Martin Smith
          3 hours ago


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.





          Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


          Please pay close attention to the following guidance:


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226186%2fcant-stop-execution-of-add-period%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

          Calculate evaluation metrics using cross_val_predict sklearn

          Insert data from modal to MySQL (multiple modal on website)