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.
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.
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:
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:
Cumulative update package 2 for SQL Server 2012
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:
The SQL Server 2012 builds that were released after SQL Server 2012 was released
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:
- 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
- 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.
Note <ID> represents the internal ID of the Data Quality Project that is being deleted.
CREATE PROCEDURE DQProject<ID>.NewIndexParseTrigramsFromLexicon
PRINT 'THIS IS A DUMMY PROCEDURE TO MAKE DELETE WORK for Microsoft KB Article 2705571';
- Verify that the data quality project is deleted from Data Quality Client as expected.
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'
SELECT 'Killing this DQS session:', @session SessionID, @text QueryText;
SET @killcommand=N'Kill '+ cast(@session as nvarchar)+';'
EXEC sp_executesql @killcommand;
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
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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
For more information about how to upgrade DQS, go to the following Microsoft TechNet website:
How to upgrade DQS