Transaction In Packages; When Do They Commit?

Oct 31, 2007

Hello,

I am a SSIS developer from the Netherlands. I have a question about the use of a transaction in a package.
I have a Sequence container with some data flow tasks and some SQL tasks. The TransactionOption for this container is set to Required. The transactionOption for the tasks in the container are set to Supported. After this container I have put some other tasks (like a script task and an send mail task). This last 2 tasks have a TransactionOption set to Not Supported.
I have done this this way because if something goes wrong in the Sequence Container everthing in this container will be rollbacked. This work fine! But if one of the last 2 task goes wrong (the tasks after the container) everthing in the container is also rollbacked. I don't know why this is happening. Can you help me with this?

Thanks! Greetings, Pieter.

View 4 Replies

How Do I Make Use Of Begin Transaction And Commit Transaction In SSIS.

Jun 1, 2007

Hi

How do I make use of begin transaction and commit transaction in SSIS.

As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:

Hresult:0x80004005

descriptionyntax error or access violation.

its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.

Thanks,

Prashant

View 3 Replies View Related

BEGIN TRANSACTION And COMMIT TRANSACTION

Oct 11, 2000

I am executing a stored procedure something like this

Create Procedure TEST
@test1
@test2
AS
BeGIN TRANSACTION ONE
SELECT...
UPDATE..
....
....

....
TRUNCATE
etc and lot of Select,update and delete statements like this
.....
COMMIT TRANSACTION ONE

The Block of code which I have b/w BEGIN TRANSACTION AND COMMIT TRANSACTION ..Will it be rolled back
if it encounters any errors in the SELECT,UPPDATE,DELETE ..statements which I have with the transaction one.
IF not How do I roll back if it encounters any erros b/w the BEGIN TRANSACTION and END TRANSACTION.

Thanks
micky

View 1 Replies View Related

BEGIN TRANSACTION COMMIT TRANSACTION Help

Sep 24, 2007

I had thought that if any statement failed within a BEING TRANS .. COMMIT TRANS block, then all the statements would be rolled back. But I am seeing different behavior (SQL Server 2000 8.00.2039)

For instance, run these statements to set up a test:
--DROP TABLE testTable1
--DROP TABLE testTable2
CREATE TABLE testTable1 (f1 varchar(1))
CREATE TABLE testTable2 (f1 varchar(1))
CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1)
insert into testTable1(f1) values ('a')

So table testTable1 has a unique index on it..

Now try to run these statements:

--DELETE FROM testTable2
BEGIN TRANSACTION
insert into testTable1(f1) values ('a')
insert into testTable2(f1) values ('a')
COMMIT TRANSACTION

SELECT * FROM testTable2

..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?

View 8 Replies View Related

How To Commit The Transaction

Apr 14, 2008

hi friends,

Iam Executing the sp logic.suppose incase if any problem occurs inbetween execution(NO SPACE,communication failure,log full)
data is getting commited partially insteady of rollbacking entire transaction.

CREATE procedure RBI_Control_sp
as
begin

set nocount on
--Checking the count before truncating
exec fin_ods..count_sp

--Truncating the Table
exec fin_ods..trun_sp

--Data Transfer
exec fin_ods..RBI_Data_Transfer_sp

--Checking the count after Data transfer
exec fin_ods..count_sp

--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp

set nocount off
end

View 1 Replies View Related

Commit Transaction Logic

Oct 28, 2007

Hai, in Sql server 2000 database, i want to do the following..
from my UI, i will be updating one table(only 3 columns among the table columns), But the number of records will be around 2000 to 2500.
So every 200th record, i want to commit the transaction, so that i cannot lose the data..
Using query can i achieve this? or do i need to use the simple while loop logic.
Pls advise me

View 6 Replies View Related

BEGIN And COMMIT Transaction

Feb 28, 2008

Can help me?
I have to insert BEGIN and COMMIT Transaction in my stored procedure (call to a trigger)

Code Snippet

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE [dbo].[DETTAGLIO_TURNI_DIFENSORI]

(
@tipo_albo VARCHAR(50),
@data_inizio DATETIME,
@data_fine DATETIME,
@descrizione VARCHAR(50),
@idturno INT,
@n_dif INT
)

AS

