SQL Server Performance

Error with the smtp script

Discussion in 'Performance Tuning for DBAs' started by vaddi, Aug 24, 2006.

  1. vaddi New Member

    Hello

    I have got a script which checks the status of the database and mails to the dba box , when the database is offline . But the procedure is thowing an error. Could anybody help.


    USE MASTER
    GO

    DECLARE @dbname VARCHAR(100)
    deCLARE @Status varchar(100)
    Declare @Message VARCHAR(8000)

    set @Message = ''

    DECLARE dbname_cursor CURSOR FOR

    SELECT name FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Status')) = 'OFFLINE'
    order by name

    OPEN dbname_cursor
    FETCH NEXT FROM dbname_cursor INTO @dbname, @Status

    WHILE @@FETCH_STATUS = 0
    BEGIN
    select @message = @message + @dbname + ' - ' + @Status + Char(13)
    FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
    END
    CLOSE dbname_cursor
    DEALLOCATE dbname_cursor

    print @message
    EXEC master.dbo.xp_smtp_sendmail
    @FROM = N'testsql2000@is.depaul.edu',
    @TO = N'dvaddi@depaul.edu',
    @server = N'smtp.depaul.edu',
    @subject = N'Status of sqlserver!',
    @type = N'text/html',
    @message = @message


    The Error Message is :

    Server: Msg 16924, Level 16, State 1, Line 13
    Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.


    Thanks
  2. Haywood New Member

    Remove the @Status from your cursor. It is not neccesary. You already know the status because you are only looking for offline databases.

    I suggest you read up on cursors and get a full understanding of how to create and use them...the error is pretty self-explanitory if you think about it for a minute. (HINT: Look at your FETCH_NEXT statements)
  3. SQL_Guess New Member

    By the way, you can generate that message without the cursor (which is almost always a good thing).

    CURSOR VERSION (WORKING)


    Set NoCount on
    DECLARE @dbname VARCHAR(100)
    deCLARE @Status varchar(100)
    Declare @Message VARCHAR(8000)

    set @Message = ''

    DECLARE dbname_cursor CURSOR FOR
    SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Status')) as [Status]
    FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Status')) = 'OFFLINE'
    order by name

    OPEN dbname_cursor
    FETCH NEXT FROM dbname_cursor INTO @dbname, @Status

    WHILE @@FETCH_STATUS = 0
    BEGIN
    select @message = @message + @dbname + ' - ' + @Status + Char(13)
    FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
    END
    CLOSE dbname_cursor
    DEALLOCATE dbname_cursor
    select @Message



    NON-CURSOR VERSION (WORKING)


    Set NoCount on
    DECLARE @dbname VARCHAR(100)
    DECLARE @Status varchar(100)
    DECLARE @Message VARCHAR(8000)

    set @Message = ''

    SELECT @Message = @Message + [Name] + ' - ' + CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Status')) + Char(13)
    FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Status')) = 'OFFLINE'
    Order by [Name]

    select @Message



    HTH

    Panic, Chaos, Disorder ... my work here is done --unknown

Share This Page