CLR doesn't work anymore with version 3.. .Help!

  • commandlinekid

    SSC Veteran

    Points: 260

    We have an app running in CLR that schedules our homeless shelter. We need to setup the calendar TODAY and never thought it would have just "stopped working," but apparently with this new "v3," none of the methods we've used for Years will work with our app.

    We have these .dlls imported into MSSQL so we can use them inside of a CLR stored procedure (.vb):

    Google.GData.AccessControl

    Google.GData.Calendar

    Google.GData.Client

    Google.GData.Extensions

    I assume we now need these .dlls:

    Google.Apis.Auth

    Google.Apis.Core

    ...maybe others.

    I need to: Get the .dll's to work with MSSQL 2008R2 which I assume means they need to work with .NET 2.0.

    Does anyone have any experience here? Please help. Thanks.

  • Solomon Rutzky

    SSCoach

    Points: 16256

    commandlinekid (12/11/2014)


    We have an app running in CLR that schedules our homeless shelter. We need to setup the calendar TODAY and never thought it would have just "stopped working," but apparently with this new "v3," none of the methods we've used for Years will work with our app.

    We have these .dlls imported into MSSQL so we can use them inside of a CLR stored procedure (.vb):

    Google.GData.AccessControl

    Google.GData.Calendar

    Google.GData.Client

    Google.GData.Extensions

    I assume we now need these .dlls:

    Google.Apis.Auth

    Google.Apis.Core

    ...maybe others.

    I need to: Get the .dll's to work with MSSQL 2008R2 which I assume means they need to work with .NET 2.0.

    Does anyone have any experience here? Please help. Thanks.

    Hi there. Can you please be a little more specific? "v3" of what? Google something-something? Do you mean that your app, running inside of SQL Server's CLR (i.e. SQLCLR) is / was communicating with Google Calendar? If so, then sounds interesting.

    In general, if you are saying that Google updated their API (or maybe more accurately sunset a prior API--which they do often enough whether anyone likes it or not, for they are the Google), then yes, you have to conform to their new API.

    Regarding the version of .Net: SQL Server 2008 R2 is statically linked to the 2.0 series of the .Net framework, but should be able to use functionality going up through .Net 3.5. HOWEVER, that requires that .Net 3.5 (and likely also .Net 3.0) are installed on the server running SQL Server. And it requires that you might need to load additional framework assemblies that might be referenced by the Google DLLs, and that is not guaranteed to work, even if loaded as UNSAFE. For example, SQL Server only allows pure .Net DLLs (i.e. only MSIL) and not mixed. So, if a Google DLL references a 3.0 framework library that is not already part of the internal CLR, and if that framework library is mixed (even if it was "pure" in .Net 2.0), then I don't think there is any way to load it. But you won't know until you load all of the Google DLLs and then try to call the methods in them that reference .Net framework methods.

    Does this help at all? I hope at least a little.

    Take care,

    Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • commandlinekid

    SSC Veteran

    Points: 260

    Google changed from version 2 of their API to version 3. Not only did they change their .dlls but they changed all the code as far as I'm reading....

    https://developers.google.com/google-apps/calendar/v2/developers_guide_protocol

    I've had this working since around 2010 and it has worked wonderfully using .NET 2.0 and CLR. I loaded the Google .dlls into MSSQL, then referenced them from a CLR project inside of Visual Studio 2010....Which is CLR because MSSQL needs to invoke it whenever someone changes their schedule (via a table in MSSQL).

    So today, I can't load in the .dll's, it gives an error that they were made with a "different version of the framework" or something like that.

    Either I'm missing something or Google is thumbing their nose at .NET developers. This is for a damn not for profit and it's going to take a Major rework. Likely I'll have to build a web service that the existing CLR procedure talks to in IIS and that web service will have the .dll's loaded. Then that web service can talk to Google. BUT even then there appears to be no documentation of substance for this new "Version 3" of the Calendar API.

  • Solomon Rutzky

    SSCoach

    Points: 16256

    Yes, I completely understand that it is very frustrating when a 3rd party API changes. I have gone through some of that pain with Twitter over the years, and worked at a place that integrated with Google for AdWords and they seemed to change that API yearly, which caused us to lose a month or two per year for the past 3 years. Unfortunately it just comes with the territory when integrating with a 3rd party :(.

    Now, regarding this:

    So today, I can't load in the .dll's, it gives an error that they were made with a "different version of the framework"

    That really doesn't sound like anything Google could have a role in. Google is a 3rd party that you connect to via the internet. Their DLLs that you currently have loaded might stop communicating with Google due to them sun-setting that version of the API, but they have no control over your local CLR host. So the question is: what changed today (or yesterday)? Was there a Windows Update done on the server that would have updated the .Net Framework libraries? That and/or a reboot of the server itself? Is there an exact error message that you can paste into here? And what are the results (should be 4 lines) from this query:

    SELECT * FROM sys.dm_clr_properties;

    I did find this, which might help: https://developers.google.com/google-apps/calendar/migration

    Take care,

    Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • commandlinekid

    SSC Veteran

    Points: 260

    This doesn't really help. Their .dll's aren't loading because they were built with a newer version of the framework it says. If you know how to do that let me know. And their migration guide doesn't tell me Anything about actual application, it's like they built the .NET piece just for fun because they had to. I see no decent documentation or discussions anywhere. I just need to get it back up again

  • Solomon Rutzky

    SSCoach

    Points: 16256

    commandlinekid (12/12/2014)


    Their .dll's aren't loading because they were built with a newer version of the framework it says.

    Right. And what I am saying is that those DLLs haven't changed. You loaded them into SQL Server in 2010 and haven't changed them. Right? That is why I asked about any updates to the .Net Framework on that server, any reboots to that server, and the results of that query. Without that info there is nothing I can do to help. Google sun-setting an API would not give you a DLL error.

    Take care,

    Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • commandlinekid

    SSC Veteran

    Points: 260

    Different DLL's. Google made new dll's. They changed the entire naming structure even from gData to Google.apis.whatever

  • Solomon Rutzky

    SSCoach

    Points: 16256

    Ok. I think I misread an earlier statement. I did find this:

    https://www.nuget.org/packages/Google.Apis.Calendar.v3/

    And yes, it does state that it only supports ".NET Framework 4 and 4.5", which is not going to work on any version of SQL Server prior to 2012.

    But, that doesn't mean you will need to do the large project with the additional IIS server that you mentioned. They do offer a REST API:

    https://developers.google.com/google-apps/calendar/v3/reference/

    It seems like your best bet is to start using HttpWebRequest to interact with the REST API.

    Another option, just to put it out there, is that you can load the new Google Client DLLs in SQL Server 2014 Express, which is free. You could set up an instance of Express Edition to use for nothing other than this Google Client Library. And if you set up Linked Servers between your current 2008 R2 instance and the 2014 Express instance, you might not have to even change much of the T-SQL code outside of referencing the Linked Server.

    Take care,

    Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • commandlinekid

    SSC Veteran

    Points: 260

    These are all things I mentioned for the most part. The .NET documentation from Google Sucks, it's really non-existent. So I'm not spending a week on this. My question remains. Thanks for your help.

  • Solomon Rutzky

    SSCoach

    Points: 16256

    Then I don't know what to say. The new DLLs, being for .Net 4, will not work in SQL Server 2008 R2.

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • commandlinekid

    SSC Veteran

    Points: 260

    Thanks for trying

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply