no results from stored procedure in access 2000 project

  •  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

  • hoe do u call this stored procedure in Access Data project ?


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • 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

     

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

  • 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

  • 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

  • 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