Transactions - Problem With Commit 2nd Time Round
May 27, 2006
Hope this is the right forum. I'm using Transaction=required on a page which inserts on multiple tables, 2 of which have a foreign key relationship. All works fine as log as I don't input erroneous data. However, I have a range check in the code, and if the range is exceeded, an exception is thrown and the transaction fails using ContextUtil.SetAbort(). I then correct the data and try to save and get a Foreign key contraint error. I have debugged and the primary key table seems to be carrying out the insert ok (I'm retreiving the key at that point, and can see it). But when I use the key in the child table it fails and cites the foreign key relationship.
I suspect that having the same data for the primary key table 2nd time around means it doesn't think it has to commit????
Grateful for any help. I'm using Sqlserver 2005 by the way.
View 1 Replies
ADVERTISEMENT
Aug 21, 2002
l also use the
begin transaction
select ........etc
commit
structure when l wrtite queries.My problem is that if l close the query analyser it asks me to commit transaction before l exit. Why?
How do you check for uncommitted trans and commit them?
View 1 Replies
View Related
Mar 14, 2001
Many times i write stoted procedures with transaction blocks.
I have delete a row after begin transaction and in continue i
read from table the select statement get back the deleted row:
begin tran
delete mytable
where id = @myid
and seqid = 3
select sum(balance)
from mytable
where id = @myid
............
...............
commit tran
.... OR
rollback tran
the sum(balance) function has calculate the balance of row 3
I use SQL 7.0
Thanks
Renato
View 1 Replies
View Related
Sep 18, 2007
I have a series of questions about SSIS and transactions. The answers to these questions are probably so obvious that I can't see them, so please feel free to just point out what it is that I'm missing. My transaction-processing experience is very low-level, so I'm probably just not seeing how it's done at the high level of SSIS.
The first question is one that I may know the answer to, so please confirm:
Consider a package with TransactionOption set to Supported. It contains a single Execute SQL Task with TransactionOption set to Required. Is it true that if that Execute SQL Task succeeds, that the transaction commits, and that if the task fails, the transaction rolls back?
Consider another package with TransactionOption set to Supported. It contains a Sequence Container with TransactionOption set to Required. That container contains our same Execute SQL Task, but that is joined to a script task by a "success" precedence constraint. The script task simply returns Dts.Results.Failure. Is it the case that the transaction will roll back? That is, is it truly a simple failure result that would initiate the rollback?
If a DataFlow Task is the one that is set to Required, does that mean that every transactional operation within that task will commit in a single transaction? For instance, if I'm inserting five rows for each input record from a flat file, and if my flat file has 1000 records in it, will I see a single transaction with 5,000 rows?
Thanks for your patience!
View 5 Replies
View Related
May 5, 2008
hi,
I have wriiten the code cn.Open();
SqlCommand CmdInsertAct1 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName1.Text.ToString() + "','" + txtActAmt1.Text.ToString() + "','" + ddlAct1.SelectedValue + "' )", cn);SqlCommand CmdInsertAct2 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName2.Text.ToString() + "','" + txtActAmt2.Text.ToString() + "','" + ddlAct2.SelectedItem.Value + "')", cn);
SqlCommand CmdInsertAct3 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName3.Text.ToString() + "','" + txtActAmt3.Text.ToString() + "','" + ddlAct3.SelectedItem.Value + "')", cn);SqlCommand CmdInsertAct4 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName4.Text.ToString() + "','" + txtActAmt4.Text.ToString() + "','" + ddlAct4.SelectedItem.Value + "')", cn);
SqlCommand CmdInsertAct5 = new SqlCommand("insert into EmpActuals(PayrollGroup,GroupName,PaymentName,PaymentValPer,Percentage) values('" + txtPayBatch.Text.ToString() + "','" + txtPayBactName.Text.ToString() + "','" + txtActName5.Text.ToString() + "','" + txtActAmt5.Text.ToString() + "','" + ddlAct5.SelectedItem.Value + "')", cn);
CmdInsertAct1.ExecuteNonQuery();
CmdInsertAct2.ExecuteNonQuery();
CmdInsertAct3.ExecuteNonQuery();
CmdInsertAct4.ExecuteNonQuery();
CmdInsertAct5.ExecuteNonQuery();
cn.Close();....................................................................
in this code I want to put Commit,Begin,Rollback Transactions.Plz help me.send replies urgently.
View 3 Replies
View Related
Apr 22, 2014
I have a update trigger. In this trigger I need to insert few records in 3 tables. If error comes in any of these inserts then previous inserts to get committed. This trigger was written in Sybase and it was possible to create transaction and commit the transactions.
View 4 Replies
View Related
Feb 8, 2004
I have to modify the table structure where the table have a lot of data already. The log is getting full due to uncommitted transactions, there is a lot of data being updated in large bulks, not all of the transactions are committed, the update task cannot be completed.
However, there is no more spare disk space for it to commit the transaction. Anyone can help?
View 2 Replies
View Related
Jun 29, 2007
If application code controls all transaction processing to SQL Server, so it starts a transaction, does any commit or rollback on teh application side, how does that actually work ON SQL Server 2005... Meaning, If the app passes in a isolation level of Repeatable Read, and the database default is different, how can I see what is being used, as a DBA? Can I see any of that via Profiler? can I see when those commits/rollbacks are issued from teh application. They are not sending in "SQL" commit/rollback transaction commands. It's built-in to their architecture to control all that... How can I see what's happening on the database if these are not SQL commands for transaction handling? and how does that work, to start a transaction on the app side, and hold locks etc, on SQL Server if normal SQL Server commands are not being sent? If anyone can point me at decent references to read on that, thanks! Bruce
View 4 Replies
View Related
Nov 12, 2007
Hi,
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp €“ all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()
update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
Regards,
Mark
View 8 Replies
View Related
Jun 2, 2011
SSRS 2008 R2. I am writing a report that uses a DB4 database. Because of this many SQL commands that I normally use are not available and I don't know what commands I can use. Because of this I am doing much of my calculations inside of SSRS.
I need to round a time field to the nearest quarter hour inside of SSRS.
For example:
8:12 becomes 8:15
2:30 becomes 2:30
1:57 becomes 2:00
4:07 becomes 4:00
View 9 Replies
View Related
Jul 20, 2005
I want to Insert 10000 rows at a time and commit in sql server , Isthere a way to do it if the source tables have no id fields ?What would be the most efficient method?ThanksAjay
View 1 Replies
View Related
Jul 27, 2012
I have a table with the below columns. I would like to create the result table below that pairs transactions with "LIKE" transactions. The only exception is that "OUT" can basically stop anything.
compIDcommIDTRANSDATEUNIT
11MSF1/1/2012101
12CS11/1/2012101
13CS22/1/2012101
110OUT5/1/2012101
112MSF5/2/2012301
113CS15/2/2012301
114CS26/2/2012301
115OUT10/2/2012301
24MSF1/1/2012102
[code]...
View 1 Replies
View Related
May 12, 2015
There is a database "Foo" sitting on server "A". There is a database "Bar" sitting on server "B". A.Foo publishes a subset of its schema. B.Bar subscribes to A.Foo's publication. The distribution database is on "B" (B.distributor). This a push subscription (transactions are pushed to the subscriber from the distributor). Every day (including the weekend) I get the following alert:
"5/12/2015 3:53:16 AM, Unsubscribed Transactions (Count) on "B" is Warning.
SQL Server instance "B" has 636771 unsubscribed replication transactions received by the Distributor and not received by a Subscriber.
Unsubscribed Transactions (Count): Number of replication transactions received by the Distributor and not received by a Subscriber."
The number of transactions will vary. The alerts will be sent between 1:20 AM (EST) and 3:30 AM (EST). I'm trying to figure out what is causing the backup of transactions. I assume the issue precedes the alerts by 30-minutes or so.
There are no backups occurringNothing is blocking the distributor agent in the subscription databaseJob activity is at a minimum; the few jobs running run throughout the dayThe machine has plenty of resources -- CPU, RAM, etc.The publisher database shows no signs of stress.
View 3 Replies
View Related
Mar 28, 2008
Microsoft SQL Server Management Studio Express
select @@trancount
begin tran
select @@trancount
use ProdNetPerfMon
select @@trancount
update Nodes set Caption = 'xxxx' where Vendor = 'yyyy'
select @@trancount
commit tran
select @@trancount
Executes as expected including trancount without errors.
Nodes.Caption is updated but reverts after a few minutes.
sa privileges
What am I missing?
View 1 Replies
View Related
Jan 30, 2006
Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt
View 3 Replies
View Related
May 22, 2005
Hi there,
I have decided to move all my transaction handling from asp.net 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 asp.net. But what about exceptions? In asp.net, 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
Sep 14, 2006
X is a float and I need to perform x/10 and round the result up to the integer. So if result is 0.4 -> 1, if 1.1 -> 2. How can I do this with SQL?
View 1 Replies
View Related
Sep 9, 2007
I want to do a simple thing but it seems to be behaving not as i am expectingI want to round number either up or down....e.g: 4.3 should round to 4 4.7 should round to 5when i use the round function like this: 83/17=4.88round( 83/17 , 0 ) it gives the answer as 4....when i was expecting it to be 5.... i know there is a ceiling function...but depending on the value of the division sometimes i want it to round up and sometimes round down. how can i do this? hope this makes sense. thanks
View 3 Replies
View Related
Mar 25, 1999
Has anyone been experiencing problems with rounding to 2 decimal places in SQL 7? I have a bunch of queries that generate web reports. Under 6.5 everything was fine. Now under 7.0 any number that needs to be rounded to 2 decimals is actually giving me several decimal places.
Here is a simplified version of what I am doing:
DECLARE @x real
DECLARE @y real
SELECT @x = 223.1
SELECT @y = 59.7
SELECT ROUND((@x/@y),2)
Result should be 3.74. But instead I am getting 3.7400000000000002
If anybody has heard if Microsoft has declared this as a known bug please let me know.
Thanks for your time.
View 1 Replies
View Related
Aug 22, 2002
I have the statement below which I use in an update
select (round(sum(tottime/60),2)) as ttime from vw_cms_suptime
where vw_cms_suptime.[tracking number] = 970
tracking.[tracking number]
Even though I have the round the statement returns a value
of 5.0000000000000003E-2
Is there something wrong with the round?
View 1 Replies
View Related
Apr 17, 2008
Hi,
I need to display the value of a certain varible rounded to two digits after decimal.
For example :
value of a is 1346.8500
I need to get the value of a as 1346.85
Please help me
View 4 Replies
View Related
Jan 4, 2007
hi,i have one criteria
if if i have time as 1:15 min means it has to show 1:15,if i have 1:20 min it has too be rounded and it has to show 1:30 min.
can any one give me query for this
thanks in anvance
View 3 Replies
View Related
May 14, 2007
How to round off the value .579 into .6 using round function.i tried doing it but in vain.
View 3 Replies
View Related
Jul 30, 2007
how do i round off value 0.23 to 0.2.
i am using this inside a scalar function and the return type is numeric(6,2).
so how do i get 0.2 and not 0.23
View 4 Replies
View Related
Aug 10, 2007
Hi
I have datacloum called 'price' (float) and using the below code in my stored procedure
cast(price * 1.175 as decimal(19, 2)) as [item-price]
I am getting the price as (ex1: 23.58, ex2: 114.25, ....etc)
So How do I round the value(price) to (ex1: 23.99, ex2: 114.99)
Advance thanks
View 3 Replies
View Related
Aug 23, 2007
i want to round off 0.23 to 0.25 ,i am trying it with round function but can't get it.
View 3 Replies
View Related
Jul 20, 2005
Hi,i need to round:3° decimal between 1 and 5 LOW3° decimal between 6 and 9 UPThanks
View 1 Replies
View Related
Oct 10, 2007
There is a filed in a table of type decimal(12, 8)
when running a select query on this table for this field i.e.
select field1 from table1
the data is shown like:
102.12500000
104.12500000
And therefore the report shows the same figure.
How do I get this field to show up in the report as 4 decimal places. i.e.
102.1250
104.1250
Tried the properties of the cell to format the text into a number but there is only 2 decimal places there.
Even tried the expression by using Rnd(field1). This does not seem to do what I am after.
Thanks
View 1 Replies
View Related
Jun 26, 2000
How do I write sql syntax to round the last digit. For example, 12.152 should be 12.15. My current sql syntax does not round the last digit as follows:
Select tblARInvoiceDetail.UnitPrice * tblARInvoiceDetail.Quantity AS ChargeBeforeDiscount
FROM tblARInvoices INNER JOIN
tblARInvoiceDetail ON
tblARInvoices.ARInvoiceID = tblARInvoiceDetail.ARInvoiceID
The tblARInvoiceDetail.UnitPrice column is numeric;length 9;precision 18
The Quantity column is integer;length 4; precision 10;scale 0
Any Suggestions?
Thanks,
Denise
View 1 Replies
View Related
May 5, 2004
Hi,
I am using sql statement to save data in SQL SERVER but even i did not apply any round function it is automatically rounding up. e.g. 3.56 when i see it in database it is 4 how can i avoid this rounding? I am using MS Access as front end.
Any help will be highly appreciated.
View 11 Replies
View Related
Jun 18, 2008
I have a money field, which I want to Round price in it to nearest .95 cent . The problem I have is, I don't know how to give 0.95 to Round function.
Thanks
Mazdak
View 1 Replies
View Related
Feb 12, 2014
I've a table having the following fields :
state|dist|company|round
1 | 1 | 2 | 2
1 | 1 | 3 | 3
2 | 1 | 3 | 2
2 | 1 | 1 | 4
2 | 2 | 12 | 1
2 | 2 | 9 | 2
2 | 2 | 4 | 3
I want to extract the max round in the state,district with the adjacent company.
View 3 Replies
View Related
Mar 19, 2014
I have already summed the values and this below query sum the values and returns zero if there are no values. How can I roundoff the values for these columns.
isnull(sum([Old_Values]),0) [Old Values],
[New_Value] = isnull(sum([Yearly_Value]-[Previous_Year_Value]),0)
Even this query I use for calculation and I need to round off the values
[Product_Price] = [Product_Profit]/4,
[Product_Profit] = [ActualValue] * 0.75,
[GrossValue]
View 1 Replies
View Related
