SQL SERVER – DQS Error – Cannot connect to server – A .NET Framework error occurred during execution

Earlier I wrote a blog post about how to install DQS in SQL Server 2012. Today I decided to write a second part of this series where I explain how to use DQS, however, as soon as I started the DQS client, I encountered an error that will not let me pass through and connect with DQS client. It was a bit strange to me as everything was functioning very well when I left it last time.  The error of DQS Error was very big but here are the first few words of it.

Cannot connect to server.
A .NET Framework error occurred during execution of user-defined routine or aggregate “SetDataQualitySessions”:
System.Data.SqlClient.SqlException (0x80131904): A .NET Framework error occurred during execution of user-defined routine or aggregate “SetDataQualitySessionPhaseTwo”:

The error continues – here is the quick screenshot of the error.

As my initial attempts could not fix the error I decided to search online and I finally received a wonderful solution from Microsoft Site. The error has happened due to latest update I had installed on .NET Framework 4. There was 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). This mismatch was to be resolved for the DQS to work properly.

The workaround is specified here in detail. Scroll to subtopic 4.23 Some .NET Framework 4 Updates Might Cause DQS to Fail.

The script was very much straight forward.

Here are the few things to not to miss while applying workaround.

• Make sure DQS client is properly closed
• The NETAssemblies is based on your OS.
• NETAssemblies for 64 bit machine – which is my machine is “c:\windows\Microsoft.NET\Framework64\v4.0.30319”. If you have Winodws installed on any other drive other than c:\windows do not forget to change that in the above path. Additionally if you have 32 bit version installed on c:\windows you should use path as “c:\windows\Microsoft.NET\Framework\v4.0.30319”
• Make sure that you execute the script specified in 4.23 sections in this article in the database DQS_MAIN. Do not run this in the master database as this will not fix your error.
• Do not forget to restart your SQL Services once above script has been executed.
• Once you open the client it will work this time.

Here is the script which I have bit modified from original script. I strongly suggest that you use original script mentioned 4.23 sections. However, this one is customized my own machine.

/*
Original source: https://social.technet.microsoft.com/wiki/contents/articles/3711.microsoft-sql-server-code-named-denali-ctp3-release-notes.aspx (Technet)
Modifications:
-- Added Database context
-- Added environment variable @NETAssemblies
-- Main script modified to use @NETAssemblies
*/
USE DQS_MAIN
GO
BEGIN
-- Set your environment variable
-- assumption - Windows is installed in c:\windows folder
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

Reference: Pinal Dave (https://blog.sqlauthority.com)