Changing Connection Transactions To Database Transactions
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
ADVERTISEMENT
Feb 8, 2006
In one of my packages, I set the package-level property called TransactionOption=Required. During run-time I saw an error saying "[Execute SQL Task] Error: Failed to acquire connection "SQL_DW". Connection may not be configured correctly or you may not have the right permissions on this connection. ". When the property is changed to anything other than Required, it works fine (the calling package that calls this package is not involved in a transaction).
The machine running the packages is Windows Server 2003, and so is the database where the data lives. I verified that the machine containing the database does has Enable Network DTC Access checked in Control Panel -> Add/Remove Windows Components -> Application Server.
Is anyone else having this problem?
View 6 Replies
View Related
Sep 23, 2015
If I have an ACID-based SQL engine and I run a long-running transaction that changes a record at some point during the transaction, and at the same time it's running another transaction changes a record that the long running transaction has/will change/d, will they both complete or will one transaction fail? If they both complete who wins the final state of the record? The long running one or the other one? (is it based on when the transaction actually started "point in time" or is it specific to when the record was changed during the transaction "point in time"?)
View 5 Replies
View Related
Jul 20, 2005
A colleague wants to insert many millions of records where thevalues are computed in a C++ program. He connects to thedatabase with ODBC, and does an INSERT for each row.This is slow, apparently because each INSERT is a separatetransaction. Is there a way to delay committing the datauntil several thousand records have been written? InsideSQL Server this is simple, but I don't see an equivalentwhen using ODBC. Or is there something better than ODBC?Or might it be faster to write values to a file and thenuse bulk insert? I would appreciate any thoughts on thisgeneral problem!Thanks,Jim
View 1 Replies
View Related
Jun 15, 2005
Hello - where I can find the tools to view/see open connections, transactions not closed ... etc of a MS Sqlexpress database?
Thanks a lot - FOX
View 3 Replies
View Related
Nov 24, 1998
Ok sql-masters, I'm stumped and need someone to come to the rescue.
The issue: nested transactions = dblibrary process dead; broken connection or runaway process.
On the first try it usually bombs with the dblibrary... error. If I continue trying to run it, it will run, but actually runaway.
The sp:
/****** Object: Stored Procedure dbo.UP_MR1700 Script Date: 10/03/1998 11:00:08 AM ******/
CREATE PROCEDURE UP_MR1700 AS
DECLARE @TotRecs int
TRUNCATE TABLE MR1700_WorkTable1
EXECUTE MR1700_Insert_WT1_OnHand
EXECUTE MR1700_Insert_WT1_Packed
EXECUTE MR1700_Insert_WT1_Allocated
EXECUTE MR1700_Insert_WT1_Capacity
SELECT @TotRecs = (SELECT COUNT(Store_No) FROM MR1700_WorkTable1)
IF @TotRecs = 0 OR @TotRecs IS Null
RETURN -100
TRUNCATE TABLE MR1700_WorkTable2
EXECUTE MR1700_Insert_WT2_Classes
EXECUTE MR1700_Insert_WT2_Depts
EXECUTE MR1700_Insert_WT2_ClassGroups
EXECUTE MR1700_Insert_WT2_DeptGroups
EXECUTE MR1700_Report_Request
View 1 Replies
View Related
Nov 21, 2007
I would like to have a log where I can see who all updated a particular database table and when.
Is there any such logging facility provided by MS SQL Server?
Thanks,
Mohan.
View 3 Replies
View Related
Jun 26, 2000
Hi
All,
I would like to know write all transcations in the log file to the
database with out doing full backup of the database..
Please let me know is there any dbcc statement or some other method to do this..
Thanks A lot,
VJ
View 1 Replies
View Related
May 23, 2008
I have SQL Server 2005 Standard Edition.
How to calculate database transactions per second?
Thank you,
Gish
View 3 Replies
View Related
Sep 19, 2007
This should be a fairly simple question. It's based on this error message:"Transaction count after EXECUTE
indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 1, current count = 0." I get this when executing a stored procedure upon processing a form. This error happens when I intentionally provide input to the stored procedure that I know should cause it to error out. I catch the exception, and it contains the error message, but it also contains the above message added on to it, which I don't want. I won't post the entire stored procedure. But I'll list a digest of it (Just those lines that are significant). Assume that what's included is what happens when I provide bad input:BEGINBEGIN TRYBEGIN TRANSACTION RAISERROR('The item selected does not exist in the database.', 16, 1); COMMIT -- This won't execute when the RAISERROR breaks out to the CATCH block END TRY BEGIN CATCHROLLBACKDECLARE @ErrorSeverity INT, @ErrorMessage NVARCHAR(4000)SET @ErrorSeverity = ERROR_SEVERITY()SET @ErrorMessage = ERROR_MESSAGE() RAISERROR(@ErrorMessage, @ErrorSeverity, 1) END CATCH END Okay, so that works fine. The problem is when I execute this with an SqlCommand object, on which I've opened a transaction. I won't include the entire setup of the data (with the parameters, since those seem fine), but I'll give my code that opens the connection and executes the query: con.Open(); SqlTransaction transaction = con.BeginTransaction(); command.Transaction = transaction; try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); } finally { con.Close(); } I'm calling the stored procedure listed above (which has its own transaction), using a SqlCommand object on which I've opened a transaction. When there is no error it works fine. But when I give the stored procedure bad data, it gives me that message about the transaction count. Is there something I need to do in either my SQL or my C# to handle this? The entire message found in the Exception's Message is a concatenation of the message in my RAISERROR, along with the transaction count message I quoted at the beginning. Thanks, -Dan
View 1 Replies
View Related
Nov 13, 2007
Help, I had a tran log grow to it's restricted size, however the person that created this made the max size almost equal to the set max size. Needless to say I have not space to work with. SQL got bounced and my db went into recovery mode. After recovery mode was complete I tried to put my database in emergency mode but it exec's but never sets the mode. Next I tried to dbcc checkdb and I get msg 7929, level 16 state 1, line i Check statement aborted. Database contains deferred transactions. There is no back up for this database. Dev play area. I can not detatch db becase of the same error. What next? Any help would be great.
View 7 Replies
View Related
Dec 7, 2006
Hey,
I hope someone can quickly tell me what I am obviously missing for this weird problem.
To give a general picture, I have an ASP.net webpage that allows users to select values from several dropdown menus and click an add button which formats and concatenates the items together into a listbox. After the listbox has been populated the users have the option to save the items via a save button.
The save button parses each item in the listbox to basically de-code the concantenated values and subsequently inserts them into a table residing on a backend MSSQL 2005 database.
PROBLEM:
In the process of testing the application, I noted this strange behavior. If I use the webpage to insert the values, go to the table where the values are stored and delete the rows; Upon a refresh of the web page the same actions seem to be getting replayed and the items are again inserted into the table.
Naturally, what I'd really like would be for the page to refresh and show that the items aren't any longer there and not the other way around.
If the code that performed the insert was residing in a component that was set for postback I'd expect this type of behavior but its in the Save buttons on_click event. I have tried practically everything in effort of targeting the problem but not having much luck with it.
Is this behavior practical and expected in ASP.net or has anyone ever heard of anything similar? I have never encountered this type of problem before and was hoping someone could provide some clues for resolving it. If more information is required I'd be happy to supply it. Hopefully, there's a simple explanation that I am simply unaware since I haven't experienced anything like this before.
Anybody got any ideas???
Thanks.
View 6 Replies
View Related
Nov 20, 2003
Hey,
Is there somewhere in MSDE (or SQL) where you can see how many transaction are made to a sertain database or by a sertain user? At this way i could figure out witch database/user uses most (or least) recources (cpu) over a period of time.
View 5 Replies
View Related
Mar 2, 2014
A server I'm working on has a very unique situation, where user tables and production tables reside on the same database. Users update / create tables or populates these tables, so it can't be a table-specific trigger. However, they give a new meaning to "kamikaze pilots" as it's not uncommon for them to "accidentally" update / insert / delete 500,000,000 + records in a single statement. I've tried educating them to use batching, but to no avail, so now I'm forced to stop these statements BEFORE they execute, based on rowcount, as they fill up the database log so quickly that it goes into recovery mode (It has a 200GB log file - insane, I know).
I recon the mosts transactions allowed should be 1,000,000 records in a single statement. Looking for database trigger to stop them from executing statements with large records?
View 6 Replies
View Related
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
Jul 20, 2005
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 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)
Thanks
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?
Thanks
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 can’t rely on that.
Is there another way of changing connection properties?
Thanks in Advance
Barath
View 4 Replies
View Related
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,
Brian
View 2 Replies
View Related
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
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:
BEGIN TRANSACTION
INSERT INTO Table (...) VALUES (...)
COMMIT TRANSACTION
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?
Thanks!
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.
Thanks
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..
DECLARE @REPORTING_PERIOD VARCHAR(6)
BEGIN TRY
BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @REPORTING_PERIOD =(Select REPORT_PERIOD_ID from dbo.T_REPORT_PERIOD where C_FLAG_ACTIVITY=1)
--Step 1
INSERT INTO [dbo].[T_COUNTRIES]
([C_COUNTRY]
,[LB_COUNTRY]
,[C_REGION]
,[FK_REPORTING_PERIOD])
SELECT [C_COUNTRY]
,[LB_COUNTRY]
,[C_REGION]
,@REPORTING_PERIOD
FROM [dbo].[IN_T_COUNTRIES]
--Step 2
INSERT INTO [dbo].[T_FLE]
([FK_P_FLE]
,[C_COSMOS]
,[LB_FLE]
,[C_PARETO]
,[C_OPCO_SCOPE]
,[C_LEVEL]
,[C_FLE_TYPE]
,[C_ACTIVITY]
,[F_MATERIAL]
,[C_MATERIAL_PRIORITY]
,[C_CALCULATION_METHOD]
,[F_CREDIT_RISK_MATERIALITY]
,[V_PARTICIPATION]
,[FK_REPORTING_PERIOD])
SELECT Null as [FK_P_FLE]
,[C_COSMOS]
,[LB_FLE]
,[C_PARETO]
,[C_OPCO_SCOPE]
,[C_LEVEL]
,[C_FLE_TYPE]
,@REPORTING_PERIOD
FROM [dbo].[IN_T_FLE]
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT
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
BEGIN
PRINT
N'The transaction is in an uncommittable state. ' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'The transaction is committable. ' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
View 3 Replies
View Related
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
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
Priw
View 1 Replies
View Related
Mar 31, 2008
Hi,
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
