Professional Web Applications Themes

.ASP, MTS transactions, and stored procedure I/O variables - ASP Database

I've got a webpage that calls some stored procedures with input variables. The procedures return recordsets and also some output variables. We're trying to get the values of the output variables. I've done this using the method I found in MSDN, as shown in the code below. The problem is that we believe doing it this way involves the use of the Microsoft Transaction Server (IIS transaction server). Is this true? (The SQL Server and IIS Server are on different machines and, I believe, a firewall separates them.) I can't see why this method would use MTS. The problem is ...

  1. #1

    Default .ASP, MTS transactions, and stored procedure I/O variables

    I've got a webpage that calls some stored procedures with input variables.
    The procedures return recordsets and also some output variables. We're
    trying to get the values of the output variables. I've done this using the
    method I found in MSDN, as shown in the code below. The problem is that we
    believe doing it this way involves the use of the Microsoft Transaction
    Server (IIS transaction server). Is this true? (The SQL Server and IIS
    Server are on different machines and, I believe, a firewall separates them.)

    I can't see why this method would use MTS. The problem is that in the past,
    we have had problems with MTS stopping for no reason, which causes the
    websites to not work. So my boss doesn't want to use any ASP code that
    relies on MTS. Not using the stored procedure output variables makes things
    way more complicated. So does this code really invoke MTS? And if it does,
    is there a way to obtain SP output variables without using MTS?

    I am running SQL Server 7.0 with the latest service pack and IIS version 4.
    We do have an IIS version 5 server we can use if that matters.

    Thanks,

    Shaun


    Set oCmd = Server.CreateObject("ADODB.Command")
    oCmd.ActiveConnection = Application("onacctEpi_ConnectionString")
    oCmd.CommandText = "CPREP_GetRegion"
    oCmd.CommandType = adCmdStoredProc

    oCmd.Parameters.Append
    oCmd.CreateParameter("location",adVarChar,adParam Input,8,locationchoice)
    oCmd.Parameters.Append
    oCmd.CreateParameter("method",adVarChar,adParamIn put,8,frtchoicecode)
    oCmd.Parameters.Append
    oCmd.CreateParameter("zip",adVarChar,adParamInput ,40,zipcode)

    oCmd.Parameters.Append
    oCmd.CreateParameter("region",adVarChar,adParamOu tput,8,0)
    oCmd.Parameters.Append
    oCmd.CreateParameter("rtn_code",adInteger,adParam Output,,0)
    oCmd.Parameters.Append
    oCmd.CreateParameter("rtn_msg",adVarChar,adParamO utput,75,0)

    oCmd.Execute, ,adExecuteNoRecords

    region = oCmd.Parameters("region")
    rtn_code = oCmd.Parameters("rtn_code")
    rtn_msg = oCmd.Parameters("rtn_msg")


    Shaun Guest

  2. #2

    Default Re: .ASP, MTS transactions, and stored procedure I/O variables

    Anytime you use

    Server.CreateObject

    MTS (COM+) is involved. If you do not want MTS to be involved, use
    CreateObject (without the "Server.").

    There is nothing intrinsic in the use of a Command object that causes MTS to
    be involved.

    There are two ways to get output variables from your stored procedures:

    Command object (recommended)
    See your code

    Dynamic SQL (not recommended)
    sSQL = "declare P1 int, P2 int; Set P1 = 0;" & _
    "Set P2=" & userinput & ";" & _
    "exec someproc P1 output, P2;" & _
    "SELECT P1 As OutParm"
    Set cn=createobject("adodb.connection")
    cn.open Application("onacctEpi_ConnectionString")
    Set rs=cn.execute(sSQL,,1)

    'if the procedure returned records, then process them here.
    'then
    Set rs = rs.NextRecordset
    outputparm = rs(0)


    HTH,
    Bob Barrows


    Shaun Stuart wrote: 

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  3. #3

    Default Re: .ASP, MTS transactions, and stored procedure I/O variables


    The following is quoted from the article Windows 2000 Web Server Best
    Practices for High Availability at
    http://www.microsoft.com/technet/prodtechnol/windows2000serv/technologies/iis/deploy/rollout/websrvbp.mspx

    "Always use Server.CreateObject.
    Using Server.CreateObject allows ASP to track the object instance. The
    server portion causes the object to be created in a transaction server
    package so resources are pooled. Using the CreateObject and GetObject
    functions in server-side scripts rather than Server.CreateObject does not
    allow for access to ASP built-in objects or participate in transactions.
    Using CreateObject and GetObject will attach each new object to a separate
    thread which will consume available system resources much faster than using
    the connection pooling features available by using Server.CreateObject."

    The use of Server.CreateObject will in no way involve your code in a
    distributed transaction. It sounds like your boss may have taken a stance
    based on a lack of experience with and understanding of the platform.

    Of course, I can't totally discount the old YMMV axiom - he could have
    stumbled on a bug that manifests in your environment.

    --
    Michael D. Long

    "Shaun Stuart" <sstuartA-TproNsOoSfPtAtMrainingD-O-Tcom> wrote in message
    news:phx.gbl... 


    Michael Guest

  4. #4

    Default Re: .ASP, MTS transactions, and stored procedure I/O variables

    Michael D. Long wrote: 

    According to Egbert, this advice is a little outdated:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&selm=erzdSEmNDHA.2128%40TK2MSFTNGP12.phx.gbl


    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=u1n2hvMtAHA.1436%40tkmsftngp03&rnum=2&pr ev=/groups%3Fas_q%3DServer.CreateObject%2520Egbert%26s afe%3Dimages%26ie%3DUTF-8%26lr%3D%26hl%3Den

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  5. #5

    Default Re: .ASP, MTS transactions, and stored procedure I/O variables

    Thanks. That fixed the problem.. I have another problem though.. My ASP
    programmer is telling me it is impossible for ADO to call a stored procedure
    and get back an output variable and a recordset at the same time. She even
    claims MSDN says so. I simply cannot believe this. I suggested the
    following:

    Set oCmd = CreateObject("ADODB.Command")

    oCmd.ActiveConnection = Application("epicor_ConnectionString")

    oCmd.CommandText = "PT_wspLoyaltyLookUpByBillTo"

    oCmd.CommandType = adCmdStoredProc

    oCmd.Parameters.Append
    oCmd.CreateParameter("BillTo",adChar,adParamInput ,8,custnum)

    oCmd.Parameters.Append
    oCmd.CreateParameter("ReturnCode",adInteger,adPar amOutput,,0)

    set rs = oCmd.Execute

    vReturnCode = oCmd.Parameters("ReturnCode")

    response.write vReturnCode & " <font color='green'> : vReturnCode - s/b 1
    </font> <br>"

    response.write rs(0) & " <font color='green'> :: <<< if this is 18184 it is
    good!</font> <br> <br>"



    I simply cannot believe it's impossible to get back an output parameter and
    a recordset.

    Shaun


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    to [/ref]
    oCmd.CreateParameter("location",adVarChar,adParam Input,8,locationchoice) 
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >[/ref]


    Shaun Guest

  6. #6

    Default Re: .ASP, MTS transactions, and stored procedure I/O variables

    Shaun Stuart wrote: 

    She's wrong. The only caveat is that all the records in the recordset must
    be sent to the client before the return and output parameter values are
    sent. In the case of a server-side recordset, you pretty much have to close
    the recordset before reading the output and return parameters. With a
    client-side cursor, they should be available right away.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  7. #7

    Default Re: .ASP, MTS transactions, and stored procedure I/O variables

    Ah! Now that you mention that, I do remember reading something along those
    lines in MSDN. That solved the problem. Thanks for your help!!

    Shaun


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    >
    > She's wrong. The only caveat is that all the records in the recordset must
    > be sent to the client before the return and output parameter values are
    > sent. In the case of a server-side recordset, you pretty much have to[/ref]
    close 


    Shaun Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  3. Stored Procedure Help
    By Jack in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 14th, 06:33 PM
  4. need help on a stored procedure
    By Helixpoint in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 8th, 01:26 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