SQLServer Spatial: The order of inserted properties counts

[danstoica]

This is about a SqlServer2008 bug. Say we have this insert statement:

INSERT INTO dbo.TEST_GEOM("ID", "GEOM", "STR1”) VALUES (?, Geometry::STGeomFromText(?, 0), ?);

SQLPrepare() will succeed but describe columns (SQLDescribeParam) will fail with “[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index” for each column in the statement.

What the provider's ODBC driver is doing in such case is to default the column type to CHAR, length=100. This is exactly what we are experiencing: all the strings get padded with spaces up to 101 chars.

Why is it failing? Apparently the geometry column has to be bound as the last column in the insert statement. This statement will succeed:

INSERT INTO dbo.TEST_GEOM("ID", "STR1”, "GEOM",) VALUES (?, ?, Geometry::STGeomFromText(?, 0));

Until MS is fixing the bug a workaround is feasable: in the provider just place the geometry properties at the end of the insert string.