Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809

    Question Unanswered: vba ado sql2K QUESTION

    I posted this in another forum, but thought someone here might be able to help....

    I have some code that works just fine EXCEPT when I call a certain stored procedure. When I call the "special" sp, the first time I reference the result set I get an error "Operation is not allowed when the object is closed." source "ADODB.Recordset". I can substitute sp_who3 for the "special" sp and everything works!

    I am stumped as to what the problem could be or what to do next. Any help would be appreciated!

    The sub is attached
    Attached Files Attached Files
    Last edited by Paul Young; 03-18-03 at 14:18.
    Paul Young
    (Knowledge is power! Get some!)

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: vba ado sql2K QUESTION

    Paul,

    Could you post the SQL for the "special" sp? Also, in your code, you reference the sp as "dbo.ap_GetMarketData_AllPhenomenon". Did you mean "ap" or "sp"?

    Regards,

    Hugh Scott

    Originally posted by Paul Young
    I posted this in another forum, but thought someone here might be able to help....

    I have some code that works just fine EXCEPT when I call a certain stored procedure. When I call the "special" sp, the first time I reference the result set I get an error "Operation is not allowed when the object is closed." source "ADODB.Recordset". I can substitute sp_who3 for the "special" sp and everything works!

    I am stumped as to what the problem could be or what to do next. Any help would be appreciated!

    The sub is attached

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    "dbo.ap_GetMarketData_AllPhenomenon" is correct.

    The SPs are attached.
    Attached Files Attached Files
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Paul,

    I'm afraid I am not going to be of very much help. The checklist that I ran through:

    1. Correct sp name
    2. Correct parameters
    3. Command is set to correct type
    4. Command has an active connection

    Some additional things that I would try to check:

    1. Permissions (under who's context is the code running?)
    2. Ownership: the code references dbo.ap_GetMarketData_AllPhenomenon; make sure that the sp exists in that owner's space (the DDL to create the sp does not contain 'dbo.'.
    3. Connection time out; verify that the query runs within the time limit you specified (60 seconds).

    Other than those items, the things that I normally look for looked okay. Sorry, I wish I could be of more assistance.

    Regards,

    Hugh Scott

    Originally posted by Paul Young
    "dbo.ap_GetMarketData_AllPhenomenon" is correct.

    The SPs are attached.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Hugh,

    Thanks for the suggestions but I went throught all of them prior to posting.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    If anyone is interested the answer can be found at:

    http://support.microsoft.com/default...b;en-us;235340
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    I didn't read the ms-knowledgbase-article Paul provided but I have had this problem when I'm using a recordset in my VB-code and I have some debugging print/select-statements in my stored procedure. It took me ages to solve that problem, but eventually when I removed those prints/selects it all worked great...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    read the article, that wasn't the problem!
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Paul,

    Do you have any hair left at this point? I know that if I go bald the reason will be trying to debug code.

    I don't think this next thought is the solution to this issue, but perhaps it will lead you somewhere (and hopefully not down a rat hole).

    I recently ran into an issue with trying to use a local cursor on a recordset that I had opened. It eventually boiled down to a SQL restriction -- I was not allowed to use a local cursor when trying to run a query (or stored procedure) that used a full-text search criteria (ie, when the query or stored proc used CONTAINS). I can't remember at this point whether or not the queries you were using had an FT or not.

    Like I said, I don't think this is the answer, but perhaps it will lead you somewhere.

    Regards,

    Hugh

    Originally posted by Paul Young
    read the article, that wasn't the problem!

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Hugh, I resolved the problem, btw I am bald so pulling hair isn't an option, my post on 3/20 @ 12:17 was the fix.

    Basically I issues a "SET NOCOUNT ON" when I opened the ADO resultset.

    I do this in all stored procedures and have this set when I use Query Analyser or OSQL but some how I was returning a row count somewhere causeing problems.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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