Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: 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!

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221

  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    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.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  4. #4
    Join Date
    Feb 2005
    Posts
    76
    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?!

  5. #5
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    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

  6. #6
    Join Date
    Feb 2005
    Posts
    76
    "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????

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Quote Originally Posted by STUCK1234
    "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.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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.
    Code:
    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:
    Code:
    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.
    Last edited by TallCowboy0614; 05-23-07 at 20:44.
    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

  9. #9
    Join Date
    Feb 2005
    Posts
    76
    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

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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.
    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

  11. #11
    Join Date
    Feb 2005
    Posts
    76
    '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...


  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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.
    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
  •