January 9, 2008 at 7:24 am
I'm having a slight issue with SQL replication between two database's. I am able to set up a Publication without any issues. I go through all the steps until I reach the end for New Subscriptions and I come up with this error...
I have been able to resolve a few issues (i.e DTC needs to be installed and other options must be turned on with Computer Services. Here's the output of the error.
===================================
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)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)
at Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn)
at Microsoft.SqlServer.Replication.ReplicationDatabase.LinkPublicationForUpdateableSubscription(String publisher, String publisherDB, String publication, String distributor, PublisherConnectionSecurityContext publisherSecurity)
at Microsoft.SqlServer.Management.UI.CreateSubscriptionWizard.AddPublisherLink(SubWizardSubscriber subscriber, SubWizardPublication pubInfo)
at Microsoft.SqlServer.Management.UI.CreateSubscriptionWizard.CreateSubscriptions(Boolean executeNow)
===================================
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPLLINK_W2K3-2-1706713180_TESTADMI-1665415706_CC6_REPLIC1142247637_TESTADMI-1665415706" was unable to begin a distributed transaction.
Changed database context to 'TestAdmin'.
OLE DB provider "SQLNCLI" for linked server "REPLLINK_W2K3-2-1706713180_TESTADMI-1665415706_CC6_REPLIC1142247637_TESTADMI-1665415706" returned message "No transaction is active.". (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=7391&LinkId=20476
------------------------------
Server Name: W2K3-1
Error Number: 7391
Severity: 16
State: 2
Line Number: 1
------------------------------
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
Any clues into this issue would be appreciated as I think slowly go blind starring at websites. 😀
January 9, 2008 at 9:29 pm
Make sure MSDTC running on both machines, the error shows there is problem in linked server and communication between the servers is not getting established. You can check this by writing a simple query from server1
select * from server2.master.dbo.sysobjects (please check the syntax :))
It should return the records if linked server is not a problem, otherwise you will see the same error and have to modify the MSDTC settings.
January 10, 2008 at 10:50 am
So I have double checked my MSDTC on both SQL Server 2005 and turned everything on just for now. I also found out running that sql query you have provided and I failed to connect from Server1 to Server2 getting the following error message:
Msg 7411, Level 16, State 1, Line 1
Server 'W2k3-1' is not configured for DATA ACCESS.
I then tried the same thing on the Server2 to Server1 with that query and failed. I then found out that I did not add a Link Server on Server2 to Server1, and once I added that, I was able to do the query to Server1 without an issue.
Now I google the Data Access error message and it is referring me to the DTApackage?
Also, just for giggle's I ran through my replication (with updatable subscription) and it told me to run an sp_link_publication...so.....huh???
Thanks for the help so far. 🙂
January 10, 2008 at 8:39 pm
error message 'Not configured for data access' can be fixed by running
sp_serveroption 'ServerName', 'data access', 1
change ServerName with the name you have created linkedserver.
Please let me know the exact error you see after this.
January 10, 2008 at 8:42 pm
Also you may have to use the below:
sp_serveroption 'ServerName', 'rpc',1
Hope this helps.
January 11, 2008 at 6:47 am
I forgot to make a post saying I fixed my Data Access issue by going into the Linked Server properties and change Data Access from false to true.
I will run that command with regards to the sp_link_publication and let you know how that goes.
Thanks
January 11, 2008 at 7:29 am
So...I read that completely wrong, which is a bad thing on my part.
I have can now connect using select * from server2.master.dbo.sysobjects from Server1 to Server2 and from Server2 to Server1.
January 14, 2008 at 4:39 am
Also please check if you have distributor_admin user created on all servers (publisher, subscriber and remote distributor (if any)). If this is not the case even after running sp_link_publication, then you might try it creating manually and give the same password for this user which you have kept while configuring the replication.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply