Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
2005 how to output messages from job

Answered 2005 how to output messages from job

  • Saturday, November 17, 2012 12:55 AM
     
     
    Sign In to Vote
    0

    I have created a job to run a few update groups. So I get many messages indicating how many rows were affected. I am using the job log to a file option but it is only capturing the messages for selects, not the updates? I want bothm how do I get it to send the messages to the job log file?

    example output:

    sql102

    1 row affected

    (6881 row(s) affected)

    (129 row(s) affected)

    (129 row(s) affected)

    In log file iget:

    sql102

    1 row affected

     

All Replies

  • Saturday, November 17, 2012 2:23 AM
     
     
    Sign In to Vote
    0

    you mean you have created the job and the output saved to file(ex :d:\out.txt)

    you mean the below outpts only for select statements, I dont think so.... those outcomes either DML operation outcome..

    Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

     
  • Saturday, November 17, 2012 5:06 AM
     
     
    Sign In to Vote
    0

    Lets say you want to include whatever you inserted that is also possible with output command which is introduced in SQL Server 2005 as below.

    INSERT TestTable (ID, TEXTVal)
    OUTPUT Inserted.ID, Inserted.TEXTVal

     
  • Saturday, November 17, 2012 5:08 AM
     
     
    Sign In to Vote
    0
    Obviously I would do this for testing purpose if you don't want to expose the data in output file then it won't be a good idea to implement this in production.
     
  • Saturday, November 17, 2012 5:20 AM
     
     
    Sign In to Vote
    0
    All I am looking for is the row counts from the updates. I don't want the data. I have a select @@servername that returns row count, but subsequent updates that would have row counts in a SSMS session do not show up in the output file for the job.
     
  • Saturday, November 17, 2012 7:23 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hallo Rick,

    I don't think its a good idea to use the log file for these parts of output. I'm wondering why not using a relation in your database. Your Proc put the output to this relation when the process starts / ends

    CREATE PROC dbo.proc_maint_Update_My_Database
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @ReturnValue int
    UPDATE dbo.mytable
    SET...
    SET @ReturnValue = @@ROWCOUNT
    INSERT INTO dbo.tbl_LogTable
    (ExecutionTime, TableName, NumOfRows)
    VALUES
    (getdate(), 'dbo.myTable', @ReturnValue)
    DELETE FROM dbo.MyOtherTable WHERE ...
    SET @ReturnValue = @@ROWCOUNT
    INSERT INTO dbo.tbl_LogTable
    (ExecutionTime, TableName, NumOfRows)
    VALUES
    (getdate(), 'dbo.myOtherTable', @ReturnValue)
    ...
END
GO

    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

     
  • Sunday, November 18, 2012 12:20 PM
    Moderator
     
     
    Sign In to Vote
    0

    <<I have a select @@servername that returns row count>>

    I don't understand what you are saying with above. @@SERVERNAME do not return rowcount.

    You can either explicitly add this information using @@ROWCOUNT after each statement for whcih you want this info. Or you can use a CmdExec jobstep instead of TSQL, which uses SQLCMD to execute the SQL commands.

    Tibor Karaszi, SQL Server MVP | web | blog

     
  • Sunday, November 18, 2012 5:15 PM
     
     
    Sign In to Vote
    0

    Tibor said right one you can use the CMDexec one.

    Thanks, Rama Udaya.K "“You only live once, but if you do it right, once is enough. ― Mae West". ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

     
  • Monday, November 19, 2012 4:07 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    The Agent job has a limited buffer for the output in the history.  If you want more data, you need to use the "output file" under Advanced options on the step to output to a file.

    Please see:

    http://msdn.microsoft.com/en-us/library/ms188952.aspx