MS SQL Server :: Problem With SSIS Transaction...Transaction Scope

SSIS, Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
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 Replies !

Transaction Scope
HelloI am trying to run a program to check for transaction scopeI have written the following code.But it seems I need to add a namespace or referenceWhat namespace am I supposed to addthanksusing (TransactionScope scope = new TransactionScope(TransactionScope.Required, options)){SqlConnection MyCon = new SqlConnection("server=hemalatha\sqlexpress;integrated security=sspi;database=demo");MyCon.Open();SqlCommand Mycmd=new SqlCommand("insert into t1 values 574,'scope','10/10/2007',3,3,3");Mycmd.ExecuteNonQuery();SqlConnection MyCon1 = new SqlConnection("server=hemalatha\sqlexpress;integrated security=sspi;database=persons");MyCon1.Open();SqlCommand Mycmd1 = new SqlCommand("insert into persons values 'scope',123,123,12,'scope'");Mycmd1.ExecuteNonQuery();}

View Replies !

Doubt In Transaction Scope
Hi ,

I have 2 packages and they get executed in SQL Job.

Each of these package will have seperate transactions with the Database and I am not sure of how to maintain the transactions between these 2 packages.

Can someone explain me the necessary setting to achieve this. If I set the TransactionOption property to Required (or) Supported in both these packages, will the Transaction Scope of the first package extend to the second one.

For me, If the second package ends up in error, the first package should also have to be rollbacked.

Any suggestions would be of great help.

Thanks

Evan

View Replies !

TRANSACTIONS In SSIS (error: The ROLLBACK TRANSACTION Request Has No Corresponding BEGIN TRANSACTION.&&"
I'm receiving the below error when trying to implement Execute SQL Task.

"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'

I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.

Anyone know of the reason?

View Replies !

Problems With Transaction Scope Time Out
Hi, i'm trying to run wrap a transaction around a block of code to make sure every statement is carried out. Here's what i have:
using (TransactionScope ts = new TransactionScope())
{ ... ts.Complete();}The code executed in the ... part is a set of sql commands within my dal. If this above expression takes less than 30 seconds everything is carried out correctly, however if it takes longer than 30 seconds i receive an sql timeout error. I tried modify the above code to:
using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromMinutes(5)))
{ ... ts.Complete();}
But it did not seem to make a difference. I suppose the easiest option would be to increase the timeout for every sql command in my dal but i feel that the 30 second timeout is sufficient for a single command.
Appreciate if someone could tell me how this should be done correctly. Thanks

View Replies !

How Do I Make Use Of Begin Transaction And Commit Transaction In SSIS.
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 Replies !

Begin Tran In An Sp Executed Fom Within A Transaction Scope
we have an update sp that must call an insert sp after the update. The update and insert must act like a transaction, ie all or none.

We believe that wrapping most of the update (including call to insert sp) sp in a begin tran block would guarantee the all or none behavior.

However, we're not sure what would happen if one of our developers calls the update sp from within a transaction scope that expects yet something additional to be included in the transaction. Would the begin tran block (assuming no errors in that block) in the sp commit both the update and insert regardless of what happens in the rest of the .net tran scope?

View Replies !

Transaction Scope - The Operation Could Not Be Performed Because OLE DB Provider &"SQLNCLI&" For Linked Server &"XXX_LINKED_SERVER&" Was Unable To Begin A Distributed Transaction. OLE DB Provider &"SQLNCLI&a
Hello, I've a problem with a software developed in C# with the framework 2.0. This is the error I receive : The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "XXX_LINKED_SERVER" returned message "No transaction is active.". If I try directly to restart the process, it works fine. Is there someone who can help me ? This is the process 1. In C# --> Call of a Query : select from the linked server (db in sql 2005) and insert into a table SQL 2005 2. In the C# --> using (TransactionScope scope = new TransactionScope()) and insert in a table in SQL 2005 which is link server Thank in advance.

View Replies !

Transaction Scope Rollback Does Not Roll Back Action Of Activation SP
Hi,

I followed Remus' post about not doing 'fire and forget'.

I have two queues, ProcessingSendQueue and ProcessingReceiveQueue.

Once i receive from ProcessingReceiveQueue, activation SP gets called on ProcessingSendQueue and ends conversation.

However,if I then get an exception, the action of the activation SP ( ie the ending of the conversation ) does not get rolled back... is this possible? I would have thought that the action of the activation SP would get rolled back too.

My ProcessingSendQueue activation SP is as follows:

ALTER PROCEDURE [dbo].[ProcessingSendQueue_AP]
AS
BEGIN
    DECLARE @dh UNIQUEIDENTIFIER;
    DECLARE @message_type SYSNAME;
    DECLARE @message_body NVARCHAR(4000);

    RECEIVE @dh = [conversation_handle], @message_type = [message_type_name], @message_body = CAST([message_body] AS NVARCHAR(4000))
    FROM [ProcessingSendQueue];

    IF @dh IS NOT NULL
    BEGIN
        IF @message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
        BEGIN
                RAISERROR (N'Received error %s from service [ProcessingReceiveQueue]', 10, 1, @message_body) WITH LOG;
        END
        END CONVERSATION @dh;
    END
END

View Replies !

Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
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 Replies !

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
Hi all, I've already read the thread by the same topic, but I found Kevin Yu's comment a bit confusing. I don't know how to solve my problem. I have two stored procedures in sql server. First one inserts into a bunch of tables so it has Begin Transaction and Commit in it. Second one just inserts into one table so it doesn't have the begin transaction statement in it. From my data access class in c#.net, I have a function which calls both of these stored procedures from within a "using transactionscope" block. So when it makes the call to the first stored procedure, it returns fine, but when it calls the second one, I get the above error. I think it has something to do with me declaring transactionscope in my application code, and then calling a stored procedure which also has its own begin transaction. But I don't know how to fix this. Any ideas? Thanks

View Replies !

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction
I'm using TransactionScope object and it is giving me problems. The situation is like this.
I've 2 tables in sql server express, say student and courses and other one studentcourse for courses a student has opted for.
An aspx page displays list of users in listbox, when user chooses student, it fetches courses he has opted for and in the checkedlistbox (for courses) these courses are checked. Now when uses makes any changes to courses and clicks update button, i've
two functions at backend first one removes the courses which were previously selected but now not selected in postback, next it inserts courses which was not selected first but now are in this postback.
I use following code:

protected void Update(object sender, EventArgs e)
{
/*
get selections, existing courses etc.
*/
using(TransactionScope tscope=new TransactionScope())
{
try
{
RemoveCourses(/*Student*/ s, /*IList*/ oldCourses, /*IList*/ newCourses);
AddCourses(/*Student*/ s, /*IList*/ oldCourses, /*IList*/ newCourses);
tscope.Complete();
}
catch(Exception ex)
{
lblMessage.Text=ex.Message;
}
}
}

If any exception occurs during update and page is reload or I go to any other page where some data is fetched from sql server I get the exception:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction

Please help!

View Replies !

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
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 Replies !

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
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 Replies !

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction. (HELP)
Hi,

i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".

my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.

Thanks in advance.

View Replies !

Unable To Shrink Transaction Log Under Transaction Replication With Sync With Backup Enabled On Distribution Db?
We have applied transaction replication servers with SQLServer2005 standard edition with SP1 running on them. Sync with backup is also enabled on distribution db. Now our transaction log is not shrinking using standard shrinking procedure(taking log backup with truncate option/shrinkfile).

select name,log_reuse_wait, log_reuse_wait_desc from sys.databases

it is showing dbname, 6, Replication respectively.

declare @db int

set @db=db_id('dbname')

dbcc loginfo(@db)

is showing 2 in status column.
tried shrinking transaction file after removing sync with backup option from distribution db. still it failed. again tried shrinking after stopping log reader, it didn't worked.

Regards,
M. Abdullah Idris

View Replies !

I Need Away To Show The Pending Transaction From Transaction Replication In A User Friendly Format.

I want to list out the pending transaction for transaction replication by publication.

Help needed.

View Replies !

Using Begin Transaction,End Transaction, Return,Rollback, Break,Continue
Hi, is there someone who can demonstrate the usgae of these codes in this simple process. I have a table that has (id int, Balance money)
I want to write codes to utilize the Subject coding in this process.
1.Every time I withdraw money from a client, I want to verrify that he/she has enough money if not I want to roll back transaction.
2.Using Begin Tran , end Tran..
3. Using Return ,Break,continue

I really appreciate your help. Althought I read about this from a book, but it really confused me and I want to see real world example using client and withdraw from there accounts...:)

regards
Ali

View Replies !

Usage Of Begin Transaction.... Commit Transaction??
Hi, anyone can help,I am trying to learn how to use begin tran..commit tran .. rollback tran by doing this exercise... unfortunatelly,it did not work...anyone can help...
I have a table named tbl_balance(investor_id int,amount money)
I want to insert/update balance for each investor. I wrote a procedure but it didnot work:::

declare @investorid int, @amount money,@error_status int
select @investorid=1
select @amount = 500
/* to check if investor exist in the tbl_balance table, if so execute the codes */
IF exists(select investorid from tbl_bal where investorid=@investorid )
begin tran
begin
update tbl_balance
set amount=amount+@amount
where investorid =@investorid

if (@error_status<> 0)
begin
ROLLBACK tran
select 'you did not update'
end
else
begin
COMMIT tran
select ' YOu did update '
end
end

IF not exists(select investorid from tbl_bal where investorid=@investorid )
begin tran
begin
insert into tbl_balance
values(@investorid,@amount)

if (@error_status<> 0)
begin
ROLLBACK tran
select 'you did not update'
end
else
begin
COMMIT tran
select ' YOu did update '
end
end

View Replies !

Sql2005:Cannot Use SAVE TRANSACTION Within A Distributed Transaction
Error returned when trying to commit the transaction to a database that is a replication distributor. (sql2005 ctp16)

View Replies !

SSIS With Transaction
Hi

I new in SSIS. I tried to do the simple package with transaction support.

I do the sql task wich "delete from TableName" and then i do a simple data flow from one table to "TableName" which i deleted from data before.

I wanted to use transaction that in case the data flow collapse the transaction will roll back the delete task of table.

But then i exucute my package it stucks like the table is locked.

Can any one help me with this package?

Thanks.

View Replies !

SSIS Transaction
I am just started working on SSIS. I created one package and want to impliment the transactions.

The purpose of the package is to truncate the destination table and insert the records into destination table from flat file

i taken execute sql task to truncate the table and dataflow task to insert the records into destination table.

at the package level i given transaction option as required and isolation level is readcommitted.

when i run the package my execution stops at dataflow task and it is always yellow.

If i am using delete statement instead of truncate than it is working fine.

is truncate table creating any problem? I need to use truncate.

please suggest me how to impliment transaction using truncate and dataflow task.

View Replies !

SSIS DTC Transaction's HOT Questions
I am using SSIS with Transaction, and I met a lot of questions as below:

1.IF DTC doesn't support ADO.NET Connection?

   Please check the ADO.net Connection's property, it has a property named like: DTCTrancactionSupport. But it's always set to False and unable to edit.

IF the answer is NO, HOW could I bound all my Tasks into one Transaction? You know, I have some Tasks with SQL connection, and I have also some Script Tasks, witch need the ADO.net connection.

2.IF DTC required to run in both the Destination Server and the Source Server?

   I have a Dataflow Task, and take some data from a table in a Source Server to a Destination Server. The DTC doesn't run on the Source Server. I found the Dataflow Task hangs when I use the SSIS Transaction(just set the Task's transactionOpion to Required, Only one Task in the Package).

3.IF DTC doesn't support RetainSameConnection?

   Many people say that. Really?

4.When a DTC transaction is running, if the table in it could not be modified by process outside of the transaction? For example, a package with a DTC transaction, and there is a SQL Task inside, when it is running, could I modified the same table's data manually(in the Sql Sever Management Studio)?

View Replies !

Transaction Causes Hanging Of SSIS
Hi

I set TransactionOption=Required for my SSIS package. In this package, 2 tasks are included,

one is execute sql (TransactionOption=supported)

2nd is DataTransformation Task (TransactionOption=Supported)

But it hangs on the execute sql task itself!

Didnt find any reason 4 it so far!

View Replies !

Transaction With SSIS / SQL Server / DB2
Hi,

very simple task... I want to copy some data from a DB2 table to SQL Server. So I want to delete the records in the SQL Server table and copy all data from DB2 to the SQL Server. That's easy...

The only thing I want to make sure is that in case of an error I still have a valid dataset in the table. So my idea was to use a transaction covering the delete and the data flow.

So I moved the two tasks in a container, switched transactions to "required"...

What I see is that now a distributed transaction is started for DB2 and the SQL Server. But that's not what I want (and it doesn't work also...). I don't need the transaction on the DB2 side, I just want it on the SQL Server to cover the delete and the insert.

What can I do?

Thanks,

View Replies !

SSIS Transaction ERROR
Hi all,
I'm having an issue with transactions in SSIS.

I have a Sequence Container, that contains 5 Tasks.

1) Data Flow Task using an OLEDB Connection to access an Oracle RDB database via a linked server. then insert into SQL Server 2005.

2) Execute Sql Task on Sql Server 2005 (UPDATE Statement)

3) Execute Sql Task on Sql Server 2005 (DELETE Statement)

4) Data Flow Task using an OLEDB Connection to insert data into a SQL Server 2005 DB

5) Execute Sql Task performing an UPDATE statement on an Oracle RDB database using an OLEDB Connection to to access the database via a linked server

If the Sequence container and all contained tasks are set to Transaction: Supported (No transaction will be created I believe), then the package runs successfully.

If I change the Sequence container to Transaction: Required, and leave all contained tasks as Transaction: Supported, then Task 1 fails with the following error:

[SELECT From RDB Change Table [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SSISPOC" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Any Ideas would be much appreciated, as I'm stumped!

View Replies !

How To Use Transaction In A SSIS Package
I try ton use a transaction in a SSIS package. When running i have an error :

[source [1]] Error: The AcquireConnection method call to the connection manager "myconnection" failed with error code 0xC0202009.

[Connection manager "myconnection"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "Le partenaire du gestionnaire de transactions a dÃ©sactivÃ© la prise en charge des transactions Ã distance/par rÃ©seau.".

Can someone help ?
thanks

View Replies !

SSIS Package As One Transaction
Hi,
I am a newbie in SSIS. I'd like to hear your recommendation to build the whole SSIS package as one transaction. In other words, how we can rollback all DML operations (insert/update/delete) that are applied in different parts of a package if for any reason the package failed to complete?. Something like rolling back a proc if the proc failed.

Thanks in advance

View Replies !

SSIS Package Transaction Integrity
1) I created many data flow task packages.

2) Then a execute package task in which I ordered relationally execute above data flow packages.

3) What I find is when a package fails, it is rolled back, execution stops but not the packages before this failed one.

4) What to do to rollback all in case of error and commit only if all are successfully executed?

View Replies !

SSIS Package Transaction Limit?
I am running a package in which all tasks participate in a transaction. One task is a ForEach Loop container that executes 1 SQL INSERT statement per loop. The Loop runs well over 100 times in order to normalize a very, very, very wide table.

It seems that at a certain point, the Loop task will fail. I've tested a few different scenarios and they all fail at the same point... the 98th loop.

The error message is below:

[Execute SQL Task] Error: Failed to acquire connection "ADO.NET Connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

I'm guessing that there is a limit to the number of connections that can be started at any one time. But I figured SSIS wouldn't start a new connection for every SQL command.

Any insight on this issue? Anyone have an idea for a work around?

View Replies !

Truncation Of Transaction Logs Using SSIS

HI,

What I have set out to do is to try and truncate the transaction logs on my server instances after a nightly backup. I went ahead and used the shrink database option in the maintenance plan wizard. Unfortunately I have found this will only truncate the logs for databases using the simple database recovery model, and I wish to truncate the whole lot to save space...

I went ahead and put together an SSIS package that does the following:

1. First thing I do is build a fresh copy of a database listing in a temp table using the following SQL statement

USE Master;
Go
SELECT name, DATABASEPROPERTYEX(name, 'Recovery') AS RecoveryModel, DATABASEPROPERTYEX(name, 'Status') AS DBStatus INTO TempTables.dbo.Recovery FROM sysdatabases ORDER BY name
GO

2. Next I read this dataset back into memory using a SQL statement as follows:

USE TempTables
GO
Select name from dbo.recovery Where name <> 'master' and name <> 'model' and name <> 'msdb' and name <> 'tempdb'

I map the results to a results set called User::TableName (which is of an object data type)

3. Next the package goes to a ForEach Loop container and I use the Foreach ADO Enumerator Enumerator to read the data I just selected into the look. I select the User::TableName variable and use the enumeration mode 'Rows in first table'. I have used a variable mapping of Variable: User::TableName with an Index 0.

4. I then use the following SQL statement (which sits in the For Each Loop) to try and alter the recovery model:

ALTER DATABASE @TableName
SET RECOVERY SIMPLE;
GO
This is where the package falls over with the following error message

SSIS package "mnt_TransLog.dtsx" starting.
Error: 0x0 at Simple Mode: Incorrect syntax near '@TableName'.
Error: 0xC002F210 at Simple Mode, Execute SQL Task: Executing the query "ALTER DATABASE @TableName
SET RECOVERY SIMPLE;
" failed with the following error: "'RECOVERY' is not a recognized SET option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Simple Mode
Warning: 0x80019002 at For Each Table Set to Simple: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at mnt_TransLog: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "mnt_TransLog.dtsx" finished: Failure.

I think the problem here is either I am not passing the variable across correctly (i.e. not declaring it or something..!) or I am passing the wrong type of variable across.

Once the recovery model is changed I can truncate the transaction logs successfully but I can€™t seem to until this is done! Any help in finding a solution to this problem or a better way of approaching this problem would be appreciated!

Thanks

Marek Kluczynski
Management Information Officer
Investors in People UK
7-10 Chandos Street
London
W1G 9DQ
Tel: 020 7467 1956

View Replies !

Roll Backing The Transaction In SSIS
Hi

I have a situation where i need to insert or update the data from a flat file to a sql server database. The flat file contains nearly one lakh records.

I am using transactions. If all the rows are inserted or updated successfully i am commiting. If there is any error i am rollbacking the transaction.

when rollbacking the transaction it is taking more than 3 to 4 hours.

Can any one suggest me how to do that in batches?

Thanks in Advance

View Replies !

New Transaction Cannot Enlist In The Specified Transaction Coordinator
I'm getting this error:

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

http://support.microsoft.com/kb/839279 this didn't help

any suggestions?

View Replies !

Why Transaction Gets Promoted To Distributed Transaction
Hi,On My local SQL server I have added a linked server to another SQLserver (remoteserver) in another Windows NT Domain.When I run this codeselect count(*) from remoteserver.mosaics.dbo.LocationThis works fine.However when I usebegin transactionselect count(*) from remoteserver.mosaics.dbo.LocationIt errors out saying thatThe operation could not be performed because the OLE DB provider'SQLOLEDB' was unable to begin a distributed transaction.New transaction cannot enlist in the specified transactioncoordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a].My question is even though I am just reading data from theremoteserver, why does the local transaction get promoted to adistributed transaction.Any help will be grately appreciated.TIA...Rohit

View Replies !

BEGIN TRANSACTION COMMIT TRANSACTION Help
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 Replies !

BEGIN TRANSACTION And COMMIT TRANSACTION
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 Replies !

New Transaction Cannot Enlist In The Specified Transaction Coordinator
Hi

I am trying transfer data from Back Office Server to Head Office using DTS. It is failing in few stores, rest of the store it is OK

Configuration

Head office :Windows 2003 SP1 and Sql Server 2000 SP3

Failing Store:Windows 2003 SP2 and SQL SERVER 2005

Successful Store :Windows 2003 SP2 qnd SQL SERVER 2000

At Store: Setting for MSDTC

Log on Account for MSDTC Service : Network Service

Network DTC Access = True

Allow Inbound = True

Allow Outbound = True

No Authentication Required = True

TurnOffRPCSecurity = 1

port Ranges for RPC = 5000- 5100

The error Message is as follows:

(Microsoft Data Transformation Services (DTS) Package (8004d00a): Connection 'Head Office SQL Server' for Task 'DTSTask_DTSDataPumpTask_1' does not support joining distributed transactions or failed when attempting to join. Unable to enlist in the transaction.
) (Microsoft OLE DB Provider for SQL Server (8004d00a): New transaction cannot enlist in the specified transaction coordinator. )

If any one can help me, that will be great!!! Thanks in advance

Shiny

View Replies !

SSIS Error - Transaction Context In Use By Another Session
All

I'm developing a package in SSIS reading data from two databases on a SQL 2000 server and moving the data (with some transformation) to a SQL 2005 server. I am executing the package on the 2005 box. The package must employ transactions and having configured MSDTC on both servers I still seem to get the abovementioned error when setting the TransactionOption value to required for the control.

I do the following inside a Data-Flow-Task:

Create and OLE DB Source connection that points to a connection manager configured against databaseA on the SQL 2000 server. The following SQL example applies:

Select col1, col2...coln
from databaseA.dbo.table1

Create and OLE DB Source connection that points to a connection manager configured against databaseB on the SQL 2000 server. The following SQL example applies:

Select col1, col2...coln
from databaseA.dbo.table1

Both of the OLE DB Source outputs are directed through a Union Transformation (since the structure of databaseA and databaseB are identical - only the data in each database is different).

The output of the union is directed to a Multicast Transformation. The multicast directs it's output to two seperate OLE DB destinations.

Destination1 - SQL Server 2000 table is populated with multicast output stream 1.
Destination2 - SQL Server 2005 table is populated with multicast output stream 2.

Both of the destinations above have their own connection managers.

When I execute the data-flow-task I get a clutch of errors as follows. If I simply set the TransactionOption to 'NotSupported' or 'Supported' instead of 'Required', this task executes successfully. Also, if I split the task into two seperate data-flow tasks (ie: select from databaseA and write Destination1 in one data flow and select from databaseB and write to Destination2 in another data flow - the package will succeed).

I have done many searches on MSDN/Technet/Google etc. to find a solution here. The bottom line is, I need to employ a transaction in this package and haven't been ab;e to get it working despite haviung broken the package down to bare bones and creating a dummy environment

Error: 0xC0202009 at Insert Unprocessed Keys From DatabaseA & DatabaseB, Write Keys - SQL_DEST_2000 [104]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E14 Description: "Transaction context in use by another session.".
Error: 0xC0202009 at Insert Unprocessed Keys From DatabaseA & DatabaseB, Write Keys - SQL_DEST_2000 [104]: An OLE DB error has occurred. Error code: 0x80040E37.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E37 Description: "Transaction context in use by another session.".
Error: 0xC004701A at Insert Unprocessed Keys From DatabaseA & DatabaseB, DTS.Pipeline: component "Write Keys - SOURCE" (104) failed the pre-execute phase and returned error code 0xC0202040.

Thanks in advance

David

View Replies !

Can A SSIS Package Join An External Transaction?

Hi there,

I'm trying to come up with the best way to build some C# Unit tests for an SSIS package I've built.

My C# code does the following

1. Creates a Transaction Scope using System.Transactions

2. Puts some source data for my package into a table that the package will read

3. Kicks off the Package using System.Diagnostics.ProcessStartInfo

Im getting a Transaction TimeOut execption which I beleive is caused because the Package can't read the source data I've inserted becuase the package has not joined the transaction.

So the question is - can you call a run a package and make it participate in a transaction that you have created outside of the package?

Thanks.

View Replies !

Implementing Transaction In SSIS Package Using SQL Task

Hi all,

I am having a simple ssis package. In that i have an sql task that will insert a record in to the database. i find that if i use a simple OLEDB connection it works fine. but the problem comes here when i change the connection string with an valiable. Once i assign the same connection string to a valiable and assign the variable as expression, there after i encounter the following error.

[Execute SQL Task] Error: Failed to acquire connection "DB connection". Connection may not be configured correctly or you may not have the right permissions on this connection.

I am struggling with this for a long time.

Any of you plz suggest me a solution for this problem

Thankz

View Replies !

SSIS With Transaction Accross Different Network Zone
Hello, all

I have developed a SSIS package using a transaction

1. Users upload excel file to web server(139.223.15.xxx)
2.Excute SSIS package in web server to import excel file to SQL SERVER 2005(139.223.3.xxx)

If web server and SQL server are in the same network zone, the package will work successfully.

However, the two servers are in the different network zone, it encounters an error :

DTS can't enlist OLE DB in distribution transaction. Error Code: 0x8004D00E

How do I set any configurations?

Environment:
Web server:windows 2003 server sp1 ent
SQL server 2005 ent:windows 2003 server sp1 ent
SSIS: isolation level-->read committed

View Replies !

SSIS Package In Transaction Required Mode?
Hi Guys,

Refering to my previous post I've a package which easily run when transaction mode is Supported but when i put it to Required and All DFs are supported inside the sequence container it won't run.

Well technically it goes and pass step 1, so it goes to green but regardless of what i put in the 2nd DF it won't run. It just hangs in there.
When i check the component service and check DTC, the transaction would be running but never commited.

I have done most of the things about DTC and configuration with the MS articles as this package was failing before but now it just sits at this point.

Any ideas?

Thank you

Gemma

View Replies !

Transaction Not Working When Inserting Data Into Oracle Db Using SSIS.
Hi Guys,

I have a package that inserts data from a sql server table to an oracle table. I use a dataflow task to do the loading.

The weird thing is, if the package fails during execution, the transaction does not perform a rollback and the inserted data is committed.

The dataflow component has its TransactionOption set to Supported (this has always been the case for our sql to sql packages). However, now that I have a sql to oracle package, it won't work anymore.. also, setting the TransactionOption of the dataflow component to Required causes the whole package to fail.

Anyone have any thoughts on this?

Thanks,
Kervy

View Replies !

[SSIS] Impossible To Use TransactionOption At Required : Transaction Using By Another Composant
Hello,

I want to build a transactional system with one package. This package is made of three packages.

The first one calls the two others using execution package. Each package is in a container.

The two container sequences are in a third one. This last one has the transaction option set to required.

The two included packages contain dataflows which transfer data from table A to table B and update data. Each dataflow of each package is in a sequence container with the FailParentOnFailure set to True.

When I execute the main package, the first included package works fine. But with the second one, an error occurred: this error deals with insertion of new sets of data in a table. These are exactly the same actions than in the previous package (insert in a new table and update) which runs fine.

Here after you will find the error message:

[Target ISYS_TEMP_INV_EXCELCLEAN [2081]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Transaction context in use by another session.".
[Target ISYS_TEMP_INV_EXCELCLEAN [2081]] Error: An OLE DB error has occurred. Error code: 0x80040E37. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E37 Description: "Transaction context in use by another session.".
[Target ISYS_TEMP_INV_EXCELCLEAN [2081]] Error: Failed to open a fastload rowset for "[dbo].[ISYS_TEMP_INV_EXCELCLEAN]". Check that the object exists in the database.
[DTS.Pipeline] Error: component "Target ISYS_TEMP_INV_EXCELCLEAN" (2081) failed the pre-execute phase and returned error code 0xC0202040.

Can you help me please?

Best regards.

Laurent Albiac

View Replies !

Using RetainSameConnection During Distributed Transaction In SSIS ForEach Container
Hi -

I've seen variations on this question in this forum, but none of the suggestions work for me, so any ideas would be greatly appreciated!

I have a SSIS package whose function is to monitor a directory for flat files and distribute the information in these files into a SQL 2005 DB for further processing later. I've configured Transactions for the package as serializable and supported, and I have a ForEach container set to serializable and required. All SQL and Data Flow tasks take place within this container. I have a single Connection Manager to pull in the flat input file, and another for the connection to the SQL DB. This setup will function, and roll back if necessary, but I take a huge hit on the continual establishment of connections to the DB. If I set the retainsameconnection property on the SQL DB Connection manager, I get an error when executing my first Data Flow task to move data from the flat input file to working tables within the DB: 0xC001A004 - Incompatible transaction context was specified for a retained connection. If I turn off Transaction support, the retainsameconnection property works as expected.

I've been running Profiler Traces, and can verify that no connection has been established to the SQL Server prior to execution of this task, so it seems like the context should be OK. I would welcome any feedback, suggestions or alternatives.

Thanks,

Jim SweeneySLC

View Replies !

SSIS Execution Error While Choosing Transaction As Required
Hi,

I have created a few packages and i want to execute this in a sequence so I created a wrapper/parent package and added all the other packages as child Package using the Execute Package Task. These packages are file system based packages. I am executing the wrapper/parent package from a web page which will execute all the child packages. All is well and works fine when I choose the TransactionOption as "Supported" in my wrapper/parent package but when I choose the TransactionOption as "Required" in my wrapper/parent package I get the following error

Error Occurred: The package is failed due to following: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".

What I am doing is connecting to 3 DB in the same server and doing some data manipulation. The MSDTC is running in the Target SQL Server and also the DTC Server in my Local is started and running. What else could be the problem.

Thanks in advance for any and all help

View Replies !

How To Create A Transaction Commit/Rollback For Oracle DB In SSIS
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 Replies !

Using Transaction On SSIS Package Failed On Cross Domain.
I want to use Transaction(MS DTC) in SSIS package across domain. It's working fine if both the servers are in Corpnet microsoft domain but failing it one of the server at extranet microsoft domain. I did all the required settings for MS DTC service to run for distrubuted transaction and in SSIS package did the "TransactionOption" property for the container object to "Required" and for all inner tasks to "Supported". Is it possible or a Security voilation which won't allow to do through SSIS package.

Example: I have a database on Extranet server and a database at Corpnet Server.

Sql Job will pull the data from Extranet Database to Corpnet database through SSIS package and update back to Extranet database. Job will reside on Corpnet server.

SSIS package is failing with the error message: The AcquireConnection method call to the connection manager "<Connection Manager name>" failed with error code 0xC0202009.

Any special setting do we require for this. I did the following setting on both the servers:

                     Ã˜ MS DTC should run on both the servers under €œNetwork Service€?
Ã˜       Set the following on both the servers(ExtranetCoptnet) to run on a Distributed Transaction:
§         Go to "Administrative Tools > Component Services"
§         On the left navigation tree, go to "Component Services > Computers
§         €œMy Computer" (you may need to double click and wait as some nodes
need time to expand)
§         Right click on "My Computer", select "Properties"
§         Select "MSDTC" tab
§         Click "Security Configuration"
§         Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP"
§         The service will restart

