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

Article translations Article translations
Article ID: 2705571 - View products that this article applies to.
Microsoft distributes Microsoft SQL Server 2012 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release.
Expand all | Collapse all

On This Page

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

Cumulative update information

Service pack information for SQL Server 2012

To resolve this problem, obtain the latest service pack for SQL Server 2012. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2755533 How to obtain the latest service pack for SQL Server 2012

SQL Server 2012

The fix for this issue was first released in Cumulative Update 2 for SQL Server 2012. For more information about how to obtain this cumulative update package for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
2703275 Cumulative update package 2 for SQL Server 2012
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2692828 The SQL Server 2012 builds that were released after SQL Server 2012 was released

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:

SQL Server Data Quality Services

--------------------------------------------------------------------------------

<MessageId>: ApplicationUnhandledException

An unhandled exception has occurred. For more details, see the Data Quality Services client log.

--------------------------------------------------------------------------------

System.Data.SqlClient.SqlException (0x80131904): A severe error occurred on the current command. The results, if any, should be discarded.

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

Properties

Article ID: 2705571 - Last Review: November 19, 2012 - Revision: 6.0
Applies to
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Web
Keywords: 
kbtshoot kbqfe KB2705571

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com