Help with Script when Database is OFFLINE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Help with Script when Database is OFFLINE

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2006-09-20 : 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

38200 Posts

Posted - 2006-09-20 : 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 - 2006-09-20 : 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

38200 Posts

Posted - 2006-09-20 : 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 - 2006-09-21 : 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

25 Posts

Posted - 2006-09-21 : 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 - 2006-09-21 : 16:26:49

This method also seems not to be working. I am still getting the blank mails.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-21 : 17:06:03

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 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

30421 Posts

Posted - 2006-09-21 : 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 - 2006-09-21 : 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 - 2006-09-21 : 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

38200 Posts

Posted - 2006-09-21 : 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

30421 Posts

Posted - 2006-09-22 : 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

vaddi
Posting Yak Master

145 Posts

Posted - 2006-09-22 : 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

38200 Posts

Posted - 2006-09-22 : 10:48:42

Oh brother!

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources