April 5, 2004 at 10:45 am
This is a copy of a post I did in the T SQL group I thought since it also has to do with access 2000 I would post it hear as well any ideas would be helpful
Thanks
Cory McRae
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 #Tdg
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
April 6, 2004 at 6:20 am
hoe do u call this stored procedure in Access Data project ?
Alamir Mohamed
Alamir_mohamed@yahoo.com
April 7, 2004 at 8:49 am
I select it from the stored procedure folder and run it. It asks me for the two parameters then returns a message. That says the stored procedure ran successfully but did not return any records. If I remove the call to the nested stored procedure it works.
Cory McRae
April 8, 2004 at 12:19 am
I am not an expert in this, but maybe these thoughts will help...
Access projects sometimes have issues with "set nocount," which controls whether rowcounts are sent to access from the the FIRST executable statement in your SP. If rowcounts are not sent to Access (Nocount ON), then (I think) Access generates an ADO recordset object based upon the LAST executable statement in your SP.
If NoCount is OFF and the first executable statement returns a count of 0 rows, Access will not fill a recordset for you.
Therefore, I wonder if a "set nocount off" (or the absence of a "set nocount on") statement in your nested SP would be messing things up. I really don't know how it affects nested SPs.
Also, perhaps playing with the position of this statement (maybe place it right before your desired SELECT statement, and after a GO) would help in tricking Access into generating a recordset from the last SELECT. Just a guess, really. The whole thing with Access and NOCOUNT is nearly undocumented.
April 8, 2004 at 9:23 am
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
April 12, 2004 at 5:01 pm
It could be the SET NO COUNT thing, but I am sure we have come across problems with accessing temp tables with the # notation. I can't remember what we did about it though, if we found a solution or just gave up. Not much help sorry.
HTH
Chris
April 27, 2004 at 7:21 am
Ive had the same issue with Access 2000 and Query Analyzer.
I have used the SET NOCOUNT ON statement as suggested and it has solved the issue.
Thanks for solving my 2 Day Headache!!!!!
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply