1

I've to send Google Client Messaging (GCM) Notifications from SQL Server when a row is inserted into a specific table.

I created a Service Broker to manage queue and so on my table, I use a trigger on insert operations to add a "work" to my queue.

I want my Service Broker to call a CLR procedure to send GCM Notification and I created it in Visual Studio.

My problem is that when I try to create an assembly in SQL Server I get many errors and it seems that many other framework dlls are needed (for example System.Runtime...etc).

Can anyone tell me why ? Any suggestions?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Badozvora
  • 383
  • 1
  • 3
  • 15
  • SQL CLR only has a handful of assemblies referenced already. You'll have to add all the assemblies that this assembly relies on in order for SQL CLR to accept the assembly. – willaien Nov 05 '15 at 16:38
  • Are you using HTTP or XMPP? I assume that your SQL Server is the "app server"? – Solomon Rutzky Nov 05 '15 at 16:59
  • I use HTTP ans SqlServer is the server called by the app. I need to send GCM notification from server to the app – Badozvora Nov 06 '15 at 10:07
  • Hey there. Have you had a chance to try out my suggestion of manually creating the JSON? – Solomon Rutzky Nov 18 '15 at 15:25
  • Hi, I solved it creating a project using the "Sql Server Database Project" template which includes only assemblies accepted for SqlServer. In my project I'd to serialize and deserialize objects but I couldn't include serialization classes and so I created my custom Serializer and Deserializer classes. In that way, Sql Server now accepts my dlls. :) – Badozvora Nov 19 '15 at 07:55

1 Answers1

1

SQLCLR is a very restricted / limited environment. The MSDN page for Supported .NET Framework Libraries lists the DLLs that are guaranteed to work. If your project is referencing a DLL that is not in that list, then you will have to attempt loading it manually. This, however, is not guaranteed to work, or if it does work, it is not guaranteed to continue working across updates of the .NET Framework or SQL Server. For a lot more detail on the nuances of working within SQL Server's CLR host, please see the following article that I wrote (free registration is required for that site):

Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server)

Now, it seems that you are trying to use Google Clould (not Client) Messaging: Overview. From what I can see in that documentation, the easiest and most stable means of communicating to a GCM connection server is to use the HTTP protocol, construct the JSON manually (this should be pretty easy to do), and call the GCM server using HttpWebRequest.

The reason I am suggesting to construct the JSON "by hand" is because DataContractJsonSerializer exists within System.Runtime.Serialization.dll and that is not one of the supported .NET Framework libraries (as previously mentioned). Since you are already using HTTP, you should just need to remove the .NET JSON serialization and instead use StringBuilder to construct it. Don't forget to remove the reference to System.Runtime.Serialization from your project.

I have some additional info in these answers:

Community
  • 1
  • 1
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171