SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Help with Script when Database is OFFLINE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vaddi
Posting Yak Master

145 Posts

Posted - 09/20/2006 :  16:34:47  Show Profile  Reply with Quote
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
36851 Posts

Posted - 09/20/2006 :  16:53:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 09/20/2006 :  17:40:44  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 09/20/2006 :  17:47:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 09/21/2006 :  01:57:34  Show Profile  Reply with Quote
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
Go to Top of Page

anilkdanta
Starting Member

India
25 Posts

Posted - 09/21/2006 :  02:31:28  Show Profile  Reply with Quote
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)

Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 09/21/2006 :  16:26:49  Show Profile  Reply with Quote
This method also seems not to be working. I am still getting the blank mails.

Thanks
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 09/21/2006 :  17:06:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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!

Tara Kizer
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 09/21/2006 :  17:23:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 09/21/2006 :  17:39:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If you want to, you can remove first line "SET NOCOUNT ON" and the "PRINT @Msg" line...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 09/21/2006 :  18:50:34  Show Profile  Reply with Quote
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
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 09/21/2006 :  18:51:41  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 09/21/2006 :  19:55:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 09/22/2006 :  07:25:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

vaddi
Posting Yak Master

145 Posts

Posted - 09/22/2006 :  10:38:14  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36851 Posts

Posted - 09/22/2006 :  10:48:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
Oh brother!

Tara Kizer
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000