SQL SERVER – DQS Error – Cannot connect to server – A .NET Framework error occurred during execution of user-defined routine or aggregate “SetDataQualitySessions” – SetDataQualitySessionPhaseTwo

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 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: http://bit.ly/PXX4NE (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 (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – DQS Error – Cannot connect to server – A .NET Framework error occurred during execution of user-defined routine or aggregate “SetDataQualitySessions” – SetDataQualitySessionPhaseTwo

  1. Hi Pinal I suddenly happened to meet a serious and surprising issue, one of my SQL Server 2008 R2 DB was missing on my local machine, but i found the same on the “Previous Versions” [Thanks to Shadow Copy from Microsoft], now I’m unable to copy and paste it in any of the drives/folders to have them as a backup, it says “You must be logged-in as an Administrator to make changes to these files”, where I myself is a Local Machine Administrator. Now How to recover the Database? Plz. guide me.

  2. Pingback: SQL SERVER – Step by Step Guide to Beginning Data Quality Services in SQL Server 2012 – Introduction to DQS « SQL Server Journey with SQL Authority

  3. you want to recover user database or user+system database?
    Also,
    1) Do you have any full “valid” backup available?
    2) is your datafile and logfile still available?

  4. Pingback: SQL SERVER – Why Do We Need Data Quality Services – Importance and Significance of Data Quality Services (DQS) « SQL Server Journey with SQL Authority

  5. The resolution has been simplified now. You just need to run the dqsinstaller.exe command with the -upgradedlls parameter to fix this issue. Refer to the following post in the DQS troubleshooting article for other similar symptoms as well: http://social.technet.microsoft.com/wiki/contents/articles/3777.troubleshoot-operational-issues-in-data-quality-services-dqs-en-us.aspx#CannotConnectDOTNETUpdates.

    If you want detailed information of why this issue occurs, see the “Upgrade SQLCLR Assemblies After .NET Framework Update” topic at http://msdn.microsoft.com/en-us/library/hh479773.aspx.

    Thanks,
    Vivek
    SQL Server Documentation

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s