Maintenance Plan Wizard generates invalid script?
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
edited Mar 13 '16 at 14:44
Paul White♦
50.2k14267431
asked Feb 22 '16 at 13:13
Álvaro GonzálezÁlvaro González
5672926
add a comment |
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
edited Mar 13 '16 at 14:44
Paul White♦
50.2k14267431
asked Feb 22 '16 at 13:13
Álvaro GonzálezÁlvaro González
5672926
add a comment |
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
edited Mar 13 '16 at 14:44
Paul White♦
50.2k14267431
asked Feb 22 '16 at 13:13
Álvaro GonzálezÁlvaro González
5672926
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
sql-server sql-server-2014 ssms maintenance-plans
edited Mar 13 '16 at 14:44
Paul White♦
50.2k14267431
asked Feb 22 '16 at 13:13
Álvaro GonzálezÁlvaro González
5672926
edited Mar 13 '16 at 14:44
Paul White♦
50.2k14267431
asked Feb 22 '16 at 13:13
Álvaro GonzálezÁlvaro González
5672926
edited Mar 13 '16 at 14:44
Paul White♦
50.2k14267431
edited Mar 13 '16 at 14:44
Paul White♦
50.2k14267431
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
asked Feb 22 '16 at 13:13
Álvaro GonzálezÁlvaro González
5672926
asked Feb 22 '16 at 13:13
Álvaro GonzálezÁlvaro González
5672926
5672926
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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:
File Type = 0 for backup files or 1 for report files.
Folder Path = The folder to delete files. The path must end with a backslash "".
File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
Date = The cutoff date for what files need to be deleted.
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:
answered Feb 29 '16 at 9:11
Álvaro GonzálezÁlvaro González
5672926
Thank you Alvaro, thats the point what i was looking for!
– ℛɑƒæĿ
Jan 10 '18 at 14:17
add a comment |
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?
answered 3 mins ago
Graeme ThomsonGraeme Thomson
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.
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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:
File Type = 0 for backup files or 1 for report files.
Folder Path = The folder to delete files. The path must end with a backslash "".
File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
Date = The cutoff date for what files need to be deleted.
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:
answered Feb 29 '16 at 9:11
Álvaro GonzálezÁlvaro González
5672926
Thank you Alvaro, thats the point what i was looking for!
– ℛɑƒæĿ
Jan 10 '18 at 14:17
add a comment |
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:
File Type = 0 for backup files or 1 for report files.
Folder Path = The folder to delete files. The path must end with a backslash "".
File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
Date = The cutoff date for what files need to be deleted.
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:
answered Feb 29 '16 at 9:11
Álvaro GonzálezÁlvaro González
5672926
Thank you Alvaro, thats the point what i was looking for!
– ℛɑƒæĿ
Jan 10 '18 at 14:17
add a comment |
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:
File Type = 0 for backup files or 1 for report files.
Folder Path = The folder to delete files. The path must end with a backslash "".
File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
Date = The cutoff date for what files need to be deleted.
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:
answered Feb 29 '16 at 9:11
Álvaro GonzálezÁlvaro González
5672926
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:
File Type = 0 for backup files or 1 for report files.
Folder Path = The folder to delete files. The path must end with a backslash "".
File Extension = This could be 'BAK' or 'TRN' or whatever you normally use.
Date = The cutoff date for what files need to be deleted.
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:
answered Feb 29 '16 at 9:11
Álvaro GonzálezÁlvaro González
5672926
answered Feb 29 '16 at 9:11
Álvaro GonzálezÁlvaro González
5672926
answered Feb 29 '16 at 9:11
Álvaro GonzálezÁlvaro González
5672926
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
add a comment |
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
add a comment |
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?
answered 3 mins ago
Graeme ThomsonGraeme Thomson
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.
add a comment |
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?
answered 3 mins ago
Graeme ThomsonGraeme Thomson
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.
add a comment |
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?
answered 3 mins ago
Graeme ThomsonGraeme Thomson
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.
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?
answered 3 mins ago
Graeme ThomsonGraeme Thomson
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.
answered 3 mins ago
Graeme ThomsonGraeme Thomson
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.
answered 3 mins ago
Graeme ThomsonGraeme Thomson
1
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.
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
