VB,STORED PROC, COM, DTC QUESTION?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: VB,STORED PROC, COM, DTC QUESTION?

Hybrid View

  1. #1
    melvin ng Guest

    VB,STORED PROC, COM, DTC QUESTION?


    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.


  2. #2
    Ted McNeal Guest

    Re: VB,STORED PROC, COM, DTC QUESTION?


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



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center