locked
Support for SQL 2008 Spatial data? (Geometry) RRS feed

  • Question

  • Using the Collaboration providers:
    Is there a way to get the Sync Framework to work with SQL 2008 Geometry data type?
    Currently, the first snag I hit is while provisioning the database:

    System.ArgumentException: {"Requested value 'mmserver_tracking.sys.geometry' was not found."}
       at System.Enum.Parse(Type enumType, String value, Boolean ignoreCase)
       at Microsoft.Synchronization.Data.SyncUtil.GetSqlDbTypeFromString(String typeString)
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncProcedureHelper.GetParameterForColumn(DbSyncColumnDescription colDesc)
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncProcedureHelper.AddColumnParametersToCommand(SqlCommand sqlCommand)
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncProcedureHelper.BuildInsertCommand()
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncProcedureHelper.CreateInsertCommandText()
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncProcedureHelper.CreateInsert(SqlTransaction trans)
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncTableProvisioning.Apply(SqlTransaction trans)
       at Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning.Apply(SqlConnection connection)
    Wednesday, February 10, 2010 10:47 AM

Answers

All replies

  • Workaround: Use the Text (WKT, STAsText()) conversion and the implicit Parsing of the geometry type to let MSF think its just passing along strings (nvarchar(max)).

    Given a table such as:
    /****** Object:  Table [dbo].[GeoTest]    Script Date: 02/10/2010 15:10:37 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[GeoTest](
    	[PKey] [int] NOT NULL,
    	[geometry] [geometry] NULL,
     CONSTRAINT [PK_GeoTest] PRIMARY KEY CLUSTERED 
    (
    	[PKey] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    1) When provisioning the database modify the DbSyncColumnDescription in the DbSyncTableDescription containing the geometry data type as such:
        DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable("GeoTest", trackingConnection);
        foreach (var item in table.NonPkColumns)
        {
            if (item.Type.Contains("geometry")) 
            {
                item.ParameterName += "_was_geometry";
                item.Type = "nvarchar";
                item.Size = "max";
                item.SizeSpecified = true;
             }
        }
    
    2) Provision the databases
    SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning();
    if (!prov.ScopeExists(scopeName, trackingConnection))
    {
        DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopeName);
        //add the approrpiate tables to this scope
        DbSyncTableDescription table = SqlSyncDescriptionBuilder.GetDescriptionForTable("GeoTest", trackingConnection);
        foreach (var item in table.NonPkColumns)
        {
            if (item.Type.Contains("geometry")) 
            {
                item.ParameterName += "_was_geometry";
                item.Type = "nvarchar";
                item.Size = "max";
                item.SizeSpecified = true;
            }
        }
    
        scopeDesc.Tables.Add(table);
    
        prov.PopulateFromScopeDescription(scopeDesc);
        prov.SetCreateTableDefault(DbSyncCreationOption.Skip);
        prov.Apply(trackingConnection);
    }
    

    3) I also have the client table created during provisioning, so I had to change the [GeoTest].[geometry] column on the client database to be a 'geometry' data type again.

    4) Modify the selectchanges and selectrow stored procedures to return the geometry column as Text:
    SELECT [side].[PKey], [base].[geometry].STAsText() as geometry, <removed the rest of the statement...>
    Do this on all the databases (server, clients).

    5) Sync!
    • Marked as answer by Rudi - Euricom Wednesday, February 10, 2010 2:58 PM
    • Unmarked as answer by Rudi - Euricom Friday, February 12, 2010 8:25 AM
    Wednesday, February 10, 2010 2:19 PM
  • Apart from the provisioning not working, MSF also suffers from a bug in SqlCommandBuilder.DeriveParameters 
    see http://social.msdn.microsoft.com/Forums/en/sqlspatial/thread/ff02f463-19b2-41da-b19a-e7d47ac7ff9d

    As suggested, setting the UdtTypeName of the parameter solves the problem. This however does mean that you can no longer use the standard SqlSyncProvider class, but you have to resort to the DbSyncProvider and build all the commands and adapters.
    • Marked as answer by Rudi - Euricom Friday, February 12, 2010 8:25 AM
    Thursday, February 11, 2010 4:44 PM
  • I am running into this same issue, but I am not clear on your proposed solution here. I can change the table description as you describe, but then I get an error trying to create the scope on the client side stating that there is an operand type clash between ntext and geometry. Any suggestions or further resources to look at?
    Shane Gilbert
    Tuesday, July 5, 2011 8:35 PM