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

Improve this question
5
  • 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. Commented Nov 5, 2015 at 16:38
  • Are you using HTTP or XMPP? I assume that your SQL Server is the "app server"? Commented Nov 5, 2015 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 Commented Nov 6, 2015 at 10:07
  • Hey there. Have you had a chance to try out my suggestion of manually creating the JSON? Commented Nov 18, 2015 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. :) Commented Nov 19, 2015 at 7:55

1 Answer 1

Reset to default
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:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.