.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.CreateOb ject("ADODB.Com mand")
oCmd.ActiveConn ection = Application("on acctEpi_Connect ionString")
oCmd.CommandTex t = "CPREP_GetRegio n"
oCmd.CommandTyp e = adCmdStoredProc

oCmd.Parameters .Append
oCmd.CreatePara meter("@locatio n",adVarChar,ad ParamInput,8,lo cationchoice)
oCmd.Parameters .Append
oCmd.CreatePara meter("@method" ,adVarChar,adPa ramInput,8,frtc hoicecode)
oCmd.Parameters .Append
oCmd.CreatePara meter("@zip",ad VarChar,adParam Input,40,zipcod e)

oCmd.Parameters .Append
oCmd.CreatePara meter("@region" ,adVarChar,adPa ramOutput,8,0)
oCmd.Parameters .Append
oCmd.CreatePara meter("@rtn_cod e",adInteger,ad ParamOutput,,0)
oCmd.Parameters .Append
oCmd.CreatePara meter("@rtn_msg ",adVarChar,adP aramOutput,75,0 )

oCmd.Execute, ,adExecuteNoRec ords

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

[Bob Barrows [MVP]]

Anytime you use

Server.CreateOb ject

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.connect ion")
cn.open Application("on acctEpi_Connect ionString")
Set rs=cn.execute(s SQL,,1)

'if the procedure returned records, then process them here.
'then
Set rs = rs.NextRecordse t
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.CreateOb ject("ADODB.Com mand")
oCmd.ActiveConn ection = Application("on acctEpi_Connect ionString")
oCmd.CommandTex t = "CPREP_GetRegio n"
oCmd.CommandTyp e = adCmdStoredProc

oCmd.Parameters .Append
oCmd.CreatePara meter("@locatio n",adVarChar,ad ParamInput,8,lo cationchoice)
oCmd.Parameters .Append
oCmd.CreatePara meter("@method" ,adVarChar,adPa ramInput,8,frtc hoicecode)
oCmd.Parameters .Append
oCmd.CreatePara meter("@zip",ad VarChar,adParam Input,40,zipcod e)

oCmd.Parameters .Append
oCmd.CreatePara meter("@region" ,adVarChar,adPa ramOutput,8,0)
oCmd.Parameters .Append
oCmd.CreatePara meter("@rtn_cod e",adInteger,ad ParamOutput,,0)
oCmd.Parameters .Append
oCmd.CreatePara meter("@rtn_msg ",adVarChar,adP aramOutput,75,0 )

oCmd.Execute, ,adExecuteNoRec ords

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"

[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/pro.../websrvbp.mspx

"Always use Server.CreateOb ject.
Using Server.CreateOb ject 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.CreateOb ject 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.CreateOb ject."

The use of Server.CreateOb ject 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-TproNsOoSfPtAtM rainingD-O-Tcom> wrote in message
news:ev******** *****@tk2msftng p13.phx.gbl...

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.CreateOb ject("ADODB.Com mand")
oCmd.ActiveConn ection = Application("on acctEpi_Connect ionString")
oCmd.CommandTex t = "CPREP_GetRegio n"
oCmd.CommandTyp e = adCmdStoredProc

oCmd.Parameters .Append
oCmd.CreatePara meter("@locatio n",adVarChar,ad ParamInput,8,lo cationchoice)
oCmd.Parameters .Append
oCmd.CreatePara meter("@method" ,adVarChar,adPa ramInput,8,frtc hoicecode)
oCmd.Parameters .Append
oCmd.CreatePara meter("@zip",ad VarChar,adParam Input,40,zipcod e)

oCmd.Parameters .Append
oCmd.CreatePara meter("@region" ,adVarChar,adPa ramOutput,8,0)
oCmd.Parameters .Append
oCmd.CreatePara meter("@rtn_cod e",adInteger,ad ParamOutput,,0)
oCmd.Parameters .Append
oCmd.CreatePara meter("@rtn_msg ",adVarChar,adP aramOutput,75,0 )

oCmd.Execute, ,adExecuteNoRec ords

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

[Bob Barrows [MVP]]

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/pro.../websrvbp.mspx

"Always use Server.CreateOb ject.
Using Server.CreateOb ject allows ASP to track the object instance. The

According to Egbert, this advice is a little outdated:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl
http://www.google.com/groups?hl=en&l...r%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"

[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("A DODB.Command")

oCmd.ActiveConn ection = Application("ep icor_Connection String")

oCmd.CommandTex t = "PT_wspLoyaltyL ookUpByBillTo"

oCmd.CommandTyp e = adCmdStoredProc

oCmd.Parameters .Append
oCmd.CreatePara meter("@BillTo" ,adChar,adParam Input,8,custnum )

oCmd.Parameters .Append
oCmd.CreatePara meter("@ReturnC ode",adInteger, adParamOutput,, 0)

set rs = oCmd.Execute

vReturnCode = oCmd.Parameters ("@ReturnCod e")

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]" <re******@NOyah oo.SPAMcom> wrote in message
news:OY******** ******@TK2MSFTN GP14.phx.gbl...

Anytime you use

Server.CreateOb ject

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.connect ion")
cn.open Application("on acctEpi_Connect ionString")
Set rs=cn.execute(s SQL,,1)

'if the procedure returned records, then process them here.
'then
Set rs = rs.NextRecordse t
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.CreateOb ject("ADODB.Com mand")
oCmd.ActiveConn ection = Application("on acctEpi_Connect ionString")
oCmd.CommandTex t = "CPREP_GetRegio n"
oCmd.CommandTyp e = adCmdStoredProc

oCmd.Parameters .Append
oCmd.CreatePara meter("@locatio n",adVarChar,ad ParamInput,8,lo cationchoice) oCmd.Parameters .Append
oCmd.CreatePara meter("@method" ,adVarChar,adPa ramInput,8,frtc hoicecode)
oCmd.Parameters .Append
oCmd.CreatePara meter("@zip",ad VarChar,adParam Input,40,zipcod e)

oCmd.Parameters .Append
oCmd.CreatePara meter("@region" ,adVarChar,adPa ramOutput,8,0)
oCmd.Parameters .Append
oCmd.CreatePara meter("@rtn_cod e",adInteger,ad ParamOutput,,0)
oCmd.Parameters .Append
oCmd.CreatePara meter("@rtn_msg ",adVarChar,adP aramOutput,75,0 )

oCmd.Execute, ,adExecuteNoRec ords

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

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]

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]" <re******@NOyah oo.SPAMcom> wrote in message
news:uy******** ******@tk2msftn gp13.phx.gbl...

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"