Stored Proc Hell

[Chris]

Hi,
I have a stored procedure that works perfectly in query
Analyser, but fails to return values in Access .adp
(Access2000 & SQL2000)

I have tracked down where the problem is occuring by
building the SP up 1 line at a time. See the following
code fragment:

CREATE PROCEDURE [dbo].[SP_T1]
(@DocumentID [Int] )
AS

DECLARE @RecCount int
SET @RecCount = 1
CREATE TABLE #TempDoc (DocumentID int)
CREATE TABLE #TempDoc1 (DocumentID int)
CREATE TABLE #TempDoc2 (DocumentID int)
----------------------------
--Other code would go here
-----------------------------

--Output of the SP
SELECT * FROM TBL_Document

This works correctly in both QA and .adp
I can select stored proc from the menu,refresh, double
click this SP,enter a parameter, and get the results of
the select statement. Exactly what I need!

If I add the next line to the stored proc so it reads:

CREATE PROCEDURE [dbo].[SP_T1]
(@DocumentID [Int] )
AS

DECLARE @RecCount int
SET @RecCount = 1
CREATE TABLE #TempDoc (DocumentID int)
CREATE TABLE #TempDoc1 (DocumentID int)
CREATE TABLE #TempDoc2 (DocumentID int)

--This extra line added
INSERT INTO #TempDoc1 VALUES (@DocumentID)

----------------------------
--Other code would go here
-----------------------------

--Output of the SP
SELECT * FROM TBL_Document


The SP continues to work in QA, but in Access I get
'The stored procedure executed correctly but did not
return records'

Does anyone know how I can get this to work correctly in
Access.

Thanks in Advance

Regards
Chris

 

[Chris]

Hi,
I have found a workaround using SQLDMO to call the SP,
but if anyone knows how to get this to work the normal way
I'd still like to know how it's done.

Cheers
Chris

 

[BJ Freeman]

what doe the Profiler say when you run it, from the ADP?
Try
AS
set nocount off

Also put a
Return 0
at the end.

Hi,
I have found a workaround using SQLDMO to call the SP,
but if anyone knows how to get this to work the normal way
I'd still like to know how it's done.

Cheers
Chris

-----Original Message-----
Hi,
I have a stored procedure that works perfectly in query
Analyser, but fails to return values in Access .adp
(Access2000 & SQL2000)

I have tracked down where the problem is occuring by
building the SP up 1 line at a time. See the following
code fragment:

CREATE PROCEDURE [dbo].[SP_T1]
(@DocumentID [Int] )
AS

DECLARE @RecCount int
SET @RecCount = 1
CREATE TABLE #TempDoc (DocumentID int)
CREATE TABLE #TempDoc1 (DocumentID int)
CREATE TABLE #TempDoc2 (DocumentID int)
----------------------------
--Other code would go here
-----------------------------

--Output of the SP
SELECT * FROM TBL_Document

This works correctly in both QA and .adp
I can select stored proc from the menu,refresh, double
click this SP,enter a parameter, and get the results of
the select statement. Exactly what I need!

If I add the next line to the stored proc so it reads:

CREATE PROCEDURE [dbo].[SP_T1]
(@DocumentID [Int] )
AS

DECLARE @RecCount int
SET @RecCount = 1
CREATE TABLE #TempDoc (DocumentID int)
CREATE TABLE #TempDoc1 (DocumentID int)
CREATE TABLE #TempDoc2 (DocumentID int)

--This extra line added
INSERT INTO #TempDoc1 VALUES (@DocumentID)

----------------------------
--Other code would go here
-----------------------------

--Output of the SP
SELECT * FROM TBL_Document


The SP continues to work in QA, but in Access I get
'The stored procedure executed correctly but did not
return records'

Does anyone know how I can get this to work correctly in
Access.

Thanks in Advance

Regards
Chris

.

 

[Vadim Rapp]

C> I have found a workaround using SQLDMO to call
C> the SP, but if anyone knows how to get this to work
C> the normal way I'd still like to know how it's
C> done.

put SET NOCOUNT ON in the beginning of the s.p.

 

[Chris]

Thanks Guys,
The SET NOCOUNT ON looks to have done the
trick.

Cheers
Chris

-----Original Message-----
Hi,
I have found a workaround using SQLDMO to call the SP,
but if anyone knows how to get this to work the normal way
I'd still like to know how it's done.

Cheers
Chris

-----Original Message-----
Hi,
I have a stored procedure that works perfectly in query
Analyser, but fails to return values in Access .adp
(Access2000 & SQL2000)

I have tracked down where the problem is occuring by
building the SP up 1 line at a time. See the following
code fragment:

CREATE PROCEDURE [dbo].[SP_T1]
(@DocumentID [Int] )
AS

DECLARE @RecCount int
SET @RecCount = 1
CREATE TABLE #TempDoc (DocumentID int)
CREATE TABLE #TempDoc1 (DocumentID int)
CREATE TABLE #TempDoc2 (DocumentID int)
----------------------------
--Other code would go here
-----------------------------

--Output of the SP
SELECT * FROM TBL_Document

This works correctly in both QA and .adp
I can select stored proc from the menu,refresh, double
click this SP,enter a parameter, and get the results of
the select statement. Exactly what I need!

If I add the next line to the stored proc so it reads:

CREATE PROCEDURE [dbo].[SP_T1]
(@DocumentID [Int] )
AS

DECLARE @RecCount int
SET @RecCount = 1
CREATE TABLE #TempDoc (DocumentID int)
CREATE TABLE #TempDoc1 (DocumentID int)
CREATE TABLE #TempDoc2 (DocumentID int)

--This extra line added
INSERT INTO #TempDoc1 VALUES (@DocumentID)

----------------------------
--Other code would go here
-----------------------------

--Output of the SP
SELECT * FROM TBL_Document


The SP continues to work in QA, but in Access I get
'The stored procedure executed correctly but did not
return records'

Does anyone know how I can get this to work correctly in
Access.

Thanks in Advance

Regards
Chris

.

.