I'm having a major problem with an Access Data Project linked to a SQL Server 2k database. I have created a complex stored procedure (see below) which inserts the results into a work table. The problem is that when the stored procedure is run from within the ADP, either directly or via a macro, it returns a message saying:

"The Stored Procedure executed successfully but did not return any records."

When I check the table it either contains no, or more often, only one record. When I run the stored procedure in SQL Query Analyzer it will return in excess of 40 records. The ADP is designed to be used as a reporting tool but I cannot make it get the required data.

Any help would be greatly appreciated because I don't know whether its a permissions issue or a problem with the structure of the stored procedure!


NB: The last section before the DROP TABLE commands which is commented out is merely to select the data gathered by the rest of the procedure and is now in a seperate Procedure.
Also, I am not concerned about changing the DATEADD functions and replacing them with parameters if necessary.

Alter PROCEDURE dbo.Major_Donors_Reporting

--This stored procedure is designed to identify potential Major and High Value donors--
--not yet flagged on OSS.
--Written by Tom Smith 21.7.2003

--Create Holding Table--

CREATE TABLE #supporter_hold
(Supporter_URN int NOT NULL,
Annual_Amount decimal NOT NULL)

--Populate Holding table with all supporters annual amounts, CASH and COG--

insert into #supporter_hold
SELECT c.supporter_urn, c.annualized_value AS 'annual_amount'
FROM MAP_Mart..commitment_dim c (nolock)
JOIN MAP_Mart..supporter_dim s (nolock) ON c.supporter_urn = s.supporter_urn
where s.current_supporter_level in ('Standard','Premium','Unknown')
AND c.commitment_current_status like 'current%'
GROUP BY c.supporter_urn, c.annualized_value


SELECT DISTINCT p.supporter_urn, SUM(p.payment_amount) AS annual_amount
FROM MAP_staging..payment_changes p (nolock)
JOIN MAP_Mart..supporter_dim s (nolock) ON p.supporter_urn = s.supporter_urn
where p.commitment_urn is null
AND s.current_supporter_level in ('Standard','Premium','Unknown')
AND p.payment_date > DATEADD(mm, -12,getdate())
GROUP BY p.supporter_urn

--Create Index on Holding table--

CREATE CLUSTERED INDEX Supporter_URN_cnu1 on #supporter_hold(Supporter_URN)

--Remove Supporters from holding table who have a DNP flag--

delete from #supporter_hold
where Supporter_URN IN (select Supporter_URN from MAP_staging..Supporter_Type_changes
where supporter_type_id = 'DNP')

--Create and insert into temporary table supporters who qualify as HIVL or MAJR donors,
--and their combined annual donations

(supporter_urn int not null,
total_annual_amount decimal,
potential_level varchar (4) not null,
created char(10) not null)


SELECT supporter_urn, SUM(Annual_Amount)
AS 'total_annual_amount',
potential_level = (CASE
WHEN SUM(Annual_Amount) > 999.99 AND SUM(Annual_Amount) < 10000 THEN 'HIVL'
WHEN SUM(Annual_Amount) > 9999.99 THEN 'MAJR' END),
convert(char(10), getdate(),103)
FROM #supporter_hold
GROUP BY Supporter_URN
HAVING SUM(Annual_Amount) > 999.99

--Create index on temporary table--

CREATE CLUSTERED INDEX Supporter_URN_cnu2 on #potential_MAJR(Supporter_URN)

--Insert supporters into work table that do not currently exist there (de-dupe)--

INSERT INTO wrk_potential_MAJR
SELECT supporter_urn, total_annual_amount, potential_level, created
FROM #potential_MAJR
WHERE supporter_urn NOT IN
(SELECT supporter_urn FROM wrk_potential_MAJR)

--Select supporters who've been newly inserted into the work table and return the data--

SELECT supporter_urn, total_annual_amount, potential_level, created
FROM wrk_potential_MAJR
WHERE created = convert(char(10),GETDATE(),103)

--Delete temporary tables and all data held--

DROP TABLE #supporter_hold
DROP TABLE #potential_MAJR