MS SQL 2005 w/ VS 2008 CLR with Table Value Function (TVF) - malformed assembly error

See more:

SQL-Server-2005

VB

SQL-Server

Okay, starting over. I solved some problems in the other post and this is basically a new issue.

Here's my vb.net (2008) code:

Private Class MapquestResults
     Public DrivingDistance As SqlString
     Public StartZipCode As SqlString
     Public EndZipCode As SqlString

     Public Sub New( _
         ByVal drivingDistance__1 As SqlString, _
         ByVal startZipCode__2 As SqlString, _
         ByVal endZipCode__3 As SqlString _
     )
         DrivingDistance = drivingDistance__1
         StartZipCode = startZipCode__2
         EndZipCode = endZipCode__3
     End Sub
 End Class

 <Microsoft.SqlServer.Server.SqlFunction( _
     DataAccess:=DataAccessKind.Read, _
     FillRowMethodName:="FillRowWithMapquestInfo", _
     TableDefinition:="DrivingDistance nvarchar(50), StartZipCode nvarchar(50), EndZipCode nvarchar(50)")> _
 Public Shared Function GetDistanceAndZipCodesForAddressesFromMapquest( _
     ByVal StartAddress As SqlString, _
     ByVal EndAddress As SqlString, _
     ByVal MapquestKey As SqlString _
 ) As IEnumerable

     'This is called from a scalar function of the same name in SQL after this dll is imported as an assembly in SQL.
     'This function is passed a URL for mapquest's directions api.
     'It goes to the web and gets the XML.
     'Then it grabs info from nodes and passes it back as a string.

     Dim drivingDistance As String = ""
     Dim startZipCode As String = ""
     Dim endZipCode As String = ""
     Dim mapquestErrors As String = ""

     Dim resultCollection As New ArrayList()

     Dim LinkForMapquest As String = _
         "http://www.mapquestapi.com/directions/v2/route?key=" & _
         MapquestKey.ToString.Replace("'", "''") & _
         "&from=" & _
         StartAddress.ToString.Replace("'", "''") & _
         "&to=" & _
         EndAddress.ToString.Replace("'", "''") & _
         "&callback=renderNarrative&outFormat=xml&unit=m"

     Dim xmlDocIn As Xml.XmlDocument = New Xml.XmlDocument

     Dim DirectionsData As String
     Try
         Using wc As New System.Net.WebClient()
             DirectionsData = wc.DownloadString(LinkForMapquest)
         End Using

         xmlDocIn.LoadXml(DirectionsData)

         If xmlDocIn.SelectNodes("/response/route/locations/location").Count <> 2 Then
             'error in the xml
             mapquestErrors = "ERROR: Wrong # of locations."
         Else

             'the distance
             drivingDistance = xmlDocIn.SelectSingleNode("/response/route/distance").InnerText()

             'the first zip code
             startZipCode = xmlDocIn.SelectNodes("/response/route/locations/location").Item(0).SelectSingleNode("postalCode").InnerText

             'the second zip code
             endZipCode = xmlDocIn.SelectNodes("/response/route/locations/location").Item(1).SelectSingleNode("postalCode").InnerText


             'geocode quality: location/geocodeQualityCode

             'error messages

             'response/info/statusCode
             'response/info/messages/message

             'response/routeError/errorCode
             'response/routeError/message

         End If

     Catch xmlEx As XmlException
         mapquestErrors = "ERROR xml: " & xmlEx.Message

     Catch wcEx As System.Net.WebException
         mapquestErrors = "ERROR web: " & wcEx.Message

     Catch ex As Exception
         mapquestErrors = "ERROR other: " & ex.Message

     End Try

     resultCollection.Add(New MapquestResults(drivingDistance, startZipCode, endZipCode))


     Return resultCollection

 End Function

 Public Shared Sub FillRowWithMapquestInfo(ByVal obj As Object, ByRef drivingDistance As SqlString, ByRef startZipCode As SqlString, ByRef endZipCode As SqlString)
     Dim mqRes As MapquestResults = DirectCast(obj, MapquestResults)

     drivingDistance = mqRes.DrivingDistance
     startZipCode = mqRes.StartZipCode
     endZipCode = mqRes.EndZipCode
 End Sub

Here's the sql code to add the function to the database:

CREATE FUNCTION GetDistanceAndZipCodesForAddressesFromMapquest(@StartAddress nvarchar(max), @EndAddress nvarchar(max), @MapquestKey nvarchar(max)) 
RETURNS TABLE (
	DrivingDistance nvarchar(50), 
	StartZipCode nvarchar(50), 
	EndZipCode nvarchar(50)
)  WITH EXECUTE AS CALLER
AS EXTERNAL NAME [UDFs].[UDFs.UserDefinedFunctions].GetDistanceAndZipCodesForAddressesFromMapquest;

The vb.net code compiles fine and creates the dll as it should. There are other functions in the project. I can add the assembly to the database and the other functions to the database as well.

But when I try to add this new table valued one, it fails. I get the following error:

Msg 6507, Level 16, State 2, Procedure ReadEventLog, Line 1
Failed to open malformed assembly 'mscorlib' with HRESULT 0x80070008.

So like my subject says, this is written in vb.net 2008 and the db is SQL 2005. I can create the function on my test 2005 db locally. But when I try to create it on the production server (also sql 2005) I get the error noted above. My sql 2005 is v 9.0.4053 and the production one is v 9.0.3042. I can't imagine that's the problem. For kicks I tried compiling the vb code on the production machine. It's running vs 2005. i recreated the whole vb.net project there and with all the exact same code it compiled into a dll just fine. But i had the same problem when I tried to add it to the database.

I've researched the error and come up with nothing helpful. I know it's not a bit issue (32 vs 64) because I am also running vs 2008 on my 32 bit XP box as oppoed to my main dev box which is W7 64 bit. THe results are identical. And the production server is also 32 bit and as I said I compiled the dll there too.

The only thing I'm left with is the version difference between the 2 sql 2005 installations but wow, that seems like a stretch.

Any help would be very much appreciated. Thanks in advance!