Transact SQL :: Server - Transactions Per Second
Oct 13, 2015I 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
ADVERTISEMENT
Transact SQL :: Is It Possible To Nest Transactions
May 28, 2015I 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.
Transact SQL :: How To Update Multiple Rows In Different Transactions In A Single Table
Jul 16, 2015We 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.Â
Transact SQL :: Replicating / Synching Data Between Two Tables On Same Database With Live Transactions
Oct 7, 2015Client 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.
Changing Connection Transactions To Database Transactions
May 22, 2005Hi 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
Transactions In Sql Server
May 9, 2007I 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 RelatedASP.NET SQL Server Transactions
May 28, 2005Hi,I am currently having dificulties in finding a way to use transactions while using classes for table representation.Let me explain, i have two tables a Customers and a Movements one, so two tables = two classes. Each class supports Insert, Edit and Delete, so if this tables worked seperatly there would be no problem but in this case whenever i create a customer i must create a movement for that customer so the connection don't pass between classes and i cannot use transactions ;(Is there any brilliant way to use transactions any other way? Even between connections or getting a solution for my implementation?Best Regards,Luis Simões
View 1 Replies View RelatedMonitoring SQL Server Transactions
Mar 22, 2006Hi,I am new to SQL Server Administraion, and straight away thereoccured a case in which some users modified the database and I have totrack 'em out something this :-A table was modified on 17 - 03 -2006 and now I want to see whatqueries occured on that day how can I do that! I have seen thetransaction log but it ain't helpful. Do help me out!
View 1 Replies View RelatedSQL Server 2005 - Transactions
Jan 24, 2007Hello guys!
I am working with SQL Server 2005 a few months, and i need your help about transactions topic.
In first, i hope you are patient with my english, i will try to explain it the best i can ;-)) and now... my question and thanks you in advance.
I am trying to set the transaction option of a foreach loop container task in run time using the Expressions option. There, I have configured the transaction option of this task with a variable value (var type int) and this variable is informed at the beginning of my dts using a script task (values: 0 for Not Supported, 1 for Supported, 2 for Required).
The problem is the transaction option gets the variable value but... the variable value by default. After, during the dts execution, the variable gets the new value but not the transaction option.
In summary, my question is ... is it possible to set the transaction option during the run time?.
Thanks for your help!
Chris.
DAO, ADO Transactions With SQL Server 2005 Using Vb6
Mar 19, 2007I have a million plus line program that uses DAO and JET/Access databases. We are modifying the code so that it will work with BOTH JET and SQL Server databases with an eye to moving to just SQL Server eventually.
Much of the code works just fine without change when using DAO and SQL server as 90+ percent of the code is accessing data READ-ONLY. (Jet's ODBC capability is used)
There are however some transactions that will not work.
When I examine the SQL Trace, I notice that after the transaction has begun, the SPID has changed. This happens often when cursors are used, BUT happens even if the only activity after a DBEngine.BeginTrans is issued DO NOT USE CURSORS as in the following code:
DBEngine.BeginTrans
strSQL = "INSERT INTO [TestTable] (MyKey,MyData, MyData2) VALUES (1,'apple','fruit')"
DB1.Execute strSQL
strSQL = "UPDATE [TestTable] SET [MyData] = 'apple2' WHERE [MyKey] = 1"
DB1.Execute strSQL
DBEngine.Committrans
The second DB1.Execute times out: [Microsoft][ODBC SQL Server Driver]Timeout expired
The trace shows a new SPID is opened when the UPDATE query is executed. A new SPID is spawned and the JET engine is sending an SELECT SQL to get the value for the MyKey:
SELECT "dbo"."TestTable"."MyKey" FROM "dbo"."TestTable" WHERE ("MyKey" = 1 )
The second SQL statement is waiting for the first one that is being done in a transaction to be completed. The program is expecting that both of these will be being done on the SAME SPID or at least the same transaction scope.
So, since I have no way of knowing how and when a new SPID will be spawned using DAO, we are not going to use it for when we do transactions.
I have tried to use ADO (Provider=SQLOLEDB.1), but ADO has the same problems as DAO as far as a new SPID being spawned in some circumstances, but it works in more situations than DAO.
The Connection Object is not guaranteed to be attached to a specific SPID - it can change at will. So Cn.Execute done twice in succession may be done on separate SPIDs.
My question is this: Is there a way to make sure that no new SPID is spawned?, Is there a way to know when a new SPID will be spawned so we can know how to avoid this situation? Is there a better way than using ADO? Keep in mind that this program has to be able to use BOTH Access/Jet databases and SQL server.
Note:
I have spent over 160 hours reading about Jet, the VB Guide to SQL Server, searching MSDN, Google etc. No article really does justice to this subject of transactions using DAO, ADO and SQL Server and the issue of the SPIDs (equivalent to a session and the session is the scope of the transaction). If you could be sure to stay on the same SPID, then you could just issue your own SQL Begin Transaction and control it all if you only plan on doing SQL statements without the need for cursors. A Microsoft article on this subject would be really helpful to all the programmers that are just now getting around to migrating to SQL Server.
Also, note, that if we did not do transactions, then we would not have to modify a single line of our code to make it work with SQL Server.
There is virtually no documentation for much of what I have written about in this Thread. I am very surprised as it seems this would have come up for thousands of programmers. I must be missing something, so thank you in advance for your help.
Is There Any Way I Can Roll Back In Sql Server Without Using Transactions ?
May 5, 2005Hi,
i wanna know how to use rollback in sql server without actually using transaction...
is there any way i can do that?
thanx in advance
How To Implement Transactions In Sql Server 2000
May 11, 2005hi,
i have developed an web-enabled database application using ASp.net, C# and sql server 2000.
now i want to implement transaction controls over the same
can anyone plz help me in implementing the same?
thanks in advance
Uncomitted Transactions On Server Outside Of Domain
Mar 16, 2004Hi
I have a couple of sql servers registered in EM which are outside of the domain. Whenever I run queries on them with the query analyzer, they seem not to commit the transaction. When I'm about to close down the window, I'm always told that there are uncomitted transactions.
Is this some kind of a bug, and is there something I can do about it?
Thanks
Martin N Jensen
Doing Transactions With Linked Server(ole Db Provider)
May 22, 2004Hi all,
When i begin a distributed transaction..., do i need to always
set xact_abort on?
But when i tried to put custom error handling to roll back a transaction with a linked server , it seems its impossible (as set xact_abort on, already automatically rolls back the transaction)
Does anyone have any ideas on this?
Thanks in advance
Benny
Does SQL Server CE Support System.Transactions?
Feb 15, 2007
Hi,
I've tried to enclose a few database operations in a TransactionScope block but it looks like SQL Server CE RM does ignores ambiental transaction.
Here is the code:
static void TestTxn() {
// Command to insert an integer in a table with a single integer column
string cmdPassText = "INSERT TESTTABLE (INTFIELD) VALUES(1)";
// Command to force field type mismatch exception
string cmdFailText = "INSERT TESTTABLE (INTFIELD) VALUES('Foo')";
using (TransactionScope scope = new TransactionScope()) {
using (SqlCeConnection conn = new SqlCeConnection("DataSource = 'Test.sdf'")) {
try {
conn.Open();
SqlCeCommand cmdPass = new SqlCeCommand(cmdPassText, conn);
returnValue = cmdPass.ExecuteNonQuery();
SqlCeCommand cmdFail = new SqlCeCommand(cmdFailText, conn);
returnValue = cmdFail.ExecuteNonQuery();
}
catch (Exception ex){
Console.WriteLine("Command failed");
Console.WriteLine("Exception Message: {0}", ex.Message);
}
}
scope.Complete();
}
}
After first command suceeds and seccond command failes table still has one affected row after transaction.
Am I doing something wrong or System.Transactions.Transaction is not supported with SQL Server CE RM?
Thanks,
Aleksandar
Transactions Using Oracle RDB Linked Server
Feb 7, 2008I have a SQL Server 2005 with a linked server to an Oracle RDB 6.0, using the Microsoft OLE DB for ODBC provider. I would like to select some rows from the Oracle RDB and then Update those specific rows afterwards. Whenever I try to do this in a transaction (BEGIN TRAN) I get an error "Driver not capable". Is there any way to do this in a single transaction?
View 1 Replies View RelatedHow To Recover Committed Transactions From SQL Server Log(.ldf) File?
Dec 23, 2003Hi ,
I got problem in production server at client place(No backup copy & not replicated,it's a SQL SERVER 2000 Enterprise server),by mistake client updated the data without using where condition then updated lakhs of rows (in SQL server autocommited),Now I need to recover this data from LOG file(.ldf).I tried with LOG EXPLORER(Third party tool) Trail version recovered from default database(Northwind,Pubs).But client not willing purchase this S/W for simple cause,How can we recover the data from LOG file.
1.Can we write the Program in C# to read the SQL Server Log and show the past transactions?
2.Is There any Stored procedures exist in SQL Server to read the day transactions in log file and take the backup?
3. How to read Transactions Log file in SQL Server 2000?
It's very Urgent,I am not expert in SQL Server 2000.
Could U Please Help me with possible solutions.
Regards,
Harikrishna.
Viewing Transactions In SQL Server 2005 Express
Aug 22, 2006Hello,I'm trying to follow some sql sentences that my system send to SQL 2005express and I don't have a deep knowlegde of databases. I know thatthere's a transactions log that keeps all sentences that go intodatabase motor. Is it correct? in case yes, is there a way to look atthis archived sentences?Thanks in advanceIgnacio
View 1 Replies View RelatedSkipping Transactions In SQL Server Transaction Replication
Jun 14, 2007I'm trying to create a transaction replication from SQL Server 2000 to 2005. Basic replication between the servers works just fine. However, what I want to accomplish is to be able to skip some of the transactions. Example - from time to time we want to purge some of the historical data from the main database (the publisher). We don't want the same purging to occur on the destination database, which will be used for reporting purposes and needs to include all the historical information. I wanted to simply stop the replication log reader, purge the records, backup the transaction log with truncation and then restart the reader. The only problem - the truncation on the replicated database keeps the transactions of the purging until they are replicated, so the transaction log backup doesn't help. Any ideas would be greatly appreciated!
View 5 Replies View RelatedMicrosoft.Jet.OLEDB.4.0 Linked Server Transactions
Dec 21, 2006Hi All,
I am writing an interface between SQL Server and Access. I have chosen to use a linked server using the Microsoft.Jet.OLEDB.4.0 provider. One of the requirements is to have multiple SQL statements in one transaction, which is where I am encountering the problem. (see below)
BEGIN TRAN
INSERT INTO ACCES...Table
Msg 7390, Level 16, State 2, Line 2
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "HES" does not support the required transaction interface.
I am having a really really hard time finding documentation on the Microsoft.Jet.OLEDB.4.0 provider, specifically I want to know for sure if it supports the ITransactionJoin interface.
The main question is - does MS Access linked servers support transactions?
Thank you for your help,
I have exausted all resources available to me in finding out the answer.
Thanks,
Eric
Which Queries Or Codes Couldn't Use In A Transactions In Sql Server
Feb 21, 2008
May i have a list of Which queries or codes couldn't use in a Transactions in Sql Server ?
SQL Server 2012 :: TRANSACTIONS After Moving To A New Server
Jun 15, 2015I have a piece of code that has happily run daily for at least 4 years. I have moved it to a machine that is meant to have an identical set-up, and it fails. I need to work out why it fails, but I am stuck. The code works as follows:
CREATE storedProc1 (Parameter1, etc)
DECLARE @TransactionName nvarchar(127), @TransactionActive bit
-- Plus Other Variables...
BEGIN
BEGIN TRY
SET @TransactionName= 'TransactionName1'
SET @TransactionActive = 'True'
BEGIN TRANSACTION @TransactionName WITH MARK
[Code] .....
When I EXECute StoredProc1, it fails with the Error Message "Cannot roll back . No transaction or savepoint of that name was found." I think that message comes from a ROLLBACK TRANSACTION statement in the CATCH of StoredProc2. I have tried commenting out that ROLLBACK statement, which changes the error message to: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2."
Viewing LIVE Transactions From AS400 In SQL-Server 2000
Dec 17, 2005I need to live update my SQL-Server 2000 database from AS400 database.
I usually use Access 2002 database between the AS400 database and the SQL-Server 2000 database to reflect any live update.
I use the (Link Tables...) option when creating a new table in access and link it with the ODBC to connect with the AS400 database.
After that I work with the Access link table.
Is there any tool in SQL-Server 2000 to show the live transactions in AS400 databasee so I can work on it?
Please advise
Distributed Transactions With Multiple Instances Of Microsoft SQL Server
Jul 23, 2005Hi,I'm having a problem running a distributed transaction between twolinked servers that both have multiple instances of SQL Serverinstalled on them. This is the error message that I receive:"The operation could not be performed because the OLE DB provider'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in thespecified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB'ITransactionJoin::JoinTransaction returned 0x8004d00a]."The query follows the format:"BEGIN DISTRIBUTED TRANUPDATE [LINKEDSERVER1INSTANCE_NAME].DB.OWNER.TABLENAMESET fieldname = alias2.fieldnameFROM tablename alias2JOIN [LINKEDSERVER1INSTANCE_NAME].DB.OWNER.TABLENAME alias1on alias2.urn=alias1,urn"[color=blue]>From what I can gather from various sources the SQL Server must be[/color]named the same as the computer which it is installed on. However, if Ihave two instances of SQL Server, they cannot both be named the same asthe computer. Does anyone know of a way around this or whether I'mbarking up the wrong tree completely?Many thanks.
View 2 Replies View RelatedDB Engine :: Distributed Transactions Fail On Linked Server
Feb 12, 2009We get the below error while performing a distributed transaction on linked server. We have several linked servers configured in the source server and all of them succeed with the distributed transaction except on one.
Â
We did all the basic troubleshooting and moreover the distributed transactions work fine if we use a remote server instead.
Error:
OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.
Â
 Test code:
begin distributed transaction
select top 10 * from [SERVERNAME.REDMOND.CORP.MICROSOFT.COM].master.sys.objects
 ROLLBACK
Â
Source server : Â Â
Microsoft SQL Server 2008 (RTM) - 10.0.1779.0 (X64)
      Nov 12 2008 12:10:04
      Copyright (c) 1988-2008 Microsoft Corporation
      Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)
Â
Target server : Â Â
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
      Jul 9 2008 14:43:34
      Copyright (c) 1988-2008 Microsoft Corporation
      Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
SQL Server 2014 :: Transaction Isolation Level And Implicit Transactions
Oct 23, 2015I'm investigating a poorly performing procedure that I have never seen before. The procedure sets the transaction isolation level, and I suspect it might be doing so incorrectly, but I can't be sure. I'm pasting a bastardized version of the proc below, with all the names changed and the SQL mucked up enough to get through the corporate web filters.
The transaction isolation level is set, but there is no explicit transaction. Am I right that there are two implicit transactions in this procedure and each of them uses snapshot isolation?
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
DECLARE @l_some_type varchar(20),
@some_type_code varchar(3),
@error int,
@error_msg varchar(50);
[Code] .....
Transact SQL :: Results From Secondary Server Inside Primary Server
Jul 14, 2015Inside my sp on the Primary server are 3 commands:
exec msdb..sp_start_job @job_name = N'JobA'Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â --
Running on Primary ServerEXEC Server2.msdb.dbo.sp_start_job @job_name = N'JobB';Running on Secondary ServerSelect * from Server2.Table                                 --
Running on Secondary Server
So far the commands are working just fine when I kick them off one at a time. But when I kick off the sp as a whole I notice that the timing just isn't keeping up. For instance, Step 3 is running before Step 2 is finished. It appears that as the Primary Server kicks off Step 2, it doesn't seem to care about the outcome and moves right to Step 3 which in turn provides me erroneous data.How can I get Step 3 to wait until Step 2 is actually finished keeping in mind the Secondary server is part of this equation?
Transactions
Aug 13, 2003Hi 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
Transactions
Apr 6, 2005I 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
TRANSACTIONS IN DTS
Apr 17, 2000Hi 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
Help With DTS Transactions
Sep 5, 2000Hi 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
I Want To See My Transactions
Dec 1, 2000I 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?
Max. Transactions
Oct 17, 2003what is maximum limit of no. of transactions per sec. in sql server 2000
View 2 Replies View Related