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


Note: Today (12/07/2017) after moving some databases to other disks I had to do it again, so I’ve discovered other related issue.

It seems sometimes the USER SID changes after a backup/restore and throws an error:

The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘Falcon’. You should correct this situation by resetting the owner of database ‘Falcon’ using the ALTER AUTHORIZATION statement.

So, before create the assemblies you (I mean me) you’ll need to change the database owner to sa.


EXEC sp_changedbowner 'sa'

ALTER DATABASE [database_name] SET TRUSTWORTHY ON
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

exec sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

exec sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

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