Re: How to capture SQL statement messages in DTS log file
- From: Ellen <DBAEllen@xxxxxxxxxxxxxxxx>
- Date: Tue, 18 Sep 2007 08:46:08 -0700
I also thought that should do it but it does not.
The only thing written to the log is in the text I included earlier.
I've tried print statements and select statements and I've had the log at
the package level and at the step level with the same output showing up. I
am hoping someone knows what else needs to be done to get the PRINT
statements to write to the log.
Thanks for trying.
Ellen
--
Thank you for sharing your knowledge
"Russell Fields" wrote:
Ellen,.
In the SQL Agent Job properties, go to the step properties for the step that
runs your DTS package. In the Advanced tab on the Job Step you can specify
either an Output File or to Log to Table. (The table is
msdb.dbo.sysjobstepslogs.)
Either approach should capture your console output through the PRINT
statements.
RLF
"Ellen" <DBAEllen@xxxxxxxxxxxxxxxx> wrote in message
news:3E08CDBC-E2FE-43BD-B74D-06E24BE05A75@xxxxxxxxxxxxxxxx
I appreciate your answer; but the issue I need to address is that this
"job"
is a DTS package that is run through the job scheduler. The only log I am
getting does not "capture" the print message. Is there a way to capture
that
print message in a log for a DTS package step. Adding the code below [or
similar code] will run without error but the log file only has the output
below. Is there a statement I need to set at the beginning of the SQL
statements like 'echo on' or something?
Thank you for your assistance.
Ellen
-------------- the log ------------------------------------
The execution of the following DTS Package succeeded:
Package Name: Update Statistics on DYNAMICS
Package Description: Update statistics for DYNAMICS User Tables
Package ID: {46569BFF-FD34-42C1-AFE9-74E4E4A0EF82}
Package Version: {1888A3F0-140B-4751-B545-A0D4B7209298}
Package Execution Lineage: {46683000-1F33-4071-A82B-91DBFEA2A652}
Executed On: SQLDY01
Executed By: ellen
Execution Started: 9/18/2007 9:55:38 AM
Execution Completed: 9/18/2007 9:55:38 AM
Total Execution Time: 0.172 seconds
Package Steps execution information:
Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 9/18/2007 9:55:38 AM
Step Execution Completed: 9/18/2007 9:55:38 AM
Total Step Execution Time: 0.109 seconds
Progress count in Step: 0
--
Thank you for sharing your knowledge
""Peter YangMSFT]"" wrote:
Hello Ellen,
Actually, when server run the command 'update statistics <tablename>'
successfully, it doesn't return anything.
You could see "Command(s) completed successfully" message in Query
Analyzer
but you shall not see this from other applications such as OSQL etc
because
the message is actaully generated by QA itself.
You could check "@@error" variable for the success of each command if you
don't get any errors. You can view the text associated with an @@ERROR
error number in sysmessages system table. For example, you could use the
following script to do check the error of update statitics command.
=====
declare @tablename varchar(50)
select @tablename = 'tbl1'
declare @query as varchar(200)
select @query= 'update statistics ' + @tablename
exec (@query)
IF @@ERROR <> 0
BEGIN
PRINT N'An error occurred while updateing statistics of table '+
@tablename;
RETURN 99;
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT N'update statistics succeeds for table '+ @tablename;
RETURN 0;
END;
If you have any further questions or comments on this issue, please feel
free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each
follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
- Follow-Ups:
- Re: How to capture SQL statement messages in DTS log file
- From: Russell Fields
- Re: How to capture SQL statement messages in DTS log file
- References:
- RE: How to capture SQL statement messages in DTS log file
- From: "Peter YangMSFT]"
- RE: How to capture SQL statement messages in DTS log file
- From: Ellen
- Re: How to capture SQL statement messages in DTS log file
- From: Russell Fields
- RE: How to capture SQL statement messages in DTS log file
- Prev by Date: Re: violation of a primary key
- Next by Date: Re: How to capture SQL statement messages in DTS log file
- Previous by thread: Re: How to capture SQL statement messages in DTS log file
- Next by thread: Re: How to capture SQL statement messages in DTS log file
- Index(es):
Relevant Pages
|
