I need help in modifying this script

Question

Post reply

I need help in modifying this script

hydbadrose

SSCarpal Tunnel

Points: 4164
More actions
November 30, 2010 at 9:11 am

#241763

I have this script, I want to use the case or if command instead of print. The print is not displaying on result , it is displaying on message. Please help me so that I will get the output on Result in query analyzer.
--- to find the server uptime
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min-2 VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

Hi

Can you explain clearly what you are trying to say,the above query runs fine for me it gives correct result

Thanks

Parthi

Thanks
Parthi

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 2

I know it does give a correct result, I am trying to turn this into store procedure for daily monitoring and when I run the sp the result is printing on message instead of Result pane.

GSquared SSC Guru Points: 260824 More actions · Answer 3

Change "print" to "select".

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

PKDC SSChasing Mays Points: 618 More actions · Answer 4

Here is the altered script.

SET NOCOUNT ON

DECLARE @crdate DATETIME, @hr VARCHAR(50), @min-2 VARCHAR(5)

SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'

SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60

IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0

SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))

ELSE

SELECT @min-2=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60

select 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')

BEGIN

PRINT 'SQL Server is running but SQL Server Agent <<NOT>> running'

END

ELSE BEGIN

PRINT 'SQL Server and SQL Server Agent both are running'

END

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 5

hydbadrose

SSCarpal Tunnel

Points: 4164

November 30, 2010 at 12:11 pm

#1256954

Thank you so much!