How do I solve "Unable to load DLL 'SqlServerSpatial.dll' The specified module could not be found"

I am developing an application that uses the SqlGeography and SqlGeometry data types from the SQL CLR datatypes installer. My application is a web application in VB.NET, and most of the functionality is in a class library. Both projects have a reference to Microsoft.SqlServer.Types.dll, which is not set to copy local.

The application works perfectly on my development machine where I also have a developer version of SQL Server 2008 installed. However when I deploy to my web server (a Windows Server 2008 R2 box over which I have full control), although the application runs, when I try to access a web service that uses the SQL Server spatial stuff, I hit this error:

Unable to load DLL 'SqlServerSpatial.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticDistance(GeoMarshalData g1, GeoMarshalData g2, EllipsoidParameters ellipsoidParameters, Double& result)
   at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticDistance(GeoData g1, GeoData g2, EllipsoidParameters ellipsoidParameters)
   at Microsoft.SqlServer.Types.SqlGeography.STDistance(SqlGeography other)
   at MyPlaceLib.MapFeatureManager.GetMapFeatures(List`1 layerNames, String q, LatLng origin, LatLngBounds bounds, Decimal radius, Int64 limit, MapFeatureSortOrder sortOrder, SortDirection sortDirection, Boolean singleResult, Boolean includeMapLayerDefinitions, Boolean allowReturnAllFeatures, Int64 allLayersLimit) in D:\Users\wwarby\Visual Studio 2010\Projects\MyPlace.NET\MyPlaceLib\Classes\MapFeatureManager.vb:line 184
   at MyPlaceLib.HttpWebServices.GetMapFeatures.ProcessRequest(HttpContext context) in D:\Users\wwarby\Visual Studio 2010\Projects\MyPlace.NET\MyPlaceLib\Classes\HttpWebServices\GetMapFeatures.vb:line 43

Open in new window


I've got the SQL Server CLR types installed on the web server - Microsoft.SqlServer.Types.dll is in the same place on Program Files on the server as on my dev machine. SqlServerSpatial.dll (not directly referenced anywhere in my code) is in system32 on the server, albeit a different version than on my development machine. I've tried copying both dlls to the bin directory of my app to no avail. and I've tried installing various versions of the Visual C++ runtime.

Help!
LVL 1
wwarbyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jerry MillerCommented:
The error message references these two locations that appear to be on your local drive. These two lines may be a good place to start debugging.

D:\Users\wwarby\Visual Studio 2010\Projects\MyPlace.NET\MyPlaceLib\Classes\MapFeatureManager.vb:line 184
D:\Users\wwarby\Visual Studio 2010\Projects\MyPlace.NET\MyPlaceLib\Classes\HttpWebServices\GetMapFeatures.vb:line 43
0
wwarbyAuthor Commented:
jmiller1979, apologies, I should have mentioned that that's a red herring - gives the line numbers in my source code because I've published the debug symbols file (the .pdb file) to the server. All that's telling me is the line in my source code at which the first call to STDistance occurs. Problem is, when I step through the code on my own computer, the error isn't thrown.
0
wwarbyAuthor Commented:
I have now produced an extremely simple test case to reproduce this problem.  I build a windows forms application with one button, which when pressed executes the attached code. On my dev machine I see a message box showing me a distance. On my server I see this:

System.DllNotFoundException: Unable to load DLL 'SqlServerSpatial.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
   at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticDistance(GeoMarshalData g1, GeoMarshalData g2, EllipsoidParameters ellipsoidParameters, Double& result)
   at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticDistance(GeoData g1, GeoData g2, EllipsoidParameters ellipsoidParameters)
   at Microsoft.SqlServer.Types.SqlGeography.STDistance(SqlGeography other)
   at WindowsApplication1.Form1.Button1_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Open in new window

Dim test1 As Microsoft.SqlServer.Types.SqlGeography = Microsoft.SqlServer.Types.SqlGeography.Point(51.62937, -0.00094, 4326)
Dim test2 As Microsoft.SqlServer.Types.SqlGeography = Microsoft.SqlServer.Types.SqlGeography.Point(51.633376, 0.0107, 4326)
MsgBox(test1.STDistance(test2).ToString)

Open in new window

0
wwarbyAuthor Commented:
I have now solved this problem. It turned out I had installed the 64 bit version of the SQL Server CLR Types installer on the server (because it is a 64 bit server) but I actually needed to install the 32 bit version. Once I did that, it worked fine.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wwarbyAuthor Commented:
Solved the problem through experimentation. Thanks to the expert who offered a solution even though it wasn't the right one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.