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

Discussion in 'ASP General' started by Shaun Stuart, Sep 23, 2004.

  1. Shaun Stuart

    Shaun Stuart Guest

    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,adParamInput,8,locationchoice)
    oCmd.Parameters.Append
    oCmd.CreateParameter("@method",adVarChar,adParamInput,8,frtchoicecode)
    oCmd.Parameters.Append
    oCmd.CreateParameter("@zip",adVarChar,adParamInput,40,zipcode)

    oCmd.Parameters.Append
    oCmd.CreateParameter("@region",adVarChar,adParamOutput,8,0)
    oCmd.Parameters.Append
    oCmd.CreateParameter("@rtn_code",adInteger,adParamOutput,,0)
    oCmd.Parameters.Append
    oCmd.CreateParameter("@rtn_msg",adVarChar,adParamOutput,75,0)

    oCmd.Execute, ,adExecuteNoRecords

    region = oCmd.Parameters("@region")
    rtn_code = oCmd.Parameters("@rtn_code")
    rtn_msg = oCmd.Parameters("@rtn_msg")
    Shaun Stuart, Sep 23, 2004
    #1
    1. Advertising

  2. 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:
    > 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,adParamInput,8,locationchoice)
    > oCmd.Parameters.Append
    > oCmd.CreateParameter("@method",adVarChar,adParamInput,8,frtchoicecode)
    > oCmd.Parameters.Append
    > oCmd.CreateParameter("@zip",adVarChar,adParamInput,40,zipcode)
    >
    > oCmd.Parameters.Append
    > oCmd.CreateParameter("@region",adVarChar,adParamOutput,8,0)
    > oCmd.Parameters.Append
    > oCmd.CreateParameter("@rtn_code",adInteger,adParamOutput,,0)
    > oCmd.Parameters.Append
    > oCmd.CreateParameter("@rtn_msg",adVarChar,adParamOutput,75,0)
    >
    > oCmd.Execute, ,adExecuteNoRecords
    >
    > region = oCmd.Parameters("@region")
    > rtn_code = oCmd.Parameters("@rtn_code")
    > rtn_msg = oCmd.Parameters("@rtn_msg")


    --
    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 Barrows [MVP], Sep 23, 2004
    #2
    1. Advertising

  3. The following is quoted from the article Windows 2000 Web Server Best
    Practices for High Availability at
    http://www.microsoft.com/technet/pr...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:...
    > 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,adParamInput,8,locationchoice)
    > oCmd.Parameters.Append
    > oCmd.CreateParameter("@method",adVarChar,adParamInput,8,frtchoicecode)
    > oCmd.Parameters.Append
    > oCmd.CreateParameter("@zip",adVarChar,adParamInput,40,zipcode)
    >
    > oCmd.Parameters.Append
    > oCmd.CreateParameter("@region",adVarChar,adParamOutput,8,0)
    > oCmd.Parameters.Append
    > oCmd.CreateParameter("@rtn_code",adInteger,adParamOutput,,0)
    > oCmd.Parameters.Append
    > oCmd.CreateParameter("@rtn_msg",adVarChar,adParamOutput,75,0)
    >
    > oCmd.Execute, ,adExecuteNoRecords
    >
    > region = oCmd.Parameters("@region")
    > rtn_code = oCmd.Parameters("@rtn_code")
    > rtn_msg = oCmd.Parameters("@rtn_msg")
    >
    >
    Michael D. Long, Sep 24, 2004
    #3
  4. Michael D. Long wrote:
    > The following is quoted from the article Windows 2000 Web Server Best
    > Practices for High Availability at
    > http://www.microsoft.com/technet/pr...technologies/iis/deploy/rollout/websrvbp.mspx
    >
    > "Always use Server.CreateObject.
    > Using Server.CreateObject allows ASP to track the object instance. The


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


    http://www.google.com/groups?hl=en&...bject%20Egbert&safe=images&ie=UTF-8&lr=&hl=en

    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 Barrows [MVP], Sep 24, 2004
    #4
  5. Shaun Stuart

    Shaun Stuart Guest

    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,adParamOutput,,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]" <> wrote in message
    news:...
    > 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:
    > > 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,adParamInput,8,locationchoice)
    > > oCmd.Parameters.Append
    > > oCmd.CreateParameter("@method",adVarChar,adParamInput,8,frtchoicecode)
    > > oCmd.Parameters.Append
    > > oCmd.CreateParameter("@zip",adVarChar,adParamInput,40,zipcode)
    > >
    > > oCmd.Parameters.Append
    > > oCmd.CreateParameter("@region",adVarChar,adParamOutput,8,0)
    > > oCmd.Parameters.Append
    > > oCmd.CreateParameter("@rtn_code",adInteger,adParamOutput,,0)
    > > oCmd.Parameters.Append
    > > oCmd.CreateParameter("@rtn_msg",adVarChar,adParamOutput,75,0)
    > >
    > > oCmd.Execute, ,adExecuteNoRecords
    > >
    > > region = oCmd.Parameters("@region")
    > > rtn_code = oCmd.Parameters("@rtn_code")
    > > rtn_msg = oCmd.Parameters("@rtn_msg")

    >
    > --
    > 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"
    >
    >
    Shaun Stuart, Sep 24, 2004
    #5
  6. Shaun Stuart wrote:
    > 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:
    >


    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 Barrows [MVP], Sep 24, 2004
    #6
  7. Shaun Stuart

    Shaun Stuart Guest

    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]" <> wrote in message
    news:...
    > Shaun Stuart wrote:
    > > 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:
    > >

    >
    > 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"
    Shaun Stuart, Sep 27, 2004
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dinesh Upare

    MTS and .NET

    Dinesh Upare, Aug 13, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    2,464
    Manoj G [MVP]
    Aug 19, 2003
  2. Raghunath
    Replies:
    0
    Views:
    473
    Raghunath
    Sep 15, 2003
  3. Dinesh Upare

    Need Help, MTS component in ASP.NET

    Dinesh Upare, Aug 12, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    309
    Natty Gur
    Aug 13, 2003
  4. Mike P
    Replies:
    0
    Views:
    3,276
    Mike P
    Jun 19, 2006
  5. Dave
    Replies:
    1
    Views:
    235
    =?ISO-8859-1?Q?Arne_Vajh=F8j?=
    Oct 14, 2006
Loading...

Share This Page