>>I am having a significant problem with agent jobs not completing, but showing successful. One glaring example is a job I have for doing DBCC CHECKDB on all my databases. I have created the stored procedure below, and have run it successfully on many occasions, manually and as called by an agent job. There are also many times that the agent job says it completed successfully, but only one or two of the databases have actually been checked. I have put code into the procedure to try to find out why, putting messages into the log. Last night, the log indicates 6 databases in the select cursor (which is correct), but only TWO of the databases were actually checked. Again, NO ERROR.
>>
>>This is SO frustrating. Google searches have not helped, nor Microsoft. Any help is greatly appreciated!!
>>
>>

>>USE [SQLSrvMonitor]
>>GO
>>/****** Object:  StoredProcedure [dbo].[sCheckEachDB08]    Script Date: 3/3/2016 7:43:18 AM ******/
>>SET ANSI_NULLS ON
>>GO
>>SET QUOTED_IDENTIFIER ON
>>GO
>>-- =============================================
>>-- Create date: 20 Oct 2015
>>-- Description:	Runs DBCC CheckDB for every DB, or one specified, and stores the History
>>-- this version for SQL 2008, not 2012 or later.
>>-- =============================================
>>ALTER PROCEDURE [dbo].[sCheckEachDB08]
>>@DatabaseName SYSNAME = NULL
>>AS
>>BEGIN
>>	-- SET NOCOUNT ON added to prevent extra result sets from
>>	-- interfering with SELECT statements.
>>	SET NOCOUNT ON;
>>
>>	--Following few lines for error checking
>>	DECLARE @message VARCHAR(255)
>>	DECLARE @CntDBs	TINYINT = (SELECT COUNT(*) FROM sys.databases WHERE database_id > 4)
>>	SELECT @message = 'The number of DBs for crEachDB is: ' + CAST(@CntDBs AS VARCHAR(3))
>>	EXEC xp_logevent 60000, @message, informational;
>>
>>	IF @DatabaseName IS NULL -- Run against all databases
>>	BEGIN
>>		DECLARE crEachDB CURSOR FOR
>>		SELECT name FROM sys.databases db
>>		WHERE database_id > 4
>>			--AND db.state_desc = 'ONLINE' 
>>			--AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
>>			--AND is_read_only = 0
>>		ORDER BY name ASC
>>		OPEN crEachDB
>>		FETCH next FROM crEachDB INTO @DatabaseName
>>		WHILE @@FETCH_STATUS=0
>>		BEGIN
>>			SELECT @message = @DatabaseName + ' is being checked.'
>>			EXEC xp_logevent 60000, @message, informational;
>>
>>			INSERT INTO SQLSrvMonitor.dbo.tDBCCHistory (
>>			[Error],[Level],[State],[MessageText],[RepairLevel],[Status],[DbId],
>>			[ObjectID],[IndexId],[PartitionID],[AllocUnitID],[File],
>>			[Page],[Slot],[RefFile],[RefPage],[RefSlot],[Allocation]
>>			)
>>			EXEC ('dbcc checkdb(''' + @DatabaseName + ''') with tableresults')
>>
>>			FETCH next FROM crEachDB INTO @DatabaseName
>>		END
>>
>>		CLOSE crEachDB
>>		DEALLOCATE crEachDB
>>	END 
>>
>>	ELSE -- run against a specified database (ie: EXEC sCheckEachDB 'DB Name Here'
>>
>>	INSERT INTO SQLSrvMonitor.dbo.tDBCCHistory (
>>			[Error],[Level],[State],[MessageText],[RepairLevel],[Status],[DbId],
>>			[ObjectID],[IndexId],[PartitionID],[AllocUnitID],[File],
>>			[Page],[Slot],[RefFile],[RefPage],[RefSlot],[Allocation]
>>			)
>>	EXEC ('dbcc checkdb(''' + @DatabaseName + ''') with tableresults')
>>
>>END
>>

>
>Where did you get with TABLERESULTS syntax? It is not listed in BOL
>
>https://msdn.microsoft.com/en-us/library/ms176064.aspx?f=255&MSPPError=-2147217396
>
>I would also add LOCAL FASTFORWARD for the cursor declaration and use quotename in the exec command and also print it before running.

Hmm, I’m not sure where I found that syntax ‘WITH TABLERESULTS’. It does work however. The result set changed in SQL 2012, so I have a different procedure for those servers.

I will add the LOCAL FASTFORWARD. Good idea. It certainly won’t hurt and might help. Also, the quotename won’t hurt either. Keep in mind that I do and have run this procedure with complete and successful results, both manually and by job call. It is the intermittent failures with success reported and no errors that is so frustrating. :/

Thanks for your help!!