-- DICHIARAZIONE VARIABILI...
DECLARE @dett_idturno as INT
DECLARE @totale_giorni as INT
DECLARE @tipo_albo_A as VARCHAR(9)
DECLARE @tipo_albo_B as VARCHAR(9)
DECLARE @data_odierna as DATETIME
DECLARE @tot_avvocati as INT
DECLARE @avv_giorno as INT
DECLARE @avv_giornoA as INT
DECLARE @avv_giornoB as INT
DECLARE @conta_giorni as INT
DECLARE @incremento_giorni as INT
DECLARE @incr_dif_giorno as INT
DECLARE @nuovo_idturno as INT
DECLARE @idalboturno as INT
DECLARE @old_turni as INT
DECLARE @tot_giorniA as INT
DECLARE @tot_giorniB as INT
DECLARE @conta_giorni_incremento as INT
DECLARE @conta_avvocati as INT
DECLARE @avv as INT
DECLARE @altri_turni AS INT
DECLARE @neg_giorni_incremento as int
-------------------------------------------------------------------------------------------------------

-- SET VARIABILI PER EVITARE PROBLEMI CON NULL o 0
SET @totale_giorni = 0
SET @tot_avvocati = 0
SET @avv_giorno = 0
SET @conta_giorni = 0
SET @incremento_giorni = 0
SET @incr_dif_giorno = 0
SET @idalboturno = 0
SET @old_turni = 0
--------------------------------------------------------------------------------------------------------

SET @nuovo_idturno = @idturno

BEGIN TRAN

-- conta i giorni
SELECT @totale_giorni = (DATEDIFF(dd, @data_inizio, @data_fine))+1

-- select da vista avvocati per tipo difensori selezionato.. (controllare anche data_fine_iscrizione?)

create table #Tmp
(
[ID_anagrafica] varchar(50)
)

IF @tipo_albo = 'DIFO'
BEGIN
SET @tipo_albo_A = 'DIFM'
SET @tipo_albo_B = 'CPT'

-- select per contare..

SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_ORDINARI

SELECT @tot_avvocati As tot_avvocati

-- creo tabella temporanea..
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_ORDINARI ORDER BY ID_ANAGRAFICA

END

IF @tipo_albo = 'DIFM'
BEGIN
SET @tipo_albo_A = 'DIFO'
SET @tipo_albo_B = 'CPT'

-- select per contare..
SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_MINORI

SELECT @tot_avvocati as tot_avvocati

-- creo tabella temporanea..
INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_MINORI ORDER BY ID_ANAGRAFICA
END

IF @tipo_albo = 'CPT'
BEGIN
SET @tipo_albo_A = 'DIFM'
SET @tipo_albo_B = 'DIFO'

-- select per contare..
SELECT @tot_avvocati = COUNT(*)
FROM VALBO_ISCRIZIONE_DIF_CPT

SELECT @tot_avvocati as tot_avvocati

INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_CPT ORDER BY ID_ANAGRAFICA
END

DECLARE @idanagrafica varchar(50)

-- GIORNI PER AVVOCATO
-- tot_giorni * n_dif_minimo / tot_avvocati = tot_giorni_avvocato (con intero successivo)
SET @incremento_giorni = ((@totale_giorni * @n_dif)/@tot_avvocati)
-- controllo se c'è resto..
IF ((@totale_giorni * @n_dif)%@tot_avvocati) <> 0
BEGIN
SET @incremento_giorni = @incremento_giorni + 1
END

-- ogni avvocato deve essere difensore per almeno 2 giorni di seguito..
-- quindi se l'incremento è minore di 2 deve essere uguale a 2
IF @incremento_giorni < '2'
BEGIN
SET @incremento_giorni = '2'
END

-- AVVOCATI AL GIORNO
-- numero variabile.. prendere in considerazione il primo intero e l'intero successivo..
SET @avv_giorno = (@incremento_giorni * @tot_avvocati)/@totale_giorni
SET @avv_giornoB = @avv_giorno

-- controllo il resto della divisione.. se <> 0 @avv_giornoB = @avv_giorno + 1..
-- altrimenti i due valori sono uguali..

IF ((@incremento_giorni * @tot_avvocati)%@totale_giorni) <> 0
BEGIN
SET @avv_giornoA = @avv_giorno + 1
END
ELSE
BEGIN
set @avv_giornoA = @avv_giorno
END

-- conteggi giorni totali difensori...
-- giorni con N difensori
SET @tot_giorniB = ((@avv_giornoB * @incremento_giorni)/@totale_giorni)
-- giorni con M difensori
SET @tot_giorniA = @totale_giorni - @tot_giorniB

declare @totale as int
declare @conta_inseriti as int
set @conta_inseriti = 0
set @totale = (@avv_giornoA * @tot_giorniA) + (@avv_giornoB * @tot_giorniB)

-- ciclo per totale dei giorni
SET @conta_giorni = 1
SET @conta_giorni_incremento = 0
SET @conta_avvocati = 0
WHILE @conta_giorni <= @totale_giorni BEGIN

-- ogni giorno @avv deve essere ZERO
SET @avv = 0

IF @conta_giorni <= @tot_giorniA
BEGIN
SET @avv_giorno = @avv_giornoA
END
ELSE
BEGIN
SET @avv_giorno = @avv_giornoB
END

-- ciclo per ogni giorno per totale di avvocati/giorno
SET @incr_dif_giorno = 0

WHILE (@incr_dif_giorno < @avv_giorno) AND EXISTS(SELECT TOP 1 ID_anagrafica FROM #Tmp) BEGIN

SET @conta_avvocati = @conta_avvocati + 1

SET @data_odierna = DATEADD(dd, (@conta_giorni-1), @data_inizio)

SET @neg_giorni_incremento = -1 * @conta_giorni_incremento

SET @old_turni = 0
SET @altri_turni = 0

SELECT TOP 1 @idanagrafica = ID_anagrafica from #Tmp
DELETE #Tmp WHERE ID_anagrafica = @idanagrafica

-- query che controlla i turni gi� assegnati per altre liste...

SELECT @old_turni = COUNT(*)
FROM Albo_Turno_Dettaglio CROSS JOIN
ALBO_TURNO
WHERE
Albo_Turno_Dettaglio.idalbo = @idanagrafica
AND
(
ALBO_TURNO.Tipo = @tipo_albo_A --- probabile problema con trigger
OR
ALBO_TURNO.Tipo = @tipo_albo_B
)
AND
(
Albo_Turno_Dettaglio.Data
BETWEEN
DATEADD(dd, (@neg_giorni_incremento + 1), @data_odierna)
AND
DATEADD(dd, (@incremento_giorni-@conta_giorni_incremento), @data_odierna)
)

SELECT @old_turni AS old_turni

-- (nel caso in cui il ciclo ricominci..) controllare che questo avvocato non abbia
-- gi� un set di giorni in questo turno...

-- passato il primo controllo.. deve passare anche questo..

SELECT @altri_turni = COUNT(*)
FROM Albo_Turno_Dettaglio
WHERE
Albo_Turno_Dettaglio.idalbo = @idanagrafica
AND
Albo_Turno_Dettaglio.idturno = @nuovo_idturno

SELECT @altri_turni AS altri_turni

IF @old_turni = NULL
BEGIN
SET @old_turni = 0
END

IF @ALTRI_TURNI = NULL
BEGIN
SET @ALTRI_TURNI = 0
END

IF (@old_turni = 0 AND (@altri_turni = 0 OR @altri_turni < (@incremento_giorni)))
BEGIN
-- se non ci sono turni sovraposti assegna il turno all'avvocato x N giorni (incremento_giorni)
-- seleziono il dettaglio con idturno max per aumentare di uno...
SET @idalboturno = 1
SET @dett_idturno = 1
SELECT @dett_idturno = MAX(idalboturno)
FROM Albo_Turno_Dettaglio

if (@dett_idturno) = null
begin
set @dett_idturno = 0
end

SET @idalboturno = @dett_idturno + 1

BEGIN TRAN

INSERT Albo_Turno_Dettaglio
(
idalboturno,
idalbo,
idturno,
data
)
VALUES
(
@idalboturno,
@idanagrafica, -- da cursore
@nuovo_idturno,
@data_odierna -- problemi inserimento data??
)

COMMIT
-- valorizza il numero degli avvocati del giorno + 1
SET @incr_dif_giorno = @incr_dif_giorno + 1
END
ELSE
BEGIN
-- non incrementare la variabile...
SET @incr_dif_giorno = @incr_dif_giorno
END

END -- END WHILE AVVOCATI PER GIORNI

SET @conta_giorni_incremento = (@conta_giorni_incremento + 1)
IF @conta_giorni_incremento < @incremento_giorni
BEGIN
--DEVO RIPOPOLARE LA TABELLA PERCHE' OGNI AVVOCATO DEVE AVERE X GIORNI..
-- cancello tutti i record e poi inserisco di nuovo..
DELETE FROM #Tmp
IF @tipo_albo = 'DIFO'
BEGIN

INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_ORDINARI ORDER BY ID_ANAGRAFICA
END

IF @tipo_albo = 'DIFM'
BEGIN

INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_MINORI ORDER BY ID_ANAGRAFICA
END

IF @tipo_albo = 'CPT'
BEGIN

INSERT INTO #Tmp
SELECT [ID_anagrafica]
FROM VALBO_ISCRIZIONE_DIF_CPT ORDER BY ID_ANAGRAFICA
END

END
ELSE
BEGIN
set @conta_giorni_incremento = 0
END

SET @conta_giorni = @conta_giorni + 1
END

DROP TABLE #TMP

COMMIT TRAN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

View 9 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

T-SQL (SS2K8) :: Add Record And Commit Transaction

Oct 6, 2014

I am simply inserting records in a table, But if record already present then i dont want add that record and display resultset as -1.If record is not present then i want to add that record and commit transaction. I am doing like below : below is sample, I am considering only two columns.

CREATE PROCEDURE [dbo].[Sproc__Save_Teacher_Details]
-- Add the parameters for the stored procedure here
@FacilitiDetailID int
,@SpecialityType varchar(50)
AS
BEGIN

[code]....

above code is working fine for new recordset but if record exists then it is giving two resultsets -1 and 1, but i want to display only -1.

View 4 Replies View Related

Transaction Commit Doesn't Work

Sep 27, 2007

Could somebody also help me on the issue: I am using OLE DB to setup database with SQL CE 3.1, what i need is to flush the buffer to database file before program exit, I am using DBPROPVAL_SSCE_TCM_FLUSH and Transaction commit, but they don't work for me, My code is here:

1. the code to do transaction commit

// Access Transaction Interface

hr = pIDBCreateCommand->QueryInterface(IID_ITransactionLocal, (void **) &pTransLocal);

ULONG lTransLevel;

// Start the transaction

hr = pTransLocal->StartTransaction( ISOLATIONLEVEL_READCOMMITTED, 0, NULL, &lTransLevel );

// Execute the command with paramters.

hr = pICommandText->Execute(NULL, IID_NULL, &params, &cRowsAffected, NULL);

if( FAILED( hr ) )

{

goto Exit;

}

// commit

hr = pTransLocal->Commit( FALSE, XACTTC_SYNC, 0 );

pTransLocal->Release();

2. the code to set up property:

void LogDatabase::SetSessionProperty( IDBCreateSession *pISession )

{

DBPROPSET dbpropset[1]; // Property Set used to initialize provider

DBPROP sessionProps[1];

VariantInit(&sessionProps[0].vValue);

ISessionProperties *pISessionProperties = NULL;

//************** We Initial a session properties here *************

// Initialize a session object.

HRESULT hr = pISession->CreateSession(NULL, IID_ISessionProperties,

(IUnknown**) &pISessionProperties);

// Initialize the property to change commit mode.

sessionProps[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;

sessionProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;

sessionProps[0].vValue.vt = VT_I4;

sessionProps[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;

// Initialize the session property set.

dbpropset[0].guidPropertySet = DBPROPSET_SSCE_SESSION;

dbpropset[0].rgProperties = sessionProps;

dbpropset[0].cProperties = sizeof(sessionProps)/sizeof(sessionProps[0]);

// Set the session property.

hr = pISessionProperties->SetProperties(sizeof(dbpropset)/sizeof(dbpropset[0]),

dbpropset);

VariantClear(&sessionProps[0].vValue);

}

View 1 Replies View Related

Commit Fail And Transaction Log Is Full

Feb 15, 2007

Hi,

Our testing server experienced a timeout exception when execute System.Data.SqlClient.SqlTransaction.Commit() in SQL Server 2000 thru .Net Framework 1.1. And this happened at 2007-02-13 18:07:05,954.
It was strange to us that all the insert statement can be executed without error within the transaction but the commit operation fails.
Moreover, after about 40 minutes, we found that the transaction log of this database is full.

Here is the exception and SQL Server 2000 Error Log:
Exception Stack Trace:
DateTime: 2007-02-13 18:07:05,954
Req Id: bccdae08-cc47-4f85-8f48-5f0b9dbbbf88
Exception: MyDatabaseException
Detail:
MyDatabaseException:
Index #0
Server: MySQLServer
Source: .Net SqlClient Data Provider
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
LineNumber: 0
Procedure: ConnectionRead (recv()).
State: 0
Error Number: -2 ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ReadBuffer()
at System.Data.SqlClient.TdsParser.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream)
at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String sqlBatch, String method)
at System.Data.SqlClient.SqlConnection.ExecuteTransaction(String sqlBatch, String method)
at System.Data.SqlClient.SqlTransaction.Commit()

SQL Server Error Log:

2007-02-13 13:02:23.72 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944769, first LSN: 9434:22326:1, last LSN: 9434:22360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:MSSQLBackupMyDatabaseName.bak'}).
2007-02-13 15:50:52.40 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944970, first LSN: 9442:38096:1, last LSN: 9443:748:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'Legato#4f96edfd-7fdb-4cd1-a740-3fe9a54d66c6'}).
2007-02-13 18:48:51.42 spid66 Error: 9002, Severity: 17, State: 62007-02-13 18:48:51.42 spid66 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:52:40.61 spid58 Error: 9002, Severity: 17, State: 6
2007-02-13 18:52:40.61 spid58 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:53:22.69 spid61 Error: 9002, Severity: 17, State: 6
2007-02-13 18:53:22.69 spid61 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:54:01.11 spid57 Error: 9002, Severity: 17, State: 6
2007-02-13 18:54:01.11 spid57 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..

Are these 2 things related? Would a full transaction log cause a commit operation fail?

Please kindly advice.

Thanks a lot.

View 3 Replies View Related

Error 266 - I'm Confused, Only One Transaction With COMMIT And ROLLBACK

Jun 15, 2008

I have a transaction that calls one other sproc and also executes another set of queries, but for some reason I'm getting error 266: "Msg 266, Level 16, State 2, Procedure AddUserHaveTag, Line 26. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1."There is NO transaction in the sproc AddTag. It is also included below.Here is the sproc with the transaction:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON
3 go4
5 ALTER PROCEDURE [dbo].[AddUserHaveTag] 6 (7 @UserHaveID int,8 @Tag varchar(24),9 @UserHaveTagExists bit OUTPUT
10 )11 AS12 SET NOCOUNT OFF13 DECLARE @ErrorCode int14 DECLARE @TagID int15 DECLARE @TagExists bit16
17 BEGIN TRAN
18 19 -- Call proc to add tag to Tags table
20 EXEC AddTag @Tag, @TagID OUTPUT, @TagExists OUTPUT
21
22 -- Check for errors
23 IF @ErrorCode <> 0 GOTO ERROR24
25 -- Check for existing record, otherwise insert
26 IF EXISTS (SELECT 1 FROM UserHaveTags WHERE UserHaveID = @UserHaveID AND TagID = @TagID)27 BEGIN28 SET @UserHaveTagExists = 129 RETURN 030 END31 ELSE32 BEGIN33 INSERT INTO UserHaveTags (UserHaveID, TagID) VALUES (@UserHaveID, @TagID)34 SET @UserHaveTagExists = 035 END
36
37 -- Check for errors
38 IF @ErrorCode <> 0 GOTO ERROR39
40 COMMIT TRAN
41
42 ERROR:43 IF (@ErrorCode <> 0)44 BEGIN45 PRINT 'Unexpected error occurred!'
46 ROLLBACK TRAN47 END
Here is the AddTag sproc:1 set ANSI_NULLS ON2 set QUOTED_IDENTIFIER ON
3 go4
5 ALTER PROCEDURE [dbo].[AddTag] 6 (7 @Tag varchar(24),8 @TagID int OUTPUT,9 @TagExists bit OUTPUT
10 )11 AS12 SET NOCOUNT OFF13 14 IF EXISTS (SELECT 1 FROM Tags WHERE Tag = @Tag)15 BEGIN16 SELECT @TagID = TagID FROM Tags WHERE Tag = @Tag17 SET @TagExists = 118 RETURN 019 END20 ELSE21 BEGIN22 INSERT INTO Tags (Tag) VALUES (@Tag)23 SET @TagID = SCOPE_IDENTITY()24 SET @TagExists = 025 ENDAny advice?Also if you see any glaring errors or things I could be doing better, I'm open to suggestions. I'm fairly new to sprocs and transactions. Thanks,Travis

View 4 Replies View Related

ADO.NET Transaction Fails To Update Database After .Commit()

Nov 24, 2003

SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives???? Anyone else ever heard of such a thing. Please help, before I loose my sanity.

View 4 Replies View Related

Commit Transaction Between Sql2000 And Sql2005 Server

Oct 20, 2006

Are there any tips/techniques/issues when doing a begin tran and commit between a sql 2000 server/db and a sql 2005 server/db. Should you still use "set xact_abort on"? This will be a recordset of about 1-2000

View 1 Replies View Related

SQL 2012 :: Possible To Create Transaction And Commit The Transactions

Apr 22, 2014

I have a update trigger. In this trigger I need to insert few records in 3 tables. If error comes in any of these inserts then previous inserts to get committed. This trigger was written in Sybase and it was possible to create transaction and commit the transactions.

View 4 Replies View Related

Commit Transaction Gets Deleted - Unable To Save SP

Jan 24, 2006

I've re-written a stored procedure and when I post the following codeinto the existing SP in EM, is saves OK. However, when I re-edit theSP, the last line 'Commit Transaction' has been removed.I cannot save the remainder of the SP as it throws error 208 (InvalidObject name #Max) about two of the temp tables I use when I post theentire script. It shows in a message box with the header : 'MicrosoftSQL-DMO(ODBC SQLState:42S02)I haven't posted the full SP nor the structure as it's quite large(2000 lines), so hopefully I have given enough detail, but my questionsare :Why does it now have problems with (temp) #Tables ? The use of thesehas not changed. All I have done is wrap the script into varioustransactions as this helps a lot for performance and tweaked a fewparts later in the SP again for performance.Also, why does the line get removed once I save the SP ?If I run this in QA, I get the same errors, so I suspect it's myscript, but don't know where I'm going wrong.SQL2000 (Need to upgrade the service pack as recently installed on myPC, so this may help)Thanks in advanceRyanCREATE PROCEDURE [dbo].[JAG_Extract] (@ExtractYear INTEGER,@ExtractMonth INTEGER) ASBEGIN TRANSACTIONSELECT 0 AS MaxYear, 0 AS MaxMonth INTO #MaxUPDATE #Max SET MaxYear = @ExtractYearUPDATE #Max SET MaxMonth = @ExtractMonthPRINT 'Stage 1 - ' + Convert(VarChar, GetDate())CREATE TABLE #Extract ([DEALER_SOURCE_DATA_ID] INT,[DSD_YEAR] INT NULL,[DSD_MONTH] INT NULL,[DEALER_CODE] VarChar(20),[FranDealerCode] VarChar(20) NULL,[Line_No] VarChar(75),[Current] [numeric](15, 5) NULL,[YTD] [numeric](15, 5) NULL,[12Months] [numeric](15, 5) NULL,[24Months] [numeric](15, 5) NULL,[Average_YTD] [numeric](15, 5) NULL,[Average12Months] [numeric](15, 5) NULL,[Average24Months] [numeric](15, 5) NULL,[Last_YTD] [numeric](15, 5) NULL,[Current_STATUS] INT,[PD1] [numeric](15, 5) NULL,[PD2] [numeric](15, 5) NULL,[PD3] [numeric](15, 5) NULL,[PD4] [numeric](15, 5) NULL,[PD5] [numeric](15, 5) NULL,[PD6] [numeric](15, 5) NULL,[PD7] [numeric](15, 5) NULL,[PD8] [numeric](15, 5) NULL,[PD9] [numeric](15, 5) NULL,[PD10] [numeric](15, 5) NULL,[PD11] [numeric](15, 5) NULL,[PD12] [numeric](15, 5) NULL,[PD13] [numeric](15, 5) NULL,[PD14] [numeric](15, 5) NULL,[PD15] [numeric](15, 5) NULL,[PD16] [numeric](15, 5) NULL,[PD17] [numeric](15, 5) NULL,[PD18] [numeric](15, 5) NULL,[PD19] [numeric](15, 5) NULL,[PD20] [numeric](15, 5) NULL,[PD21] [numeric](15, 5) NULL,[PD22] [numeric](15, 5) NULL,[PD23] [numeric](15, 5) NULL,[PD24] [numeric](15, 5) NULL,[PD25] [numeric](15, 5) NULL,[PD26] [numeric](15, 5) NULL,[PD27] [numeric](15, 5) NULL,[PD28] [numeric](15, 5) NULL,[PD29] [numeric](15, 5) NULL,[PD30] [numeric](15, 5) NULL,[PD31] [numeric](15, 5) NULL,[PD32] [numeric](15, 5) NULL,[PD33] [numeric](15, 5) NULL,[PD34] [numeric](15, 5) NULL,[PD35] [numeric](15, 5) NULL,[PD36] [numeric](15, 5) NULL)INSERT INTO #ExtractSELECT DISTINCTSD.DEALER_SOURCE_DATA_ID,SD.DSD_YEAR,SD.DSD_MONTH,DN.DEALER_CODE,DN.FRAN_DEALER_CODE,DV.FIELD_CODE,0,0,0,0,0,0,0,0,SD.STATUS,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0FROMDEALER_NAW DN WITH (NOLOCK)INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)ON DN.DEALER_CODE = SD.DEALER_CODEINNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_IDAND SD.STATUS < 4096INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)ON DN.DEALER_CODE = GR.DEALER_CODEAND GR.FIXED_GROUP_ID IN(11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,113,114,115,130,131,132,133,134,135,136,137)GOCOMMIT TRANSACTION

View 2 Replies View Related

Rolling Back Transaction After .Commit() Runs Successfully

Jun 10, 2007

I have a page that runs a transaction correctly after a button click.
I want to allow someone to click a button that rolls back the transaction, after the transaction runs on the first button click.
I can also successfully roll back within the first button click.
I'm getting a NullReference error when trying to access SqlTransaction.Rollback() outside the button click.
If SqlTransaction.Commit() completes without error can SqlTransaction.Rollback() be called after?
I tried making 'trans' a more global variable and it still gave me the error.
Button Click 1:
Dim trans As SqlTransaction
trans = connection.BeginTransaction()
try
'run SQL Statement
trans.Commit()
Catch e As Exception
trans.Rollback()
throw e
end try

Button Click 2:
trans.Rollback()

View 4 Replies View Related

Error: COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Why?

Nov 13, 2007

Hello:
I am implimenting the creation of sequence numbers .I use an insert proc on a table that generates the numbers using an identity field:
procedure usp_createidentity

begin transaction

insert into [tblOrderNumber] with default values

rollback ' done so no records in this table

select @OrderNumber = scope_identity()

I call this from another proc that inserts values into my order table:

procedure usp_Insert @OrderNumber int
as

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION

EXEC usp_GetNewOrderNumber @OrderNumber = @OrderNumber output
INSERT INTO [dbo].[tblOrder] ([OrderNumber]) values (@orderNumber) ' inserts value from other stored proc

