Forum Discussion

MLingo's avatar
MLingo
Frequent Visitor
4 months ago
Solved

SQL DB Error with geometry::STGeomFromText

Has anyone else run into an error using geometric functions in SQL Database in Fabric?  Are thses functions not yet available in Fabric SQL DB?  The STGeomFromText (geometry Data Type) article indica...
Need Help
SQL database
  • MLingo's avatar
    MLingo
    4 months ago

    When I created the ticket on Friday, it didn't run in the SQL Database or SQL Analytics Endpoint, but it did work in Fabric Warehouse (I tried that statement in all).  I will close this out as solved now that it is working in SQL Database.

     

     

sukkaur's avatar
sukkaur
Microsoft Employee
4 months ago

MLingo  - I just tried this code in my SQL database in Fabric and it does work. Can you please try again and let me know if you still having the issue? Please do share which region is your tenant in.

thanks

Sukhwant

  • MLingo's avatar
    MLingo
    Frequent Visitor
    4 months ago

    I just tried that line again and it still doesn't work.  My tenant is in East US.

     

    I still get the same error...

    Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

    • sukkaur's avatar
      sukkaur
      Microsoft Employee
      4 months ago

      MLingo - Can you please create a support case? Feel free to drop the support case number here and I will keep an eye on it.

       

      Thanks

      Sukhwant

      • MLingo's avatar
        MLingo
        Frequent Visitor
        4 months ago

        I opened case 2604090010000730.

        Below is a more through comparison of the geometric function in SQL Database, SQL Analytics Endpoint, and Fabric Warehouse (all US East Region).  The script below contains comments and results for statements run in the three different tools... 

         

         

        /******** All Queries Run in US East Region ********/
--Warehouse: Query below runs and returns expected restult
--SQL Database: Query below runs and returns expected restult
--SQL Analytics Endpoint: Query Error - Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
select geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)


--Fabric Warehouse: Query runs and returns expected restult (US East)
--SQL Database: Query runs and returns expected restult (US East)
--SQL Analytits Enpoint: Query runs and retuns expected result (US East)
with cte as (
    select 1 as [LineNo], geometry::STGeomFromText('LINESTRING (100 0, 200 0)', 0) as [Line1_Geom]
    union all select 1 as [LineNo], geometry::STGeomFromText('LINESTRING (150 0, 250 0)', 0) as [Line1_Geom]
    union all select 2 as [LineNo], geometry::STGeomFromText('LINESTRING (300 0, 450 0)', 0) as [Line1_Geom]
    union all select 2 as [LineNo], geometry::STGeomFromText('LINESTRING (3000 0, 6500 0)', 0) as [Line1_Geom]
    union all select 2 as [LineNo], geometry::STGeomFromText('LINESTRING (2000 0, 5000 0)', 0) as [Line1_Geom]
)
select  [LineNo]
        ,geometry::UnionAggregate([Line1_Geom]).STLength()
        ,count(*)
from cte
group by [LineNo]


--Save sample data in WKT format to SQL Datbase and Warehouse
--SQL Database: Save WKT to table
--Warehouse: Save WKT to table
with cte as (
    select  1 as [LineNo], 'LINESTRING (100 0, 200 0)' as [Line1_WKT]
    union all select 1 as [LineNo], 'LINESTRING (150 0, 250 0)' as [Line1_WKT]
    union all select 2 as [LineNo], 'LINESTRING (300 0, 450 0)' as [Line1_WKT]
    union all select 2 as [LineNo], 'LINESTRING (3000 0, 6500 0)' as [Line1_WKT]
    union all select 2 as [LineNo], 'LINESTRING (2000 0, 5000 0)' as [Line1_WKT]
)
select [LineNo], [Line1_WKT]
into dbo.testline1
from cte

--Confirm data in table
select * from dbo.testline1


--Warehouse: Error 8676, Level 16, State 21, Line 1 - Invalid Plan (US East)
--SQL Database: Query runs and returns expected restult (US East)
--SQL Analytics Endpoint: Error 8676, Level 16, State 21, Line 1 - Invalid Plan (US East)
with cte as (
    select  t.[LineNo]
            ,geometry::STGeomFromText(t.[Line1_WKT], 0) as [Line1_Geom]
    from    dbo.testline1 t
)
select  cte.[LineNo]
        ,geometry::UnionAggregate(cte.[Line1_Geom]).STLength()
        ,count(*)
from    cte
group by [LineNo]

         

  • MLingo's avatar
    MLingo
    Frequent Visitor
    4 months ago

    For what it's worth... I'm converting PostgreSQL query to Fabric and I have two ways to solve the problem I'm working on

    1. Use recursive cte (must be SQL DB) using the  geometry::STGeomFromText() funtion which is the error in this thread
    2. My preferred method is to use Fabric Warehouse and the geometry::UnionAggregate() funtion, but it returns "Invalid Plan" for any query except the most basic (meaning WKT Linestring hard coded in CTE).  If I save the same simple data to a table and try to run, I get "Invalid Plan".  My query is more complicated, but I was willing to save to a table to continue processing using the geometry::UnionAggregate().
    • lbendlin's avatar
      lbendlin
      Super User
      4 months ago

      In US West region the query runs fine against the SQL Database but not against its SQL Analytics Endpoint.