Professional Web Applications Themes

Stored Proc problem - ASP Database

Hi all, I have a stored proc which looks like this ---------------------------------------- create procedure PROC1 param1,param2,param3,... as set nocount on exec PROC2 param1,param2,param3,... select .... from TABLE1 where... return create procedure PROC2 param1,param2,param3,... as IF NOT EXISTS (select 1 from TABLE1 WHERE FLD1 = param1 AND FLD2 = param2 AND ...) BEGIN begin transaction insert record commit transaction end return(1) ----------------------------------------- when executing PROC1 from ADO I receive, an error: ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed. when I check the same procedure with the same input params in Query yzer everything is ok, I ...

  1. #1

    Default Stored Proc problem

    Hi all,

    I have a stored proc which looks like this

    ----------------------------------------
    create procedure PROC1 param1,param2,param3,...
    as
    set nocount on

    exec PROC2 param1,param2,param3,...

    select ....
    from TABLE1
    where...

    return


    create procedure PROC2 param1,param2,param3,...
    as

    IF NOT EXISTS (select 1 from TABLE1 WHERE FLD1 = param1 AND FLD2 =
    param2 AND ...)
    BEGIN

    begin transaction
    insert record
    commit transaction

    end

    return(1)


    -----------------------------------------

    when executing PROC1 from ADO I receive, an error:

    ADODB.Recordset error '800a0e78'
    Operation is not allowed when the object is closed.

    when I check the same procedure with the same input params in Query
    yzer
    everything is ok, I can see all records

    Thanks,
    mitko
    mitaka Guest

  2. #2

    Default Re: Stored Proc problem

    > when executing PROC1 from ADO I receive, an error:

    What happens when you add SET NOCOUNT ON to the beginning of each procedure?
    If [url]http://www.aspfaq.com/2307[/url] doesn't help, what does your code "in ADO" (I
    assume you meant ASP) look like?

    --
    Aaron Bertrand
    SQL Server MVP
    [url]http://www.aspfaq.com/[/url]


    Aaron Bertrand - MVP Guest

Similar Threads

  1. MySQL 5 + MX7 stored proc problem
    By jperkins99 in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 29th, 09:12 PM
  2. CF5 to MX7 Problem: Sybase Stored Proc's dont work right
    By DaveF67 in forum Coldfusion Database Access
    Replies: 3
    Last Post: September 28th, 06:37 PM
  3. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM
  4. Stored proc problem on 9.30.UC1
    By Weaver, Bill in forum Informix
    Replies: 1
    Last Post: July 9th, 02:35 PM
  5. stored proc and tcp/ip
    By Helmut Wöss in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: June 30th, 08:13 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139