SCCM 2007 Custom Report shows no results in SQL Server Management Studio

I understand that I might be overlooking something extremely simple. I am trying to create a couple of custom reports that are based on the existing reports with SMS 2003 I was able to copy the SQL from the existing report and add the appropriate declare statements and was able to tweak the report to get the results that i was looking for. I am currently running SCCM 2007 R3 SP2 with SQL 2005 SP3 and although the query returns with no errors I don't see any results. Here is a sample of one of the reports that I am trying to modify.
 

 declare @Vendor varchar;
 declare @UpdateClass varchar;
 declare @CollID varchar;
 
set @CollID='SMS000ES';
 set @Vendor='Microsoft';
 set @UpdateClass='';
set nocount on
 declare @VendorID int;if @Vendor='' set @VendorID=0 else select @VendorID=CategoryInstanceID from v_CategoryInfo where CategoryTypeName='Company' and CategoryInstanceName=@Vendor
 declare @ClassID int;if @UpdateClass='' set @ClassID=0 else select @ClassID=CategoryInstanceID from v_CategoryInfo where CategoryTypeName='UpdateClassification' and CategoryInstanceName=@UpdateClass
 declare @NumTotal int;select @NumTotal=count(*) from v_ClientCollectionMembers where CollectionID=@CollID --and IsClient=1
 declare @CI table(CI_ID int primary key)
 ; with CA as (select AssignmentID from v_CIAssignmentTargetedCollections where CollectionID=@CollID)
     , CM as (select ResourceID from v_ClientCollectionMembers where CollectionID=@CollID and IsClient=1)
 insert @CI
 select ci.CI_ID
 from v_UpdateCIs ci
 where ci.IsHidden=0
 and (@VendorID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@VendorID))
 and (@ClassID=0 or exists(select 1 from v_CICategories_All where CI_ID=ci.CI_ID and CategoryInstanceID=@ClassID))
 and exists(select 1 from CM join v_UpdateComplianceStatus cs on cs.CI_ID=ci.CI_ID and cs.ResourceID=CM.ResourceID and cs.Status=2)
 and not exists(select 1 from CA join v_CIAssignmentToCI ac on ac.AssignmentID=CA.AssignmentID and ac.CI_ID=ci.CI_ID)
 select  
     ui.ArticleID as ArticleID,
     ui.BulletinID as BulletinID,
     ui.Title as Title,
     NumMissing as Missing,
     PComputers=convert(numeric(5,2), (isnull(NumMissing, 0))*100.0 / isnull(nullif(@NumTotal, 0), 1)),
     ui.InfoURL as InformationURL,
     ui.CI_UniqueID as UniqueUpdateID
 from @CI ci
 left join v_UpdateInfo ui on ci.CI_ID=ui.CI_ID
 left join v_UpdateSummaryPerCollection cs on cs.CI_ID=ci.CI_ID and cs.CollectionID=@CollID and cs.NumMissing>0
 order by NumMissing desc
 

 
 Again, the query runs without error but it does not show me the results. I am pretty sure that I am not the first to run into this issue and that there is a simple fix or work around but I have not been able to find one as of yet. Any help that anyone can provide would be greatly appreciated.
 
Thanks in Advance,
 
TimN_FL

may need to change the colleciton id samoething else and or set vendor and update class to use like instead "=". not sure if they are all called "microsoft" they could be "Mirosoft Corp" or Microsoft corporation etc..

Carl Polk

CAP


may need to change the colleciton id samoething else and or set vendor and update class to use like instead "=". not sure if they are all called "microsoft" they could be "Mirosoft Corp" or Microsoft corporation etc..

I have tried those suggestions to no avail. the values for those two items are straight out of the values for the default report that I am trying to modify. If I paste what submitted before (minus the 3 declare statements at the top ) and supplying the same values for those items, I get the results as I would expect. The query that I detailed in the OP was a copy of Report ID 141 "Management 1 - Updates required but not Deployed". I made a copy of the report for testing\backup. The issue that I am having is that pasting this same query into SQL Management Studio doesn't seem to process the results. All I ever get is "Query Executed successfully" but with a blank results window but it has the correct Column headers.
 
As some additional information, simpler SQL queries ( ones without multiple selects, etc... ) seem to work just fine. I have taken some of the reports that have been discussed on this forum and pasted them into SQL management studio and gotten the expected results without issue. 
 
Thanks again for any help that anyone might have,
 
TimN_FL