COMMIT TRANSACTION

END TRY

BEGIN CATCH

if (XACT_STATE() = -1)

ROLLBACK TRANSACTION

else

if (XACT_STATE() = 1)

COMMIT TRANSACTION
END CATCH

Here is the problem. When I run usp_Insert I get the following: Error 266 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

This refers to the usp_GetNewOrderNumber that is called inside the other proc as shown above.

The problem does not happen if I put each statement in usp_Insert in its own try/catch. transaction statements.

Maybe it has something to do with the rollback call in the usp_getneworder.

What do I need to do to get rid of this. problem and still run these within one try/catch trans statement set.

Thanks

View 9 Replies View Related

How To Create A Transaction Commit/Rollback For Oracle DB In SSIS

Oct 8, 2007

I'm able to connect to the Oracle database to insert the data into multiple tables using OLEDB connection via Oracle Provider for OLEDB. However, i wish to create a transaction so that i'm able to rollback all the data in the case where the insertion fails in one of the table. May i know where should i start from?

View 4 Replies View Related

Transact SQL :: Multiple Update Top On Commit Transaction Doesn't Work

Jul 10, 2015

I have this sql stored procedure in SQL Server 2012:

ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes]
@newBatches as dbo.CreateBatchList READONLY
, @productId int
, @cLevelRatio int
, @nLevelRatio int
AS
set nocount on;

[Code] ....

View 4 Replies View Related

Transact SQL :: Commit And Rollback Transaction Based Upon Results Of A Calculation

Oct 5, 2015

In t-sql 2012, I have the following sql that I would like the following to occur:

1. commit or rollback a transaction based upon the results of a calculation listed below,
2. I would like to have a message appear if the commit was successful or the rollback needed to occur. I basically want a way to be able to tell from messages if a rollback occurred or a commit happened.

DECLARE @TransactionName varchar(20) = 'Transaction1';
        @STARTLOCKERCNT INT = 0, @LOCKDIFCNT INT = 0, @ENDLOCKERCNT INT = 0
DECLARE @lockmap TABLE (lockID int NOT NULL PRIMARY KEY,
                       schoolID int NOT NULL,
                       UNIQUE(schoolID,lockID)
)

[Code] ....

Thus can you modify the sql I just listed above so that I meet the goals that I just listed above?

View 5 Replies View Related

Error 209 Ambiguous Column Name ...Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK ....

Aug 28, 2006

i'm getting following exception when i try to execute stored procedure.{"Ambiguous column name 'MemberID'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting"Transaction count after EXECUTE indicates that......" In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */ here is my stored procCREATE PROCEDURE dbo.ExportFile @intMonth INT, @intYear INT, @dtFirstDayOfMonth DATETIMEAS BEGINBEGIN TRANSACTION /* I have some logic here that will select rows into temporary table #TEMPRESULT */ UPDATE dbo.RebateInstanceItem SET ResubmitCreated = @dtmNewCreated FROM #TEMPRESULT tTempResult WHERE MemberID = tTempResult.MemberID AND RebateInstanceItem.IsResubmit = 'Y' AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated OR RebateInstanceItem.ResubmitCreated IS NULL) IF @@ERROR<>0 GOTO ERR_HANDLER // when error it will goto error_handler that will rollback DROP TABLE #TEMPRESULT IF @@ERROR<>0 GOTO ERR_HANDLERCOMMIT TRANSACTIONRETURN 0ENDERR_HANDLER: ROLLBACK TRANSACTION RETURN 1GO

View 5 Replies View Related

