Transactional Replication :: Error Creating New Publication
-
Attempting to create a new publication and received this error:
TITLE: New Publication Wizard
------------------------------
SQL Server could not create publication 'XX_TABLES'.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
Changed database context to 'XXX_STAGING'. (Microsoft SQL Server, Error: 3933)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.4064&EvtSrc=MSSQLServer&EvtID=3933&LinkId=20476%5B/quote%5D
Ran a DBCC OPENTRAN on both the server that's creating the publisher and on the distribution server - there were no open transactions.
Any ideas out there?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
It might help...
active save point
-
Appreciate it Dev, but I've tried that already. In fact, I've completely disabled Distribution/Publication on the server, dropped all subscriptions and started over from scratch...I still get the same error each time when attempting to create a new publication...
The thing I haven't tried is restarting the mssql service/reboot - which I think may be a tad too drastic.
Any other thoughts?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
Has anyone any additional advice on this?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
I thought it would be helpful to point these few things out:
- The distribution database exists on another server
- The server I'm creating the publication on is set up as a publisher
- The server I'm creating the publication has been added as a publisher on the distribution server
When I change the setup and place the distributor on the same server as the publisher, I don't get this error...and it appears to work just fine.
This would seem to be an issue when the publication attempts to contact the distribuion server???
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
More light reading that may be of help:
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan -
Have you read this - http://sqlserver-qa.net/blogs/replication/archive/2009/02/11/5327.aspx
Let me know what you find. Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
David, your first link helped. In reference to a post in the blog:
EXEC sp_configure 'remote proc trans', '0';
RECONFIGURE;
While I don't understand the difference between running the above script or unchecking the "Require distributed transactions for server-to-server communication" under server properties...it has solved my issue.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
Wondering if somehow it doesn't execute the Reconfigure when using the GUI.?.?
Very odd but glad that helped.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Through the GUI, the TSQL is:
EXEC sys.sp_configure N'remote proc trans', N'1'GO
RECONFIGURE WITH OVERRIDE
GO
I noticed "WITH OVERRIDE"...
I guess I don't fully understand the difference between the two and why it would not work via the GUI when i disabled remote proc trans!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
There should be no difference between the two. Definitely a mystery......
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
Oh well. Consider this forum post...resolved.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
Note, after sp_configure 'remote proc trans',0 and the following reconfigure, close the query analyzer session and start a new one.
-
I know that this thread is really old, but I'm going to post this here because I found this form in my search and if I can help someone else all the better for them.
Our configuration has different AD domains for users and applications. However, there is a trust set up so that it is possible to log in with your user domain credentials to manage SQL Server. Basically, my account would be CONTOSO\User, but the accounts that run the SQL Services would be like ACME\sql_svc. For the most part, SQL Server does not seem to care about this. Someone in the CONTOSO Domain can be set up as a sysadmin and they can perform any of the tasks via SSMS and not run into issues. However, when it comes to SQL Replication all bets are off.
What happened:
I was trying to create a Transactional Publication, but I was connected to the Database Server using my CONTOSO user account. Open SSMS using pass-through Windows Auth and each time I tried to create the Transactional Publication with specific Snapshot and Log Reader Agent accounts in the ACME Domain I'd see an error that looked very similar to this, just that it was on SQL 2014:
Error Text (so that it can be searched):
SQL Server could not create a subscription for %s
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The current transaction cannot be committed and cannot support operations that write to the log file. Rollback the transaction.
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
Changed context to %d (Microsoft SQL Server Error: 50000
Well, I could not figure this one out. I mean I could not find any open transactions or anything. I was left scratching my head and reviewing the configuration and just generally being frustrated. I had created Transactional Publications many, many times in the past and never encountered anything like this.What resolved the problem:
As it turns out I found another post that more or less suggested that it was not the way I was creating the Publication that was the problem, it was the account that I was logged into SSMS as. I changed my connection so that I was logging in as sa. As soon as I did, no issues creating the Publication. Believe me, I reviewed the configuration of everything, but I was totally stumped becuase the entire time I was logged in using credentials that were part of the SQL Server sysadmin fixed Security Group.So, you can either a) log in as SA when creating the Publication or b) log into SSMS with a Windows Account that is on the same Domain as the account(s) that will be the Snapshot and Log Reader Agent are in and is configured as a sysadmin.
I hope this helps someone else down the road.
Regards, Irish
-
Sorry... wrong window..
Comment deleted
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply