SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min-2 VARCHAR(5)
SELECT @crdate=create_date FROM sys.databases 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
select * from sys.databases
Two corrections needs to be done in the script for successful execution
1. Change sysdatabases to sys.databases
2. Change the 3 line in the script (query) from "SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'" to "SELECT @crdate=create_date FROM sys.databases WHERE NAME='tempdb'". The error in the original query is, there is no such column (crdate) exists in the sys.databases table.
Correct me if am wrong anywhere.
Thanks,
Nagarjun.