A merge replication error - The schema script 'xxx.sch' could not be propagated to the subscriber.

Scenario: see http://msdn.microsoft.com/en-us/library/aa337457.aspx for the project.

Problem:

When I view synchronization status, I get the error message.

Solution:

In my case, it is a permission issue. For some reason, the merge agent account does not have a read permission on the snapshot folder.

For other possible reasons, see this thread.

http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/7dab2dc0-6dc4-4e4f-8624-b495fec02c04/

P.S. After successfully configuring the merge transaction,the merge transaction can go bidirectionally as long as you click 'Start' on the View Synchronization Status' dialog box for the local subscription after the MDL activities on either the subscriber or the publisher.

Finally the transactional replication works - a clarification on View Synchorization Status

Scenario:

see http://msdn.microsoft.com/en-us/library/aa337437.aspx for creating a continuous transactional replication on Windows 7 Ultimate, SQL Server 2012 Dev 64 bit (the instance is a named instance CK-PC\SQL2012). The pub and sub are in the same instance.

Problems:

I have faced several problems as documented in the earlier posts. But after I correctly created a local publication and subscription, I finally see the following screen in the last step of Lesson 2.

This is good! 

Solution: 

Just do some DML activities, the dialog box will indicate the synchronization between pub and sub as below.

Cannot drop a publication due to the missing owner of the database

Scenario: 

Drop a publication

Problem:

-- Dropping the transactional publication

USE [AdventureWorks2012]
EXEC sp_droppublication @publication = N'AdvWorksProductTrans'
GO


Msg 15517, Level 16, State 1, Procedure sp_replcmds, Line 1

Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Solution:

I run as CK-PC\CK (an admin). The publication is for a table on the AW2012 database. When I check the owner of the AW2012 database, it is empty. I select CK-PC\CK as the owner for the database. Now I can drop the publication.

Snapshot Agent Failed to Start due to a wrong snapshot folder name

Scenario: 

View Snapshot Agent Status to make sure it is working

Problem:

More details the error from the job history:

Solution:

In my case, I specify \\CK-PC\repldata for the snapshot folder in the publication definition phase. However, I name the physical folder RepData (which missed l after p). So just give it a correct name ReplData:

D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\ReplData

Remove the Distribution Configuration for Replication

Scenario:

I configure a brand new distribution configuration for a replication today. At the last step, I get an error on complaining the setup of SQL Server Agent automatic startup.

I think this is due to the fact that I have disabled SQL Server agent to start as the default. So I start the SQL Server Agent. But, now, I cannot re-configure the distribution anymore. The distributor properties do not help. So I decide to delete the distribution configuration and start a new one.

How to do it?

Exec sp_dropdistributor @no_checks = 1

or

Exec sp_removedbreplication 'PublicationName','both'

If you already have publishers and subscribers associated with this distributor, see Hilary Cotter's post on
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/7c12242d-cc1f-4f23-92e1-b8cffd55a59e/

Well, I still get the same error message on Access is denied.

Solution: 

Use SQL Server Configuration Manager to configure the SQL Server Agent start automatically. Then in the distribution configuration process, the option to set up an auto/manual startup of the SQL Server does not appear anymore.

Note: Open SQL Server explicitly as an admin does not work. Re-starting SQL Server and SQL Server Agent does not help either.

Crashed MDS Add-In for Excel - Need to enable it annually

I have SQL Server 2012 Master Data Add-In for Excel 2010 (32bit) installed. Everything works OK until I define the domain-based attribute. It runs forever....Updating attributes takes hours...keep running... So I have to manually shut down Excel. Then when I open Excel again. The Master Data add-in is not in the menu anymore! It is disabled by Excel to protect the program. To bring it back, you have to manully enable it. See below.



Master Data Services Configuration Manager - An error on deleting the master database services databases and websites

Problem: 

During the experimental process, I have created several master database services databases and websites from the Master Data Services Configuration Manager GUI. After everything finally works out, I like to just keep one copy and delete the rest. How do I do it? The only source is from the Microsoft to suggestion uninstalling the Master Data Services.

http://msdn.microsoft.com/en-us/library/ff487065.aspx

So I did it. This time, the IIS part is OK. But when I try to create a MDS database with the previously used name MDS. I got an error message. So the earlier names were not removed when the MDS feature was uninstalled.

Solution:

The MDS folks stated in their website (http://sqlblog.com/blogs/mds_team/)

.....Important   If you do not at least connect the new MDS Web application to a database, you'll run into problems when you select the site again. It will display the message that the site has no MDS Web applications, even though you created one. You must use IIS Manager or another IIS tool to delete the incomplete MDS Web application. Then add it to MDS Configuration Manager, making sure to connect it to the database this time, and click Apply. If you do not delete the incomplete MDS Web application before trying to add it again, you'll receive the error message "An unexpected error occurred: Object reference not set to an instance of an object."........

So how to use IIS Manager to delete the incomplete MDS Web application?

http://technet.microsoft.com/en-us/library/cc771205(v=ws.10).aspx

I delete all of them including the default one.

For the database part, I finally find they are in the SQL Server as regular database after creation on the Master Data Service configuration manager. So I backed them up first and then use the name MQS for a new database. It is OK this time. Now the database and the web application are fresh.

Now the beautiful screen with one database called MDS and one website application linking to the database with the default URL.

So, in retro-prospective, I think that I do not need to uninstall the Master Data Services. I just need to delete the web applications from the IIS manager and the MDS databases from SQL Server. Then I should be able to have a fresh configuration.

Master Data Services Configuration Manager - Issue on the URL setup

Do not forget to set up the URL

Master Data Services Configuration Manager - Issues on IIS

Problem:

I use a 64xWindows 7 ultimate SP1 and SQL Server 2012 SP1 CP3 Dev for the Master Data Services. I have configured the settings for IIS based on suggestions on the page:

http://msdn.microsoft.com/en-us/library/ee633744.aspx

Or specifically as follows:

 Internet Information Services

          World Wide Web Services

                                Common HTTP Features

Static Content

Default Document
Directory Browsing

HTTP Error

Application Developmet

                 ASP.NET

                 .NET Extensibilitt

                  ISAPI Extensions

                  ISAPI Filters

Health and Diagnostics

              HTTP Logging

                  Request Monitor

Security

              Windows Authentication

                   Request Filtering

Performance

               Static Content Compression

Web Management Tools

                 IIS Management Console

Microsoft .NET Framework 3.5.1 Features

                HTTP Activation

                Non-HTTP Activation

Windows Process Activation Service

                Process Model

                .NET Environment

                Configuration APIs

But I still got the error/warning message: ...Internet Information Services (IIS) is not configured on this server. The required .svc handler mappings are not installed in IIS...

Reason: 

I have .NET 4.0 installed first, the IIS configuration, which is compatible with .NET 3.5.1, was just configured. They do not match. I need to re-configure IIS to make it compatible with .NET 4.0

Solution:

Open a command prompt and go to C:\Windows\Microsoft.NET\Framework64\v4.0.30319). Run aspnet_regiis -i.  It fixed the problem.

You need to subscribe the Reference Data Providers Service first

Problem: 

When I click the Browser button on the Reference Data tab for Configuring Composite Domain to Use Reference Data Service, nothing shows up in the Online Reference Data Services dialog box.

Reason: 

I have not subscribed any Online Data Providers Services

Solution: 

Go to https://datamarket.azure.com/browse/Data?Category=dqs, subscribe the service you need. Free for a low volume validation.

A quick fix to make the code window showing up in Visual Studio 2012 - Reset Window Layout

Problem:

Kind of weird, when I open a solution in VS2012 today, I cannot see the code window anymore. Double-click the program file, right-click to view code, from the View menu, from the code menu icon...All of them CANNOT bring the code window tup. I simply cannot see the code!

Solution:

The last resort - Reset Window Layout from the Window menu. It works!

An error on connecting to DQS Client and the solution

Problem:

When I started the DQS Client earlier today at (Local)\SQL2012 (where SQL2012 is the instance name), I got the following error msg:

A new version of .NET or Data Quality Services was installed on this machine. In order to continue to work with DQS please run 'DqsInstaller.exe -upgrade'.

Reason: 

I installed DQS with SQL Server RTM. Then I upgrade SQL Server to SP1 CU3, but not upgrading DQS

Solution:

1. open a command prompt: CD D:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn
2. dqsinstaller.exe -upgrade

But I still have the same error message. 

Based on this article:
http://social.technet.microsoft.com/wiki/contents/articles/8442.upgrade-dqs-installing-cumulative-updates-or-hotfix-patches-on-data-quality-services-en-us.aspx

3. dqsinstaller.exe -upgradeDLLs

Now it works!