Transact SQL :: Is It Possible To Nest Transactions

May 28, 2015

I have the following requirement that I would like some inspiration on.We have a requirement in our database to allow data to be entered that may show data errors, however the errors are a bit complex so that a normal FK relationship can not catch it.How this will work is

1) the user will be allowed to enter data in the database, the data can be across a lot of tables, so we will do it in a transaction
2) a store procedure will be executed that before commit will check the data (as a dirty uncommitted read), the store procedure either passes or fail, if it passes the data is committed into the database.
3) If the stored procedure reports that the data is not consistent it is rolled back, and the reason it is rolled back (which will be generated from the store procedure) should be stored in the database.

What I would like to know is this, is there any way of structuring the the code in a specific way that will rollback the dirty changes but commit the "Reason why it failed" bit of it.

View 7 Replies


Is It Not Possible To Nest IFs In A Stored Procedure

Nov 18, 2007

I have been working on this stored porcedure and now that I have it ready I get an error:
Msg 156, Level 15, State 1, Procedure UpdateFundAllocation, Line 38
Incorrect syntax near the keyword 'ELSE'.
Is it not possible to nest ifs the way I have them?
I greatly appreciate your help.@Receipt_ID Int,
@PRFund_ID Int,
@Fund_ID int,
@Amount_allocated money,
@LastUpdate datetime,
@LastUpdateBy nvarchar(50)


Declare @row_count int
Declare @total_allocations decimal(18,2)
Declare @total_payments decimal(18,2)

SELECT @row_count = COUNT(tblPayReceiptsFunds.Receipt_ID)
FROMtblPayReceiptsFunds INNER JOIN
tblPayReceipts ON tblPayReceiptsFunds.Receipt_ID = tblPayReceipts.Receipt_ID
WHERE tblPayReceiptsFunds.Receipt_ID=@Receipt_ID

IF (@row_count = 1)

SELECT @total_allocations = @Amount_allocated, @total_payments = Sum(tblPayReceipts.AmountPaid)
FROM tblPayReceiptsFunds INNER JOIN
tblPayReceipts ON tblPayReceiptsFunds.Receipt_ID = tblPayReceipts.Receipt_ID
WHERE tblPayReceipts.Receipt_ID=@Receipt_ID

UPDATE tblPayReceiptsFunds SET [Fund_ID]=@Fund_ID,[Amount_ALLOCATED]=@Amount_ALLOCATED,LastUpdate=@LastUpdate,LastUpdateBy=@LastUpdateBy WHERE [PRFund_ID]=@PRFund_ID

SELECT 'Fund was allocated.' AS MESSAGE


SELECT @total_allocations = (SUM(tblPayReceiptsFunds.Amount_allocated + @Amount_allocated)), @total_payments = Sum(tblPayReceipts.AmountPaid)
FROM tblPayReceiptsFunds INNER JOIN
tblPayReceipts ON tblPayReceiptsFunds.Receipt_ID = tblPayReceipts.Receipt_ID
WHERE tblPayReceipts.Receipt_ID=@Receipt_ID

IF (@total_allocations > @total_payments)

SELECT 'You are attempting to allocate more to funds than your total payment.' AS MESSAGE


UPDATE tblPayReceiptsFunds SET [Fund_ID]=@Fund_ID,[Amount_ALLOCATED]=@Amount_ALLOCATED,LastUpdate=@LastUpdate,LastUpdateBy=@LastUpdateBy WHERE [PRFund_ID]=@PRFund_ID

SELECT 'Fund was allocated.' AS MESSAGE


View 2 Replies View Related

Transact SQL :: Server - Transactions Per Second

Oct 13, 2015

I have a requirements to collect Transactions per second on 50 databases from a sql server instances.provide me a script to collect TPS for 50 databases?

View 8 Replies View Related

SQL 2012 :: Nest If Statement In Server

May 27, 2015

I am looking to create a simple IF Else statement in a SPROC but my syantax seems to be wrong.

