Home > Archive > ASP > September 2004 > .ASP, MTS transactions, and stored procedure I/O variables
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
.ASP, MTS transactions, and stored procedure I/O variables
|
|
| Shaun Stuart 2004-09-23, 8:56 pm |
| 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] 2004-09-23, 8:56 pm |
| 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"
| |
| Michael D. Long 2004-09-23, 8:56 pm |
|
The following is quoted from the article Windows 2000 Web Server Best
Practices for High Availability at
http://www.microsoft.com/technet/pr...t/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:evjHXEboEHA.556@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,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 2004-09-24, 8:55 pm |
| 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]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OYSam8boEHA.1776@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:
oCmd.CreateParameter("@location" ,adVarChar,adParamInput,8,locationchoice
)[color=darkred]
>
> --
> 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] 2004-09-24, 8:55 pm |
| 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 2004-09-27, 3:55 pm |
| 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]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uyvpyxnoEHA.1664@tk2msftngp13.phx.gbl...
> 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"
|
|
|
|
|