Warning: Null value is eliminated by an aggregate or other SET operation.

Ok, I have been trying to figure this out, it keeps breaking but this is all it gives me, If i run each individual filter by itself they work just fine, and this process works fine for a couple days and then breaks itself.  How can I trouble shoot to find out where the exact error is coming from while runnning this thing SP.
 Can I put a print somewhere in here that might lead me to the problem for this darn thing.

First what it does is slect all the filters (SQL code) that are assigned to people, then it goes through and runs the code on every persone to see if they have problems, per the sql code, if they do then it saves it to a table.

Here is an example of the sql code.
Select p.intPositionId  , p.strPara  , p.strline  , 'SRP Dental'  , mt.dtMODRE_Date  , pp.strName  , pp.strSSN   from tblUnitPosition as p     Inner join tblUnitPersonnel as pp          on p.intPositionId = pp.intPositionId     INNER JOIN ModreTracker.dbo.tblResults as mt          on mt.strSSN = pp.strssn   where mt.bitDental = 1   And intUnitMobId =
declare @counter int, 
		@countfilter int, 
		@FilterID int, 
		@AssignedID int, 
		@UnitID int, 
		@DeployDate datetime,
		@TaskName varchar(60), 
		@Proponent varchar(60),
		@ApproveLev varchar(15),
		@Days int, 
		@Sql nvarchar(4000), 
		@PositionId int, 
		@Para varchar(4), 
		@Line varchar(3), 
		@Posn varchar(60), 
		@Sid varchar(60), 
		@Name varchar(60), 
		@SSN varchar(10), 
		@cur cursor, 
		@taskID int 
 
Declare Vend_Cursor CURSOR FOR Select uf.intFilterId, uf.intAssignedID, uf.intUnitMobId, ud.dtdeployDate,
										f.strTaskname, f.strProponent, f.strApprovalLevel, f.strSusDays, f.strSql 
										from tblSoldierFilters as uf Inner join tblUnitDeployData as ud on 
										ud.intUnitMobId = uf.intUnitMobId Inner Join tblFilter as f on 
										f.intFilterID = uf.intFilterId where uf.dtRemoved is NULL 
										order by uf.intfilterID

OPEN Vend_Cursor

FETCH NEXT FROM Vend_Cursor INTO @FilterID,@AssignedId,@UnitId,@DeployDate,@Taskname,@Proponent,@ApproveLev,@Days,@Sql
 
while @@FETCH_STATUS =0 
begin
	--- Call the SQL Filter and assign the unit # as the variable to get at table	
	
	set @Sql = N'set @cur = cursor for ' + @Sql + Cast(@UnitID as varchar) + ';open @cur'

	exec sp_executesql @Sql, N'@cur cursor OUTPUT', @cur output

	fetch next from @cur into @PositionId, @Para, @Line, @Posn, @Sid, @Name, @SSN
	
	while @@FETCH_STATUS =0 
	begin
		set @taskID = (select intTaskId from tblTask where intFilterId = @FilterID and intPositionId = @PositionId)
		
		if (select isnull(@taskID, 0)) = 0
		begin
			Insert Into tblTask (intAssignedId, intFilterId, intPositionID, strTaskName, dtFound, strStatus, 
						dtStatus, strSSN, strName, strPositionData, strSoldierData, strProponent, dtNotification,
						dtCompleteTask) 
			VALUES (@AssignedId, @FilterID, @PositionId, @Taskname, Getdate(), @ApproveLev, Getdate(),@SSN, 
					@Name, @Posn, @Sid, @Proponent, Getdate(), dateadd(DAY, -@Days, @DeployDate)) 
		end
		
		fetch next from @cur into @PositionId, @Para, @Line, @Posn, @Sid, @Name, @SSN
	end
	
	close @cur
	deallocate @cur	
	  
	FETCH NEXT FROM Vend_Cursor INTO @FilterID,@AssignedId,@UnitId,@DeployDate,@Taskname,@Proponent,@ApproveLev,@Days,@Sql
end

Close vend_Cursor
deallocate vend_Cursor

Open in new window

kdeutschAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You can't -- and don't want -- to try to get around that.

Instead, you'll have to modify the SQL code that contains the grouping function.  That'll be a pain because I don't see it in your code, so it must be in the @sql / cursor def code.
0
 
Scott PletcherSenior DBACommented:
If that's the *only* error you're getting, issue this statement inside the stored proc / start of T-SQL code:

SET ANSI_WARNINGS OFF

But that's just a warning, it shouldn't keep the code from finishing.
0
 
kdeutschAuthor Commented:
ScottPletcher:
Ok trying now it takes this things about 20 min to run.  Is there a way to put some print statments in this thing to see where its failing or at what part its failing.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
kdeutschAuthor Commented:
ScottPletcher:
I ran into this error, I have some sql queries that go over a linked server to get results, don't know if that would cause this.

Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
0
 
kdeutschAuthor Commented:
ScottPletcher:

Found the problem was a bit of sql code that runs insdie the whole process was picking to much information instead of the standard 7 items that the stored procedure allow.
0
 
kdeutschAuthor Commented:
This is really not solution but because picking mine and divding out points seems to be a pain and I want to colse out the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.