no return from stored procedure
-
I have a stored proc that creates a temp table then is supposed to return the table with a select statement
It works in query analyzer
But after creating the procedure and running it from an access project it says no results where returned
this is the code copied from the stored procedure
any help would be great
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure dbo.SPDenGrads
(
@TimesToRun int
,@listofValues nvarchar (100)
)
as
set nocount on
-- declars local variables @n counter for string funtion
--@x is out put of string funtion to DensityGrad SP
declare @n int
,@x int
set @n = 0
--drops and creats temp table
--drop table #T
Create Table #Tdg
(place real
,den float
,Block_ID nvarchar (15)
)
--starts loop
while @TimesToRun > 0
begin
-- taks out put of string funtion
set @x = (dbo.udf_GetValueFromList( @listOfValues , @n ))
--inserts the output of DensityGrad SP to temp table
Insert #Tdg (place ,den ,block_ID)
execute dbo.DensityGrad @x
--moves up counters
set @TimesToRun = @TimesToRun - 1
set @n = @n + 1
--stops while loop
end
--outputs data from temp table
select place
,den
,Block_ID
from #Tdg
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Cory McRae
-
RIght off I don't see anything specific. However try removing the return at the end as it isn't needed for your case and see if maybe that is someho related???
Also, have you watched it with Profiler while running to see if maybe anything unsual such you trying to use the temp table name somewhere else in the other SP. I did that once to myself and never got data because in my internal most SP I used the same temp tabel name it checked to drop and ended up dropping the outer one. Ran fine then dropped the inner one but I neevr got an error. That is vaguely what caused it and there was a bit more to how it got by but not knowing what is in the other SP I am only guessing.
-
I ran into a similar problem with a stored proc being run from my asp.net application. Like yours, it worked in query analyzer and returned results...but nothing was displayed in my datagrid. As it turned out...it was a permissions problem. I am using sql authentication and it turned out that I forgot to grant EXECUTE privileges to the stored proc for this sql user. The weird thing, to me, was that no error or message indicating that the user didn't have rights to this stored proc. Not sure if your problem is the same...but thought I'd toss this out.
-douglas
-
I had the same thing happen to me in regular ASP on Fri. Worked OK in QA, error in ASP page was that the recordset (thru ADO) wasn't being opened. checking "rs.EOF" returned an error. Turned out that I had chopped out the SET NOCOUNT ON. Once I put that back it worked fine. I see you have it, might want to comment it out and give it a shot. BTW, it doesn't look like you ever do drop your temp table...
-
Thanks for all the replies
I tried getting rid of the return statement same results
I am using windows permissions and it is a local SQL server so permissions should not be a problem
I have tried running it with “set nocount on” in and out same results
Excuse my lack of knowledge but I am not sure how to use some of the trouble shooting methods used “checking "rs.EOF" returned an error”
Also this is the only temp table for all the stored procedures that are called
I have tried using ##tdg and that did not help either.
Are there any other ideas I am trying to run it from an access 2000 project on the same physical computer that the SQL server is on. That same Access project runs other stored procedures fine.
-
I suggest you to simplify the SP to isolate the problem. Just create a physical table with same column names and fill it with some dummy data to see whether it works with Access. Depending on the results we can actually start trouble shooting this in a different way.
If it's working fine I would like to see The Exact SQL called by the Access App (Profiler output).
I just want to see the parameter values you have passed in with Access app.
-
I broke it down and this is what I found but still don’t know why it is not working
If I create a SP like this
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure test1
as
set nocount on
Create Table #Tdg
(place real
,den float
,Block_ID nvarchar (15)
)
Insert #Tdg
values (1 , 3.22,999999)
select * from #tdg
return
every thing works fine I get the table with one recod returnd
if I create a SP like this
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure test1
as
set nocount on
Create Table #Tdg
(place real
,den float
,Block_ID nvarchar (15)
)
Insert #Tdg
execute dbo.DensityGrad '217989'
select * from #tdg
I get the same MSG as before the SP completed successfully but returned no results
Both work fine in query analyzer and the SP I call here I can run in the access project by itself and it works fine
Would it matter if the return table from the nested stored procedure had the same column names as the temp table?
All of the data types line up I checked that out all ready
Cory Lee McRae
-
does dbo.DensityGrad have SET NOCOUNT On ?
-
dbo.densityGrad did not have nocount on but it does now and same results (Does not return any records). Thanks for all the pointers I think we will get this eventually
Cory Lee McRae
-
Thanks for the comments I would have liked to solve this problem but dead lines being what they are I just got rid of the nested SP and made it one big confusing SP it works but not the easiest to read. Any way I hope some one figures out how to make this work it might be handy to use from time to time
Cory Lee McRae
-
Hi,
Just wanted to let you know, that whenever a SP that creates a table, is executed via an Access project. The SP always states that "no rows were returned" when in actuallity, it worked just fine.
Good luck,
Karen
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply