Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Post Unanswered: Getting the word out...opinions?

    Hi all,

    I took a search through the archives for related topics (and got Des in trouble along the way ) but couldn't find a directly related thread. If I missed one, feel free to tell me where to go (hey...watch that...only if I MISSED one!)

    I wrote what is, essentially, a data verification stored proc that goes out to each of FOUR servers we have - each one running a mirror database. In a nutshell, there is one table that contains a row with a column in it that, if everything has gone well in the daily processing in all 4 databases, will match identically between all 4 DBs.

    So, that said, here is the output:
    Code:
    Job 'Index - Verify PortfolioIndex Across Servers' : Step 1, 'PortfolioIndex Check across all servers and portfolios' : Began Executing 2004-11-09 15:30:00
    
    ------------------- BEGINNING PortfolioIndex VERIFICATION -------------------- [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 2 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 3 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 11 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 67 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 72 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 84 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 90 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 92 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 100 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 105 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 110 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 115 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 120 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 125 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 130 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 135 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 140 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 145 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 150 on 11/09/2004! [SQLSTATE 01000]
    WHOO-HOO!!!  EVERYTHING MATCHES for porfolio number 155 on 11/09/2004! [SQLSTATE 01000]
    UH-OH - TROUBLE!!!  CloseIndex mismatch for porfolio number 160 on 11/09/2004! [SQLSTATE 01000]
    --> Server TA1 shows an index of 110.582 [SQLSTATE 01000]
    --> Server TRADEANALYSIS shows an index of 110.582 [SQLSTATE 01000]
    --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
    UH-OH - TROUBLE!!!  CloseIndex mismatch for porfolio number 1000 on 11/09/2004! [SQLSTATE 01000]
    --> Server TA1 shows an index of 189.623 [SQLSTATE 01000]
    --> Server TRADEANALYSIS shows an index of 189.623 [SQLSTATE 01000]
    --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
    UH-OH - TROUBLE!!!  CloseIndex mismatch for porfolio number 1001 on 11/09/2004! [SQLSTATE 01000]
    --> Server TA1 shows an index of 164.058 [SQLSTATE 01000]
    --> Server TRADEANALYSIS shows an index of 164.058 [SQLSTATE 01000]
    --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
    UH-OH - TROUBLE!!!  CloseIndex mismatch for porfolio number 1002 on 11/09/2004! [SQLSTATE 01000]
    --> Server TA1 shows an index of 255.978 [SQLSTATE 01000]
    --> Server TRADEANALYSIS shows an index of 255.978 [SQLSTATE 01000]
    --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
    UH-OH - TROUBLE!!!  CloseIndex mismatch for porfolio number 1003 on 11/09/2004! [SQLSTATE 01000]
    --> Server TA1 shows an index of 159.009 [SQLSTATE 01000]
    --> Server TRADEANALYSIS shows an index of 159.009 [SQLSTATE 01000]
    --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
    UH-OH - TROUBLE!!!  CloseIndex mismatch for porfolio number 1004 on 11/09/2004! [SQLSTATE 01000]
    --> Server TA1 shows an index of 318.981 [SQLSTATE 01000]
    --> Server TRADEANALYSIS shows an index of 318.981 [SQLSTATE 01000]
    --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
    UH-OH - TROUBLE!!!  CloseIndex mismatch for porfolio number 1005 on 11/09/2004! [SQLSTATE 01000]
    --> Server TA1 shows an index of 145.921 [SQLSTATE 01000]
    --> Server TRADEANALYSIS shows an index of 145.921 [SQLSTATE 01000]
    --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
    UH-OH - TROUBLE!!!  CloseIndex mismatch for porfolio number 1006 on 11/09/2004! [SQLSTATE 01000]
    --> Server TA1 shows an index of 141.035 [SQLSTATE 01000]
    --> Server TRADEANALYSIS shows an index of 141.035 [SQLSTATE 01000]
    --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
    UH-OH - TROUBLE!!!  CloseIndex mismatch for porfolio number 1007 on 11/09/2004! [SQLSTATE 01000]
    --> Server TA1 shows an index of NULL [SQLSTATE 01000]
    --> Server TRADEANALYSIS shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
    UH-OH - TROUBLE!!!  CloseIndex mismatch for porfolio number 1008 on 11/09/2004! [SQLSTATE 01000]
    --> Server TA1 shows an index of 123.179 [SQLSTATE 01000]
    --> Server TRADEANALYSIS shows an index of 123.179 [SQLSTATE 01000]
    --> Server RECEIVE1 shows an index of NULL [SQLSTATE 01000]
    --> Server RECEIVE2 shows an index of NULL [SQLSTATE 01000]
    ------------------- COMPLETE -------------------- [SQLSTATE 01000]
    This was cut-n-pasted here from a log file created by the actual SQL SERVER 2000 job created to run the afore-mentioned stored procedure.

    After all that...my quandry is this:

    What is the best way to send this info out in an email format to interested parties? Currently I have the job send out an email notification on completion, but that still requires my lazy buttocks to go look at the log file in the job (or, more accurately, on the server in the logfile directory).

    I want to get the actual DATA as shown above into the email.

    As I see it, my options are:
    (1) write the data out to a flat file during the run (or, as is done now, into a log file by the SQL Server scheduled job) and then attach that FILE to the email - this still requires my lazy buttocks to OPEN the attachment that comes with the email.
    ro (2) write the message out a line at a time to a table with an IDENTITY column (used to order them on the select) and a VARCHAR(128) column that each line in the log would be written to. This option allows me to just do a SELECT in the call to xp_sendmail to get the data into the actual email...but I just really hate the idea of creating a permanent table for this cheesy solution.

    I tried it with a temp table within my stored proc, but of course, when I made the call to xp_Sendmail, it can't see my temp table in order to select from (mind you, it's not that I mind USING a cheesy table, just that I don't want it to have a lifespan longer than the time I need to use it and toss it aside)

    I know the common denominator here is "My Lazy Buttocks", but I really can't understate the laziness of my buttocks, so this is a valid concern

    Any thoughts? How do people get status messages like this into an email without using an attachment or a cheesy middleman table?

    Sorry, as always, about the miniseries...just trying to set the mood before popping the question
    Last edited by TallCowboy0614; 11-11-04 at 14:59.
    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

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    bump

    if no opinions, how about random thoughts?

    Preferably related to the subject of the thread, but other permissible subjects are beer, The Jack Daniels Girls, Kermit and Miss Piggy, or Replacement of the e-brake handle on a '67 Mercury Cougar.
    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
  •