Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58

    Unanswered: getting recordset via ado/sproc

    Trying to get output from sqlserver stored procedure into access recordset via ado.

    I have stored procedure with select statement that returns rows when executed from query analyser:

    CREATE procedure ps_tblMackVendors_Select_test
    AS
    set quoted_identifier on
    declare @strsql nvarchar(300)
    set @strsql = 'Select * from tblMackVendors’
    exec (@strsql)
    GO

    When I execute following bit of code from Access, the set rst = cmd.execute runs ok, but the rst.movefirst gets error 3704, “Operation is not allowed when the object is closed”.

    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = mcnnSQLDB
    cmd.CommandText = "ps_tblMackVendors_Select_test"
    cmd.CommandType = adCmdStoredProc
    Set rst = cmd.Execute
    rst.MoveFirst

    I have tested the stored procedure & the connection using an output parameter, doing a cmd.execute (without the rst..) & the value is retrieved by access ok

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    probably you need to rethink your app structure before you go too far with it. i bet your rst will successfully movefirst if you just do a straight select from your sp.

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    My question would be why do you have

    Code:
    CREATE procedure ps_tblMackVendors_Select_test
    AS
    set quoted_identifier on
    declare @strsql nvarchar(300)
    set @strsql = 'Select * from tblMackVendors’
    exec (@strsql)
    GO
    instead of,....

    Code:
    CREATE procedure ps_tblMackVendors_Select_test
    AS
    Select * from tblMackVendors
    GO
    ?????

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    well, i understand he's just experimenting, getting ready for the real stuff, but i'm saying "don't go that route, there is a sign there - no outlet!"

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    What happens if you open a recordset using a sql string instead of a stored procedure ? Also, you should debug your vb code and view the locals to see what is going on with the rst object.

  6. #6
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58
    my sp uses string for qry because I want to build the sql from values passed to the sp in the final version.
    I did just try it without the string for the sql & it worked - good idea. But it does work with the string when I call the sp from query analyzer. Why do you think there is a difference ?

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    multiple recordsets is one option,....

    using sp_executesql instead of exec might help.

    personally I'd really avoid this method of doing things though.... if you are going to use dynamic sql then use it in the application, not in a stored proc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •