Transaction Calling?

T-Virus
T-Virus used Ask the Experts™
on
Hello,

let's assume I would have 2 query windows opened in MSQL Studio...
now i would like to call the transaction h1 executed in the first window

BEGIN TRAN h1

TRUNCATE TABLE test

SELECT  *
FROM    dbo.Test

INSERT  INTO dbo.Test ( field1, field2 )
VALUES  ( 'Test1', 'Test1' )

from the 2nd query window...

BEGIN/CALL? TRAN h1

SELECT  *
FROM    dbo.Test

COMMIT TRAN h1

how ist it supposed to work ´?

Thanks for help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> how ist it supposed to work ´?

You have to issue COMMIT TRAN h1 in your first window to see the inserted records in the second window unless otherwise the Transaction isolation level is set to Read uncommitted.

If not, you have to commit the transaction in first window to view the inserted records.

Hope this clarifies.

Author

Commented:
Well i know that but what if i would like to insert more informations form the 2nd query window before commiting the transactin?

Is there a way?

Thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Yes.. You can depending upon your Transactional Isolation level.
If you haven't changed your Isolation level, then it would be READ COMMITTED by default and you can insert values without any issues.

Kindly run the query below and tell me your existing Isolation level

DBCC useroptions

Isolation level will help me to answer in a better way as it depends upon it.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
OK it is...

Set Option      Value
isolation level      read committed
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
If it is READ COMMITTED, then you can insert some records in your Second Window which will be visible to SELECT statement in First Window and vice versa.

Once the user commits in his window, inserted / modified records will be visible to the second window.

Hope this helps.

Author

Commented:
Well Thanks!
But there must be a way to access/Jump into the Transaction and do some additional stuff
before commiting in query window 2.

I would like to leave the Transaction open till i close it somewhere... and access it aswell from different
PC's for exaple

Would you have a code sample for me like...
Query Window1:

BEGIN TRAN h1

TRUNCATE TABLE test

SELECT  *
FROM    dbo.Test

INSERT  INTO dbo.Test ( field1, field2 )
VALUES  ( 'Test1', 'Test1' )

Query Window2:

BEGIN/CALL? TRAN h1

INSERT  INTO dbo.Test ( field1, field2 )
VALUES  ( 'Test2', 'Test2' )

SELECT  *
FROM    dbo.Test

COMMIT TRAN h1
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> But there must be a way to access/Jump into the Transaction and do some additional stuff

There's no option to jump into a transaction from some other connection / transaction as of now.

>> I would like to leave the Transaction open till i close it somewhere... and access it aswell from different PC's for example

Not possible. A transaction is Session / connection dependent and hence you can't call that transaction from some other session / connnection.

That's the reason SQL Server allows you to create Same Transaction name from different sessions.
You can create a transaction with same name from different sessions like

begin transaction test1

from both query windows and you wont obtain any errors for it. You need to then rollback / commit it separately in both windows

Hope this clarifies.

Author

Commented:
Thank you very well!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Glad to help you out..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial