We currently have S.P.'s that are already coded with sql transactions.
Is it possible to have a EXE that has an ADO transaction call an S.P.
that has a transaction.  No matter how hard I try I cant get it to
work.  I cant seem to find any concrete info that it can or can not be
done.

I can run the S.P. in analyzer no problem.
I can execute the S.P. from a command object w/o a transaction no
problem.

I can NOT call the S.P. from a command object with a transaction
I get the following error when I try it

"System.Data.SqlClient.SqlException: Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 1, current count = 0."


Subman

subman wrote:
Show quoteHide quoteSome code would probably help use figure out what's going on easier.

Also, since the proc has the transaction built into it, why do you need
to wrap the command in a transaction, are you calling multiple procs?

Chris

There are over 500 S.P.'s that have been written already.  Alot of
times we(.net devs)  use 1 to many of the sp's in a exe.  it would be
nice to be able to "wrap" the sp's in a ado transaction.

Subman

SP Follows

ALTER PROCEDURE dbo.proc_Bajio_SalesPosting

AS

SET NOCOUNT ON

--Declarations
DECLARE @_StatusString            VARCHAR(200)
DECLARE    @_ProcessTime             DATETIME
DECLARE @_WED                    DATETIME
DECLARE    @_StartTime             DATETIME
DECLARE    @_MyRowCount            INTEGER
DECLARE    @_MyError                INTEGER
DECLARE @_SalesPostingRunning    INTEGER
DECLARE @_TERST varchar(10)

--Start The Transaction
BEGIN TRANSACTION SalePosting

SELECT    @_StatusString = 'Trans count ' +
CONVERT(VARCHAR(50),@@TRANCOUNT)
PRINT    @_StatusString


-- Start logging progress
SELECT    @_StatusString = 'Started Stored
Procedure(proc_Bajio_SalesPosting) At - ' +
CONVERT(VARCHAR(50),GETDATE())
PRINT    @_StatusString
SELECT    @_ProcessTime = GETDATE()
SELECT    @_StartTime = @_ProcessTime

-- Verify all sales have been entered
CREATE TABLE #MissingStores
(FranchiseNum    INTEGER)

--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
   BEGIN
        SELECT    @_StatusString = 'STEP 10|ERROR! Creating #MissingStores
