not receiving records in ADP

G

Guest

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?

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)

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

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 (#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
 
N

Norman Yuan

Put "SET NOCOUNT ON" at the beginning of the SP.

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?

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)

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

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 (#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...
 
A

aaron.kempf

or used derived tables.

I dont think that real #temp tables should be used for anything; by
anyone

-Aaron



Norman said:
Put "SET NOCOUNT ON" at the beginning of the SP.

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?

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)

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

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 (#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...
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

Stored procedure works in Query Analyser, not ADP 3
Using ADP Subreports with Stored Procedures with Parameters 3
create calendar event using external data 1
VFP9 and SQL 2005 using VFP remote view Cannot insert the value NULL into column 0
Calling SQLServer Proc: If nulls passed, proc changes value to '0.0' 1
attaching an event to an email 1
vb.net 2003 V7 bug!? 3
"IF" test in SP evals to True and False at same time using DataAdapter.FillSchema() 1

Top