Solved

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

Posted on 2010-08-31
6
435 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:kdeutsch
  • 4
  • 2
6 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33570677
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
 

Author Comment

by:kdeutsch
ID: 33570715
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
 

Author Comment

by:kdeutsch
ID: 33570729
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 33589807
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
 

Author Comment

by:kdeutsch
ID: 33590211
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
 

Author Closing Comment

by:kdeutsch
ID: 33620474
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now