September 7, 2009 at 4:12 am
Declare @sqlcommand nvarchar(1500),@path nvarchar(1500),@sqlstmt nvarchar(2500),@Cmd nvarchar(1500)
SET @path = 'C:\output'+'_'+convert(char(8),getdate(),112)+'.txt'
SET @sqlcommand = 'echo "'+'This SQL Server Jobs on "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+' >> '+ @path
select @Cmd = @sqlcommand
exec master..xp_cmdshell @Cmd
First part i got the result in 'c:\output20090907.txt, result is servername
set @sqlstmt='SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id AND B.last_run_outcome = 0'
exec master..sp_executesql @sqlstmt, @path
In second part could load the result in text file. can anyone suggestion me.
Thanks
September 7, 2009 at 6:36 am
Well since you are already using xp_cmdshell you could use the query that gets to results and pass it to ISQL (since you are using SQL 7 or 2000). Lookup ISQL in BOL for the syntax. You would set that filename as the Output File..
CEWII
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply