SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.spJobHistory    Script Date: 23/11/2006 18:00:08 ******/
ALTER   procedure spJobHistory
@strServer Varchar(100),
@login  Varchar(100)=null,
@password Varchar(100)=null
/*
This stored procedure gets the job history from a server. This allows you to automatically 
check for errors on one or a number of servers and, if necessary, react. It also allows
you to analyse jobs to check on a number of conditions, such as whether operators were 
alerted to errors, from a central 'controlling' database

It contains a demonstration of how one might get data from the 'QueryResults' object in
DMO, which is not the easiest of DMO objects to manipulate

--if SQL Server authentication
spJobHistory 'MyServer', 'MyID', 'MyPassword'

or
--if windows authentication
spJobHistory 'MyServer'

The output is a tab-delimited result. I use a generic method of getting the information
from a 'QueryResults' and then parse it according to the structure or type of the data.
For example, dates need to be checked and parsed correctly. This is reasonably trivial
to do, according to individual requirements

*/
as
declare @hr int,		--the HRESULT returned from the OLE operation
	@ii int,		--a simple counter
	@jj int,		--a simple counter
	@kk int,		--a simple counter
	@objServer int,		--the Server object
	@objQueryResults int,	--the QueryResults object
	@objErrorObject int,	--the error object
	@ErrorMessage varchar(255),--the potential error message
	@Command varchar(8000),
	@ResultSetCount int,
	@CurrentResultSet int,
	@cols int,
	@Rows int,
	@Colname varchar(100),
	@strCurrentRow varchar(8000),
	@string varchar(8000)

Declare @members table (Role_ID int, RoleName varchar(50),appRole Varchar(10),Member varchar(50))
Declare @ResultTable table (MyID int identity(1,1),row varchar(8000))
set nocount on

exec @hr = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT

if @password is null or @login is null
	begin
	--use a trusted connection 
	if @hr=0 Select @ErrorMessage='Setting login to windows authentication on '+@strServer,
	@objErrorObject=@objServer
	if @hr=0 exec @hr = sp_OASetProperty @objServer, 'LoginSecure', 1
	if @hr=0 Select @ErrorMessage='logging in to the requested server using windows authentication on '+@strServer
	if @login is null and @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @strServer 
	if @login is not null and @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @strServer ,@Login
	end
else
	Begin
	if @hr=0 SELECT @ErrorMessage = 'Connecting to '''+@strServer+
		''' with user ID '''+@login+'''', @objErrorObject=@objServer
	if @hr=0 exec @hr = sp_OAMethod @objServer, 'Connect', NULL, @strServer ,
						 @login , @password
	end
if @hr=0 select @command='JobServer.EnumJobHistory',
		@errorMessage='Getting the EnumJobHistory '
if @hr=0 EXEC @hr = sp_OAGetProperty @objServer, @command, 
		@objQueryResults OUT
if @hr=0 Select @ErrorMessage='getting resultSetCount '+@strServer,
	@objErrorObject =@objQueryResults
if @hr=0 exec @hr = sp_OAGetProperty @objQueryResults, 'ResultSets', @ResultSetCount OUT
Select @ii=1
while @hr=0 and @ii<=@ResultSetCount
	begin
	if @hr=0 Select @ErrorMessage='getting column count'
	if @hr=0 exec @hr = sp_OAGetProperty @objQueryResults, 'Columns', @cols OUT
	if @hr=0 Select @ErrorMessage='getting row count'
	if @hr=0 exec @hr = sp_OAGetProperty @objQueryResults, 'Rows', @rows OUT
	if @Rows>0 and @hr=0 
		begin
		--get the result set headings
		Select @jj=1, @strCurrentRow=null
		while @jj<=@cols and @hr=0
			begin
			select @command='ColumnName('+cast(@jj as varchar(2))+')'
			if @hr=0 Select @ErrorMessage='getting '+@command
			if @hr=0 exec @hr = sp_OAGetProperty @objQueryResults, @command, @colname OUT
			if @hr=0 select @strCurrentRow=coalesce(@strCurrentRow+char(09),'')+@colName
			Select @jj=@jj+1
			end
		if @hr=0 insert into @ResultTable (row) select @strCurrentRow
		Select @jj=1, @kk=1
		while @jj<=@rows and @hr=0 
			begin
			select @strCurrentRow=null
			while @kk<=@cols and @hr=0
				begin
				select @command='GetColumnString('+cast(@jj as varchar(10))+', '
						+cast(@kk as varchar(10))+')'
				if @hr=0 Select @ErrorMessage='doing '+@command
				if @hr=0 exec @hr = sp_OAMethod @objQueryResults, @command, @string OUT
				if @hr=0 select @strCurrentRow=coalesce(@strCurrentRow+char(09),'')+@string
				Select @kk=@kk+1
				end
			if @hr=0 insert into @ResultTable (row) select @strCurrentRow
			--select @ii,@jj,@strCurrentRow

			Select @jj=@jj+1,@kk=1
			end
		end
	Select @ii=@ii+1
	end
Select row from @ResultTable order by MyID
--and handling any errors
if @hr<>0
	begin
	Declare 
		@Source varchar(255),
		@Description Varchar(255),
		@Helpfile Varchar(255),
		@HelpID int
	
	EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
		@source output,@Description output,@Helpfile output,@HelpID output

	Select @ErrorMessage='Error whilst '+@ErrorMessage+', '+@Description
	raiserror (@ErrorMessage,16,1)
	end

exec sp_OADestroy @objQueryResults
exec sp_OADestroy @objServer



GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO