I'm using the Microsoft Sample Code to create the subscription. First on the Publisher I delete any subscription manually using SQL Server Management Studio. Then on the client using SQL Express Management studio, I delete the subscription on the subscribing PC.
So my code starts off presumably with database engines on subscriber and publisher having no subscriptions. It then tries to create the subscription on the client PC and then synchronize it.
Again this is Web Synchronization, and the websync siagnostic information on the publisher/distributor/iis machine all looks good, "success" in all the right places.
I get the error below, indicating the subscription already exists when I go to create the subscription.
I look in SQL Express subscriber and sure enough the subscription is back. But I right click on it in the Management Studio and select "Properties" and I get an error saying the subscription doesn't exist (even though I see it in Management Studio) or I don't have access to it. I'm logged in with Administrator credentials.
On the publishing server, the subscription is not created. The publication is set for anonymous synching.
Here is the exception trapped by my code
System.ApplicationException: The subscription could not be synchronized. Verify that the subscription has been defined correctly. > System.ApplicationException: The subscription could not be created. Verify that the subscription has been defined correctly. > Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. > System.Data.SqlClient.SqlException: Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information.
The subscription properties table 'MSsubscription_properties' does not exist in the current database.
So apparently the subscription gets partially created, and then fails and is inaccessible, but holding space.
I'll follow this post with the code in the next reply.
Thanks for any help, I need help figuring out:
1) How to create the subscription correctly?, and
2) How to detect that it is already there so I won't try to create it again?, and
3) How to synch it correctly?
I am using the sample codes and books online.
SQLExpress subscribing client and SQL Server publisher are on SP2.
David
'Here is the codeDim publicationName As String = "CALM"
Dim publisherName As String = "DEV2K3"
Dim subscriberName As String = "(local)\SQLEXPRESS"
Dim subscriptionDbName As String = "dbName_on_subscriber"
Dim publicationDbName As String = "dbName_on_publisher"
'Dim hostname As String = My.Computer.Name
Dim webSyncUrl As String = "https://myserver.mydomain.com/replication/replisapi.dll"
'Create the Subscriber connection.
Dim conn As ServerConnection = New ServerConnection(subscriberName)
' Create the objects that we need.
Dim subscription As MergePullSubscription
Try
' Connect to the Subscriber.
conn.Connect()
' Define the pull subscription.
subscription = New MergePullSubscription()
subscription.ConnectionContext = conn
subscription.PublisherName = publisherName
subscription.PublicationName = publicationName
subscription.PublicationDBName = publicationDbName
subscription.DatabaseName = subscriptionDbName
subscription.SubscriberType = MergeSubscriberType.Anonymous
subscription.UseWebSynchronization = True
subscription.InternetUrl = webSyncUrl
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.InternetLogin = "mylogin"
subscription.InternetPassword = "mypassword"
subscription.CreateSyncAgentByDefault = True
Try
subscription.Create()
Catch ex As Exception
Throw New ApplicationException("The subscription could not be " + _
"created. Verify that the subscription has " + _
"been defined correctly.", ex)
End Try
Dim agent As MergeSynchronizationAgent
If subscription.LoadProperties() Then
agent = subscription.SynchronizationAgent
If agent.PublisherSecurityMode = Nothing Then
agent.PublisherSecurityMode = SecurityMode.Integrated
agent.Distributor = "myserver.mydomain.com"
agent.DistributorSecurityMode = SecurityMode.Integrated
agent.UseWebSynchronization = True
agent.InternetUrl = webSyncUrl
agent.InternetSecurityMode = SecurityMode.Standard
agent.InternetLogin = "mydomain\mylogin"
agent.InternetPassword = "mypassword"
End If
agent.OutputVerboseLevel = 1
agent.Output = ""
agent.Synchronize()
Else
Throw New ApplicationException(String.Format( _
"A subscription to '{0}' does not exist on {1}", _
publicationName, subscriberName))
End If
Catch ex As Exception
Throw New ApplicationException("The subscription could not be " + _
"synchronized. Verify that the subscription has " + _
"been defined correctly.", ex)
Finally
conn.Disconnect()
End Try|||Hope everybody had a good weekend!
Pray for rain in Texas, we're dry.
Well on this issue I have proceeded to determine that the web synch is not configured correctly somehow, and I'm trying to get through that issue. I may end up spend the $$ with Microsoft support.
Using the Microsoft Synchronization Manager I can not get the subscription to work with ftp snapshot.
I get all the way to the point that the https://mywebserver.com/replication/replisapi.dll?diag is showing all success.
I have a client PC on the same segment, no firewalls between, domain names in hosts file to insure they resolve exactly as on the certificate, and so that's all resulting in a successful diagnostics page.
But from that same client PC with SQL Server Express on it I am attempting to get the subscription correct even with the Synch Manager and with the SQL Server Management Studio Express, the dropmergesub used to clean up, and cleanining up on the Publisher also, then attempting the subscription again, same failures.
So in SQL Manager express I go to Replication, Local Subscriptions, nothing is there. I right click, and select New Subscription. I get the Wizard. I try the web address and SQL authentication and get SQL Server error 53, Named Pipes provider error 40, could not open connection. That's expected at this point. But since I'm on the same segment, domain and logged in as a Domain Admin. I connect to the server from the new subscription Wizard and see the publication. I select it and select the database it is going into on the SQL Express client, setting it as a pull subscription. I mark it for Web Synchronization, snapshot to transfer at first, on demand only. It starts off showing progress then fails, saying "The subscription already exists. (New Subscription Wizard)"
So either something is going wrong in the subscription process, or something is wrong in the clean up of my previous attempts.
Keep in mind, however, that ultimately I do not want to subscribe on the domain, I want to subscribe from clients that are never on the domain, web only.
Do you have any insights into what I'm screwing up here?
Thanks,
David|||
Okay, I ran this again after deleting the subscription in SQL Management Studio Express on the client PC
sp_dropmergepullsubscription @.publication=N'mypub',@.publisher=N'myserver', @.publisher_db = N'mydb'
But I found this among the stored procedures and ran it on the publishing server, though I haven't seen it mentioned anywhere:
sp_cleanupdbreplication, and it totally nuked the publication. So I totally recreated the publication from scratch, getting all success along the way.
From a client PC browser I browse to https://myserver/replication/replisapi.dll?diag and I get prompted as expected for my ID and password, I use a domain admin credentials, and I get the SQL Websync Diagnostic Information page, and it shows all success. As the app user ID, I get access denied. I understand this is proper and what I should expect.
Then I set up the subscription on SQL Server Express on the client laptop. It says all success too.
So I open the Microsoft Synchronization Manager and try to synch the subscription and it says it can't connect to https://.... but doesn't show me the rest of the message, because I get a popup error reporting screen that sends the following error to Microsoft, and nukes out my app so I can't read anything.
This is the report getting sent to Microsoft.
Event Type: Error
Event Source: Application Error
Event Category: (100)
Event ID: 1000
Date: 3/26/2007
Time: 11:12:41 AM
User: N/A
Computer: DELL4150
Description:
Faulting application mobsync.exe, version 5.1.2600.2180, faulting module comctl32.dll, version 6.0.2900.2982, fault address 0x0006d0cc.
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 41 70 70 6c 69 63 61 74 Applicat
0008: 69 6f 6e 20 46 61 69 6c ion Fail
0010: 75 72 65 20 20 6d 6f 62 ure mob
0018: 73 79 6e 63 2e 65 78 65 sync.exe
0020: 20 35 2e 31 2e 32 36 30 5.1.260
0028: 30 2e 32 31 38 30 20 69 0.2180 i
0030: 6e 20 63 6f 6d 63 74 6c n comctl
0038: 33 32 2e 64 6c 6c 20 36 32.dll 6
0040: 2e 30 2e 32 39 30 30 2e .0.2900.
0048: 32 39 38 32 20 61 74 20 2982 at
0050: 6f 66 66 73 65 74 20 30 offset 0
0058: 30 30 36 64 30 63 63 006d0cc
David
|||I ran it again and got that the Synch failed, and it says "The parameter is incorrect:" then has a funny character on the end of the string.
David
|||I finally discovered the websync log in IIS in the replication folder and it has this very interesting clue:
CReplicationListenerWorker , 2007/03/26 19:59:50.828, 1952, 174, S2, INFO: =============== START PROCESSING REQUEST ==============
CReplicationListenerWorker , 2007/03/26 19:59:50.828, 1952, 298, S2, INFO: Processed request type: MESSAGE_TYPE_SyncContentsUpload.
replrec!FillErrorInfo , 2007/03/26 19:59:50.937, 1952, 19823, S1, ERROR: ErrNo = 0x80045901, ErrSrc = <null>, ErrType = 9, ErrStr = The process could not connect to Distributor 'dev01.ensourcebsi.com'.
DatabaseReconciler , 2007/03/26 19:59:50.937, 1952, 19935, S2, :T:,110,0,10,,,,,,,
DatabaseReconciler , 2007/03/26 19:59:50.937, 1952, 19942, S2, INFO: Session Highlights: FAIL, WEBSYNC_SERVER,
ReconcilerHost , 2007/03/26 19:59:50.937, 1952, 189, S1, ERROR: ReconcilerHost::Initialize failed, hr = 0x80045901.
CReplicationListenerWorker , 2007/03/26 19:59:50.937, 1952, 315, S1, ERROR: Failure initializing ReconcilerHost, hr = 0x80045901.
CReplicationListenerWorker , 2007/03/26 19:59:50.937, 1952, 396, S2, INFO: =============== DONE PROCESSING REQUEST ===============
Do you know what this is telling me?
David
|||My final solution: I will not use Web Synchronization nor SQL server Replication for my software product, it is not suitable in this case.Instead I'm using Visual Studio and writing my own web service and my own business logic to synchronize my data.
David
No comments:
Post a Comment