IF (vchDept='IT')
IF vchEmail like '' AND vchDept<>1


View 9 Replies View Related

Transact SQL :: How To Update Multiple Rows In Different Transactions In A Single Table

Jul 16, 2015

We have control table which will be useful whether we need to start the job or not. If we are starting the Job we will make it to 1.

Below is the Table Structure.

Table Name       IN_USE_FG
CUST_D                     0
PROD_D                     0
GEO_D                       0
DATE_D                     0

Now we have different packages for 4 tables data loading. These 4 packages will start at a time. Before going to load the data we have to make the Flag to 1 and after that we have to load it. Because of this we have written Update statement to update the Value to 1 in respective Package. 

Now we are getting dead lock because we are using same table at a same time. Because we are updating different records. 

View 6 Replies View Related

Transact SQL :: Replicating / Synching Data Between Two Tables On Same Database With Live Transactions

Oct 7, 2015

Client is running X- version of application and corresponding database size is huge. Now client's vendor is releasing Y-version of same application with many database schema changes (like new tables added, new columns added, renamed existing columns and etc) To upgrade to the Y-version, vendor is suggesting to my client that down the system and do the upgrade for application/database to Y-version. We are sure that this process will take days together to upgrade to the Y-version. My client is not ready to down the system for that long. So we are trying to find the solution with minimal down time.The approach we are thinking is, 

1) Create the replicated database to another server (server2) from production server(server1) using golden gate with X-version

2) Create new tables/schema updated tables from Y-version database on same server1. Here for  Updated schema tables we are planning to use the name <table_name_Y_version> as the same table name exists in X-version.

3)With above 2 steps, golden gate replicate the changes from production to server1 and server1 will have the new Y-version table schema (with different concatenate name ' _Y_version'). BTW , there is no affect for the production

4) At this stage we are planning to find best approach, to fill the '<table_name>_Y_version' from X-version tables. two challenges here a) all data needs to be moved to Y-version tables b) they have to sync data in real time.

we thought of going to

a) ssis package to pump the data to Y-version tables, but real time data will not sync.

b) trigger based technique, previous experience said, lot of load

c) thinking about sql replication.

View 5 Replies View Related

Changing Connection Transactions To Database Transactions

May 22, 2005

Hi there,
I have decided to move all my transaction handling from to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in But what about exceptions? In, I am used to doing the following:
<code>Try   'execute commands   myTransaction.Commit()Catch ex As Exception   Response.Write(ex.Message)   myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated

View 3 Replies View Related


Aug 13, 2003

Hi All,
Can anybody suggest me a website where I can find articles on Managing transactions with Sql server. Also a scenario where the transactions take place in a environment involving 2 different databases, Like the bank account and credit card transactions (specifically of 2 way kind)

View 5 Replies View Related


Apr 6, 2005

I have a web application with a shopping cart, how do I stop all the shopping cart transaction from going into the db log? Is this possible?  These are are only transient data movements, and will never be need to to restore to, and they are cause log bloat. Or is there a better way to stop log bloat?

View 2 Replies View Related


Apr 17, 2000

Hi All,

How can we change connection properties in a DTS pkg with connection?
You can loop through the connection count but the connection ID is not static one.So cant rely on that.
Is there another way of changing connection properties?

Thanks in Advance


View 4 Replies View Related

Help With DTS Transactions

Sep 5, 2000

Hi all....

I am currently designing a DTS Package to import data that is processed daily into a large database.

I have to design the package such that if any step fails when importing, I roll back the entire transaction.

I have designed the package with this in mind, checked "join transaction if present" and "rollback transaction on failure" in all of the workflows. I have also made all workflows serialized.

However, when I run the package, it fails on one of the data pumps with the error:

Transaction context in use by another session.

Any ideas?

Thank you,


View 2 Replies View Related

I Want To See My Transactions

Dec 1, 2000

I am replicating (finally!!) and on my publishers agent history I can see it says xx transactions with xx commands were delivered. (xx being the number)
Where can I look to see what the transactions or commands are?

Is there a place the system stores this information?

View 1 Replies View Related

Max. Transactions

Oct 17, 2003

what is maximum limit of no. of transactions per sec. in sql server 2000

View 2 Replies View Related


Sep 6, 2004

Is there a point to wrapping a single UPDATE or INSERT statement in an explicit TRANSACTION:


INSERT INTO Table (...) VALUES (...)


I understand ACID and concept of transactions. However, I thought they were only necessary for multi-statement operations. I'm maintaining code that does this and am wondering if this is necessary. Does SQL Server guarantee ACID for single statements? Are single UPDATE/INSERT statements prone to race condition like affects without using explicit transactions?

View 2 Replies View Related


Apr 17, 2008

Are there any scenarios where an un-commited transaction would block further queries?

View 1 Replies View Related


May 27, 2008

If you run the Begin Transaction code and then run a create such as an update query and you see that it effects the number of rows that you wanted it to effect is there a way to look at the actual data that changed before you Commit Transaction?


View 7 Replies View Related


Jul 1, 2007

I have a table with around 240 columns and one of the column in the Table is the Inserttime ( DATETIME ) and I using a GETDATE() function in the stored Proc, when we insert data into the table. In the same Milli second 2007-06-27 09:32:58.303 , I have around 7600 records in the database. The Stored Proc is called for each Individual record and we don't bunch the transactions. Is this possible.

I did some bench marking on this server and I can insert only 700 - 800 records approx / sec on this particular table.


View 6 Replies View Related


Aug 1, 2007

I have a small database that I have been testing.I get an error about a transaction deadlock.The code is in stored procedures and I added transactions to the sp'sbut the error happened again.I wrapped the whole sp in just one transaction and I don't have anyindex on the tables.When I test just by running a program that sends 3 calls at a time itwill get a deadlocked transaction as I send 6 or 9 at a time.I am not sure how it can have a deadlocked transaction after I usedtransactions(begin and commit) in the sp's.Steve

View 4 Replies View Related


Oct 8, 2007

I am working with transactions and use try catch to capture errors and in the event of an error i have to rollback the transaction. How can i perform this?, most of the errors which i forsee are either insertion of null values into non nullable columns or violation of Primary keys while inserting duplicates.
I started by coding the following way but it does not rollaback apparently the try catch does not work for above kind of errors..Can somebody help..




-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.



--Step 1











--Step 2
























FROM [dbo].[IN_T_FLE]





ERROR_NUMBER() as ErrorNumber,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage;

-- Test XACT_STATE for 1 or -1.

-- XACT_STATE = 0 means there is no transaction and

-- a commit or rollback operation would generate an error.

-- Test whether the transaction is uncommittable.

IF (XACT_STATE()) = -1



N'The transaction is in an uncommittable state. ' +

'Rolling back transaction.'



-- Test whether the transaction is active and valid.




N'The transaction is committable. ' +

'Committing transaction.'




View 3 Replies View Related

Sql Transactions

Nov 8, 2006

Hello All,

When i am working with Transactions i got one doubt.

If i am inserting any records into a table with primary key if a transaction is rolled back i am finding one primary ID is missing. Is it so.

View 3 Replies View Related


Apr 2, 2007

I've been searching around and haven't found anything that simply states what I want to know.

I want to use a transaction within my CLR Stored Proc, to do so I've got System.Transactions referenced and I can access the current transaction via Transaction.Current.

My questions are

Will there always be a current transaction?

Do I need to create a new transaction if one doesn't already exist?

View 3 Replies View Related

Transactions In SQL CE 2.0 And CF1.1

Sep 4, 2006

Hi world:

I have this issue:

I need to push rows from CE to SQL Server 2000 and after delete these rows of CE database only if all rows have been sent to SQL Server 2000.

I think the best is work with transactions. Since I know I can use transactions for this purpose, can anybody give me a link with push transaction examples ?

View 4 Replies View Related


Dec 17, 2007

Hi all,

What does "Transactions/sec" counter in SQL 2005 under databases do in terms of performance. My counter shows almost 100% all the time in 4 terrbyte DB in superdome with many CPUs.

Which is the optimal value for this counter?

thanks in advance


View 1 Replies View Related

Using Transactions

Mar 31, 2008


I am using C#.NET 2005, SQL Server 2000.

I have a few questions to ask. Firstly, can anybody help me by telling the difference between the code below and the same code with comments removed:

string localquery = "select bla bla"; SqlDataAdapter _sda; DataTable _dt; SqlCommand _cmd; bool isErrCatched = false; Exception ExcToThrow = new Exception(); using (SqlConnection _cn = NewConnection) // NewConnection returns a static SQLConnection object { _cn.Open(); _cmd = new SqlCommand(localquery, _cn); //SqlTransaction myTrans; //myTrans = _cn.BeginTransaction(); //_cmd.Transaction = myTrans; _dt = new DataTable(); _sda = new SqlDataAdapter(_cmd); try { _sda.Fill(_dt); _sda.Dispose(); //myTrans.Commit(); } catch (Exception exc) { //try { myTrans.Rollback(); } catch {} isErrCatched = true; ExcToThrow = exc; } finally { _cn.Close(); } _cmd.Dispose(); } _cmd = null; if (isErrCatched) throw ExcToThrow; return _dt;

My first question: Can there be a performance loss if I uncomment the lines about transaction usage? I mean, when I do this I start to get more timeouts.

My problem goes on. When I comment those lines and run a stress tool, I am getting "column X does not belong to table Y" errors. If those lines are not commented i am not getting this error, but I get timeout errors frequently. So, my second question: is there something wrong in my query or is there a bad coding practice I am following? Could someone offer a better and more robust sample for this code block?

By the way, connection pooling is on. And these errors are observed under high loads.

Thanks everybody.

View 3 Replies View Related


Jun 7, 2006

I have transaction that will run on an hourly bases. I need to make sure that no one will start this transaction while it is running. I just need to know the system table that has all the transaction names in them and check to see if the transaction is running or not. what i am trying to do is not to have locks....just wanting to make sure that no one would run the same transaction twice. does anyone have any idea on how we can do this?

View 7 Replies View Related


Mar 11, 2008

If you ask a .net developer he would likely say that he uses System.Transactions to manage transactions, a DBA on the other hand places transactions within the T-SQL of stored procedures.

What rules do architects and others, use when determining if the transactions should be placed in:

.net Middle Tier Components using System.Transactions for example.

T-SQL Stored Procedures.

Both .net Middle Tier Components and T-SQL Stored Procedures.
Thanks in advance,

Doug Holland

View 1 Replies View Related

Transactions And Exceptions

Nov 1, 2006

if i have a loop that runs through records in a dataset like thisfor(int i=0;i<ds.Tables[0].Rows.Count;++i) and in this loop i have several sql commands that run as a transaction in a try / catch block like : try{ // do stuff}catch{    trans.RollBack();}how can i keep the loop going even if a transaction failed.  So the transaction works for each individual row.  if row 100 fails for whatever i would like the loop to continue running, do i just simply remove the "throw" and it will continue looping ? my catch block currently looks like catch(Exception ex){transaction.Rollback();activity.Log("Transaction aborted, rolling back. Error Message: " + ex.Message + " Stack Trace: " + ex.StackTrace.ToString());throw; }thanks,mcm

View 4 Replies View Related

Transactions And MSDTC

Nov 3, 2006

I have a class that use a TransactionScope object for make make two operations into a database. The problem is that in a computer works well but in another I receive a error message: "MSDTC is not running in .SQLEXPRESS". I had watched the services in the computer and the "Microsoft Distributed Transaction Coordinator" is running.
Does somebody know what is happening?
Thankyou and sorry for my English.

View 2 Replies View Related

Transactions In Sql Server

May 9, 2007

I am using ADO.Net for data access and was wondering if anyone knows a good resource for information of sql transactions? Also, do you know if the ForEach statement can be made in sql transactions?

View 3 Replies View Related

DataSet And Transactions...

May 14, 2007

hi all,
i'm developing a WebApplication with VisualStudio2005 (with C# code).i've defined a DataSet with some TableAdatpter. For each TableAdapter, i've definet some "customized" commands, performing specialized operations over the table on DB. (db is SqlServer2000).For each TableAdapter, i've set as "public" the connection, so that i can use it within my code for beginning a new transaction. Now, how can i set that transaction, to the specialized commands I defined for each TableAdapter?
Thanks all.Andrea.

View 1 Replies View Related

Ending Transactions

Feb 2, 2008

Hi Guys,
Im having trouble stopping a transaction once the result on one or more of my sql statements rolls back the transaction. Take a look at one my first 'IF' statement (below).  If the parameter '@Return' is 'False' i want the transaction rolleback and all sql execution to stop. But my problem is the rest of the sql gets executed and i end up getting rubbish data. Im still new to transactions so i think im doing something really wrong. Please take a look at my sql and help me out in what ever way you think is best. thanks in advance.
SELECT @Return = (SELECT IsReturnable FROM INVENTORY WHERE InventoryID = 6)
DECLARE @Already_In INT;
SELECT @Already_In = (SELECT ReturnedBy FROM InventoryInUse WHERE (CheckOutNumber = 375))
DECLARE @Outstanding As INT;
SELECT @Outstanding = (SELECT (QtyTaken - 40) AS Outstanding FROM InventoryInUse WHERE (CheckOutNumber = 375));
DECLARE @OutstandingReason As VARCHAR(200);
Select @Get_Person_ID = (SELECT PersonID From [User] WHERE Username = 'matthewsk');
ELSE IF (@Outstanding > 0)BEGIN
IF (@OutstandingReason = '') SELECT @RollBack = @RollBack + 1;
UPDATE InventoryInUse SET ReturnedBy = @Get_Person_ID, ReturnDate = GETDATE(), QtyReturned = 40, QtyOutstanding = @Outstanding, WriteOffReason = 'Testing This' WHERE (CheckOutNumber = 375);
UPDATE Inventory SET TotalQty = TotalQty - @Outstanding, QuantityInUse = QuantityInUse - (40 + @Outstanding) WHERE InventoryID = 6;
END;ELSE IF (@Outstanding = 0)
UPDATE InventoryInUse SET ReturnedBy = @Get_Person_ID, ReturnDate = GETDATE(), QtyReturned = 50 WHERE CheckOutNumber = 375;
UPDATE Inventory SET QuantityInUse = QuantityInUse - 50 WHERE InventoryID = 6;

View 3 Replies View Related

Testing Transactions

May 21, 2008

I am using SqlServer 2005  Express Edition.  I have written a  stored procedure that has a transaction to make sure that it rolls back in case of failure.  Is there a way to test that the transaction works. I don't now how to cause an error that would cause the transaction to roll back and raise the error with the details of the exception.

View 2 Replies View Related

Transactions In C# Code.

Jan 9, 2004

I have a form that calls three separate queries to insert its contents in my database. The first query runs once and returns an identity value back to the calling code. Using this ID, it runs a foreach() on the selected items of two listboxes; running multiple inserts for each.

I want to group all of those inserts into a transaction that will rollback if any insert fails. I'm familiar with doing this in a stored procedure, but since this is a case where I can't use a single sproc I'm not sure exactly how.

I've been stumbling through using a SqlTransaction object and handling it from my code, but I can't seem to find much information on doing this. Any examples of a multiple query transaction using the SqlTransaction object would be very helpful.

View 6 Replies View Related

Copyrights 2005-15, All rights reserved