Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Transactions in SQL Server (Read 4327 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Transactions in SQL Server
12.03.14 at 13:49:49
Transactions in SQL Server
A test was performed to show how transactions work in SQL*XL in a connection to SQL Server.  
We use SQL*XL to call a stored procedure in the SQL Server database to add a row to the test database. We will inspect the creation of the new row in SQL*XL and also in the database server. Using the transaction button in SQL*XL we have control over the transaction.
We used SQL Server 2008 Express and SQL*XL 5.1.7.
The following command was run in the SQL Server database to create the procedure that inserts the row.
alter procedure test_proc1
as begin
set nocount on
begin transaction
inse [/code] [code]rt into dbo.test(id, a)
values (1,123)
commit transaction

From SQL*XL, in the SQL dialog the following command is run:
exec test_proc1
Next in SQL*XL the command: select * from test is executed. The inserted row is displayed in Excel.
Next in Microsoft SQL Server Management Studio the same select statement is executed. The query is hanging because probably the table is locked by SQL*XL.  
When pressing the commit button (or rollback button) in SQL*XL the transaction is ended (despite the commit transaction in the stored procedure) and the result can be seen in SQL Server. The creation of the row is canceled when the rollback button was pressed.
Back to top
« Last Edit: 13.03.14 at 08:33:58 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1