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
Manmeet1. 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.|||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|||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|||That way .. i can maintain a archive of the errors for future reference.|||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|||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|||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|||I'm also curious about this...and all I'm doing is trying to save the errors to a trace log table...

/*================================================= ====================================
| 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:
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:

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