KB2705571 - FIX: SQL Server Data Quality Client stops responding when you try to delete a data quality project after you install Cumulative Update 1 for SQL Server 2012

Symptoms

Consider the following scenario:

• You create one or more data quality projects in Data Quality Client in Microsoft SQL Server 2012.

• You install Cumulative Update 1 for SQL Server 2012 (SQL Server 2012 CU1), and then you run the DQSInstaller.exe file to upgrade Data Quality Services (DQS) catalogs and to add new objects.

• You try to delete one of the created data quality projects by using Data Quality Client.

In this scenario, Data Quality Client stops responding, and the SQL Server database engine enters an infinite loop. Additionally, the sqlservr.exe process experiences high CPU usage.

Notes

• To recover from this issue, restart Data Quality Client and DQS.

• This issue only occurs if the data quality project was created before you installed SQL Server 2012 CU1.

Cause

This issue occurs because of an infinite loop in a system stored procedure that is caused by updating a loop counter incorrectly.

Note This issue occurs because of a regression in SQL Server 2012 CU1.

Resolution

Workaround

To work around this issue, use one of the following methods.

Method 1

To add a dummy procedure that enables the stored procedure infinite loop to continue and then exit correctly, follow these steps:

1. Run the following query to identify the schema name for the affected project that you tried to delete:
   
   

   SELECT 'KnowledgeManagement'+CAST(ID AS NVARCHAR) AS SchemaName, Name FROM DQS_MAIN.dbo.A_KNOWLEDGEBASE WHERE TYPE=2

2. Use the SchemaName value that is returned, and then run statements that resemble the following in SQL Server Management Studio to supply the matching schema name and the Data Quality Project ID. A new dummy procedure will be created for the schema and the corresponding data quality project. Therefore, the infinite loop proceeds correctly.
   
   

   USE DQS_PROJECTS
   GO
   CREATE PROCEDURE DQProject<ID>.NewIndexParseTrigramsFromLexicon
   
   AS 
   BEGIN
   PRINT 'THIS IS A DUMMY PROCEDURE TO MAKE DELETE WORK for Microsoft KB Article 2705571';
   END
   

   Note <ID> represents the internal ID of the Data Quality Project that is being deleted.

3. Verify that the data quality project is deleted from Data Quality Client as expected.

Method 2

If you have to kill the looping procedure quickly to conserve CPU resources, run the following statements to identify the correct session. However, killing the looping procedure does not delete the data quality project cleanly. The following Transact-SQL statements help identify and kill the correct session. If more than one session from Data Quality Client is running, you may have to run the statements multiple times:

DECLARE @session smallint, @text nvarchar(2000), @killcommand nvarchar(200)

SELECT TOP 1 @session=s.session_id, @text=st.text

FROM sys.dm_exec_sessions s

FULL OUTER JOIN sys.dm_exec_requests r ON s.session_id=r.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st

WHERE program_name like 'DQ Services Client: Client Id = %, KB Id = %, Proxy = KnowledgebaseManagementEntryPointClient'

AND s.status='running';

SELECT 'Killing this DQS session:', @session SessionID, @text QueryText;

SET @killcommand=N'Kill '+ cast(@session as nvarchar)+';'

EXEC sp_executesql @killcommand;

Note If you use method 2 to kill the DQS connection, you may receive the following error message in Data Quality Client:

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

For more information about how to upgrade DQS, go to the following Microsoft TechNet website:

How to upgrade DQS