Include stored procedure text output in the maintenance plan log
I have a maintenance plan subtask in SQL Server 2012 Enterprise that executes a stored procedure (or a SQL Server agent job).
In the log file for this maintenance plan, there are a few lines for this task, such as the start- and end- date, and the SQL statement used to execute the stored procedure.
Can I somehow generate text output in the stored procedure and have it included in the log?
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
sql-server sql-server-2012 maintenance-plans debugging
edited Mar 6 '17 at 12:55
Paul White♦
49.4k14260414
asked Mar 6 '17 at 12:18
HugoRuneHugoRune
236212
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a maintenance plan subtask in SQL Server 2012 Enterprise that executes a stored procedure (or a SQL Server agent job).
In the log file for this maintenance plan, there are a few lines for this task, such as the start- and end- date, and the SQL statement used to execute the stored procedure.
Can I somehow generate text output in the stored procedure and have it included in the log?
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
sql-server sql-server-2012 maintenance-plans debugging
edited Mar 6 '17 at 12:55
Paul White♦
49.4k14260414
asked Mar 6 '17 at 12:18
HugoRuneHugoRune
236212
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
SQL jobs can be configured to capture the output of the SQL it runs; specifically, this is not recordset output, but messages (PRINT statements, for example). Note that large amounts of output may be truncated. To guarantee full output, you need to set options that would result in the data being written to a file, or to a table, which you're trying to avoid.
– RDFozz
Mar 6 '17 at 15:21
I am currently experimenting with a SQL agent job encapsulating the stored procedure, which writes to a file with a similar name as the regular maintenance task job, using RAISERROR. Truncation is not a problem as the data is moderately small.
– HugoRune
Mar 6 '17 at 16:51
But a major problem is that if I execute the job from the maintenance plan, it will just start the job in the background and continue with the next maintenance plan step, without checking whether the job completed successfully. There are some very ugly workarounds for this issue, which I do not yet understand, mentioned here: stackoverflow.com/questions/12249056/…
– HugoRune
Mar 6 '17 at 16:52
Sometimes, the old ways can be the easiest to follow. Consider creating an empty text file before you start your job, then deleting it as the last step in your job. Then, after you start the job, wait until the file no longer exists. You can get additional flexibility by creating a second file if the job fails, so you know that happened. NOTE: I'd still dig into the other solutions proposed, and probably switch to them once you get them, as they cover more situations.
– RDFozz
Mar 6 '17 at 17:15
add a comment |
I have a maintenance plan subtask in SQL Server 2012 Enterprise that executes a stored procedure (or a SQL Server agent job).
In the log file for this maintenance plan, there are a few lines for this task, such as the start- and end- date, and the SQL statement used to execute the stored procedure.
Can I somehow generate text output in the stored procedure and have it included in the log?
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
sql-server sql-server-2012 maintenance-plans debugging
edited Mar 6 '17 at 12:55
Paul White♦
49.4k14260414
asked Mar 6 '17 at 12:18
HugoRuneHugoRune
236212
I have a maintenance plan subtask in SQL Server 2012 Enterprise that executes a stored procedure (or a SQL Server agent job).
In the log file for this maintenance plan, there are a few lines for this task, such as the start- and end- date, and the SQL statement used to execute the stored procedure.
Can I somehow generate text output in the stored procedure and have it included in the log?
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
sql-server sql-server-2012 maintenance-plans debugging
sql-server sql-server-2012 maintenance-plans debugging
edited Mar 6 '17 at 12:55
Paul White♦
49.4k14260414
asked Mar 6 '17 at 12:18
HugoRuneHugoRune
236212
edited Mar 6 '17 at 12:55
Paul White♦
49.4k14260414
asked Mar 6 '17 at 12:18
HugoRuneHugoRune
236212
edited Mar 6 '17 at 12:55
Paul White♦
49.4k14260414
edited Mar 6 '17 at 12:55
Paul White♦
49.4k14260414
edited Mar 6 '17 at 12:55
Paul White♦
49.4k14260414
49.4k14260414
asked Mar 6 '17 at 12:18
HugoRuneHugoRune
236212
asked Mar 6 '17 at 12:18
HugoRuneHugoRune
236212
asked Mar 6 '17 at 12:18
HugoRuneHugoRune
236212
236212
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
SQL jobs can be configured to capture the output of the SQL it runs; specifically, this is not recordset output, but messages (PRINT statements, for example). Note that large amounts of output may be truncated. To guarantee full output, you need to set options that would result in the data being written to a file, or to a table, which you're trying to avoid.
– RDFozz
Mar 6 '17 at 15:21
I am currently experimenting with a SQL agent job encapsulating the stored procedure, which writes to a file with a similar name as the regular maintenance task job, using RAISERROR. Truncation is not a problem as the data is moderately small.
– HugoRune
Mar 6 '17 at 16:51
But a major problem is that if I execute the job from the maintenance plan, it will just start the job in the background and continue with the next maintenance plan step, without checking whether the job completed successfully. There are some very ugly workarounds for this issue, which I do not yet understand, mentioned here: stackoverflow.com/questions/12249056/…
– HugoRune
Mar 6 '17 at 16:52
Sometimes, the old ways can be the easiest to follow. Consider creating an empty text file before you start your job, then deleting it as the last step in your job. Then, after you start the job, wait until the file no longer exists. You can get additional flexibility by creating a second file if the job fails, so you know that happened. NOTE: I'd still dig into the other solutions proposed, and probably switch to them once you get them, as they cover more situations.
– RDFozz
Mar 6 '17 at 17:15
add a comment |
SQL jobs can be configured to capture the output of the SQL it runs; specifically, this is not recordset output, but messages (PRINT statements, for example). Note that large amounts of output may be truncated. To guarantee full output, you need to set options that would result in the data being written to a file, or to a table, which you're trying to avoid.
– RDFozz
Mar 6 '17 at 15:21
I am currently experimenting with a SQL agent job encapsulating the stored procedure, which writes to a file with a similar name as the regular maintenance task job, using RAISERROR. Truncation is not a problem as the data is moderately small.
– HugoRune
Mar 6 '17 at 16:51
But a major problem is that if I execute the job from the maintenance plan, it will just start the job in the background and continue with the next maintenance plan step, without checking whether the job completed successfully. There are some very ugly workarounds for this issue, which I do not yet understand, mentioned here: stackoverflow.com/questions/12249056/…
– HugoRune
Mar 6 '17 at 16:52
Sometimes, the old ways can be the easiest to follow. Consider creating an empty text file before you start your job, then deleting it as the last step in your job. Then, after you start the job, wait until the file no longer exists. You can get additional flexibility by creating a second file if the job fails, so you know that happened. NOTE: I'd still dig into the other solutions proposed, and probably switch to them once you get them, as they cover more situations.
– RDFozz
Mar 6 '17 at 17:15
SQL jobs can be configured to capture the output of the SQL it runs; specifically, this is not recordset output, but messages (PRINT statements, for example). Note that large amounts of output may be truncated. To guarantee full output, you need to set options that would result in the data being written to a file, or to a table, which you're trying to avoid.
– RDFozz
Mar 6 '17 at 15:21
SQL jobs can be configured to capture the output of the SQL it runs; specifically, this is not recordset output, but messages (PRINT statements, for example). Note that large amounts of output may be truncated. To guarantee full output, you need to set options that would result in the data being written to a file, or to a table, which you're trying to avoid.
– RDFozz
Mar 6 '17 at 15:21
I am currently experimenting with a SQL agent job encapsulating the stored procedure, which writes to a file with a similar name as the regular maintenance task job, using RAISERROR. Truncation is not a problem as the data is moderately small.
– HugoRune
Mar 6 '17 at 16:51
I am currently experimenting with a SQL agent job encapsulating the stored procedure, which writes to a file with a similar name as the regular maintenance task job, using RAISERROR. Truncation is not a problem as the data is moderately small.
– HugoRune
Mar 6 '17 at 16:51
But a major problem is that if I execute the job from the maintenance plan, it will just start the job in the background and continue with the next maintenance plan step, without checking whether the job completed successfully. There are some very ugly workarounds for this issue, which I do not yet understand, mentioned here: stackoverflow.com/questions/12249056/…
– HugoRune
Mar 6 '17 at 16:52
But a major problem is that if I execute the job from the maintenance plan, it will just start the job in the background and continue with the next maintenance plan step, without checking whether the job completed successfully. There are some very ugly workarounds for this issue, which I do not yet understand, mentioned here: stackoverflow.com/questions/12249056/…
– HugoRune
Mar 6 '17 at 16:52
Sometimes, the old ways can be the easiest to follow. Consider creating an empty text file before you start your job, then deleting it as the last step in your job. Then, after you start the job, wait until the file no longer exists. You can get additional flexibility by creating a second file if the job fails, so you know that happened. NOTE: I'd still dig into the other solutions proposed, and probably switch to them once you get them, as they cover more situations.
– RDFozz
Mar 6 '17 at 17:15
Sometimes, the old ways can be the easiest to follow. Consider creating an empty text file before you start your job, then deleting it as the last step in your job. Then, after you start the job, wait until the file no longer exists. You can get additional flexibility by creating a second file if the job fails, so you know that happened. NOTE: I'd still dig into the other solutions proposed, and probably switch to them once you get them, as they cover more situations.
– RDFozz
Mar 6 '17 at 17:15
add a comment |
1 Answer
1
active
oldest
votes
Does it have to be part of the log? You could just create a table and modify the stored procedure to store its output there.
answered Mar 6 '17 at 12:24
Cody KoniorCody Konior
3,274616
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
– HugoRune
Mar 6 '17 at 12:29
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%2f166319%2finclude-stored-procedure-text-output-in-the-maintenance-plan-log%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
Does it have to be part of the log? You could just create a table and modify the stored procedure to store its output there.
answered Mar 6 '17 at 12:24
Cody KoniorCody Konior
3,274616
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
– HugoRune
Mar 6 '17 at 12:29
add a comment |
Does it have to be part of the log? You could just create a table and modify the stored procedure to store its output there.
answered Mar 6 '17 at 12:24
Cody KoniorCody Konior
3,274616
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
– HugoRune
Mar 6 '17 at 12:29
add a comment |
Does it have to be part of the log? You could just create a table and modify the stored procedure to store its output there.
answered Mar 6 '17 at 12:24
Cody KoniorCody Konior
3,274616
Does it have to be part of the log? You could just create a table and modify the stored procedure to store its output there.
answered Mar 6 '17 at 12:24
Cody KoniorCody Konior
3,274616
answered Mar 6 '17 at 12:24
Cody KoniorCody Konior
3,274616
answered Mar 6 '17 at 12:24
Cody KoniorCody Konior
3,274616
answered Mar 6 '17 at 12:24
Cody KoniorCody Konior
3,274616
3,274616
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
– HugoRune
Mar 6 '17 at 12:29
add a comment |
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
– HugoRune
Mar 6 '17 at 12:29
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
– HugoRune
Mar 6 '17 at 12:29
It does not absolutely have to be part of the log file, but I would greatly prefer it that way. I would like to avoid another table which has to be separately cleaned up, backed up, documented, etc.
– HugoRune
Mar 6 '17 at 12:29
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%2f166319%2finclude-stored-procedure-text-output-in-the-maintenance-plan-log%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
SQL jobs can be configured to capture the output of the SQL it runs; specifically, this is not recordset output, but messages (PRINT statements, for example). Note that large amounts of output may be truncated. To guarantee full output, you need to set options that would result in the data being written to a file, or to a table, which you're trying to avoid.
– RDFozz
Mar 6 '17 at 15:21
I am currently experimenting with a SQL agent job encapsulating the stored procedure, which writes to a file with a similar name as the regular maintenance task job, using RAISERROR. Truncation is not a problem as the data is moderately small.
– HugoRune
Mar 6 '17 at 16:51
But a major problem is that if I execute the job from the maintenance plan, it will just start the job in the background and continue with the next maintenance plan step, without checking whether the job completed successfully. There are some very ugly workarounds for this issue, which I do not yet understand, mentioned here: stackoverflow.com/questions/12249056/…
– HugoRune
Mar 6 '17 at 16:52
Sometimes, the old ways can be the easiest to follow. Consider creating an empty text file before you start your job, then deleting it as the last step in your job. Then, after you start the job, wait until the file no longer exists. You can get additional flexibility by creating a second file if the job fails, so you know that happened. NOTE: I'd still dig into the other solutions proposed, and probably switch to them once you get them, as they cover more situations.
– RDFozz
Mar 6 '17 at 17:15