Using ROW_NUMBER() in SQL Server update statement

 No Responses »
Sep 182014
 

You can’t use ROW_NUMBER() in an update statement in SQL Server, so:

UPDATE TheTable
SET    TheColumn = ROW_NUMBER();

won’t work. But sometimes that’s just what you want. This will do the trick:

UPDATE  Target
SET     TheColumn = RowNum
FROM
(
    SELECT  t.TheColumn, ROW_NUMBER() OVER(ORDER BY t.ID) AS RowNum
    FROM    TheTable t
	WHERE    ...
) AS Target;

GeoServer on Linux to serve SQLServer data

 No Responses »
Aug 022013
 

I never knew that there was Linux support for SQLServer, but it turns out that there is a JDBC 4.0 driver for it that can be used in GeoServer.

From the download, put sqljdbc4.jar in the tomcat/lib directory if you only want JNDI connections, or in the GEOSERVER/WEB-INF/lib directory if you want connections that can be defined in GeoServer as well.
Download the sqlserver plugin for GeoServer, and put the jar file (in my case gt-jdbc-sqlserver-9.3.jar) into the GEOSERVER/WEB-INF/lib directory.

Now restart Tomcat (or just the GeoServer webapp if you don’t want JNDI connections). Now create a new store. Be sure to open up port 1433 on your Windows firewall, or otherwise GeoServer can’t reach it.