Tuesday, May 25, 2010

MSDB,Data Collector

Recently i had decided to enable the Data Collection feature in sql server 2008. This is a very neat feature to capture some important statistics about the database and queries. When you enable this feature it also creates SSIS packages. One day when we were cleaning up all the unwanted SSIS packages, we decided not to use the Data Collection feature hence removed all the SSIS packages. I encountered a problem while removing the SSIS packages, go some errors pertaining to Foreign Key constraints. This lead me to deleting the syscollection tables in the msdb database, this was a wrong thing to do i realised later. These system tables were deleted. Later i had to stop and start the dev server, once the dev server restarted i started getting errors while trying to expand the Management folder in SSMS. I realised that there were views in the msdb database which were referencing the system tables. This was kind of a mjor problem since msdb houses lot of important information related jobs,SSIS packages,policies. Then based on the article here http://feodorgeorgiev.com/blog/2010/03/how-to-rebuild-msdb/
i located the install msdb script, i used this to recreate all the data collector system tables, once this done my errors with SSMS went away. The take away was to completly understand all the related objects which are created for the new feature (here data collector) and plan the disabling of the feature in a organized way.

Thursday, May 13, 2010

Map Reports...

I have been working on report builder 3.0 and trying to understand and build out map based reports. Usually i have using the sahpe file option or use the USA maps given in the map wizard to build out sample reports. One of the other option available is to build a spatial query. This is done by running a query against a table that has spatial data types defined, in my case I had a table with a location column and another geographytype column, this column had the LINESTRING between 2 points. When i built out the query
DECLARE @GeoLocation GEOGRAPHY


SET @GeoLocation = GEOGRAPHY::STPointFromText('POINT(-80.7941 35.3189)',4326)
SELECT NAME,Distance,Location
FROM
(select Name,(Bank.STDistance(@GeoLocation)/1000.00)* 0.62 AS Distance,Location
from venue) AS a
WHERE Distance <= 20
and executed i got back the results. In the results i had data like LINESTRING(-80.88 35.0228, -80.7941 35.3189), since there was LINESTRING data, the report builder prompted me 2 different kind of line maps. The first one was like a regular line based map, the other one was to display analytical data. To make these maps more meaningful, it would be good to add a tile layer using Bing maps. This way the line maps would make more sense visually. Here is the image without the bing maps background:

Monday, May 3, 2010

Geocoding...

Recently started to work on Geocoding project where in there is going to be use of report builder 3.0 mapping report features. To begin with what does geocoding mean: as per wikipedia(http://www.wikipedia.com/) definition:Geocoding is the process of finding associated geographic coordinates (often expressed as latitude and longitude) from other geographic data, such as street addresses, or zip codes (postal codes).  The idea here we have a list of addresses and pass it to a geocoding software, the one being used is alteryx,http://www.alteryx.com/pages/default.aspx. The software in turn produces a list of geocoded locations, these can then transferred to a spreadsheet or the out put can be directly routed to a spatial database, in this context is to use the spatial data types in sql server and receive the data, This portion is still being tested out. The issues is whether the geocoded locations can be stored as a geometry or geography data type or store the latitude,longitude as an integer. Once the geocoded locations are stored in a sql server table, this can be used for reporting purposes using report builder 3.0/SSRS.