Friday, March 9, 2012

Help - SQL Server 2005->Compact Edition Replication

Hi All ...

Ihave been following the MS on-line tutorials to get replication setupbetween SQL Server 2005 and SQL Server Compact Edition. I get to thepoint where I run the Configure Web Synchronization and it is runningthrough the process where it is doing the automated configuration ofthe shared directory - assigning users etc.

I have attemptedthis step with a user account that I expressly created for snapshotsand with the Administrator account and get the following error message:

* The operation completed successfully. (Exception from HRESULT: 0x80070000)

Atfirst, I thought this was a rights issue due to the fact that I wasusing an account that I manually created. However, by using theAdministrator account (test only until I can figure this out) thatshould have removed any rights issues associated with accessing theshared directory where the snapshot will reside.

Any help in getting this figured out will be greatly appreciated.

David L. Collison
Any day above ground is a good day!Is the account local admin on the box? Also does the account belong to "sa" group?|||

Administrator is a local account - windows user.

"sa" is the database account - and yes, for test purposes I have the system using "sa". Again, tried a local account that I had created, but got error message. Once I can get working under Administrator and SA, I will then work on getting the local account working correctly.

|||

Whatever account you use should be BOTH local admin on the box as well as sysadmin for the db.

|||

I've made the changes to allow <localaccount>\Administrator to be a sysadmin on the DB. Still get the same error message.

Any other ideas?

|||Can you try re-initializing the snapshot? Also, when you login with the <Administrator> account and browse to the shared folder do you have write access to the folder?|||

As an update:

1. I have disabled Publishing and Distribution for the database and then reconfigured.

2. I have dropped the Snapshot Agent.

3. I have dropped and recreated the virtual folder off the Default Web Site.

4. I have switched between the Administrator account and a specific account I created to manage activity on the snapshots.

5. I have switched between the DB SA account and a specific DB account that I created to manage activity on the snapshots.

6. I have modified the rights of the DOMAIN Users Administrator and the specific account I created to have "Full Control" rights to the snapshot directory and to the virtual directory.

7. I have modified the DB rights of the DB users to have db_owner, db_datareader, db_datawriter.I continue to get the error:The operation completed successfully. (Exception from HRESULT: 0x80070000)(mscorlib)

8. I have attempted to create the virtual directory manually within the Default Web location and assign the rights and then run the wizard to no avail, I get the same error message.

I'm at my wits end on this.

|||

Let me explain. Create one domain account. Add the account to the Administrator group for the box. Also, add the same account to both the SQL Server's (publisher and subscriber) and give it "sa" rights (on both servers). Replication agents copy the files to the local folder and then copy it onto the subscriber. So the account that does all this needs to be local admin as well as "sa" on the SQL box. Grant the rights at the server level. Individual db_owner, db_reader is not required if you give the account "sa" rights at the server level. Also, make sure you are logged into the box where you are setting up replication. Just logging into the SQL server through enterprise manager and setting up replication will not work. You need to be logged onto the server locally. When you are done, check each of the replication jobs. See what account each job is running under. It should be the same domain account that was created earlier.

|||

Dinakar

I've setup the accounts as you have indicated and I'm still getting the error message on the last page of the wizard. I don't want to, but I'm thinking I may have to blow away SQL Server and reinstall everything - which causes me a great deal of heartburn because of all of the development activity I have on this server, not to mention having to reconfigure SQL Server Reporting Services.

Any further ideas before I take this fatalistic step?

Thanks

|||

Folks ...

I finally ended up initiating an incident with Microsoft on this issue and have found a resolution to the problem.

1) You must create a Network share - I was forced to use the NET SHARE command from a Command Prompt - for whatever reason, the system would not recognize the share created via Windows.

2) Next, during the Wizard, you get to the screen labeld "Snapshot Share Access". In this window, I had browsed to the physical directory directory where the snapshot exists - THIS IS NOT VALID (note to MS - remove the Browse function). Here, you must enter the share created in the previous step -\\ComputerName\ShareName

The wizard then completes normally.

Side note, during all of this, MS tech support had me delete the Snapshot and recreate the snapshot. Once I deleted the snapshot and was attempting to recreate the snapshot, I began receiving errors that certain system tables did not exist. I was forced to manually remove the replication functionality by opening SQL Server Management Studio and executing the following:

1) USE <dbName>

2) EXEC sp_removedbreplication '<dbName>'

I was then able to recreate the snapshot.

Hope this helps others out who are having similar issues.

No comments:

Post a Comment