not receiving records in ADP

A

Ashfaq Parkar

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

here is the sp
------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE dbo.Receivable_Aging_InDays_2

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

AS

--SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF



--**********************************************************
IF @AgingDate IS NULL SET @AgingDate = CONVERT(DATETIME, CONVERT(CHAR(10),
GETDATE(), 101))
--SET @AgingDate = '07/31/2006'

--**********************************************************
--IF ((SELECT object_id('tempdb..#tempAIOI')) IS NOT NULL) DROP TABLE
#tempAIOI

CREATE 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
WHERE

(tblAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
AND (tblAIOI.strAcct = @Account OR @Account IS NULL)
AND (tblAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)

-- tblAIOI.strVouchDate <= @AgingDate

--**********************************************************
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strAcctTitle,
#tempAIOI.strSubCode,
#tempAIOI.strSubCodeTitle,

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

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

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

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

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

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

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

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

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

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

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

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

FROM
#tempAIOI
JOIN (
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef,
Sum(#tempAIOI.curAmount) AS [OSvalue]
FROM
#tempAIOI
-- WHERE
-- #tempAIOI.strVouchDate < '08/01/2008'
-- #tempAIOI.strVouchDate <= @AgingDate
GROUP BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef
HAVING
NOT Sum(#tempAIOI.curAmount) = 0

) AS tblOSItems
ON
#tempAIOI.strCo = tblOSItems.strCO
AND #tempAIOI.strAcct = tblOSItems.strAcct
AND #tempAIOI.strSubCode = tblOSItems.strSubCode
AND #tempAIOI.strRef = tblOSItems.strRef
/*
WHERE
(#tempAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
-- AND (left(strAcct,3) = @Account OR @Account IS NULL)
AND (#tempAIOI.strAcct = @Account OR @Account IS NULL)
AND (#tempAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)
*/

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

ORDER BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
-- #tempAIOI.strAcctTitle,
#tempAIOI.strSubCode
-- #tempAIOI.strSubCodeTitle,
-- #tempAIOI.strVouchDate





--SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
P

Paul Conroy

Do you the necessary SQL table permissions when running this query from
Access ?

Ashfaq Parkar said:
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

here is the sp
------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE dbo.Receivable_Aging_InDays_2

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

AS

--SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF



--**********************************************************
IF @AgingDate IS NULL SET @AgingDate = CONVERT(DATETIME, CONVERT(CHAR(10),
GETDATE(), 101))
--SET @AgingDate = '07/31/2006'

--**********************************************************
--IF ((SELECT object_id('tempdb..#tempAIOI')) IS NOT NULL) DROP TABLE
#tempAIOI

CREATE 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
WHERE

(tblAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
AND (tblAIOI.strAcct = @Account OR @Account IS NULL)
AND (tblAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)

-- tblAIOI.strVouchDate <= @AgingDate

--**********************************************************
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strAcctTitle,
#tempAIOI.strSubCode,
#tempAIOI.strSubCodeTitle,

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

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

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

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

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

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

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

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

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

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

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

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

FROM
#tempAIOI
JOIN (
SELECT
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef,
Sum(#tempAIOI.curAmount) AS [OSvalue]
FROM
#tempAIOI
-- WHERE
-- #tempAIOI.strVouchDate < '08/01/2008'
-- #tempAIOI.strVouchDate <= @AgingDate
GROUP BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
#tempAIOI.strSubCode,
#tempAIOI.strRef
HAVING
NOT Sum(#tempAIOI.curAmount) = 0

) AS tblOSItems
ON
#tempAIOI.strCo = tblOSItems.strCO
AND #tempAIOI.strAcct = tblOSItems.strAcct
AND #tempAIOI.strSubCode = tblOSItems.strSubCode
AND #tempAIOI.strRef = tblOSItems.strRef
/*
WHERE
(#tempAIOI.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
-- AND (left(strAcct,3) = @Account OR @Account IS NULL)
AND (#tempAIOI.strAcct = @Account OR @Account IS NULL)
AND (#tempAIOI.strSubCode = @SubCode OR @SubCode IS NULL)
AND (tblAIOI.strVouchDate <= @AgingDate OR @AgingDate IS NULL)
*/

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

ORDER BY
#tempAIOI.strCo,
#tempAIOI.strAcct,
-- #tempAIOI.strAcctTitle,
#tempAIOI.strSubCode
-- #tempAIOI.strSubCodeTitle,
-- #tempAIOI.strVouchDate





--SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

not receiving records in ADP 3
not receiving records in ADP 2
Stored procedure works in Query Analyser, not ADP 3
Numaric to Inword in excel by VB 1
The procedure executed successfully but did not return records 3
Using ADP Subreports with Stored Procedures with Parameters 3
create calendar event using external data 1
attaching an event to an email 1

Top