SQL Server 2008R2 deployment ?

[Julien22bzh]

Hi Eitan,

now i try to deploy sql-clr-ics on SQL Server 2008 R2 (with framework v2.0.50727), but i have error n°6257.

Creating Assembly [dbo].[sql_clr_ics]... Msg 6257, Niveau 16, État 1, Ligne 1 Échec de CREATE ASSEMBLY pour l'assembly 'sql_clr_ics' car ce dernier a été créé pour une version du CLR (Common Language Runtime) qui n'est pas prise en charge. ** Une erreur a été rencontrée lors de l'exécution du lot. Abandon en cours.

Is it possible to deploy your solution on SQL Server 2008 ? Can you help me to deploy solution on this server ?

Thanks,

Julien.

[EitanBlumin]

Hi again Julien,

The solution could not be deployed to SQL 2008R2 because it was built with .NET Framework 4, while SQL 2008R2 CLR only supports .NET Framework 3.5 at most.

I created a new pull request after converting the assembly to support .NET Framework 3.5

Please try this out and see if it helps: https://github.com/MadeiraData/sql-clr-ics/blob/v2.3/sql_clr_ics/sql_clr_ics_install.sql

If you can verify that it works in SQL2008R2, then I'll publish it as a new release.

Thanks!

[Julien22bzh]

Hi Eitan,

Thanks, i tried sql_clr_ics_install.sql (v2.3) and no error message appears but no storedProcedure was created. And if i test a script sp_send_calendar_event, i have this error "Procédure stockée 'sp_send_calendar_event' introuvable."

Julien.

[EitanBlumin]

I updated the assembly signing on the CLR.

Please try the following:

1. Drop the two stored procedures and the assembly:

IF OBJECT_ID('sp_send_calendar_event') IS NOT NULL DROP PROCEDURE [dbo].[sp_send_calendar_event]
GO
IF OBJECT_ID('clr_send_ics_invite') IS NOT NULL DROP PROCEDURE [dbo].[clr_send_ics_invite]
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'sql_clr_ics') DROP ASSEMBLY [sql_clr_ics];
GO

2. Drop the assembly login and asymmetric key:

USE [master]
GO
DROP LOGIN [sql_clr_ics_login]
GO
DROP ASYMMETRIC KEY [sql_clr_ics_pkey]
GO

3. Get this updated version of the compiled DLL in the attached ZIP file and extract it to C:\SqlClrIcs\ on the SQL Server machine:

sql_clr_ics.zip

4. Execute the updated version of sql_clr_ics_install.sql on your SQL Server instance. Make sure you run the script in SQLCMD mode.

Let me know if it helps.

[Julien22bzh]

Eitan,

I think sql_clr_ics_install.sql create nothing. Because no message appear during execution and duration=0s. The only message is "Commande(s) réussie(s).".

Julien.

[EitanBlumin]

Julien, I believe that's because you're re-using a query window where you possibly tried to execute this script without SQLCMD mode.

This causes the script to activate NOEXEC ON which disables all consequent commands.

Please either run the command SET NOEXEC OFF and try again, or open in a new query window.

[Julien22bzh]

Eitan,

sql_clr_ics_install.sql is ok with your procedure. Now exec sp_send_calendar_event generate a message related to @importance :

Msg 6522, Niveau 16, État 1, Procédure clr_send_ics_invite, Ligne 0
Une erreur .NET Framework s'est produite au cours de l'exécution de la routine ou de la fonction d'agrégation définie par l'utilisateur "clr_send_ics_invite" : 
System.Exception: Error while Parameter validations: [sql_clr_ics] Unable to send mail due to validation error(s): sensitivity Public is invalid. Valid values: SYSTEM.STRING[]
@importance Normal is invalid. Valid values: SYSTEM.STRING[]

 ---> System.Exception: Unable to send mail due to validation error(s): sensitivity Public is invalid. Valid values: SYSTEM.STRING[]
@importance Normal is invalid. Valid values: SYSTEM.STRING[]

System.Exception: 
   à StoredProcedures.clr_send_ics_invite(SqlString profile_name, SqlString recipients, SqlString copy_recipients, SqlString blind_copy_recipients, SqlString from_address, SqlString reply_to, SqlString subject, SqlString body, SqlString body_format, SqlString importance, SqlString sensitivity, SqlString file_attachments, SqlString location, SqlDateTime start_time_utc, SqlDateTime end_time_utc, SqlDateTime timestamp_utc, SqlString method, SqlInt32 sequence, SqlString prod_id, SqlBoolean use_reminder, SqlInt32 reminder_minutes, SqlBoolean require_rsvp, SqlString recipients_role, SqlString copy_recipients_role, SqlString blind_copy_recipients_role, SqlString smtp_serve
    ...
System.Exception: 
   à StoredProcedures.clr_send_ics_invite(SqlString profile_name, SqlString recipients, SqlString copy_recipients, SqlString blind_copy_recipients, SqlString from_address, SqlString reply_to, SqlString subject, SqlString body, SqlString body_format, SqlString importance, SqlString sensitivity, SqlString file_attachments, SqlString location, SqlDateTime start_time_utc, SqlDateTime end_time_utc, SqlDateTime timestamp_utc, SqlString method, SqlInt32 sequence, SqlString prod_id, SqlBoolean use_reminder, SqlInt32 reminder_minutes, SqlBoolean require_rsvp, SqlString recipients_role, SqlString copy_recipients_role, SqlString blind_copy_recipients_role, SqlString smtp_servername, SqlInt32 port, SqlBoolean enable_ssl, Sq...

Julien.

[EitanBlumin]

~Interesting... That might be happening due to the installed language of your .NET Framework.~ ~Perhaps you need to use the French variant of "Normal" and "Public"?~

I also notice in your error message that the valid values list is not being outputted properly. I'll try to look into that.

~Meanwhile, try to input the french version of "Public" and "Normal" to the @sensitivity and @importance parameters.~

EDIT: Nevermind, I think it's a bug following the .NET 3.5 adaptation. I'll try to solve it and update here.

[EitanBlumin]

Alright, Julien.

I think I fixed it now.

Please try the updated version:

https://github.com/MadeiraData/sql-clr-ics/blob/v2.3/sql_clr_ics/sql_clr_ics_install.sql

[Julien22bzh]

Eitan,

it works perfectly, Thank you very much.

Julien