locked
32/64 bit problems using spatial queries on Azure RRS feed

  • Question

  • I have a WCF application/service that I host in Windows Azure. I alos am using SQL azure for my database. I have various spatial columns in my database and various stored procedures and text based queries with spatial data passed as WKB with type of VARBINARY that I call from .NET. I also have a few that I pass the parameters directly as GEOGRAPHY type. 

    So I recently upgraded to VS2012 and .NET 4.5, and along with this upgraded my Azure instances to Server 2012. Since then I am getting an error when passing geography type parameters to text based queries from .NET in Azure. Everything works as expected on my dev machine. Please note that I only get the error when the query is text based and the same parameter can be passed to an sp without issue. I will provide some code below to illustrate this. 

    The error I am getting is ''An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)",''

    I understand that this is related to a problem calling a 32 bit dll. I found some kbs that suggest changing my target cpu to x86, but this seems like the wrong thing to do just to get this function to work. I could also write sps for all of my spatial functions, but I am really curious why this is happening with text based queries and not stored procedures at this point. 

    Any insight on this issue is greatly appreciated. Code sample below:

    Both functions include the following:

                Dim param As New SqlClient.SqlParameter
                param.ParameterName = "@geo"
                param.Value = Location
                param.UdtTypeName = "GEOGRAPHY"
                Command.Parameters.Add(param)

    sp based query (works normally):

                    Dim dtMaps As DataTable = FillDT("LPE_IM_GetNearby", CommandType.StoredProcedure)

    text based query (returns incorrect format exception):

                Dim sCMD As String = "SELECT IM_ID, IM_Extent FROM LPE_IndoorMaps WHERE IM_Extent.STIntersects(@geo) = 1"


    • Edited by davenexrf Monday, August 19, 2013 6:59 PM
    Monday, August 19, 2013 6:58 PM

Answers

  • So I was able to get this to work by creating a startup task in my Azure config to install the 64 bit version of SQL Types on the Azure instance upon startup. Here is a quick rundown on how to do this:

    Include SQLSysClrTypes.msi (and SQLServerSpatial.dll while you are at it) in your web service or application project. These should both be v11 to work with .NET 4.5. Set build action to content and copy always for copy to output directory. 

    Create a new script named setupsqlspatial.cmd and include this in your service/app project as well. build action=none and copy always. Put teh following text in this file (do not include my ***):

    ***

    REM Install Sql Sys Clr Types v11

    cd /d "%~dp0"

    start /w msiexec /i SQLSysClrTypes.msi /qn

    ***

    Finally, in your Azure service definition .csdef file, you must create a startup task to run the script. Insert the following in the WebRole node in your .csdef:

        <Startup>
          <Task commandLine="setupsqlspatial.cmd" executionContext="elevated" taskType="simple"></Task>
        </Startup>


    This should allow you to run SQL spatial queries on SQL Azure or any SQL 2012 from .NET 4.5 in Windows Azure. You should even be able to pass Geography parameters directly in and out of queries/procedures. Hopefully this helps someone having the same issues I did. 


    Dave Stewart - NexRF

    • Marked as answer by davenexrf Wednesday, August 21, 2013 5:01 AM
    Wednesday, August 21, 2013 5:00 AM

All replies

  • This is not a SQL Server problem.  You will get better help on the .Net forums.

    The error is a .Net error indicating you have a 32bit app calling an 64bit dll or vise-versa. 

     

    • Proposed as answer by Sofiya Li Wednesday, August 21, 2013 3:19 AM
    • Unproposed as answer by davenexrf Wednesday, August 21, 2013 5:03 AM
    Monday, August 19, 2013 7:30 PM
  • Agreed, but the issue is specific to the Microsoft.SQLServer.Types library, which from what I am reading is only 32 bit. I would expect the .NET group to know a little about the SQL Types library and the SQL crowd to know a lot about this and a little about .NET. I will post in both forums. 

    Dave Stewart - NexRF

    Monday, August 19, 2013 9:04 PM
  • So I was able to get this to work by creating a startup task in my Azure config to install the 64 bit version of SQL Types on the Azure instance upon startup. Here is a quick rundown on how to do this:

    Include SQLSysClrTypes.msi (and SQLServerSpatial.dll while you are at it) in your web service or application project. These should both be v11 to work with .NET 4.5. Set build action to content and copy always for copy to output directory. 

    Create a new script named setupsqlspatial.cmd and include this in your service/app project as well. build action=none and copy always. Put teh following text in this file (do not include my ***):

    ***

    REM Install Sql Sys Clr Types v11

    cd /d "%~dp0"

    start /w msiexec /i SQLSysClrTypes.msi /qn

    ***

    Finally, in your Azure service definition .csdef file, you must create a startup task to run the script. Insert the following in the WebRole node in your .csdef:

        <Startup>
          <Task commandLine="setupsqlspatial.cmd" executionContext="elevated" taskType="simple"></Task>
        </Startup>


    This should allow you to run SQL spatial queries on SQL Azure or any SQL 2012 from .NET 4.5 in Windows Azure. You should even be able to pass Geography parameters directly in and out of queries/procedures. Hopefully this helps someone having the same issues I did. 


    Dave Stewart - NexRF

    • Marked as answer by davenexrf Wednesday, August 21, 2013 5:01 AM
    Wednesday, August 21, 2013 5:00 AM