0

I've published from Visual Studio the same CLR database function to both the Staging and Live db instances, they both live on the same MS SQL Server which is version 11.0.2100.60.

They both install fine however when I run a same function on the Live, I get the following error message:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "RankWords": 
System.IO.FileLoadException: Could not load file or assembly 'System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) ---> System.IO.FileLoadException: The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
System.IO.FileLoadException: 

System.IO.FileLoadException: 
   at net.johnhenry.lib.search.SearchAndRankingHelper.RankWords(String original_text, String words_being_searched, Int32 min_search_words_length, Boolean is_default_behaviour_and, Boolean is_partial_match)
   at SqlClrExtension.UserDefinedFunctions.RankWords(String original_text, String words_being_searched, Int32 min_search_words_length, Boolean is_default_behaviour_and).

Any advice?

Giuseppe Romagnuolo
  • 3,362
  • 2
  • 30
  • 38
  • "The located assembly's manifest definition does not match the assembly reference." sounds like an interface problem. Did you try to just override the old assembly with the new one, without a proper import? – LuigiEdlCarno Jun 17 '13 at 07:11
  • @LuigiEdlCarno I have used Visual Studio 2010 Publish option (right click the CLR Project/Publish), the result was 'Publish completed successfully'. I have republished on top of the Staging db too for sanity check and it still works there. Is this how you import the code? – Giuseppe Romagnuolo Jun 17 '13 at 07:29
  • As far as I remember you still had to import the routine in the database server. You are generating a dll, aren't you? – LuigiEdlCarno Jun 17 '13 at 07:40
  • @LuigiEdlCarno those are in the database, the publish executes a script like: ALTER ASSEMBLY [MyCLRDAddition] DROP FILE ALL ADD FILE FROM 0x4D6963726F736F667420432F432B2B204D534620372E30300D0A1A44530... – Giuseppe Romagnuolo Jun 17 '13 at 07:53
  • Ah, ok. It was just a thought. From my experience an assembly is only imported once per server. You still have to create a stored procedure and/or function in each db that uses that assembly's custom method. Make sure, that those are the same in both databases. – LuigiEdlCarno Jun 17 '13 at 07:54
  • Good thought however I did a bit of research, actually not, on SQL 2012 it is not imported once per server but once per database instance, in fact the binary is stored in sys.assembly_files. I've compared the binary of the live and staging and they match. – Giuseppe Romagnuolo Jun 17 '13 at 08:17

1 Answers1

0

I eventually managed to solve the problem after having spent the entire day searching the most esoteric sites on the topic! :)

I kept republishing on top of the existing Assembly, just tweaking little bits of the settings but when doing this Visual Studio does not generate the entire SQL script to install the CLR code like if a fresh install but only the SQL script for what VS thinks it has changed.

The solution has been to drop the assembly and all its dependencies and republish afresh!

Visual Studio's Publish function will generate the entire SQL script needed to install the CLR code which resolved what I suspect was a badly registered library.

.

Giuseppe Romagnuolo
  • 3,362
  • 2
  • 30
  • 38