Msdtc/transaction Is Not Active
Oct 1, 2007
Server 1: Win2003, Clustered Environment, SQL 2005sp2
Server 2: Win2003, no cluster, SQL 2000sp3a
Server 3 (aka, client): Win2003, using ODBC (sql native client) to connect to server 1
I'm attempting to run an SQR that connects to server 1, yet updates server 2. Right now, all I'm doing is the statement
delete from <server2>.<database>.dbo.table
and am getting the error (DUL41 is server 2):
SQR 5528) ODBC SQL dbexec: SQLExecute error 7412 in cursor 1:
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "SQLNCLI" for linked server "DUL41" returned message "No transaction is active.".
(SQR 5528) ODBC SQL dbexec: SQLExecute error 7391 in cursor 1:
[Microsoft][ODBC SQL Server Driver][SQL Server]The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "DUL41" was unable to begin a distributed transaction.
If I use query analyzer (or whatever we're calling it now) to connect to server 1 and run the statement, it works fine. But, SQR is connecting via ODBC (that's different) and also is SQR.
I'm also not 100% sure all of the new MSDTC aspects are configured correctly, but I've gone through the settings a few times. Right now, I pretty much have everything set about as open as possible (these are test servers on the same network) and am still getting the error.
Any help, or ideas I could try, would be great. Thanks.
View 8 Replies
ADVERTISEMENT
Jun 29, 2004
How do I install MSDTC in an active/active MSCS cluster environment?
If I run comclust.exe, I can only activate MSDTC on one of the nodes at a time.
I need DTC on both nodes.
(Opsys is W2K with SQL 2000)
//Mange :)
View 2 Replies
View Related
May 18, 2005
If this is in the wrong forum, my apologies.
Here is the situation. We have a .Net application that some
consultants developed several years ago. They made use of DAO for
database access, using some inbuilt classes like ContextUtil to get a
transation environment.
Until last weekend, the envorinment was:
Web application on IIS5 / Windows 2000 Advanced Server
Database on SQL2000 on Windows 2000 Advanced Server
We switched over to a new database server environment - a cluster of
two Windows 2003 servers wth SQL Server 2000. This required
Active Directory to be set up on the two database server machines.
Everything worked but this particular application - where it goes to
update the database. It appears (but since I haven't yet been
able to rebuild the .Net solution) that the error - "Object reference
not set to an instance of object." is in fact a spurious error caused
when the transation tries to roll back.
I have spent about 10 hours on this, and have come to the conclusion
that the fact I have MSDTC involved on a database server that is in an
AD domain, while the web server that is issuing the transaction is
outside that domain is the cause of the problem. Now, I don't
want to put AD on the IIS 5 web server - we are migrating this to a new
Win2k3/IIS6 server in 5 or 6 weeks time.
Any suggestions? If I have to, I can move the database for this
particular app back to the non-clustered Windows 2000 server (which is
also outside the AD domain).
Thanks for your indulgance.
View 1 Replies
View Related
Dec 30, 2005
I am trying to do something simple to test TransactionOption. I have a ScriptTask (out of the box), which runs fine. To test TransactionOption, I set the Package Properties TransactionOption = Required. Straight away the package fails with the following message
Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.
I am running this on my Laoptop, which has XP Pro with SP2.
I checked the services and MSDTC and it is running fine. I have checked the Event Viewer and saw the following messages... One is Information and the other is Error
Information (Disk - Event ID 2444)
----------------------------------------------
MSDTC started with the following settings:
Security Configuration (OFF = 0 and ON = 1):
Network Administration of Transactions = 0,
Network Clients = 0,
Inbound Distributed Transactions using Native MSDTC Protocol = 1,
Outbound Distributed Transactions using Native MSDTC Protocol = 1,
Transaction Internet Protocol (TIP) = 0,
XA Transactions = 0
Error (Devices - Event ID 4437)
------------------------------------------------
The account that the MS DTC service is running under is invalid. This can happen if the service account information has been changed using the Services snap-in in Microsoft Management Console (MMC). MS DTC service will continue to start. Please make sure that the MS DTC service account information is updated using the Component Services Explorer.
I have checked Component Services. All looks fine from what I can see.
Anyone else came across this?
Thanks
Sutha
View 10 Replies
View Related
Mar 12, 2007
In this article http://download.microsoft.com/download/f/1/0/f10c4f60-630e-4153-bd53-c3010e4c513b/ReadmeSQLEXP2005.htm , it explains that you must fully enable the security settings to allow use of transaction processing for a SqlExpress database. Is there a way to set these option in a setup? Or is there another way to use transaction processing without setting these options?
Thanks
Dave T
View 1 Replies
View Related
Aug 27, 2006
I get the following error in the SQL ErrorLog file and in the Event Viewer when using a J2EE web application deployed on BEA WLS 8.1sp5 using the Weblogic JDBC Type4 XA driver with MSSQL Server 2000 SP4
Enlist of MSDTC transaction failed: XACT_E_NOTRANSACTION.
View 2 Replies
View Related
May 3, 2006
I have a sql job which has to pull some data from a remote server. This job is running fine last few days and suddenly failing to execute with the following error. The same job is running fine in other servers.
Executed as user: NT AUTHORITYNETWORK SERVICE. The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction. [SQLSTATE 42000] (Error 1206). The step failed.
Note: The steps I have already done :
1. Verified the MSDTC configurations and restarted the MS DTC
2. Restarted the Sql Server
3. Restarted the machine.
Any help will be highly appreciated.
View 5 Replies
View Related
Nov 8, 2007
Hi All, I am using the CSLA framework for the data access with SQL Server 2005 and we are not using distributed transaction. When we are using this architecture for our local machine it is working fine and our DB is in local machine itself.
But I am getting the following error in our development server and our DB is in the Network. " DataPortal.Fetch failed
(DataPortal.Fetch failed (DataPortal.Fetch failed (Network access for
Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for
network access in the security configuration for MSDTC using the Component
Services Administrative tool.))) ---> DataPortal_Fetch method call failed
---> DataPortal.Fetch failed (DataPortal.Fetch failed (Network access for
Distributed Transaction Manager (MSDT... Procedure Script call failed." Please help me out to get rid of it. Your help would be appreciated.
RegardsGomaz
View 1 Replies
View Related
Mar 13, 2008
Distributed transactions were happily running between our SQL servers and then we installed Windows 2003 Server SP2 and all of a sudden we got this nice error message when attempting a remote data update via linked servers. I couldn't even run "BEGIN DISTRIBUTED TRANSACTION" without getting the error message. The full error message is:
"Enlist operation failed: 0x8004d00e(XACT_E_NOTRANSACTION). SQL Server could not register with Microsoft Distributed Transaction Coordinator (MSDTC) as a resource manager for this transaction. The transaction may have been stopped by the client or the resource manager."
Apparently, SP2 disables network DTC access (how nice, I feel more secure knowing that my data can't be updated remotely anymore).
Solution:
From Control Panel | Add or Remove Programs | Add Remove Windows Components
Select "Application Server" and click the "Details" button
Check "Enable network DTC access" and click OK for however many prompts it takes to apply the changes.
****NOTE**** this change WILL SHUT DOWN SQL SERVER so plan accordingly (this would have been handy to know in advance since I ran it in the middle of the workday and everyone got disconnected).
Hope this helps!
View 2 Replies
View Related
May 3, 2008
Hi all,
I m trying to get record from one server to another using Commit and Rollback feature in the Sql Server and getiing error message--
"No transaction is active."
below is my sql block
BEGIN TRAN
BEGIN TRY
INSERT INTO [SERVERNAME1].[DATABASENAME].[dbo].[TABLENAME](PurchaseByUserID,price,purchasedate,affiliateID,LicenseeID,debittype)
SELECT PurchaseByUserID,price,purchasedate,affiliateID,LicenseeID,debittype
FROM [SERVERNAME2].[DATABASENAME].[dbo].[TABLENAME]
WHERE Convert(smalldatetime,purchasedate)>=Convert(smalldatetime,'2/14/2008 00:00:00') and Convert(smalldatetime,purchasedate)<= Convert(smalldatetime,'2/14/2008 23:59:59')
COMMIT TRAN
END TRY
BEGIN CATCH
Rollback TRAN
END CATCH
END
Please replay ASAP.
View 5 Replies
View Related
May 5, 2008
Posted - 05/03/2008 : 13:37:25
Hi all,
I m trying to get record from one server to another using Commit and Rollback feature in the Sql Server and getiing error message--
"No transaction is active."
below is my sql block
BEGIN TRAN
BEGIN TRY
INSERT INTO [SERVERNAME1].[DATABASENAME].[dbo].[TABLENAME](PurchaseByUserID,price,purchasedate,affiliateID,LicenseeID,debittype)
SELECT PurchaseByUserID,price,purchasedate,affiliateID,LicenseeID,debittype
FROM [SERVERNAME2].[DATABASENAME].[dbo].[TABLENAME]
WHERE Convert(smalldatetime,purchasedate)>=Convert(smalldatetime,'2/14/2008 00:00:00') and Convert(smalldatetime,purchasedate)<= Convert(smalldatetime,'2/14/2008 23:59:59')
COMMIT TRAN
END TRY
BEGIN CATCH
Rollback TRAN
END CATCH
END
View 4 Replies
View Related
Jan 25, 2001
Is there a stored proc to tell details of the oldest active transaction ?
View 3 Replies
View Related
Jun 8, 2015
I was expecting this to return an active transaction
begin transaction
select * from table1
dbcc opentran
(no COMMIT or ROLLBACK)
View 1 Replies
View Related
Apr 11, 2007
This may be a simple answer, but I was wondering when setting up transactional replication with updatable subscriptions why does this error occur?
Error:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPLLINK " was unable to begin a distributed transaction.
Changed database context to 'MyDB'.
OLE DB provider "SQLNCLI" for linked server "REPLLINK" returned message "No transaction is active.". (.Net SqlClient Data Provider)
I've looked for MSDTC security documents and checked every box in the security dialog and selected the 'No Authentication Required€™. MSDTC and SQL Agent are running as NETWORKSERVICE user. I also setup permissions on each individual DCOM Objects for SQL Server. And MSDTC service is running. Both Servers are SQL Server 2005 Standard and Windows 2003 R2. The publisher is on the same box as the distributor. No domains or trusts setup on either. This error occurs when changing data on the subscriber.
Thanks,
Patrick
View 6 Replies
View Related
May 22, 2007
I am relatively new to SQL Server so excuse my ignorance.
I have noticed in many of my SQL Server 2005 instances that the tempdb database is very often reporting the log mode as waiting active_transaction or waiting checkpoint.
Is this of concern, how can I determine the cause of these waits and how can they be reduced or eliminated ?
View 1 Replies
View Related
Jan 22, 2015
I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.
TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime.
OpDatesessionStartTimesessionCloseTime
2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166
2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442
2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853
2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707
[code]....
can see how many sessions logged in per hour with the following statement:
SELECT
opDate,
FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour,
Count(*) AS Total
FROM #empSessionLog
Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00')
Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults:
opDateopHourTotal
2015-01-20041
[code]....
Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.
View 5 Replies
View Related
Mar 5, 2008
Hi
I am having some teething problems while installing SQL on a 3 node cluster. Within the Cluster configuration I have 3 Cluster Groups with each of them having their associated disk resources. All these disk resources physically exist on a SAN.
The actual cluster is running absolutely fine and I can access all the disks from their respective owner node. The problem only starts when I start installing SQL Server 2005 on this cluster. I specify the Cluster group from the Cluster Group Selection and choose the desired partition and then the error message pops up
"There is not enough diskspace on the destination disk for the current SQL Server data files. To proceed, free up disk space to make room for data files, or install the data files to a different drive"
But the disk I am trying to install it on is 264Gb and none of it is used. I have also tried to change it to a different disk within the same Cluster group but to no avail. I have even tried to install it in a different cluster group all together but I get the same error message.
I have googled around havent found anything so far. The disks have got full permissions for the account I am installing SQL with.
Any help will be much appreciated.
Regards
Adnan
View 5 Replies
View Related
May 12, 2008
Hi
I am newbie in SQL Clustering. I have set up a Windows Server Cluster with 2 nodes and am having the following problem with Physical Disk resource for cluster groups:
My Default Cluster Group (named Cluster Group) has IP Address, Network Name, Physical Disk and MSDTC resources. In addition to that my Default SQL Server instance resources are also in this group. I had this initial set up for Active/Passive mode.
Now I am trying to set up a SQL Cluster in Active/Active mode. For this I have to install another instance of SQL Server in the existing cluster and make a separate cluster group for its resources. I made a new cluster group (SQL Instance Group) with an IP Address and a Network Name resource for this new instance but I dont have any Physical Disk resource to allocate to it. As such while installing the SQL Server Instance I get stuck when I'm asked to select the quorum disk to be used.
Is it possible to configure two quorum disks, one for each group?
What's the concept of dedicated disks resource for each sql instance in a group? Is this same as the quorum disk? If this is not a shared disk how do I configure a dedicated disk resource for my second cluster group (SQL Instance Group)?
Anyone could please help me out with this?
View 12 Replies
View Related
Nov 30, 2006
Hi folks,
We are going thru the process of scoping an active/active cluster at one site.
I was wondering whether there will be any issues with mirroring (DB by DB) off the cluster into non clustered server at an alternate DRP site.
Regards, Brian
Sutherland
View 1 Replies
View Related
Mar 12, 2008
Hi all, My aim to install active/active 2 node SQL 2005 cluster. I have installed sql cluster on one mode, which automatically installed on node 2.
it works great. However When i tried to install second virtual server, it is not allowing me to install.
it says already install, can anyone tell me how to install active active cluster
View 1 Replies
View Related
Nov 2, 1999
Hi,
We are trying to set up an active/active configuration of a SQL Server
cluster, and we had a few questions.
Initially, we want to have 2 Database Servers that would share the same
Database (both reading/writing to the same tables).
However, from reading the MS docs, we find out that we can have what
they call an "active/active" configuration using a Cluster but they
need to have 2 different disk sets, i.e. having 2 separate databases.
If this assumption is correct, how does the data get synchronised
between the 2 databases (that are on the 2 different disks sets)?
Can anyone help? thanks
Axel
View 1 Replies
View Related
Oct 12, 2002
ACTIVE/ACTIVE SQL Server 2000 Cluster
This was my plan.
Physical box - box1,box2
virtual box - virtual1
i have a active/passive cluster virtual1 (primary node box1, secondary box2)
I am creating another virtual box on box2 as primary node by name virtual2)
whenever my installation setup runs my default server name is greyed out. it always requires a instance name.
Is that the way it should work?
i can only use instance for active/active failover?
please advice
View 1 Replies
View Related
Sep 27, 2005
Hello folks,
have you ever heard of an Active/Active SQL Sever 2000 Cluster deployed in a pure OLTP environment?
Some 8 years ago I have learned about a bespoke solution for the SAP ERP system (not the BW!) with DB2 Parallel Server for a huge German company. Then again, I would expect that Oracle RAC might fit into an OLTP environment, although I never heard about a real world implementation.
All this led me to believe clustering is good for failover purposes, and for decision support services, not quite for OLTP applications.
So if you see a point in Active/Active Clustering and OLTP please come forward and explain.
Cheers,
Johann
P.S: For those of you who want to know: Consultants from www.hiltes.com want us to deploy an Active/Active Cluster for their Fashion 3000 Net stock software.
View 6 Replies
View Related
Mar 22, 2007
We run several SQL 2000 SP4 instances on IA64 active/active cluster. The OS we run with is Windows Server 2003 SP1. We have different network cards : 2 network cards teamed for production purposes in domain X and 1 network card dedicated for problem solving in domain Y.
First we configured the cluster with only the 2 teamed network cards for production purposes in domain X. Later we introduced the second network card for problem solving in domain Y.
Everything looks fine. The (virtual) SQL instance listens on two different IP adresses on TCP port 1034. If we try to make connection via isql, EM or Query analyzer than we can directly contact the SQL instance from a workstation/server within domain X but this doesn't work form a workstation/server within domain Y. However if we use the specific TCP port in the connection in domain Y the connection is setup. We wish however not to use explicit TCP ports in setting up connections.
Has anyone experienced the same problem before or has anyone an idea how to solve the problem?
View 1 Replies
View Related
Jun 8, 2007
I have a 2005 active/active cluster and want to apply SQL server 2005 SP2 to both node.
I know that for active/passive , the sp can simple be installed on the active node(instance) and everything will be replicated to the other node.
bear in mind what I have SSIS and SSAS and SSRS running on the active/active cluster.
what is the rigth method for applying the SP to activeactive cluster?
Thanks
View 1 Replies
View Related
Mar 5, 2001
Hi
While configuring an active / active cluster, do I need to run the SQL setup on both nodes?
The SQL2K setup installs binaries on localdisks of both nodes - hence the question.
thanks
Liju
View 3 Replies
View Related
Feb 22, 2000
I have setup an active/active clustering environment for SQL Server, however it is 2 seperate virtual servers. How can I set them up to exist as one virtual server containing both active installations? Can this be done? I have two compaq 8500 w/8 processors each. I need to be able to cluster these configurations to take advantage of all 16 processors in one virtual SQL Server. Can it be done?
If you can answer these questions, you will have my undying gratitude.
Thanks in advance.
View 1 Replies
View Related
Sep 21, 2000
Hi
I am running some tests on SQL7 in an active/active cluster and have a couple of queries
1. When I create an ODBC DSN, why is the "Use the Failover SQL Server if the primary SQL Server" checkbox disabled
2. To test, I was running SQL queries from Access over the DSN created. When the Primary Server went down, I had to reconnect to re run the query - Is this normal?
3. Can someone point me to any documentation on the above scenario that would shed some more light?
Thanks
Liju
View 5 Replies
View Related
Oct 26, 1999
I currently have a two node HP hardware active/active cluster server. Running windows NT 4.0 Enterprise and dSQL server 6.5 sp 3. I want to upgrade the cluster to SQL 7. I would like to know if anyone ran into any problems or has sucessfully attempted this.
Also I read a few months ago about a gottcha involving NT sp4 and SQL sp5a that would prevent a node from failing over. Has this been corrected and does it affect SQL 7.
thanks
View 1 Replies
View Related
Sep 27, 2002
Hi
Is there anyone who knows where I can get some information about
installing an ACTIVE/ACTIVE SQL 2000 Cluster server (MSCS).
I have no problem setting up an active/passive failover cluster.
Where can I get more information? Do I need two instances? Two databases?
What's the difference between active/active and active/passive?
Thanks!
View 2 Replies
View Related
Jan 20, 2004
Hello, Can anyone please explain how the failover processes works on a sql server 2000 2 node active/active cluster.
Given the following
You have 2 node active/active cluster NodeA & NodeB
Question
1. How many SQL instances need to be installed on each node?
2. If the answer to question #1 is one instance per node, then
say if NodeA fails NodeB will take over all the resources of NodeA
including the master database, How does that work , how can once instance (in nodeB) handle two master database i.e its own master database and the one taken over from NodeA.
View 1 Replies
View Related
May 18, 2008
Hi,
I've just setup an 2 node active/active sql cluster (my first). Both servers have exactly the same specification, 16GB of RAM each and SAN attached. My question is, how do I configure the min and max memory for each sql instance. I've read some where that I need to follow the 20-40-40 rule as, 20% for the OS, 40% for the active sql instance on the node and the other 40% is for the other sql instance if it fails over. Are there any other gotcha that I need to be aware of? Can someone share some light with me in regards to this? Thanks
Ken
View 4 Replies
View Related
Jul 3, 2007
We have an active/active node setup with SQL 2005. Does anyone have any samples of VBS I could use to see what node is actually taking requests at a certain time? What I want to determine is what the actual active node is.
Thanks.
View 1 Replies
View Related
