Error with the smtp script

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

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)

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