none
CLR Problem with system.web.dll RRS feed

  • Question

  • I get this problem about once per year when MSFT upgrades something. I have a fix, but it's not working now for some reason.

    The setup:

    I have a CLR stored procedure written in VB, that needs to authenticate to twitter. To do that it needs system.web.dll. Now, I'm running MSSQL 2005 server so as I understand it I need to have the system.web.dll loaded for .NET 2.0 (Even though I have .net 4.x on my server).

    So when it breaks once per year or so...I go in, drop the .dll, and CREATE ASSEMBLY to reference system.web.dll from the .net 2.x folder.

    I just tried it today: And it all works, except I get an error when executing the CLR SP...

    A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_checktwitter": 
    System.IO.FileLoadException: Could not load file or assembly 'System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)
    System.IO.FileLoadException: 
      at oAuthExample.oAuthTwitter.oAuthWebRequest(Method method, String url, String postData, String consumer_token, String consumer_tokensecret)
      at oAuthExample.StoredProcedures.clr_futz_twittercheckcredentials(String Token, String TokenSecret)

    Please advise.

    What do I do???

    Thanks.

    Thursday, April 12, 2012 2:36 PM

Answers

  • (agreed, let's not get into the 'legal' discussion)

    Let me double-check few things:

    1. You are able to reproduce your problem on more than 1 machine.
    2. You did not do any unsupported modifications with Windows, .NET Framework or SQL (or any other product used).
    3. You are able to reproduce the problem within a new empty database with a small CLR store procedure.
    4. The 3 links I pasted above did not help at all.

    Can you please confirm these are true? (To avoid chasing unrelated issues)

    Based on you report, your CLR store procedure worked on earlier version of CLR (which one was it? - KB number, or FileVersion of mscorwks.dll and System.Data.dll will help) and stopped working after installing some CLR patch (which one was it? - again KB number/FileVersions will help).

    I will be more than happy to help you or at least route your question to the right audience/forum,
    -Karel

    Friday, April 13, 2012 1:58 AM
    Moderator

All replies

  • Please be more specific about the steps you do:

    1. "drop the .dll" - which DLL? Where do you drop it from? Where do you drop it to?
    2. CREATE ASSEMBLY - What is this command? SQL command?

    -Karel

    Thursday, April 12, 2012 4:47 PM
    Moderator
  • Karel,

    Thanks for writing.

    1.) I copy the system.web.dll from the old .net 2.0 directory into whereever I want, rename it, and create assembly to add it, something like this

    CREATE ASSEMBLY [system.web] from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\system.web.dll'
        WITH PERMISSION_SET = UNSAFE 

    This has worked for years,  and every time .Net gets updated and the .dll gets overwritten, I have to do it again. The problem is : I'm using MSSQL 2005 so it doesn't appear to support a newer system.web.dll to be used with CLR.

    Keep in mind that it does still "work" regarding the create assembly part...the problem (and error) is in the first message above when I try to run a CLR procedure Using the dll.

    Please advise.


    • Edited by Joe B NY Thursday, April 12, 2012 6:20 PM
    Thursday, April 12, 2012 6:09 PM
  • If you copy System.We.dll file from .NET 2.0 installation somewhere else, you are violating .NET EULA and you are in totally unsupported area.
    If it accidentally worked before, you were "lucky". If it breaks now, it is exactly what it should do and why it is unsupported scenario.

    What you're doing is similar as if I took 1 DLL from your application and used it somewhere else without its dependencies and then complained it stopped working (because you changed something internal in new version). I don't think you would be happy about what I did and you probably wouldn't want to support my weird use case.

    Also I do not understand why the fact that MSSQL 2005 supports only .NET 2.0 would force you to copy .NET 2.0 version of System.Web.dll around. What am I missing? Did you mean you tried to copy 4.0 version of System.Web.dll around? Or are you trying to run MSSQL 2005 on .NET 4.0?

    -Karel

    Thursday, April 12, 2012 7:47 PM
    Moderator
  • I didn't realize I was "violating" something, but thanks for putting it that way. 

    Anyway, here's what I need to do. If you can tell me how to do it that would be great:

    1.) Run a CLR procedure Inside MSSQL 2005.

    2.) Use system.web.dll from Whichever version of .net you want me to use. YES I would prefer 4.x but it doesn't work with CLR on MSSQL 2005 for some reason....or at least not with the config I have.

    The Reason I need system.web.dll is because I need to hit Twitter, which needs to use it for some reason.

    Am I missing a hotfix or service pack or something?

    And Yes the machine itself is running .net 4.x 

    Thanks.

    Thursday, April 12, 2012 8:01 PM
  • MSSQL 2005 supports .NET 2.0 (per quick internet search I did). Do you say that it does not allow to load System.Web.dll?

    If it does not, did you search why it is not supported? (Maybe there are good reasons why SQL prevents that, or maybe it is just configuration problem)

    -Karel

    Thursday, April 12, 2012 8:12 PM
    Moderator
  • That's my entire point.

    I needed to somehow Include the system.web.dll From .net 2.0 Into MSSQL 2005, which is why I did the "create assembly" thing.

    This has been functioning for like 2-3 years and I'm pretty sure I'm also using it in another database that connects to Google's APIs. For some reason it doesn't work here.

    Bottom line: Someone please tell me how to do this Properly...and I would be glad to implement it as such.

    Thursday, April 12, 2012 8:15 PM
  • If I remove the systemweb (the name I called the .net 2.0 system.web.dll) file from the VS project, I can't even compile it. I get the error:

    Error 2 The type or namespace name 'UI' does not exist in the namespace 'System.Web' (are you missing an assembly reference?)  

    ...So I need to get it from somewhere and if MSSQL won't let me use the .net 4.0 .dll....what do I use? And how?

    Thursday, April 12, 2012 8:16 PM
  • Joe,

    I don't know "how to do this end-to-end goal properly" ... you might want to ask on SQL forums (I am not familiar with CREATE ASSEMBLY, nor SQL specific usage/support for System.Web.dll - one of the links above says it is not tested and there is some kind of warning from SQL, you might want to learn more about it).

    The exception you see says: "Assembly in host store has a different signature than assembly in GAC". That makes me believe that your copy of System.Web.dll is stale. I think the easiest way to fix that is to not make any copy of the DLL (the more because it is not legal). Can you use the DLL directly from .NET installation directory or from GAC?

    -Karel

    Thursday, April 12, 2012 8:30 PM
    Moderator
  • I get concerned when you keep saying it's not "legal" to copy a .DLL from one directory to another; That's all I've done. You should be careful when making such accusations of a mysterious "illegality". I will not get into such a discussion.

    Just in case it is somehow illegal (which I really doubt but you have me scared now...thanks for that time waste!) to copy a .dll from one directory to another, I have a solution: I will simply reference the file from its current directory instead of copying it.

    Thanks for your help.

    If anyone has a solution to this please post it.

    Again, all I'm trying to do here: Is Run a CLR that needs to access system.web.dll On MSSQL 2005. It barfs with any version other than 2.0...but now with 2.0 I'm getting an error.

    Much appreciated. Thank you.




    • Edited by Joe B NY Friday, April 13, 2012 12:44 AM
    Friday, April 13, 2012 12:41 AM
  • (agreed, let's not get into the 'legal' discussion)

    Let me double-check few things:

    1. You are able to reproduce your problem on more than 1 machine.
    2. You did not do any unsupported modifications with Windows, .NET Framework or SQL (or any other product used).
    3. You are able to reproduce the problem within a new empty database with a small CLR store procedure.
    4. The 3 links I pasted above did not help at all.

    Can you please confirm these are true? (To avoid chasing unrelated issues)

    Based on you report, your CLR store procedure worked on earlier version of CLR (which one was it? - KB number, or FileVersion of mscorwks.dll and System.Data.dll will help) and stopped working after installing some CLR patch (which one was it? - again KB number/FileVersions will help).

    I will be more than happy to help you or at least route your question to the right audience/forum,
    -Karel

    Friday, April 13, 2012 1:58 AM
    Moderator