The SQL Server 2005 Integration Assembly adds two User-Defined Types and several geospatial functions to your database. The assembly allows you to make geospatial
calculations directly inside the database and thus lighten the load on your webserver
when displaying geographically defined data. Setting up the assembly is simple and
can be done in just a few steps.
But before you jump into it, just make sure that you know what you are doing. While
there is nothing difficult or damaging in the assembly and it can be uninstalled
without leaving any trace, I just want to ensure that you know what you are doing,
or at least that you are playing with non-live data so you don't inadvertently damage
anything important. Also remember to back up any relevant data.
When you download the assembly you will find two SQL script files included in the
download. One to help you install the assembly in your database and one to uninstall
it should you wish to do this.
Even though the assembly and functions will install without error you will need
to ensure that your database server actually allows running CLR assemblies before
you can use any of the types or functions. By default CLR support is disabled in
SQL Server 2005. Turning it on can be done by running this script:
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
go
Now your database is ready to work with CLR assemblies and you can run the install
script. If you have the SQL Server Management Studio (Express) then this is the
easiest place to do it. You can load the script directly in the management studio
and run it with the click of a button.
If you can't do this or don't have access to management studio or some other application
(from your web host) to run queries you can of course do it with a good old fashioned
SqlCommand. This is one of the places where using VB.NET is not fun. As the script
uses line breaks it is best run using C# verbatim string literals. In this way the
query can be set directly. I haven't tried doing it in other ways than these two,
so if you are using VB.NET and cannot use SQL Server Management Studio (Express)
then you will have to find some other way. The C# way to run this query is like
any other:
string query = @"USE myDatabase
GO
All the rest of the script
blah blah";
SqlConnection conn = new SqlConnection("Your connection string");
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
As you notice from the above I start the script with 'USE myDatabase GO'. It is
a good idea to add this line to the top of the script to make sure that you install
it in the right database (note that assemblies are not loaded for the entire server).
After the script has run the database will correctly display the User Defined Types
SqlLatLng and SqlBounds as possible data types when you design your tables in that
database.
Licenseholders can download the assembly from
here.