VB,STORED PROC, COM, DTC QUESTION?

[melvin ng]

Dim StoredProc As New ADODB.Command
Set StoredProc.ActiveConnection = objConnect
StoredProc.CommandText = spCommand
StoredProc.Execute

1 Question: How to release the StoredProc memory or instance?
2 Question: How to disconnect the connection after objContext.setcomplete/
setabort

-------------------------------------------------------------

'-- Close Connection for Pooling --
objConnect.Close
Set objConnect = Nothing

'-- Inform MTS to reuse the object --
mobjContext.SetComplete

3. Question: "Set objConnect = Nothing" first or "SetComplete " first? How
to let MTS to release or control my instance?
All of the codes can run, but it's standard?

-------------------------------------------------------------

BEGIN DISTRIBUTED TRANSACTION vs BEGIN TRANSACTION
----------------------------- -----------------
4. Question: What different between the first and second one.
After I tried to use in StoredProc, both also can run under DTC Provided
service.

[Ted McNeal]

Melvin,
1: Release the stored proc by setting the object to nothing

2: I use the follow error handling in my components

Dim conn as ADODB.Connection
Dim cmd as ADODB.Command
Dim prm as ADODB.Parameter

On Error Goto PRoc_Err

'Do my call

GetObjectContext.SetComplete

Proc_Exit:
On error resume next
Set prm = Nothing
set cmd = Nothing
conn.Close
set conn = Nothing
Exit Sub

Proc_Err:
GetObjectContext.SetAbort
Err.Raise vbObjectError,...
Resume Proc_Exit
End Sub

Setting your vote is responsible for releasing transaction locks and the
action commit/rollback. You must still close the connection after your vote.

3: set your vote then destroy your objects.

4: If you call BEGIN TRANSACTION from a MTS/COM+ object that is in a transaction
it will run under DTC because your database transaction is already under
DTC control.
BEGIN DISTRIBUTED TRANSACTION is used by SQL Server to run a transaction
under the control of DTC as oppose to local native transaction support.
This is primarily for running transactions between your local database and
a database on a linked server. An example would be if SQL Server were running
a job that runs against a remote database.

Ted McNeal


"melvin ng" <melvin@myetutor.com> wrote:
>
>Dim StoredProc As New ADODB.Command
> Set StoredProc.ActiveConnection = objConnect
> StoredProc.CommandText = spCommand
> StoredProc.Execute
>
>1 Question: How to release the StoredProc memory or instance?
>2 Question: How to disconnect the connection after objContext.setcomplete/
>setabort
>
>-------------------------------------------------------------
>
> '-- Close Connection for Pooling --
> objConnect.Close
> Set objConnect = Nothing
>
> '-- Inform MTS to reuse the object --
> mobjContext.SetComplete
>
>3. Question: "Set objConnect = Nothing" first or "SetComplete " first? How
>to let MTS to release or control my instance?
>All of the codes can run, but it's standard?
>
>-------------------------------------------------------------
>
>BEGIN DISTRIBUTED TRANSACTION vs BEGIN TRANSACTION
>----------------------------- -----------------
>4. Question: What different between the first and second one.
>After I tried to use in StoredProc, both also can run under DTC Provided
>service.
>