i have created sp using one temporary table, which works excellent in SQL Query Analyzer. However

when I used the same sp in Access Data Project with :

1) Datasheet view, receiving the following message

“the stored procedure executed successfully but did not return records”

2) Report

“provider command for child rowset does not produce a rowset”

can anybody help me? i have attached sp.

Thanks and kind regards

My guess is that you are not dropping the temporary table and it is having issues with it. Instead of using a temporary table, try using a table variable. It is stored in memory and is just right for your needs here.




CREATE PROCEDURE dbo.Receivable_Aging_InDays_2

@CompanyNumber CHAR(2) = NULL,
@Account CHAR(4) = NULL,
@SubCode CHAR(5) = NULL,
@AgingDate DATETIME = NULL

AS


--************************************************** **************************

IF @AgingDate IS NULL SET @AgingDate = CONVERT(DATETIME, CONVERT(CHAR(10), GETDATE(), 101))

DECLARE TABLE @tempAIOI (strCo CHAR(2),
strAcct CHAR(4),
strAcctTitle CHAR(30),
strSubCode CHAR(5),
strSubCodeTitle CHAR(30),
strRef CHAR(6),
strTran CHAR(3),
strVouch CHAR(6),
strVouchDate DATETIME,
curAmount DEC(11,2))
INSERT INTO @tempAIOI
SELECT *
FROM tblAIOI ta
WHERE
(ta.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
AND (ta.strAcct = @Account OR @Account IS NULL)
AND (ta.strSubCode = @SubCode OR @SubCode IS NULL)
AND (ta.strVouchDate <= @AgingDate OR @AgingDate IS NULL)

SELECT
ta.strCo,
ta.strAcct,
ta.strAcctTitle,
ta.strSubCode,
ta.strSubCodeTitle,

ta.strRef,
ta.strTran,
ta.strVouch,
ta.strVouchDate,
@AgingDate AS [Aging_UpTo],

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 0 AND 30
THEN ta.curAmount
ELSE 0
END) AS 'D_0_30',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 31 AND 60
THEN ta.curAmount
ELSE 0
END) AS 'D_31_60',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 61 AND 90
THEN ta.curAmount
ELSE 0
END) AS 'D_61_90',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 91 AND 120
THEN ta.curAmount
ELSE 0
END) AS 'D_91_120',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 121 AND 150
THEN ta.curAmount
ELSE 0
END) AS 'D_121_150',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 151 AND 180
THEN ta.curAmount
ELSE 0
END) AS 'D_151_180',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 181 AND 270
THEN ta.curAmount
ELSE 0
END) AS 'D_181_270',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 271 AND 360
THEN ta.curAmount
ELSE 0
END) AS 'D_271_360',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 361 AND 540
THEN ta.curAmount
ELSE 0
END) AS 'D_361_540',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 541 AND 720
THEN ta.curAmount
ELSE 0
END) AS 'D_541_720',

SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) > 720
THEN ta.curAmount
ELSE 0
END) AS 'D_G_720'

FROM @tempAIOI ta
JOIN (
SELECT
ta2.strCo,
ta2.strAcct,
ta2.strSubCode,
ta2.strRef,
Sum(ta.curAmount) AS [OSvalue]
FROM #tempAIOI ta2
GROUP BY
ta2.strCo,
ta2.strAcct,
ta2.strSubCode,
ta2.strRef
HAVING
NOT Sum(ta2.curAmount) = 0

) AS tblOSItems
ON
ta.strCo = tblOSItems.strCO
AND ta.strAcct = tblOSItems.strAcct
AND ta.strSubCode = tblOSItems.strSubCode
AND ta.strRef = tblOSItems.strRef

GROUP BY
ta.strCo,
ta.strAcct,
ta.strAcctTitle,
ta.strSubCode,
ta.strSubCodeTitle,
ta.strRef,
ta.strTran,
ta.strVouch,
ta.strVouchDate

ORDER BY
ta.strCo,
ta.strAcct,
ta.strSubCode

hi
pdx man
thanks for ur mail.

i tried with table variables but unfortunately same messages.
can anybody help me? i m stuck.

thanks and kind regards
ashfaq.parkar

Add:
SET NOCOUNT ON

After the AS statement.

http://groups.google.com/group/microsoft.public.sqlserver.clients/browse_thread/thread/4afde934c9297196/6689144ed9a61c36%236689144ed9a61c36