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

Take a look at this:

http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

Basically, if you want the scope of your transaction to be bigger than a single Execute SQL task, you need to define your transactions declaratively through the way you build your packages. The key is the TransactionOption property of containers, which include packages, Sequence containers and the "invisible" TaskHost container that wraps each task. This property works much like transactions did in COM+ and MTS, so if you've done work there it should look at least a little familiar.

|||

thanks mattew, the paper was very useful

But I used a different way to achieve my purpose.

I used same connection manager for all Execute SQL tasks with RetainSameConnection = True.

Thanks,

Prash

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

Take a look at this:

http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp

Basically, if you want the scope of your transaction to be bigger than a single Execute SQL task, you need to define your transactions declaratively through the way you build your packages. The key is the TransactionOption property of containers, which include packages, Sequence containers and the "invisible" TaskHost container that wraps each task. This property works much like transactions did in COM+ and MTS, so if you've done work there it should look at least a little familiar.

|||

thanks mattew, the paper was very useful

But I used a different way to achieve my purpose.

I used same connection manager for all Execute SQL tasks with RetainSameConnection = True.

Thanks,

Prash

How do I find out whos locking a table? and than do something about it?

So randomly every 1 to 6 days queries start timing out and I'm almost positive it's from an improperly terminated transaction

Is there a way to snoop this out the next time it happens? Like when a table's locked I can look and see yea this is the transaction it's in the middle of?

You can either do this pro-actively or reactively.

Proactive : Set up a job that runs perhaps every minute, queries sysprocesses and spits the result into a table. So when a timeout happens you can query this table and see who/what/when.

Reactive: As soon as you see a timeout, query sysprocesses, get the spid, do dbcc inputbuffer(@.spid) get the T-SQL and fix it.

|||

If you already know the proc, fix it.

|||

You can use the Activity Monitor in SQl Server 2005 Management studio. Located under Management -> Activity Monitor.

|||

jperry4:

You can use the Activity Monitor in SQl Server 2005 Management studio. Located under Management -> Activity Monitor.

SWEET that's exactly what I've always been looking for :)

one more questions
There's like 100 processes in there that are "Sleeping" "Awaiting COmmand"

Are these like connections that haven't been closed?
So in CODE if conn.Close() or conn.Dispose()

NEver got called it's just left dangling in a "Sleeping" "Awaiting Command" state?

Shane

|||

No, those are likely connections that are in the connection pool.

Don't get confused by a database physical connection and a logical connection. Conn.Close closes the logical connection, which (probably) is tied to a physical one. The physical connection is then returned to the connection pool waiting for someone to request a connection again.

|||

Cool thanks for the info :)

Yea I ran a bunch of tests with basic programs just opening and closing connections and then read a bunch of stuff on pooling...

The Activity Monitor tool worked out great though for sniffing around the application and finding all the leaks though

Finding those places where the logical was never shut down so it kept opening 100s of new ones

how do i empty the transaction log

good day to everybody

I have here again encountered the problem that the transaction log came too big that it consume my 10 gig hardisk. How do I empty this in a simplest way.

I follow the error message to backup the transaction log to empty the table, but i think it's still the same. I did not see the difference, in short nothing happens. it doesn't empty the transaction log.

So what I did is make another database with different name (ex. tempdatabase), and import the tables including the record on the original database... then delete the original database which the transaction log got too big, and after that make a new database with the name of the original database and again import from the tempdatabase, and manually create the index for every table. It works OK, but is there any other way aside from my own way which is much easier and not time consuming.?

Thank you very much . . . and any help is very much appreciated.
MadixHi Madix,

Do you have any replications on the database whose log is too large? And if there is, is it transactional or merge replication? And what is the recovery mode of your database?

BACKUP LOG without NO TRUNCATE should help to make the logical log file shorter. But it may not reduce the size of a physical log file. You may consider to shrink the log (see BOL "Shrinking the transaction log").

Thanks,
-Peng|||actually sir, i use SQL Server database as my back-end database for my VISUAL FOXPRO front-end, and it has no other problem except that one. So I did not bother to study some of the feature of SQL capabilities. I do not know what the sql server is doing inside of my system.

I make a database, add a table, & select a key field in SQL Server then close it, set the back-up time, that's all i know in SQL. that is why when i encounter this problem, i've got a headache using my own way. it waste time. Maybe its time to know and ask from the experts what is the shortiest solution to solve this problem.

so far that is the only problem i encounter using it in Visual Foxpro.

Thanks for any help..

madix|||The simplest way to resolve this is to change the recovery mode to Simple.
This will remove the need for a transaction log, but may not meet your DR requirements.|||Hi,

U can also try this command below

DUMP TRAN <DATABASE NAME> WITH NO_LOG

Regards
Sachin

How do I disable transaction logging in a stored procedure?

Greetings,

I would like to turn off transaction logging for a long-running stored procedure. It is doing some one-shot utility work in a temporary table. If the stored procedure fails then I'll delete all the rows in the table.

Is is possible to turn off (or at least minimize) the logging for a single stored procedure? I'm looking at this from a performance perspective, thinking that doing no logging will speed things up a bit. The stored procedure will potentially update hundreds of thousands rows.

Am I on the right track or barking up the wrong tree?

Thanks,

BCB

hello,

everything that happens in the database are entered into the transaction log

when you are in the full recovery.

you may only choose any of the three recovery model to handle the log size

1. simple

2. bulk logged

3. fully logged

you can deal with the logs only as a part of your db maintenance procedure.

thanks,

joey