Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    9

    Unanswered: Log file in T-sql

    Hi Folks,
    I really need help on this...it very urgent for me.
    How can I create a log file from a stored proc in T-SQL. I mean everytime there is an error, I need to put it into a log file. This stored proc is not being called by a DTS package..please suggest some method

    Regards
    Manmeet

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    1. use xp_cmdshell and echo ....
    2. write into a table and bulk copy that out in the end

    I prefer approach 2 for my procedures.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Aug 2003
    Posts
    9
    Originally posted by Enigma
    1. use xp_cmdshell and echo ....
    2. write into a table and bulk copy that out in the end

    I prefer approach 2 for my procedures.
    thanks a lot..I know this sounds stupid...can you just send me a sample code .....I myself am looking into it also

  4. #4
    Join Date
    Aug 2003
    Posts
    9
    Originally posted by Enigma
    1. use xp_cmdshell and echo ....
    2. write into a table and bulk copy that out in the end

    I prefer approach 2 for my procedures.
    hey dude...

    Ignore my last msg (about sending a code)..I got it..thanks a ton man...u have been a real life saviour!!!!!!!!

    but just 1 question...why do you prefer option 2..any drawback with option 1???

    regards
    Manmeet

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    That way .. i can maintain a archive of the errors for future reference.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Aug 2003
    Posts
    9

    New Doubt

    Originally posted by Enigma
    That way .. i can maintain a archive of the errors for future reference.
    but then we can still use the echo command with >> option to keep appending the errors to the file right...that way you will still have the archive right??

    --IS there a way you can get the description of the error messages. We get the error code and then query the master table for error descriptions. But the description has place holder and hence we cannot get the complete message...any sugestions dude??

    Regards
    Manmeet

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    IS there a way you can get the description of the error messages. We get the error code and then query the master table for error descriptions. But the description has place holder and hence we cannot get the complete message...any sugestions dude??
    Am a little confused .. could you elaborate
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Aug 2003
    Posts
    9
    Originally posted by Enigma
    Am a little confused .. could you elaborate
    I am just running a stored proc from a batch file. I need to capture any error and log it into a txt file. So If I use @@ERROR, I get only the error code. SO to get the description of the error, I hit the sysmessages table in master database and get the description.

    so say I have a sql statement 'drop table MyTab'...then if the table does not exist...the error should be 'cannot drop the table MyTab as it does not exist in the system catalog' (Error No 3701)

    But if I use @@ERROR and query the table sysmessages...I get the description 'Cannot %S_MSG the %S_MSG '%.*ls', because it does not exist in the system catalog'...bcos thats what the table stores for Error no 3701....

    So how can I get the complete description pf the error message??

    Regards
    Manmeet

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I'm also curious about this...and all I'm doing is trying to save the errors to a trace log table...
    Code:
    /*=====================================================================================
     |  Name:	ErrorHandler
     |  Description:	Just a basic function to raise an error when necessary.  Also writes to tracelog table.
     |                         
     |  Created:	MM/DD/YYYY by JoMama Angiedaddy
     |  Modified:
     |  Inputs:	errorNum - The error number that occurred.
     |		app - The application description in which the error occurred.
     |		function - the function in which the error occurred.
     |======================================================================================*/
    CREATE  PROC dbo.ErrorHandler ( 
         @errorNum  int =0 ,
         @app  varchar(50) ='' ,
         @function  varchar(255) ='' ) 
    AS
      BEGIN
    
    	DECLARE @msg varchar(100)
    	
    	IF @errorNum <> 0	
    	BEGIN
    		IF Exists(SELECT error FROM master..sysmessages WHERE error = @errornum)
      			SELECT @msg = 'Error Number: ' + 
      			Convert(varchar(5), error) + ' ' + description 
      			FROM master..sysmessages 
      			WHERE error = @errornum
    		ELSE
    			SELECT @msg = 'Error Number: ' + Convert(varchar(5), @errornum)
    		
    		EXECUTE tracelog 0, @app, @function, @msg
    		
    		RAISERROR ('ErrorHandler %s %s %s', 16, 1, @app, @function, @msg)
    		RETURN 1
    	END
    	ELSE
    		RETURN 0	
    END
    GO
    And the results are:
    Code:
    Server: Msg 515, Level 16, State 2, Procedure sp_Rebuild_IBD8585_CurrentList, Line 79
    Cannot insert the value NULL into column 'StockOSID', table 'IBDIndex.dbo.CurrentList'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    
    (1 row(s) affected)
    Here's what gets written to the tracelog and appears on the screen as a result of the RaiseError call:
    Code:
    Server: Msg 50000, Level 16, State 1, Procedure ErrorHandler, Line 33
    ErrorHandler IBDIndex sp sp_Rebuild_IBD8585_CurrentList Error Number: 515 Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not a
    So...I am trying (as are the previous poster and several other frustrated and head-scratching individuals I have found around the 'net on this subject) to discover a way to make the data that appears on the first (system-generated) error message make its way into the final trace error message string....instead of the placeholders from the sysmessages table.

    Any insights and/or, well...just plain ole' magical code snippets or how-to's?

    Thankyouverymuch
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •