Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    19

    Unanswered: Stored Procedure

    Hi,
    There is a stored procedure in our master database that runs regularly and generate email report for all the things like, Instance status, Performance Status, if any new database added, backup status, database size status, job status and drive status for the server. Now i have to add

    1. DATABASEPROPERTYEX (database, property)
    2. HTML table % size
    3. Conversions

    so that it can generate reports for DATABASEPROPERTY, HTML table % size, convrsions for all the database of that server. I am new in scripting, anyone please help me find out the solution, what to edit in script.

    Regards.
    sumit

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can we see what you've got so far?

    -PatP

  3. #3
    Join Date
    Apr 2006
    Posts
    19
    HI,
    Following is the script we use to collect error log information--

    ----------------------------------------------------
    IF object_id('tempdb..#DBErrorLog') IS not null
    DROP TABLE #DBErrorLog

    CREATE TABLE #DBErrorLog
    (
    errormsg varchar(1000),
    continuation tinyint
    )

    SET NOCOUNT ON
    INSERT #DBErrorLog EXEC sp_readerrorlog

    SELECT @errlogstattext = @vbCrLf+'<table width="100%" cellspacing="0" cellpadding="0">' +
    @vbCrLf+'<tr><td><B>ErrorLog Errors for ' + @@SERVERNAME + ' as of ' +
    @vbCrLf+CONVERT(varchar(30), getdate())+ '</b></td></tr>'
    SELECT @errlogstattext = @errlogstattext +
    @vbCrLf+'<tr>' +
    @vbCrLf+'<td width="100%" class="tdbhead">MESSAGES</td></tr>'

    DECLARE @errmsg varchar(1000),
    @errcnt int

    SELECT @errcnt = 0

    DECLARE errlog_cursor CURSOR
    FOR
    SELECT errormsg FROM #DBErrorLog

    OPEN errlog_cursor
    FETCH NEXT FROM errlog_cursor INTO @errmsg
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
    IF PATINDEX('%Error:%',@errmsg)>0 OR
    (PATINDEX('%found % errors%',@errmsg)>0 AND
    PATINDEX('%found 0 errors%',@errmsg)=0) OR
    PATINDEX('%BACKUP failed%',@errmsg)>0 OR
    PATINDEX('%I/O error%',@errmsg)>0
    BEGIN
    SELECT @errlogstattext = @errlogstattext + @vbCrLf+'<tr>' + '<td class="tdboth" ' + @errstyle +'>'+@errmsg+'</td></tr>'
    EXEC email_add @errlogstattext
    SELECT @errlogstattext = ''
    IF PATINDEX('%Error:%',@errmsg)>0
    BEGIN
    FETCH NEXT FROM errlog_cursor INTO @errmsg
    SELECT @errlogstattext = @errlogstattext + @vbCrLf+'<tr>' + '<td class="tdboth" ' + @errstyle +'>'+@errmsg+'</td></tr>'
    exec email_add @errlogstattext
    SELECT @errlogstattext = ''
    END
    SELECT @errcnt = @errcnt + 1
    IF (len(@errlogstattext)>500)
    BEGIN
    exec email_add @errlogstattext
    SELECT @errlogstattext=''
    END
    END
    END
    FETCH NEXT FROM errlog_cursor INTO @errmsg
    END
    IF @errcnt = 0
    BEGIN
    SELECT @errlogstattext = @vbCrLf+@errlogstattext + '<tr>' +
    '<td class="tdboth">None</td></tr>'
    END
    CLOSE errlog_cursor
    DEALLOCATE errlog_cursor
    SELECT @errlogstattext = @vbCrLf+@errlogstattext + '</table>' + @newline

    EXEC email_add @errlogstattext

    This scripts generate error log information and send it through email, this kind of script i need to write for
    DATABASEPROPERTYEX (database, property)
    HTML table % size
    Conversions

    please help me to edit this script and add all the above noted items.
    Regards,
    sumit
    Last edited by sumitsahay; 05-03-06 at 04:23.

Posting Permissions

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