Upgrade SQL Server Assemblies .NET version


(This is a personal post, just a reminder)

note-to-self

Damn! :/

Each time a new version of .NET Framework is installed (or upgraded) in our SQL Server I need to update some assemblies we’re using in some projects.

Related posts

HowTo: Send Notifications from SQL Server using SignalR

HowTo: Redeploy CLR assemblies to SQL Server (Newtonsoft.Json version issue)

The issue

Executing the send message to hub stored procedure throws an exception:

EXEC clr.SendMessageToHub 'http://THESERVER:8082', 'NotificationsHub','SendMessageNotification', @jsonmsg

‘Could not load file or assembly ‘System.Runtime.Serialization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′ or one of its dependencies. Assembly in host store has a different signature than assembly in GAC.

Solution (it works for me):

1 – Remove clr stored procedure

2 – Remove assemblies (removing Falcon_SqlProxy should remove other dependences, if not, remove manually)

3 – Go to the GAC folder.: C:\Windows\Microsoft.NET\assembly\GAC_MSIL

4 – Find the assemblies (in this case only ‘System.Runtime.Serialization.dll and System.Neh.Http.dll were needed) and copy them to the SQL Assemblies folder (C:\Falcon\SqlServerAssemblies)

5 – Create assembly Falcon_SqlProxy again:

CREATE ASSEMBLY Falcon_SqlProxy from
 'C:\Falcon\SqlServerAssemblies\Falcon.NotificationsClient.SqlProxy.dll'
WITH PERMISSION_SET = UNSAFE

6 – Create clr stored procedure again:

CREATE PROCEDURE [clr].[SendMessageToHub]
 @url [NCHAR](125),
 @hubName [NCHAR](75),
 @hubMethod [NCHAR](75),
 @message [NVARCHAR](MAX)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Falcon_SqlProxy].[Falcon.NotificationsClient.SqlProxy.SqlServerNotificationsClientService].[SendMessageToHub]
GO

7 – Check if SP is working:

DECLARE @jsonmsg VARCHAR(MAX)
SET @jsonmsg = '{"message": "GenerateProposalProgress", "description": "Resolving Portfolios", "userid": "' +
 REPLACE(ORIGINAL_LOGIN(), '\', '\\') +
 '", "formName" : "Falcon.Client.Strategies.Proposals.Forms.ObjectSelectorForm", "value" : '
 + CAST(5 AS VARCHAR(MAX)) +'}'
EXEC clr.SendMessageToHub 'http://elsuper:8082', 'NotificationsHub','SendMessageNotification', @jsonmsg

Hope this helps to my future me :P

Advertisements

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