October 31, 2009 at 12:07 pm
Hi,
I have 5 stored procs in a sql 2005 db and an excel 2003 application that calls the stored procs to populate sheets.
3 of the 5 stored procs successfully pull data from the db to the excel app. The other 2 do not pull any data.
When I run the 2 stored procs in the db that return no data in excel, the 2 stored procs pull the data. I have checked the excel code and it is fine.
The only thing that is different between the 2 SPs that don't pull data and the 3 that do is that the 2 have Union Alls and variables that are not associated with the SP parameter. The following is one of the SPs that doesn't pull data.
ALTER PROCEDURE [dbo].[uspGetToDateActuals]
-- Add the parameters for the stored procedure here
@Program Varchar(9)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @ratio Numeric(6,4)
Declare @CurrFiscalYearMonth int
Declare @MaxActualsYearMonth int
Set @ratio = (
Select Top 1 b.Ratio
From DimWeeks a
Join
LU_Calendar b
ON a.Week = b.date
Where a.Week = (Select Max(Week) from DimWeeks)
)
Set @CurrFiscalYearMonth = (
Select Top 1 b.YearMonth
from ExtractCognos a
Join LU_YearMonth b
On a.Year = b.Year
And a.Month = b.Month
)
Set @MaxActualsYearMonth = (
Select Top 1 b.YearMonth
from ExtractActuals a
Join LU_YearMonth b
On a.Year = b.Year
And a.Month = b.Month
)
SELECT a.Month, a.Year, Program, Project, ResourceType,
ForecastFTEs AS ActualFTEs,
ForecastCost AS ActualCost
FROM ViewForecastReport a
Join LU_YearMonth b
On a.Year = b.Year
And a.Month = b.Month
WHERE b.YearMonth < @CurrFiscalYearMonth
AND ResourceType in ('Business', 'Manager', 'Executive', 'Operations', 'Consultant',
'Consultant Executive','Consultant Manager',
'Consultant Senior Analyst', 'Consultant Analyst')
AND UnAssigned = 0
AND Program = @Program
Union All
SELECT a.Month, a.Year, Program, Project, ResourceType,
(ForecastFTEs)*(1-@Ratio) AS ActualFTEs,
(ForecastCost)*(1-@Ratio) AS ActualCost
FROM ViewForecastReport a
Join LU_YearMonth b
On a.Year = b.Year
And a.Month = b.Month
WHERE b.YearMonth = @CurrFiscalYearMonth
AND ResourceType in ('Business', 'Manager', 'Executive', 'Operations', 'Consultant',
'Consultant Executive','Consultant Manager',
'Consultant Senior Analyst', 'Consultant Analyst')
AND UnAssigned = 0
AND Program = @Program
Union All
SELECT a.Month, a.Year,Program,Project, ResourceType,
ActualFTEs,
Cost AS ActualCost
FROM ViewTimeDetailReport a
Join LU_YearMonth b
On a.Year = b.Year
And a.Month = b.Month
WHERE b.YearMonth >= @MaxActualsYearMonth
AND Program = @Program
And ResourceType <> ''
END
Do I need to create and OutPut parameter for these kinds of stored procedures? If so, a parameter that will contain an entire recordset?
Any help would be greatly appreciated.
Thanks.
October 31, 2009 at 12:44 pm
I apologize for the post. I figured out what the problem was and it was on the excel side. Apologies.
Please remove if possible.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply