exec master..xp_cmdshell

Question

Post reply

exec master..xp_cmdshell

SQL Galaxy

One Orange Chip

Points: 29472
More actions
October 16, 2008 at 11:59 pm

#73061

Hi,
The following script works fine, but want result to create one text file, please tell me where should i writen script.
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min-2 VARCHAR(5),@cmdtxt as varchar(255),@count_rows varchar(200)
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 running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END
This script result as belw
SQL Server "server_Name" is Online for the past 166 hours & 55 minutes
SQL Server and SQL Server Agent both are running
So this result i want write one text file.
--exec master..xp_cmdshell
thanks for reply me.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

DECLARE @path varchar(1056),@msg1 varchar(1025),@msg2 varchar(512),@msg3 varchar(512)

SET @path = 'D:\servercheck'+'_'+convert(char(8),getdate(),112)+'.txt'

SET NOCOUNT ON

DECLARE @crdate DATETIME, @hr VARCHAR(50), @min-2 VARCHAR(5),@cmdtxt as varchar(255),@count_rows varchar(200)

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

SET @msg1 = 'echo "'+'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'+'" >> '+@path

exec master..xp_cmdshell @msg1

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

BEGIN

SET @msg2 = 'echo '+ 'SQL Server is running but SQL Server Agent running'+' >> '+@path

exec master..xp_cmdshell @msg2

END

ELSE BEGIN

SET @msg3 = 'echo '+ 'SQL Server and SQL Server Agent both are running' +' >> '+@path

exec master..xp_cmdshell @msg3

END

/*

just edit the value of the @path variable (only the bolded part) if executed daily, the script will generate a message in a textfile with date (date today) at the last part of the file name

*/

"-=Still Learning=-"

Lester Policarpio

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

hi Lester Policarpio ,

Thanks for reply me.

Script was working fine,

U J SSCrazy Points: 2423 More actions · Answer 3

U J

SSCrazy

Points: 2423

October 19, 2008 at 2:33 pm

#886926

Thnks a ton Lester..it helped me also 😀