Transact SQL :: Transaction Count After EXECUTE Indicates Mismatching Number Of BEGIN And COMMIT Statements

Aug 19, 2015

I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."

View 2 Replies View Related

SQL Server 2012 :: Transaction Count After EXECUTE Indicates Mismatching Number Of BEGIN And COMMIT Statements

Oct 23, 2015

I'm all of a sudden getting this error on a Stored Procedure that has not been touched since it was created.

Msg 266, Level 16, State 2, Procedure usp_ArchivexactControlPoint, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

CREATE PROCEDURE [dbo].[usp_ArchivexactControlPoint]
AS
DECLARE @TableName VARCHAR (50)

[Code] ...

View 2 Replies View Related

Problem With Getdate() In Transaction Takes The Insert Time Instead Of The Commit Time

Nov 12, 2007

Hi,

We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.

If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.

We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.

We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:

/* Different functions to get current timestamp �� all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()

insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()

update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don��t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table �� we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE

Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).

Regards,

Mark

View 8 Replies View Related

DTS Packages And Transaction

May 4, 2007

Hello everyone,

My question:
Is it possible to gate the complete execution of a DTS package?

Experimentation:
-I have to databases, dbSource and dbArchive.
-Each database has 1 table of the same format, tableData

1- I put 50 000 rows in dbSource
2- Once the insertion completed, I start my DTS package. The package does this:
2.1 - Move all rows of dbSource in dbArchive
2.2 - Truncate rows from dbSource
3- Immediatly after the DTS had start, I start the insertion of another 1000 rows inside dbSource.

Results:
Once everything is terminated (DTS and 1000-rows insertion), here is the count inside each database:

dbSource:881
dbArchive:50079

So I "lost" 40 rows in the process.

My expectations:
By setting the package property Use Transactions with the Serialization isolation level, I thought that the reading part of the bulk move would be gated, so any insertion in dbSource would be officially inside dbSource after the DTS completion only.

Feel free to ask any other information you might need. I can also explain my problem from a higher point a view and what I try to achive.

Thanks !

View 2 Replies View Related

Parent/Child Packages And Transaction Option Not Working As Expected

Dec 18, 2007

Has anyone had experience of using Parent/Child packages while enlisting them in Transactions.
I tested this on a small sample and thought that I had got it to work, but in my real-world package it does not.

The parent package essentially calls three child packages.
In each child package there are multiple DFT's that import and transform data into SQL Server.
All data must be imported or not at all.
Therefore I created a FELC container into which three Exec child package tasks were placed.
The FELC is set to Trans Option 'Required' and the Exec child package tasks to supported.
Unfortunately upon failure of one of the DFT's in the child the data was not rolled back.

So initially we had in terms of container hierarchy for the Trans Option property:
Parent package Supported
FELC for calling child packages Required
Task execute child package Supported
Child package Suppored
Tasks Suppored

Looking at this more closely we thought that we would need
Parent package Supported
FELC for calling child packages Required
Task execute child package Required
Child package Required
Tasks Suppored

for it to work.
However, the latter now gives us failures with error messages on the tasks on the child packages.
[Execute SQL Task] Error: Failed to acquire connection "Conn ECARS1CEDImport". Connection may not be configured correctly or you may not have the right permissions on this connection.

Even more strange the first couple of tasks in the child pkg complete successfully even though they use the same connection listed in the error.
These tasks also have Event handlers.

View 7 Replies View Related

COMMIT TRAN Does Not Commit

Mar 28, 2008

Microsoft SQL Server Management Studio Express

select @@trancount
begin tran
select @@trancount
use ProdNetPerfMon
select @@trancount
update Nodes set Caption = 'xxxx' where Vendor = 'yyyy'
select @@trancount
commit tran
select @@trancount

Executes as expected including trancount without errors.
Nodes.Caption is updated but reverts after a few minutes.

sa privileges

What am I missing?

View 1 Replies View Related

Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

May 31, 2008

Hi All

I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.

If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.

I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.

set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN

It's got me stumped, so any ideas gratefully received.Thx

View 1 Replies View Related

SSIS, Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Feb 22, 2007

I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.

for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.

I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.

if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

and

Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.

Please help me it's very urgent.

View 3 Replies View Related

Calling SSIS Packages From ASP.NET - Packages With File System Tasks End Abruptly

Jan 9, 2007

I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.

Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.

When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.

I've tried giving administrative permissions to the ASPNET worker process user to no avail.

I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.

I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.

Anybody got any idea on how to go about this?

View 33 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Feb 6, 2007

I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!

View 1 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Dec 22, 2006

i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.

Message is like this:

Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

View 8 Replies View Related