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

[Shaun Stuart]

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")

 

[Bob Barrows [MVP]]

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

 

[Michael D. Long]

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.

 

[Bob Barrows [MVP]]

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&[email protected]


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

Bob Barrows

 

[Shaun Stuart]

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]]

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

 

[Shaun Stuart]

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