com.microsoft.sqlserver.jdbc.SQLServerException wrong query string on wms GetMap request

A problem with a plugin cannot possibly be a blocker

solution for this issue is using older driver from geotools
this gt-jdbc-sqlserver-2.6.0.jar driver work fine

I have had this same issue, it comes down the to geotools sql server code.

Rolling back to gt-jdbc-sqlserver-2.6.0.jar solves the issue with gt-jdbc-sqlserver.2.6.4 not picking up and passing the srid through to the query on sqlserver, as it doesn't use the spatial filter at all.

As this is technically a geotools issue, should I be posting up a similar ticket as above over to there? I have had a look through the jira for geotools and nothing like this is mentioned.

sorry for long wait, this is a table script with Spatial data

i fund relation with sqljdbc.jar
if we using sqljdbc.jar (version 3) this problem does not appear but if we using sqljdbc4.jar (version 4 ) this problem affects.
so, i suppose, that problem is in sql 2008 Microsoft driver that not similar generate sql queries in different driver version and it depend on witch jre is using (1.5 or 1.6) because sqljdbc4.jar working only on jre 1.6 and higher.

I tried to reproduce this with the information on this issue and could not. I was able to add the data, publish with geoserver and render the map. I did however have to use an old sqljdbc driver (1.2) due to the issue on another ticket (GEOT-3270).

@Lazarev: The test data seemed a bit strange. It does not match the featuretype.xml you sent me. Can you send me a featuretype.xml with some sql that matches it? Also the sql that you sent has an srid of 1? Which causes error.

Also you could you include the exact wms request that you are sending to the server which results in the error.

Hopefully with that i can replicate.

the correct data and featuretype a will send today, i found relation with jdbc driver. all work perfectly with version 3 but with version 4 there are troubles. so version 4 work only with java 1.6 and higher. if we using 1.5 we can use version 3. Maybe internal query creation in driver produce different sql in different versions.

there are new featureType that is correct and still doesn't want to work.

there are correct data for this feature type

I still can;t reproduce this problem. When I add your data it renders fine via WMS. I tried a few different configurations, both java 5 and 6 and it all works. Can you provide the exact WMS request that fails?

For my case, for some reason some of my tables in SQL Server 2008 don't seem to come through with an SRID in the NATIVE SRID assignment when creating the layer in Geoserver. I can enter my own SRID for the Declared Srid, but when I go to demo (display) the layer, I am getting the error outlined in this action. This makes sense to me, no SRID picked up, no SRID transferred through into the selection query on the backend. I have tried creating test datasets in different schemas and I can't seem to find what exactly is causing the issues with some of my tables. I have attached the SQL for one table that doesn't pick up the native srid.

You can check the srids, but they should be 4283 (using select geom.STSrid from temp.plantationInsp)

Notes on my environment
Clean Geoserver 2.0.2 running from within tomcat 6.0.20 (running JRE6u21) on windows server 2003 (32 bit)
Using Geoserver-2.0.2-sqlserver-plugin (gt-jdbc-sqlserver-2.6.4.jar)
Using SQL Server JDBC Driver 2.0 (sqljdbc4.jar)

OK, I paid more attention to the sql server logs this time, and have worked out what is happening.

When creating a new layer from within geoserver the following call is being run on SQL Server to get the srid of the layer.

SELECT "geom".STSrid FROM "theTable" WHERE "geom" IS NOT NULL

The issue here is that no schema is being supplied into this call, so nothing is returned, so the NATIVE SRID value is not being picked up by geoserver. I have got around this temporarily by creating a view in the dbo schema (not pretty, but it works).

great!! we found resolution! it is not hard to fix it

Great work James! Thanks for tracking this one down. So indeed this was an issue but has recently been fixed as per GEOT-3265. If you upgrade to a recent nightly build hopefully the issue will have done away.

So i am going to close this one as fixed. Thanks for all the help tracking this one down guys.

Fixed as per GEOT-3265

Just tried the nightly gt-jdbc-sqlserver-2.6.snapshot extension, works a treat.

Mass closing all issues that have been in the resolved state for the last month without anyone commenting or reopening them