Connection problem SQL Server 2008 and FDO

[LFELLI]

Hi to all,
I have just installed Windows server 2008, SQL Server 2008 Express, MapGuide Open Source 2.0.2 and FDO 3.3.2
To configure the SQL Server 2008 i followed : http://trac.osgeo.org/fdo/wiki/FdoSQLServerSpatialNotes
I downloaded SqlSpatialTools. With this tool i've uploaded a .shp file on the Sql Server with Shape2Sql and previewed correctly with SqlSpatial tool included.
When i try to connect  SQL Server using MapGuide Maestro 1.0.9 (OSGeo FDO Provider for SQL Server Spatial) i'm able to create the feature source, but when i try to create a layer based on that feature source i receive the following error:
 "Failed to read schema from data source.
The operation gave the error message: Bad document."

This is all my test:
I created a db called "navteq" with just one table called "dbo.isole"

-I change the table using only a column of type int (without any geometry or geography type data) but nothing changed.

-I downloaded and installed FDO Toolbox. The connection is ok, but when i expand the treeview i receive the following error:

Exception occurred: OSGeo.FDO.Common.Exception: Unable to find class 'S' at OSGeo.FDO.Commands.Schema.IDescribeSchemaImp.Execute() at ....
The connection has a strange behavior, in fact if i collapse and then reexpand the node of the table the error is slightly different each time, for example:
"Exception occurred: OSGeo.FDO.Common.Exception: Unable to find class 'sole' at ...
I attached a picture with another slightly different error.


Using DescribeSchema of mapagent (http://localhost:8008/mapguide/mapagent) this is the empty result:
<?xml version="1.0" encoding="UTF-8" ?>
  <fdo:DataStore xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:gml="http://www.opengis.net/gml"
xmlns:fdo="http://fdo.osgeo.org/schemas" xmlns:fds="http://fdo.osgeo.org/schemas/fds" />


During installation progress of SqlServer 2008 Express i set "Mixed Mode" for the "Authentication Mode".
I use "sa" user with the correct password for the connection, but i've also tried to connect with windows authentication mode but the result is always the same.

Am i missing something in the SQL Server configuration ?
Does FDO needs a particular configuration for SQL Server 2008 ?

P.S.I've also tried with FDO 3.4.0 and 3.4.1 with no result.

Any hints any will be appreciated.

Lorenzo

[Jackie Ng]

Hi Lorenzo,

If you're using the latest version of FDO Toolbox, try checking the OSGeo.SqlServerSpatial provider under the "Providers" tab in the Preferences, and see if you get the same problem.

If you don't get the same problem after checking that provider, you are probably experiencing this problem: http://trac.osgeo.org/fdo/ticket/552, which affects the ODBC Provider and I'm guessing, any provider under the GenericRdbms framework (MySQL, SQL Server)

- Jackie

LFELLI wrote:

Hi to all,
I have just installed Windows server 2008, SQL Server 2008 Express, MapGuide Open Source 2.0.2 and FDO 3.3.2
To configure the SQL Server 2008 i followed : http://trac.osgeo.org/fdo/wiki/FdoSQLServerSpatialNotes
I downloaded SqlSpatialTools. With this tool i've uploaded a .shp file on the Sql Server with Shape2Sql and previewed correctly with SqlSpatial tool included.
When i try to connect  SQL Server using MapGuide Maestro 1.0.9 (OSGeo FDO Provider for SQL Server Spatial) i'm able to create the feature source, but when i try to create a layer based on that feature source i receive the following error:
 "Failed to read schema from data source.
The operation gave the error message: Bad document."

This is all my test:
I created a db called "navteq" with just one table called "dbo.isole"

-I change the table using only a column of type int (without any geometry or geography type data) but nothing changed.

-I downloaded and installed FDO Toolbox. The connection is ok, but when i expand the treeview i receive the following error:

Exception occurred: OSGeo.FDO.Common.Exception: Unable to find class 'S' at OSGeo.FDO.Commands.Schema.IDescribeSchemaImp.Execute() at ....
The connection has a strange behavior, in fact if i collapse and then reexpand the node of the table the error is slightly different each time, for example:
"Exception occurred: OSGeo.FDO.Common.Exception: Unable to find class 'sole' at ...
I attached a picture with another slightly different error.


Using DescribeSchema of mapagent (http://localhost:8008/mapguide/mapagent) this is the empty result:
<?xml version="1.0" encoding="UTF-8" ?>
  <fdo:DataStore xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:gml="http://www.opengis.net/gml"
xmlns:fdo="http://fdo.osgeo.org/schemas" xmlns:fds="http://fdo.osgeo.org/schemas/fds" />


During installation progress of SqlServer 2008 Express i set "Mixed Mode" for the "Authentication Mode".
I use "sa" user with the correct password for the connection, but i've also tried to connect with windows authentication mode but the result is always the same.

Am i missing something in the SQL Server configuration ?
Does FDO needs a particular configuration for SQL Server 2008 ?

P.S.I've also tried with FDO 3.4.0 and 3.4.1 with no result.

Any hints any will be appreciated.

Lorenzo

[LFELLI]

Hi Jackie,
you r right!
Checking the OSGeo.SqlServerSpatial provider under the "Providers" tab in the Preferences there are no more errors.
But how i can bypass the problem in Maestro?
Sql Server 2008 is based on GenericRdbms framework so this means that no one now can use Sql Server 2008 with MGOS???

Lorenzo

[psciga]

Hi Lorenzo,

I have the same problem. I have done the following in MapGuide Maestro :

created a new layer
selected the sqlServer FeatureSource
ignored the error message (pushed the OK-button)
saved the layer
edited the layer in XML-Editor (competed the "<FeatureName>dbo:geom</FeatureName>")
saved the changes

This runs and I can use the layer in a map. It's not convenient but it runs.

A messge if the pbroblem is solved would be nice.

Regards - Peter

sorry for bad english

LFELLI wrote:

.....
But how i can bypass the problem in Maestro?
Sql Server 2008 is based on GenericRdbms framework so this means that no one now can use Sql Server 2008 with MGOS???

....

[LFELLI]

Hi Peter,
thx for the tip, but unfortunatly doesn't solve my problem.
My table is called "Streets" so i write
<FeatureName>dbo:Streets</FeatureName>. I added the layer to the map but i still have no layer on the client side.
Just for a test, in the map editor, i push the "Set view to current layers extent" using the Sql Server layer and i receive the following error message: "Failed to get layer extent: No spatial data found in layer"
I notice that no Geometry is set in the XML, is this correct?
May i ask you to post your layer configuration in XML?
Opening the WebGis client, i'm able to see the other layer loaded with FDO SHP but no evidence for the sql one.
No error reports on Error.log file.
All my SHP layers has SRID 4326. I loaded the Streets layer in SqlServer using SqlSpatialTools from www.sharpgis.net using SRID 4326.

Lorenzo

psciga wrote:

Hi Lorenzo,

I have the same problem. I have done the following in MapGuide Maestro :

created a new layer
selected the sqlServer FeatureSource
ignored the error message (pushed the OK-button)
saved the layer
edited the layer in XML-Editor (competed the "<FeatureName>dbo:geom</FeatureName>")
saved the changes

This runs and I can use the layer in a map. It's not convenient but it runs.

A messge if the pbroblem is solved would be nice.

Regards - Peter

sorry for bad english

LFELLI wrote:

.....
But how i can bypass the problem in Maestro?
Sql Server 2008 is based on GenericRdbms framework so this means that no one now can use Sql Server 2008 with MGOS???

....

[psciga]

Hi Lorenzo,

here the Layer definition:
#### begin ###
<LayerDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:noNamespaceSchemaLocation="LayerDefinition-1.1.0.xsd" version="1.1.0">
  <VectorLayerDefinition>
    <ResourceId>Library://sqlservertest2/db1.FeatureSource</ResourceId>
    <FeatureName>dbo:geom</FeatureName>
    <FeatureNameType>FeatureClass</FeatureNameType>
    <Filter />
    <PropertyMapping>
      <Name>id</Name>
      <Value>id</Value>
    </PropertyMapping>
    <PropertyMapping>
      <Name>name</Name>
      <Value>name</Value>
    </PropertyMapping>
    <Geometry>graphik</Geometry>
    <Url />
    <ToolTip />
    <VectorScaleRange>
      <PointTypeStyle>
        <DisplayAsText>false</DisplayAsText>
        <AllowOverpost>false</AllowOverpost>
        <PointRule>
          <LegendLabel />
          <Label>
            <Unit>Points</Unit>
            <SizeContext>DeviceUnits</SizeContext>
            <SizeX>10</SizeX>
            <SizeY>10</SizeY>
            <Rotation>0</Rotation>
            <MaintainAspect>true</MaintainAspect>
            <Text>name</Text>
            <FontName>Arial</FontName>
            <ForegroundColor>000000</ForegroundColor>
            <BackgroundColor>ffffff</BackgroundColor>
            <BackgroundStyle>Transparent</BackgroundStyle>
            <HorizontalAlignment>'Center'</HorizontalAlignment>
            <VerticalAlignment>'Baseline'</VerticalAlignment>
            <Bold>false</Bold>
            <Italic>false</Italic>
            <Underlined>false</Underlined>
          </Label>
          <PointSymbolization2D>
            <Mark>
              <Unit>Points</Unit>
              <SizeContext>DeviceUnits</SizeContext>
              <SizeX>10</SizeX>
              <SizeY>10</SizeY>
              <Rotation>0</Rotation>
              <MaintainAspect>true</MaintainAspect>
              <InsertionPointX>0</InsertionPointX>
              <InsertionPointY>0</InsertionPointY>
              <Shape>Circle</Shape>
              <Fill>
                <FillPattern>Solid</FillPattern>
                <ForegroundColor>ff000000</ForegroundColor>
                <BackgroundColor>ffffffff</BackgroundColor>
              </Fill>
            </Mark>
          </PointSymbolization2D>
        </PointRule>
      </PointTypeStyle>
      <LineTypeStyle>
        <LineRule>
          <LegendLabel />
          <LineSymbolization2D>
            <LineStyle>Solid</LineStyle>
            <Thickness>1</Thickness>
            <Color>000000</Color>
            <Unit>Points</Unit>
            <SizeContext>DeviceUnits</SizeContext>
          </LineSymbolization2D>
        </LineRule>
      </LineTypeStyle>
    </VectorScaleRange>
  </VectorLayerDefinition>
</LayerDefinition>

#### end ###

I'm only working with GEOMETRY type in MSSQL.

My table "geom" have 3 columns:

ID (primary key, autoincrment)
name (nchar)
graphik (geometry, with index)

Have you set a primary key column?

In the database I use mixed mode for login. From MG maestro I'm using Specified credentials to make the connection to mssql server.

Sorry, I din't tell you, that I'm using MGOS 2.1 beta with PHP on XP.

With MGOS 2.0.2 (and the beta provider) I got an other problem: If there are more the 7323 lines (I have only tested line objects) in the database, it was impossible to make a selection. So I was trying 2.1.

LFELLI wrote:

Hi Peter,
thx for the tip, but unfortunatly doesn't solve my problem.
My table is called "Streets" so i write
<FeatureName>dbo:Streets</FeatureName>. I added the layer to the map but i still have no layer on the client side.
Just for a test, in the map editor, i push the "Set view to current layers extent" using the Sql Server layer and i receive the following error message: "Failed to get layer extent: No spatial data found in layer"
I notice that no Geometry is set in the XML, is this correct?
May i ask you to post your layer configuration in XML?
Opening the WebGis client, i'm able to see the other layer loaded with FDO SHP but no evidence for the sql one.
No error reports on Error.log file.
All my SHP layers has SRID 4326. I loaded the Streets layer in SqlServer using SqlSpatialTools from www.sharpgis.net using SRID 4326.

Lorenzo

[LFELLI]

Hi Peter,
thank you very much man! Now i can see the layer.
I've the selection problem too with only 891 record so i think it should depend by some other factors (number of columns and number of record...)
i'll try the beta of MGOS.
Anyway... THX!

psciga wrote:

Hi Lorenzo,

here the Layer definition:
#### begin ###
<LayerDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:noNamespaceSchemaLocation="LayerDefinition-1.1.0.xsd" version="1.1.0">
  <VectorLayerDefinition>
    <ResourceId>Library://sqlservertest2/db1.FeatureSource</ResourceId>
    <FeatureName>dbo:geom</FeatureName>
    <FeatureNameType>FeatureClass</FeatureNameType>
    <Filter />
    <PropertyMapping>
      <Name>id</Name>
      <Value>id</Value>
    </PropertyMapping>
    <PropertyMapping>
      <Name>name</Name>
      <Value>name</Value>
    </PropertyMapping>
    <Geometry>graphik</Geometry>
    <Url />
    <ToolTip />
    <VectorScaleRange>
      <PointTypeStyle>
        <DisplayAsText>false</DisplayAsText>
        <AllowOverpost>false</AllowOverpost>
        <PointRule>
          <LegendLabel />
          <Label>
            <Unit>Points</Unit>
            <SizeContext>DeviceUnits</SizeContext>
            <SizeX>10</SizeX>
            <SizeY>10</SizeY>
            <Rotation>0</Rotation>
            <MaintainAspect>true</MaintainAspect>
            <Text>name</Text>
            <FontName>Arial</FontName>
            <ForegroundColor>000000</ForegroundColor>
            <BackgroundColor>ffffff</BackgroundColor>
            <BackgroundStyle>Transparent</BackgroundStyle>
            <HorizontalAlignment>'Center'</HorizontalAlignment>
            <VerticalAlignment>'Baseline'</VerticalAlignment>
            <Bold>false</Bold>
            <Italic>false</Italic>
            <Underlined>false</Underlined>
          </Label>
          <PointSymbolization2D>
            <Mark>
              <Unit>Points</Unit>
              <SizeContext>DeviceUnits</SizeContext>
              <SizeX>10</SizeX>
              <SizeY>10</SizeY>
              <Rotation>0</Rotation>
              <MaintainAspect>true</MaintainAspect>
              <InsertionPointX>0</InsertionPointX>
              <InsertionPointY>0</InsertionPointY>
              <Shape>Circle</Shape>
              <Fill>
                <FillPattern>Solid</FillPattern>
                <ForegroundColor>ff000000</ForegroundColor>
                <BackgroundColor>ffffffff</BackgroundColor>
              </Fill>
            </Mark>
          </PointSymbolization2D>
        </PointRule>
      </PointTypeStyle>
      <LineTypeStyle>
        <LineRule>
          <LegendLabel />
          <LineSymbolization2D>
            <LineStyle>Solid</LineStyle>
            <Thickness>1</Thickness>
            <Color>000000</Color>
            <Unit>Points</Unit>
            <SizeContext>DeviceUnits</SizeContext>
          </LineSymbolization2D>
        </LineRule>
      </LineTypeStyle>
    </VectorScaleRange>
  </VectorLayerDefinition>
</LayerDefinition>

#### end ###

I'm only working with GEOMETRY type in MSSQL.

My table "geom" have 3 columns:

ID (primary key, autoincrment)
name (nchar)
graphik (geometry, with index)

Have you set a primary key column?

In the database I use mixed mode for login. From MG maestro I'm using Specified credentials to make the connection to mssql server.

Sorry, I din't tell you, that I'm using MGOS 2.1 beta with PHP on XP.

With MGOS 2.0.2 (and the beta provider) I got an other problem: If there are more the 7323 lines (I have only tested line objects) in the database, it was impossible to make a selection. So I was trying 2.1.

LFELLI wrote:

Hi Peter,
thx for the tip, but unfortunatly doesn't solve my problem.
My table is called "Streets" so i write
<FeatureName>dbo:Streets</FeatureName>. I added the layer to the map but i still have no layer on the client side.
Just for a test, in the map editor, i push the "Set view to current layers extent" using the Sql Server layer and i receive the following error message: "Failed to get layer extent: No spatial data found in layer"
I notice that no Geometry is set in the XML, is this correct?
May i ask you to post your layer configuration in XML?
Opening the WebGis client, i'm able to see the other layer loaded with FDO SHP but no evidence for the sql one.
No error reports on Error.log file.
All my SHP layers has SRID 4326. I loaded the Streets layer in SqlServer using SqlSpatialTools from www.sharpgis.net using SRID 4326.

Lorenzo