Have you ever encountered an error while trying to use Data Quality Services (DQS) in SQL Server? If so, you’re not alone. In this blog post, we will discuss a common error that prevents users from connecting with the DQS client and provide a step-by-step solution to resolve it.

The error message you may encounter is “Cannot connect to server. A .NET Framework error occurred during execution of user-defined routine or aggregate ‘SetDataQualitySessions'”. This error can be quite frustrating, especially if everything was working fine previously.

After some investigation, we found that the error is caused by a mismatch between the Module Version IDs (MVIDs) of the SQL Common Language Runtime (SQLCLR) assemblies in the SQL Server 2012 database and the Global Assembly Cache (GAC). Fortunately, Microsoft has provided a workaround for this issue.

To resolve the DQS error, follow these steps:

1. Make sure the DQS client is properly closed before proceeding.
2. Identify the correct path for the NETAssemblies based on your operating system. For a 64-bit machine, the path is typically “c:\windows\Microsoft.NET\Framework64\v4.0.30319”. If your Windows installation is on a different drive or you have a 32-bit version installed, adjust the path accordingly.
3. Execute the provided script in the database DQS_MAIN. It is important not to run this script in the master database, as it will not fix the error.
4. Restart your SQL Services once the script has been executed.

Here is a modified version of the script provided by Microsoft:

USE DQS_MAIN
GO
BEGIN
DECLARE @NETAssemblies NVARCHAR(200)
-- For 64 bit uncomment following line
SET @NETAssemblies = 'c:\windows\Microsoft.NET\Framework64\v4.0.30319\' 
-- For 32 bit uncomment following line
-- SET @NETAssemblies = 'c:\windows\Microsoft.NET\Framework\v4.0.30319\'
DECLARE @AssemblyName NVARCHAR(200), @RefreshCmd NVARCHAR(200), @ErrMsg NVARCHAR(200)
DECLARE ASSEMBLY_CURSOR CURSOR FOR
SELECT name AS NAME
FROM sys.assemblies
WHERE name NOT LIKE '%ssdqs%'
AND name NOT LIKE '%microsoft.sqlserver.types%'
AND name NOT LIKE '%practices%'
AND name NOT LIKE '%office%'
AND name NOT LIKE '%stdole%'
AND name NOT LIKE '%Microsoft.Vbe.Interop%'
OPEN ASSEMBLY_CURSOR
FETCH NEXT FROM ASSEMBLY_CURSOR
INTO @AssemblyName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @RefreshCmd = 'ALTER ASSEMBLY [' + @AssemblyName + '] FROM ''' +
@NETAssemblies + @AssemblyName + '.dll' +
''' WITH PERMISSION_SET = UNSAFE'
EXEC sp_executesql @RefreshCmd
PRINT 'Successfully upgraded assembly ''' + @AssemblyName + ''''
END TRY
BEGIN CATCH
IF ERROR_NUMBER() != 6285
BEGIN
SET @ErrMsg = ERROR_MESSAGE()
PRINT 'Failed refreshing assembly ' + @AssemblyName + '. Error message: ' + @ErrMsg
END
END CATCH
FETCH NEXT FROM ASSEMBLY_CURSOR
INTO @AssemblyName
END
CLOSE ASSEMBLY_CURSOR
DEALLOCATE ASSEMBLY_CURSOR
END
GO

Note: It is recommended to use the original script provided by Microsoft in section 4.23 of their article. The modified script above is specific to my machine and may need adjustments for your environment.

Once you have followed these steps and restarted your SQL Services, you should be able to open the DQS client without encountering the error. Your DQS functionality will be restored, allowing you to effectively use Data Quality Services in SQL Server.

We hope this blog post has helped you resolve the DQS error in SQL Server. If you have any further questions or need additional assistance, feel free to leave a comment below.