By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,976 Members | 1,162 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,976 IT Pros & Developers. It's quick & easy.

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

P: n/a
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")
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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,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")


--
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"
Jul 19 '05 #2

P: n/a

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.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:ev*************@tk2msftngp13.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.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")

Jul 19 '05 #3

P: n/a
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.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&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"
Jul 19 '05 #4

P: n/a
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:OY**************@TK2MSFTNGP14.phx.gbl...
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,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")


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

Jul 19 '05 #5

P: n/a
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"
Jul 19 '05 #6

P: n/a
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******@NOyahoo.SPAMcom> wrote in message
news:uy**************@tk2msftngp13.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"

Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.