Note: If I will chage the Extranet server to Corpnet then it's working file for me.

View Replies !

SSIS Transaction Management && Strange Deadlock When Using XLOCK, ROWLOCK
I have a SSIS package that is run from one job, nowhere else. The package has TransactionOption NotSupported.

In the SSIS package I first have a sequence container that has TransactionOption Required (Serializable). The sequence container contains several Execute SQL tasks that access the same SQL Server 2005 database. The Execute SQL tasks have TransactionOption supported / Serializable. The first SQL statement is:

select p.column_name

from table_name p with (XLOCK, ROWLOCK)

where p.second_column_name = 'COLUMN_VALUE'

After this there are a couple of SQL tasks, and finally a task containing the following SQL:

select p.third_column_name

from table_name p

where p.second_colomn_name = 'COLUNM_NAME'

The idea is that the first query exclusively locks the row in the table_name-table for mutual exclusion purposes, i.e. so that if for some reason the SSIS package would be executed simultaneously two or more times, only one package execution could lock the row and proceed and the other executions will have to wait. There's an index on column second_column_name in the table to avoid the select locking other rows in addition to the required row.

Some questions:

1) Is it so in my setup that when SSIS runtime executes the sequence container it creates a transaction in the beginning of the sequence container and commits the transaction in the end of the sequence container? And in my setup the Execute SQL tasks in the sequence containar are executed under the same transaction?

2) I have a problem that the second query sometimes gives this error:"ErrorCode: -1073548784. ErrorDescription: Executing the query "XXXXX" failed with the following error: "Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

The 1st query has locked the row for the transaction. How can the second query be deadlocked, shouldn't the transaction have a lock on the row? I'd understand if the 1st query failed sometimes, but don't understand how the 2nd quey can fail.

r,

JM

View Replies !

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.
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 Replies !

The Microsoft Distributed Transaction Coordinator (MS DTC) Has Cancelled The Distributed Transaction.
We have a test db, a staging db and a live db. I have a stored procedure that runs fine on test and staging, but throws the following error on live.

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

The stored procedure uses linked servers and a transaction.
We're using the following transaction code in the stored procedure

BEGIN

BEGIN TRANSACTION

BEGIN TRY

---
procedure stuff here
---
COMMIT TRANSACTION
END TRY

BEGIN CATCH

DECLARE @ErrorSeverity INT, @ErrorNumber INT, @ErrorMessage NVARCHAR(4000), @ErrorState INT

SET @ErrorSeverity = ERROR_SEVERITY()

SET @ErrorNumber = ERROR_NUMBER()

SET @ErrorMessage = ERROR_MESSAGE()

SET @ErrorState = ERROR_STATE()

IF @ErrorState = 0

SET @ErrorState = 1

RAISERROR ('ERROR OCCURED:%d', @ErrorSeverity, @ErrorState, @ErrorNumber)

IF XACT_STATE() < 0

ROLLBACK TRANSACTION

END CATCH

END

I found the following link which seems to be the problem we're experiencinghttp://support.microsoft.com/kb/937517

The link includes a workaround which is the following:
"To prevent the SQLNCLI provider from sending an attention signal to the server, use the SQLNCLI provider to consume fully any rowsets that the OLE DB consumer creates. "

How do I use the SQLNCLI provider to fully consume any rowsets?

View Replies !