| Author |
 Topic  |
|
|
vaddi
Posting Yak Master
145 Posts |
 Posted - 09/20/2006 : 16:34:47
|
Hello ,
I have got a script which checks for the databases that are offline. and mails the dba when any of the database is offline.
When I schedule the job , for every hour , it gives me an blank mail only with the subject ' status of database on testsql' even though no databases are offline.
So how can I change the script , so that it mails the dba only when a database is offline even though its scheduled every hour or half an hour.
The script is:
Set NoCount on
DECLARE @dbname VARCHAR(100)
deCLARE @Status varchar(100)
Declare @Message VARCHAR(8000)
DECLARE @date varchar(100)
set @date = convert(varchar(100), getdate(),109)
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 + @@Servername + '-' + @dbname + ' - ' + @Status + Char(13)+ ‘- ‘ + @date
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 the Database on Testsqlserver!',
@type = N'text/html',
@message = @message
Thanks |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/20/2006 : 16:53:49
|
So what does @message show? Comment out the e-mail part of the code. Debug the rest of the code until @message is correct in all circumstances. There's no point in e-mailing something that isn't coded correctly.
Tara Kizer |
 |
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 09/20/2006 : 17:40:44
|
I donot get any message.
The output mail that I get is :
subject : Status of database on Test sql
From : testsql2000@is.depaul.edu
to : dvaddi@depaul.edu
--------------------------
-- message is empty---
Thanks
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/20/2006 : 17:47:29
|
Ummmm, I said what does @message show. Stop executing the code to send the e-mail! What does the print statement show. You first need to get that to show something before the e-mail will ever work. Take this one step at a time.
Tara Kizer |
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 09/21/2006 : 01:57:34
|
When I comment the email part of the code and bring a test database offline the output I am getting is :
The command(s) completed successfully.
The @print can be deleted and its not necessary.
The output even when no databases are offline of the @message is :
The command(s) completed successfully.
when I execute the following code below:
Set NoCount on
DECLARE @dbname VARCHAR(100),deCLARE @Status varchar(100)
Declare @Message VARCHAR(8000),DECLARE @date varchar(100)
set @date = convert(varchar(100), getdate(),109)
set @Message = ''
SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Status'))
as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Status')) = 'OFFLINE'
order by name
select @message = @message + @@Servername + '-' + @dbname + ' - ' + @Status + Char(13)+ '- ' + @date
I am getting the result :
Name Status
Test OFFLINE
I am not sure if I was clear or not. Please let me know if I should send in any detail
Thanks
|
|
|
|
anilkdanta
Starting Member
India
25 Posts |
Posted - 09/21/2006 : 02:31:28
|
Vaddi,
Just a small addition to your code to send mail only when any of the databases is offline.
Declare a Counter variable with initialized to zero. And increment this inside the cursor loop. After the cursor is closed,
if Counter > 0 then
send mail (1 or more DBs are offline)
else
do nothing (Nothing to report)
|
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 09/21/2006 : 16:26:49
|
This method also seems not to be working. I am still getting the blank mails.
Thanks |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/21/2006 : 17:23:13
|
Why complicate things? And using CURSOR?SET NOCOUNT ON
DECLARE @Msg VARCHAR(8000)
SELECT @Msg = ISNULL(@Msg + CHAR(13), '') + 'Database ' + z.Name + ' on machine ' + z.ServerName + ' is ' + z.Status + ' at ' + z.Now + '.'
FROM (
SELECT TOP 100 PERCENT @@SERVERNAME ServerName,
Name,
CONVERT(VARCHAR, DATABASEPROPERTYEX(Name, 'Status')) Status,
CONVERT(VARCHAR, GETDATE(), 109) Now
FROM master..sysdatabases
WHERE status & 512 = 512
ORDER BY Name
) z
PRINT @Msg
IF @Msg IS NOT NULL
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 the Database on Testsqlserver!',
@type = N'text/html',
@message = @Msg
Peter Larsson
Helsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/21/2006 : 17:39:14
|
If you want to, you can remove first line "SET NOCOUNT ON" and the "PRINT @Msg" line...
Peter Larsson
Helsingborg, Sweden |
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 09/21/2006 : 18:50:34
|
Thanks Peso
The scripts seems to be working. I have been working on this for a long time. Thanks for your time and code.
Regards |
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 09/21/2006 : 18:51:41
|
Thanks tkizer
Thanks for your time on this. I was forced to get the module I was working to get over fast. So I have posted the similar disscuion in another form also.
Regards |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/21/2006 : 19:55:18
|
Please post links to anywhere else that you post so that we know if it's been answered or not. It is not considered good netiquette otherwise.
Tara Kizer |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/22/2006 : 07:25:20
|
quote:
Originally posted by tkizer
I was just about to start working on this when I found out you had the same post on another forum:
http://www.dbforums.com/showthread.php?t=1608709
I give up!
Don't give up Tara! He later posted my suggestion as his own solution 
IF @Msg IS NOT NULL
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 the database(s) on ' + @@SERVERNAME,
@type = N'text/html',
@message = @Msg
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 09/22/2006 07:35:33 |
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 09/22/2006 : 10:38:14
|
Hello Peso,
Sorry for not mentioning your name in the other form , that I have posted the solution given by you. I was quite in a bit of hurry.
Thats for helping me out.
Regards |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 09/22/2006 : 10:48:42
|
Oh brother!
Tara Kizer |
|
|
| |
Topic |
|
Help with Script when Database is OFFLINE
