Maintenance Plan Wizard generates invalid script?












4















I've used the Maintenance Plan Wizard to create a plan. When I execute the plan, the "Maintenance Cleaup Task" fails with this error message:




Executing the query "EXECUTE master.dbo.xp_delete_file
0,N'',N'',N'2016..." failed with the following error: "Error executing
extended stored procedure: Invalid Parameter". Possible failure
reasons: Problems with the query, "ResultSet" property not set
correctly, parameters not set correctly, or connection not established
correctly.




Generated T-SQL is this (according to Log File Viewer):



EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2016-01-25T13:50:30''

GO


Please note all single quotes are doubled.



Is there a bug in log viewer or wizard is actually generating invalid T-SQL code?





Just realised that I get "Error executing extended stored procedure: Invalid Parameter" if I remove dupe quotes and run the query manually:



EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2016-01-25T13:50:30'

GO









share|improve this question





























    4















    I've used the Maintenance Plan Wizard to create a plan. When I execute the plan, the "Maintenance Cleaup Task" fails with this error message:




    Executing the query "EXECUTE master.dbo.xp_delete_file
    0,N'',N'',N'2016..." failed with the following error: "Error executing
    extended stored procedure: Invalid Parameter". Possible failure
    reasons: Problems with the query, "ResultSet" property not set
    correctly, parameters not set correctly, or connection not established
    correctly.




    Generated T-SQL is this (according to Log File Viewer):



    EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2016-01-25T13:50:30''

    GO


    Please note all single quotes are doubled.



    Is there a bug in log viewer or wizard is actually generating invalid T-SQL code?





    Just realised that I get "Error executing extended stored procedure: Invalid Parameter" if I remove dupe quotes and run the query manually:



    EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2016-01-25T13:50:30'

    GO









    share|improve this question



























      4












      4








      4


      2






      I've used the Maintenance Plan Wizard to create a plan. When I execute the plan, the "Maintenance Cleaup Task" fails with this error message:




      Executing the query "EXECUTE master.dbo.xp_delete_file
      0,N'',N'',N'2016..." failed with the following error: "Error executing
      extended stored procedure: Invalid Parameter". Possible failure
      reasons: Problems with the query, "ResultSet" property not set
      correctly, parameters not set correctly, or connection not established
      correctly.




      Generated T-SQL is this (according to Log File Viewer):



      EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2016-01-25T13:50:30''

      GO


      Please note all single quotes are doubled.



      Is there a bug in log viewer or wizard is actually generating invalid T-SQL code?





      Just realised that I get "Error executing extended stored procedure: Invalid Parameter" if I remove dupe quotes and run the query manually:



      EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2016-01-25T13:50:30'

      GO









      share|improve this question
















      I've used the Maintenance Plan Wizard to create a plan. When I execute the plan, the "Maintenance Cleaup Task" fails with this error message:




      Executing the query "EXECUTE master.dbo.xp_delete_file
      0,N'',N'',N'2016..." failed with the following error: "Error executing
      extended stored procedure: Invalid Parameter". Possible failure
      reasons: Problems with the query, "ResultSet" property not set
      correctly, parameters not set correctly, or connection not established
      correctly.




      Generated T-SQL is this (according to Log File Viewer):



      EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2016-01-25T13:50:30''

      GO


      Please note all single quotes are doubled.



      Is there a bug in log viewer or wizard is actually generating invalid T-SQL code?





      Just realised that I get "Error executing extended stored procedure: Invalid Parameter" if I remove dupe quotes and run the query manually:



      EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2016-01-25T13:50:30'

      GO






      sql-server sql-server-2014 ssms maintenance-plans






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Mar 13 '16 at 14:44









      Paul White

      50.2k14267431




      50.2k14267431










      asked Feb 22 '16 at 13:13









      Álvaro GonzálezÁlvaro González

      5672926




      5672926






















          2 Answers
          2






          active

          oldest

          votes


















          7














          The master.dbo.xp_delete_file procedure appears to be undocumented and it also seems to be a binary procedure (sp_helptext 'master.dbo.xp_delete_file' prints xpstar.dll) so we can't figure out arguments from source code. Unofficial sources suggest these are its arguments:





          1. File Type = 0 for backup files or 1 for report files.


          2. Folder Path = The folder to delete files. The path must end with a backslash "".


          3. File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.


          4. Date = The cutoff date for what files need to be deleted.


          5. Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.


          In other words, command is missing the actual path to remove. If we go to "Management/ Mainteinance Plans", right click on our plan and select "Modify" we can edit the individual tasks. In this case, the "Maintenance Cleanup Task" was missing the path (the wizard does not seem to validate whether you enter one). It should look like this:



          Maintenance Cleanup Task






          share|improve this answer
























          • Thank you Alvaro, thats the point what i was looking for!

            – ℛɑƒæĿ
            Jan 10 '18 at 14:17



















          0














          I have a very similar problem.



          In my situation the generated SQL is
          "EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2019-01-02T10:44:21''"
          but the execution returns error message
          "Msg 102, Level 15, State 1, Line 1
          Incorrect syntax near '2019'."



          It dislikes the date parameter!
          I think it is to do with the fact my server is in Australia not USA, and we view dates differently!
          Any ideas how I overcome this bug?





          share








          New contributor




          Graeme Thomson is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
          Check out our Code of Conduct.




















            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%2f130050%2fmaintenance-plan-wizard-generates-invalid-script%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            7














            The master.dbo.xp_delete_file procedure appears to be undocumented and it also seems to be a binary procedure (sp_helptext 'master.dbo.xp_delete_file' prints xpstar.dll) so we can't figure out arguments from source code. Unofficial sources suggest these are its arguments:





            1. File Type = 0 for backup files or 1 for report files.


            2. Folder Path = The folder to delete files. The path must end with a backslash "".


            3. File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.


            4. Date = The cutoff date for what files need to be deleted.


            5. Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.


            In other words, command is missing the actual path to remove. If we go to "Management/ Mainteinance Plans", right click on our plan and select "Modify" we can edit the individual tasks. In this case, the "Maintenance Cleanup Task" was missing the path (the wizard does not seem to validate whether you enter one). It should look like this:



            Maintenance Cleanup Task






            share|improve this answer
























            • Thank you Alvaro, thats the point what i was looking for!

              – ℛɑƒæĿ
              Jan 10 '18 at 14:17
















            7














            The master.dbo.xp_delete_file procedure appears to be undocumented and it also seems to be a binary procedure (sp_helptext 'master.dbo.xp_delete_file' prints xpstar.dll) so we can't figure out arguments from source code. Unofficial sources suggest these are its arguments:





            1. File Type = 0 for backup files or 1 for report files.


            2. Folder Path = The folder to delete files. The path must end with a backslash "".


            3. File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.


            4. Date = The cutoff date for what files need to be deleted.


            5. Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.


            In other words, command is missing the actual path to remove. If we go to "Management/ Mainteinance Plans", right click on our plan and select "Modify" we can edit the individual tasks. In this case, the "Maintenance Cleanup Task" was missing the path (the wizard does not seem to validate whether you enter one). It should look like this:



            Maintenance Cleanup Task






            share|improve this answer
























            • Thank you Alvaro, thats the point what i was looking for!

              – ℛɑƒæĿ
              Jan 10 '18 at 14:17














            7












            7








            7







            The master.dbo.xp_delete_file procedure appears to be undocumented and it also seems to be a binary procedure (sp_helptext 'master.dbo.xp_delete_file' prints xpstar.dll) so we can't figure out arguments from source code. Unofficial sources suggest these are its arguments:





            1. File Type = 0 for backup files or 1 for report files.


            2. Folder Path = The folder to delete files. The path must end with a backslash "".


            3. File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.


            4. Date = The cutoff date for what files need to be deleted.


            5. Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.


            In other words, command is missing the actual path to remove. If we go to "Management/ Mainteinance Plans", right click on our plan and select "Modify" we can edit the individual tasks. In this case, the "Maintenance Cleanup Task" was missing the path (the wizard does not seem to validate whether you enter one). It should look like this:



            Maintenance Cleanup Task






            share|improve this answer













            The master.dbo.xp_delete_file procedure appears to be undocumented and it also seems to be a binary procedure (sp_helptext 'master.dbo.xp_delete_file' prints xpstar.dll) so we can't figure out arguments from source code. Unofficial sources suggest these are its arguments:





            1. File Type = 0 for backup files or 1 for report files.


            2. Folder Path = The folder to delete files. The path must end with a backslash "".


            3. File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.


            4. Date = The cutoff date for what files need to be deleted.


            5. Subfolder = 0 to ignore subfolders, 1 to delete files in subfolders.


            In other words, command is missing the actual path to remove. If we go to "Management/ Mainteinance Plans", right click on our plan and select "Modify" we can edit the individual tasks. In this case, the "Maintenance Cleanup Task" was missing the path (the wizard does not seem to validate whether you enter one). It should look like this:



            Maintenance Cleanup Task







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Feb 29 '16 at 9:11









            Álvaro GonzálezÁlvaro González

            5672926




            5672926













            • Thank you Alvaro, thats the point what i was looking for!

              – ℛɑƒæĿ
              Jan 10 '18 at 14:17



















            • Thank you Alvaro, thats the point what i was looking for!

              – ℛɑƒæĿ
              Jan 10 '18 at 14:17

















            Thank you Alvaro, thats the point what i was looking for!

            – ℛɑƒæĿ
            Jan 10 '18 at 14:17





            Thank you Alvaro, thats the point what i was looking for!

            – ℛɑƒæĿ
            Jan 10 '18 at 14:17













            0














            I have a very similar problem.



            In my situation the generated SQL is
            "EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2019-01-02T10:44:21''"
            but the execution returns error message
            "Msg 102, Level 15, State 1, Line 1
            Incorrect syntax near '2019'."



            It dislikes the date parameter!
            I think it is to do with the fact my server is in Australia not USA, and we view dates differently!
            Any ideas how I overcome this bug?





            share








            New contributor




            Graeme Thomson is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
            Check out our Code of Conduct.

























              0














              I have a very similar problem.



              In my situation the generated SQL is
              "EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2019-01-02T10:44:21''"
              but the execution returns error message
              "Msg 102, Level 15, State 1, Line 1
              Incorrect syntax near '2019'."



              It dislikes the date parameter!
              I think it is to do with the fact my server is in Australia not USA, and we view dates differently!
              Any ideas how I overcome this bug?





              share








              New contributor




              Graeme Thomson is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
              Check out our Code of Conduct.























                0












                0








                0







                I have a very similar problem.



                In my situation the generated SQL is
                "EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2019-01-02T10:44:21''"
                but the execution returns error message
                "Msg 102, Level 15, State 1, Line 1
                Incorrect syntax near '2019'."



                It dislikes the date parameter!
                I think it is to do with the fact my server is in Australia not USA, and we view dates differently!
                Any ideas how I overcome this bug?





                share








                New contributor




                Graeme Thomson is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.










                I have a very similar problem.



                In my situation the generated SQL is
                "EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2019-01-02T10:44:21''"
                but the execution returns error message
                "Msg 102, Level 15, State 1, Line 1
                Incorrect syntax near '2019'."



                It dislikes the date parameter!
                I think it is to do with the fact my server is in Australia not USA, and we view dates differently!
                Any ideas how I overcome this bug?






                share








                New contributor




                Graeme Thomson is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.








                share


                share






                New contributor




                Graeme Thomson is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.









                answered 3 mins ago









                Graeme ThomsonGraeme Thomson

                1




                1




                New contributor




                Graeme Thomson is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.





                New contributor





                Graeme Thomson is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.






                Graeme Thomson is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.






























                    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.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f130050%2fmaintenance-plan-wizard-generates-invalid-script%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

                    Liste der Baudenkmale in Friedland (Mecklenburg)

                    Single-Malt-Whisky

                    Czorneboh