Stored Proc Hell

  • Thread starter Thread starter Chris
  • Start date Start date
C

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


Chris said:
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

.
Click to expand...
Click to expand...
 
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.
 
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

.
Click to expand...
.
Click to expand...
 
Back
Top