(Rolling Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
        PRINT    @_StatusString
        GOTO    ErrorHandler
    END
ELSE
    BEGIN
        SELECT    @_StatusString = 'STEP 10|Created #MissingStores - Total
Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
        PRINT    @_StatusString
        SELECT    @_ProcessTime = GETDATE()
    END

-- Get current WeekEndingDate
SET @_WED = (SELECT TOP 1 WeekEndingDate FROM dbo.WeekEndingDate)

--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
   BEGIN
        SELECT    @_StatusString = 'STEP 20|ERROR! Setting WED Variable (Rolling
Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
        PRINT    @_StatusString
        GOTO    ErrorHandler
    END
ELSE
    BEGIN
        SELECT    @_StatusString = 'STEP 20|Set WED Variable - Total Minutes - '
+
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
        PRINT    @_StatusString
        SELECT    @_ProcessTime = GETDATE()
    END

-- Check if the sales posting is already running
SELECT    @_SalesPostingRunning = AvailableInd
FROM    SubwayNet.dbo.ObjectGroup
WHERE    ObjectGroupID = 1 -- Sales Posting

--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
   BEGIN
        SELECT    @_StatusString = 'STEP 30|ERROR! Setting Sales Posting
Variable (Rolling Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
        PRINT    @_StatusString
        GOTO    ErrorHandler
    END
ELSE
    IF @_SalesPostingRunning <> 1
        BEGIN
            SELECT    @_StatusString = 'Sales Posting Currently Running (Rolling
Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
            PRINT    @_StatusString
            GOTO    ErrorHandler
        END
    ELSE
        BEGIN
            SELECT    @_StatusString = 'STEP 30|Set Sales Posting Variable - Total
Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
            PRINT    @_StatusString
            SELECT    @_ProcessTime = GETDATE()
        END

-- populate the temp table with all stores that are missing sales
INSERT    INTO #MissingStores
EXEC    proc_Bajio_Franchise_GetAllByMissingSales

--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
   BEGIN
        SELECT    @_StatusString = 'STEP 40|ERROR! Populating #MissingStores
(Rolling Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
        PRINT    @_StatusString
        GOTO    ErrorHandler
    END
ELSE
    IF @_MyRowCount > 0
        BEGIN
            SELECT    @_StatusString = 'Not All Stores Sent Sales (Rolling Back) -
Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
            PRINT    @_StatusString
            GOTO    ErrorHandler
        END
    ELSE
        BEGIN
            SELECT    @_StatusString = 'STEP 40|Inserted Into #MissingStores -
Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
            PRINT    @_StatusString
            SELECT    @_ProcessTime = GETDATE()
        END

COMMIT TRANSACTION SalePosting
RETURN 0

-- Error Handler Subroutine
ErrorHandler:
    BEGIN
            SELECT    @_StatusString = CONVERT(VARCHAR(50),@@TranCount)
            PRINT    @_StatusString
            IF @@TRANCOUNT = 1
                BEGIN
                    ROLLBACK TRANSACTION
                END
            SELECT    @_StatusString = CONVERT(VARCHAR(50),@@TranCount)
            PRINT    @_StatusString
        RETURN -1
    END





Test Code Follows


Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As
Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form
Designer
    'It can be modified using the Windows Form Designer.
    'Do not modify it using the code editor.
    Friend WithEvents SqlConnection1 As
System.Data.SqlClient.SqlConnection
    Friend WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents RichTextBox1 As System.Windows.Forms.RichTextBox
    Friend WithEvents Button2 As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
        Me.Button1 = New System.Windows.Forms.Button
        Me.RichTextBox1 = New System.Windows.Forms.RichTextBox
        Me.Button2 = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation
id=""IS-WARAKOMSKI"";packet size=4096;integrated security=SSPI;data
sou" & _
        "rce=""SPINOZA\SPINOZA2000"";persist security info=True;initial
catalog=Bajio"
        '
        'SqlCommand1
        '
        Me.SqlCommand1.CommandText =
"dbo.[proc_Bajio_SalesPosting03022006]"
        Me.SqlCommand1.CommandType =
System.Data.CommandType.StoredProcedure
        Me.SqlCommand1.Connection = Me.SqlConnection1
        Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(8, 16)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Button1"
        '
        'RichTextBox1
        '
        Me.RichTextBox1.Location = New System.Drawing.Point(8, 64)
        Me.RichTextBox1.Name = "RichTextBox1"
        Me.RichTextBox1.Size = New System.Drawing.Size(904, 96)
        Me.RichTextBox1.TabIndex = 1
        Me.RichTextBox1.Text = ""
        '
        'Button2
        '
        Me.Button2.Location = New System.Drawing.Point(776, 192)
        Me.Button2.Name = "Button2"
        Me.Button2.TabIndex = 2
        Me.Button2.Text = "Button2"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(912, 266)
        Me.Controls.Add(Me.Button2)
        Me.Controls.Add(Me.RichTextBox1)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
        Dim tnn As SqlTransaction = Nothing
        Dim strConnectionString = "data
source=devsql.subapp.subwaydai.com\Spinoza2000;initial
catalog=Bajio;user iD=Subs;password=sixears;packet size=4096"
        Try
            AddHandler SqlConnection1.InfoMessage, New
SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
            SqlConnection1.ConnectionString = strConnectionString
            SqlConnection1.Open()
            tnn = SqlConnection1.BeginTransaction()
            SqlCommand1.Transaction = tnn
            SqlCommand1.CommandText =
"dbo.proc_Bajio_SalesPosting03022006"
            SqlCommand1.ExecuteNonQuery()
            '            SqlCommand1.CommandText =
"dbo.proc_Bajio_SalesPosting03022006_dropme"
            '           SqlCommand1.ExecuteNonQuery()
            '            SqlHelper.ExecuteNonQuery(tnn,
SqlCommand1.CommandText, SqlHelper.enuTimeOut.Default, Nothing)

            tnn.Commit()
        Catch Ex As SqlException
            If ex.Number <> 266 Then
                tnn.Rollback()
            End If


        Catch Ex As Exception
            tnn.Rollback()

        Finally
            If Not tnn Is Nothing Then tnn.Dispose()
            SqlConnection1.Close()
        End Try
    End Sub

    Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As
SqlInfoMessageEventArgs)
        Try
            Dim err As SqlError
            For Each err In args.Errors
                RichTextBox1.Text += ControlChars.CrLf & err.Message
            Next
        Catch ex As Exception

        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
        Dim strConn As String =
"Server=jalapeno;Database=Northwind;Integrated Security=True;"
        Dim cnn As New SqlConnection(strConn)
        Dim cmd As New SqlCommand
        cnn.Open()


        Dim trans As SqlTransaction = Nothing
        Try
            trans = cnn.BeginTransaction()

            ' @AccountNo (From Account)
            Dim paramFromAcc As New SqlParameter("@AccountNo",
SqlDbType.Char, 20)
            paramFromAcc.Value = "12345"

            ' @AccountNo (To Account)
            Dim paramToAcc As New SqlParameter("@AccountNo",
SqlDbType.Char, 20)
            paramToAcc.Value = "67890"

            ' @Money (Credit amount)
            Dim paramCreditAmount As New SqlParameter("@Amount",
SqlDbType.Money)
            paramCreditAmount.Value = 500

            ' @Money (Debit amount)
            Dim paramDebitAmount As New SqlParameter("@Amount",
SqlDbType.Money)
            paramDebitAmount.Value = 500

            With cmd
                .Connection = cnn
                .CommandType = CommandType.StoredProcedure
                .Transaction = trans
                .CommandText = "Debit"
                .Parameters.Add(paramFromAcc)
                .Parameters.Add(paramDebitAmount)
                .ExecuteNonQuery()
            End With

            With cmd
                .Connection = cnn
                .CommandType = CommandType.StoredProcedure
                .Transaction = trans
                .CommandText = "Credit"
                .Parameters.Add(paramToAcc)
                .Parameters.Add(paramCreditAmount)
                .ExecuteNonQuery()
            End With


            trans.Commit()
        Catch ex As Exception
            ' throw an exception
            trans.Rollback()
            Throw ex
        Finally
            If Not trans Is Nothing Then trans.Dispose()
        End Try
    End Sub
End Class

"VB (9) Rocks" slide show
VB.Net 2005 Treeview example required
Progressbar and treeview stops refreshing
Optimizing Repeated PictureBox.Paints
padding text field with blank in MS Access vs OLEDB Jet 4.0
Getting icon from form in dynamically loaded DLL
control collection
VB doesn't allow temp tables in Stored Procedures?
Parsing XML strings
Copy files and progressbar