Creating a log file

I have a system which has several different dbases involved, and have created an update procedure within it all. It all works fine, but as a refrenence point I want to create a log file,each time the procedure is run.

This file will have useful info such as no of transactions added, no of product row n customer rows, any unmatching entries and any exception records. What I dont know is how do I create such a file?

I was thinking maybe I could save the info into a table, and then somehow transfer this into a txt file? The txt file will be replaced each time the stored procedure is run... Does any1 know any links or ideas that might help me?...I never does anything lik this b4 but I have seen it done....

Thanks in advance!found a perfect article for you
http://www.codeproject.com/useritems/Stored_Procedure_Log_File.asp

:beer: :beer: :beer:|||It might be my paranoia, but I'm not all that happy with the proposed solution. xm_cmdshell is after all among the most severe security risks in SQL Server. Would it not be a better choice to have a log TABLE instead? You could always script a command line tool to transfer the data to a text file on a regular basis.|||Thanks for the replies!!! !

roac, do you have an example site that does something like what you are sayin!!!

I really cant seem to find anything useful on the net?!|||what he meant to say is you have a log table something like

table_log
(logid identity key,
description --description will contain all relevant data you require
)

add a few more relevant columns.....

and each time then, u keep on updating this log table...so instead of having a log file u have a log table and u then need to query this table to get the description as to how many rows were inserted/updated

and his suggestion is the best way out....however if you want a log then u will have to xp_cmdshell :D|||"You could always script a command line tool to transfer the data to a text file on a regular basis."

how do i do this bit??|||"You could always script a command line tool to transfer the data to a text file on a regular basis."

how do i do this bit??
You might look at BCP, which can extract data from a table to a file. It's pretty well documented in Books Online I recall. You can use task scheduler in Windows or a SQL Server Agent Job to schedule it.|||Another alternative would be to use the SQL Server Job Agent to run the job, and include in the stored procedure/code it runs such things as "Print 'whatever data I want to log" and then have the SQL Server agent write the job output to a log.

You can use the "print" statement within the stored procedures OR the job step command window and it will be captured in the output log.

That way you can put the log anywhere on the network you want it to go, name it anything you want to name it, and not have to code anything special to create the log.

This is easily done by entering the code in the job step "command" box, similar to the following, which is a cut and past from one of our maintenance jobs.
DELETE
FROM EventLog
WHERE DATEDIFF(day, [Date], getdate()) > 90

PRINT CONVERT(varchar(10), @.@.ROWCOUNT) + ' old rows were deleted.'

DECLARE @.Count int
SELECT @.Count = (SELECT COUNT(*) FROM EventLog)
PRINT CONVERT(varchar(10), @.Count) + ' rows remain in the table.'
PRINT ' ++ JOB COMPLETE ++'
In the "advanced" tab of the step there is a place where you can enter the path/filename of the "output" file, which then becomes the log you so desperately desire.

This is the output cut and pasted from the generated log file:Job 'DailyProd- Weekly EventLog Cleanup' : Step 1, 'Delete From EventLog Where Older Than 90 Days' : Began Executing 2005-06-09 15:45:06

2244 old rows were deleted. [SQLSTATE 01000]
334 rows remain in the table. [SQLSTATE 01000]
++ JOB COMPLETE ++ [SQLSTATE 01000] and you can set up the job to overwrite the log file each time it runs, or append to the log file each time it runs.|||thanks SO much!!!

I've jsut tested it and its worked fone...
(I used the PRINT command that TallCowboy0614 spoke about)

2 questions:
1) Is there any way of inserting blank lines so that it is easier to read?
2) How do I get it to stop adding '[SQLSTATE 01000]' after each line?

Thanks again|||I think adding a PRINT ' ' (to print a blank line) will allow you that capability.

I don't think there is any way to avoid the [SQLSTATE 01000] at the end of each line, though - using this method, at least. At least if there is, I haven't been bothered by it enough to search for and/or implement it.|||'I think adding a PRINT ' ' (to print a blank line) will allow you that capability.'

I thought thats what it would be, but it didnt wrk...
also tried PRINT char(13) + char(10) but it doesnt wrk either...

:confused:|||Try a couple of them in sequence. I also have noticed sometimes stuff like that (formatting I try to do) doesn't seem to work as expected. I do get mixed results with both methods you describe though. Again, it hasn't been enough of an issue in my applications to put much thought and time into fixing it.