Updatable Subscriptions - No Transaction Is Active
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.
I have a problem with "Updatable Subscriptions" under concurrent transactions on both pub and sub effecting same data. At high level of concurrency it Subscriber starts giving error msg "Rows do not match between Publisher and Subscriber. Run the Distribution Agent to refresh rows at the Subscriber.".Please tell me any way arround for this.
I have been trying to set up replication with updateable subscriptions. I first followed the tutorial on MSDN and set up basic transactional replication. This all worked fine. I then tried, using that tutorial as a basis, to set up replication with updatable subscriptions. On the Agent Security page you are asked for a Snapshot Agent, a Log Reader Agent and a Queue Reader Agent. I assigned these to the following accounts, which I created and added as logins, PUBLISHERSERVER epl_snapshot, PUBLISHERSERVER epl_logreader and PUBLISHERSERVER epl_queuereader.
I then tried to set up a subscriber on SUBSCRIBERSERVER. Under the publication I add new subscriber, select the publisher, add SUBSCRIBERSERVER as the new SQL server Subscriber. In the Distribution Agent Security page of the wizard it asks for process account, which will be run when synchronizing the subscription. I entered PUBLISHERSERVER epl_distribution here and selected the other two default options of connecting to the distributor and subscriber by impersonating the process account. I then took the default options for the next few screens and finally get to:
Login for updateable subscriptions. This offers the option of a login or using a linked server. I have tried various logins here, initially trying the €˜sa€™ login and password. This produced the error: The user is not associated with a trusted SQL Server connection. The servers are set up for mixed mode operation. I then tried PUBLISHERSERVER epl_distribution and subsequently every other account I had created on PUBLISHERSERVER. All of these failed. I tried linking the servers, but this also failed. How exactly do you set up subscriber with the ability to login to the publisher? I have spent days trying to set this up, and am as you can gather new to this technology, any help would be greatly appreciated.
I have a problem replicating data from Subscriber to Publisher.
I configured Queued updatable subscriptions in transactional replication. Publisher is on SQL Server Standard edition and Subscriber is on SQL Server Express edition, both on Windows XP.
When I insert/update data in publisher, the data is replicated to subscriber. If I insert/update data on subscriber the data is not replicated. When I see the replication monitor for errors, it shows an error "Cannot load a DLL xprepl.dll or one of its resources".
Could any one give me a solution or please tell me if I missing something?
Hi to evebody. I'm working with the transactional publication with updatable subscriptions provided by SQL Server 2005. The replication works pretty good from the publisher to the subscriber, but I'm having some problems when the data must go from the subscriber to the publisher.
When I do an update in a subscriptor's table, the database engine shows the following error:
21064 - 16 - The subscription is unavailable for immediate updating because it is marked for reinitialization. Try again after the reinitialization completes.
And rollbacks the transaction.
Does anybody knows what to do to solve this problems.
The publisher is a Windows XP with the SQL Server 2005 Developer edition with SP2 The subscriber is a Windows 2003 Server with SQL Server 2005 Developer edition without SP2 I'm using also the inmediate updating subscriptions. Both operative systems have the MSDTC runing.
Thank you in advance.
Sebastian.-
PS: Sorry about my english, it's been a long time without using it.
So, Microsoft decided that they were deprecating Transactional Replication with Updatable subscriptions. In that case, you have 2 options (if I am correct): Pay for Enterprise (if you are already not) and use peer-to-peer or use bidirectional transactional replication which is basically setting up a transactional from db1 to db2 and also transactional from db2 to db1.
The issue I see in both cases is conflict resolution. With updatable subscriptions, you could specify how to handle the conflict. With either of these 2 options (from what I can tell) you cannot allow the engine to handle this for you.
Any thoughts? Seems like a slap in the face to those who have been using MS for years and a damn good reason for companies that rely on updatable subscriptions to not upgrade to 2012.
Subscription to "Transactional Publication with Updateable Subscriptions" works only one way. Changes take effect on subscriber, but the subcriber is unable to update data on publisher.
I have Sanpshot Agent process running under SQL Server Agent service account with login 'sa.' All agents are running at the Distributor (Publishing Server.)
The subscriber is unable to connect to the Distributor using the SQL Server login.
Following is the error message I get:
Creating Subscription(s)...
- Creating subscription for 'SQL3' (Warning)
Messages
Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)
Has anyone seen this error? It comes up when my app executes a select statement via linked server (from MSSQL 2000 to 2005) the second time it runs. It's basically a timed poll of tables in the remote database. It works once but the second time it executes I get the error from the remote db, it's just a select but I guess the cursor is still open so it can't run again. After the exception the select will run again, once and it just repeats. I have researched it a little and it looks like it mostly has to do with the isolation level of the transaction. Unfortunately based on the componet being used to access the database I don't beleive I have the ability to use transact SQL to call the isolation level (s) listed.
Here's the weird part though, at another site the same scenario is running fine. Same primary and remote db versions of MSSQL as well as the application and it runs fine. I guess my question is what type of setup parameters that may be defined in MSSQL 2005 for the remote database might make it behave in this manner in one instance and not the other? TIA in advance for any thoughts/assistance.
We are currently testing a Replication topology as follows: SQL Server 2005 Developer as publisher and distributor of a push updatable transaction replication 2 SQL Server 2005 Express clients as subscribers.
The replication is working great. If the connection is lost, the subscribers switch from Immediate to Queued Updating, and seem to shift back to Immediate when they regain the connection.
My question is to determine if there is a way that I can ask SQL Express on the client which mode is it currently running, or ask SQL Express if it has a current live connection to the distributor.
I know that they are usually mutliple ways to accomplish tasks, and I would be open any suggestions, preferrable programming in VB (such as with SMO or RMO) or using stored procedures or functions.
I hope this give some people something to chew on during the weekend and thank you to all that have taking the time to read this.
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
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
[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.
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 ?
I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?
It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).
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.
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.
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)?
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.
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)?
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.
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?
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?
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.
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.
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?
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.
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?
