Embedding Map Data in Report Builder 3

OK now we have some data (see the 2 previous articles) we can now add a map using this spatial data to a report.

OK First open up report builder 3, you can download it from here

Open it and start a new project and select the "Map Wizard" option.

On the source screen select "SQL Server spatial query"

Next add a datasource with a connection to a table / view or Stored Proc that contains some spatial data.

Select the fields you want to view, these will almost certainly be labels under the points on the map, or in some cases bubble sizes on a map.

On the next screen the spatial data will be plotted, at this point you want to add a bing map overlay so select the "Add a bing layer" option and then watch the map appear along with your plotted data!!!

The next couple of screens are just layout screens for the map, you can then go ahead and add additional information to your now more professional report.

Converting Long,Lat to SQL Server Spatial Data

In my last post I pulled across the Postcode database for the UK, I wanted to display some information using this data on the New Map functionality of Report Builder 3, although unfortunately it requires Spatial data for this to work. Having looked around I came across the information, the GEOGRAPHY datatype in SQL Server looks to allow you to store point information in a single column of SQL Server, and there are a few functions for allowing you to convert longitudinal and latitudinal values into this new data type... So without further ado...

Using the previous table we created we need to expand it to create a new column

ALTER TABLE Postcodes ADD [GeoLocation] GEOGRAPHY

next we need to convert the longitude and latitude into point information

UPDATE Postcodes SET GeoLocation = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20))
+ ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

As you can see we're using the inbuilt STPointFromText function, this requires a point reference and a SRID reference (something to do with initial map referencing, which for our case if 4326), you can view the function in more detail here.

Additional function you can use are geography::STGeomFromText Same as above by the looks of it, geography::Point again similar but without the text modification or geography::Parse again same as the rest but with a SRID (assumes 4326 anyway by the looks of it)

My next post will probably be about placing this stuff on a map in Report Builder 3

UK Postcodes database

Managed to find a CSV on the Internet with all the details needed for the UK Postcodes (download it here). Decided to import it into a database, you can use the script below I made.... of course you could just use the import that comes with SQL.. but where's the fun in that?

CREATE TABLE Postcodes ([Postcode] VARCHAR(10), [IntDate] DATE, [GREast] VARCHAR(5), [GRNorth] VARCHAR(5), [County] INT, [District] VARCHAR(2), [Ward] VARCHAR(2), [UserType] BIT, [GridStatus] SMALLINT, [Country] SMALLINT, [WardStatus] SMALLINT, [NHSCode] VARCHAR(3), [NHSRegion] VARCHAR(3), [Longitude] DECIMAL(9,6), [Latitude] DECIMAL(9,6), [OSRef] VARCHAR(15), [Updated] DATE)
CREATE TABLE #Postcodes ([Postcode] VARCHAR(10), [IntDate] VARCHAR(10), [GREast] VARCHAR(255), [GRNorth] VARCHAR(255), [County] VARCHAR(255), [District] VARCHAR(255), [Ward] VARCHAR(255), [UserType] VARCHAR(255), [GridStatus] VARCHAR(255), [Country] VARCHAR(255), [WardStatus] VARCHAR(255), [NHSCode] VARCHAR(255), [NHSRegion] VARCHAR(255), [Longitude] varcHar(255), [Latitude] varchar(255), [OSRef] VARCHAR(17), [Updated] VARCHAR(255))
DECLARE @SQL VARCHAR(255)
SET @SQL = 'BULK INSERT #Postcodes FROM "c:\uk-post-codes-2009.csv" WITH (FIELDTERMINATOR= '','', ROWTERMINATOR='''+CHAR(10)+''', FIRSTROW=2)'
EXEC(@SQL)

INSERT INTO Postcodes ([Postcode], [IntDate], [GREast], [GRNorth], County, District, Ward, UserType, GridStatus, Country, WardStatus, NHSCode, NHSRegion, Longitude, Latitude, OSRef, Updated)
SELECT Replace([PostCode],'"',''), LEFT(REPLACE([IntDate],'"',''),6)+'01', REPLACE([GREast],'"',''), REPLACE([GRNorth],'"',''), REPLACE([County],'"',''), REPLACE([District],'"',''), REPLACE([Ward],'"',''), REPLACE([UserType],'"',''), REPLACE([GridStatus],'"',''), REPLACE([Country],'"',''), REPLACE([WardStatus],'"',''), REPLACE([NHSCode],'"',''), REPLACE([NHSRegion],'"',''), [Longitude], [Latitude], REPLACE([OSRef],'"',''), REPLACE([Updated],'"','') FROM #Postcodes
DROP TABLE #Postcodes

The reason I used EXEC on the BULK INSERT Command is because the file has Char(10) as a line terminator, and I couldn't seem to specify it as a ROWTERMINATOR option without stuffing it into a string.

After the script has finished you should have 1,841,117 rows, you'll need to add your own indexes.

Creating Random Names in SQL Server

Came across a problem when I needed a load of random names in an SQL Database (for an upcoming project on using maps in SSRS.. which I'll post up later). Looked around but couldn't find anything on the net so I decided to make some up. I Manged to get a list of forenames and surnames from the internet into a text file and decided to merge them up in SQL. For this to work you'll need to download the boys names, girls names and surnames then run the following SQL.

-- Lets Create some People.. a thousand will do
DECLARE @Counter BIGINT
SET @Counter = 1000

-- Create a table to put this in 
CREATE TABLE [Patients] ([Forename] VARCHAR(50), [SurName] VARCHAR(50), Gender BIT)
-- Sort out the Forenames we'll be using for the data, we make a #Name2 table because I have yet to figure our
-- inserting specific columns using BULK INSERT and without using a format file.
CREATE TABLE #Name (Name VARCHAR(50))
CREATE TABLE #ForeNames (FirstName VARCHAR(50), Gender BIT)
-- Move data in the #Name2 table
BULK INSERT #Name FROM "c:\Data\girlsforenames.txt" WITH (ROWTERMINATOR='\n')
-- Now move it to the forename table and add the gender
INSERT INTO #ForeNames SELECT [Name], 1 FROM #Name
-- Delete the names from temporary table
TRUNCATE TABLE #Name
-- Same for the boys
BULK INSERT #Name FROM "c:\Data\boysforenames.txt" WITH (ROWTERMINATOR='\n')
INSERT INTO #ForeNames SELECT [Name], 0 FROM #Name
-- Now do the surnames
TRUNCATE TABLE #Name
BULK INSERT #Name FROM "c:\Data\surnames.txt" WITH (ROWTERMINATOR='\n')

-- Now create these people, we'll be using the Names from above to create a random set of xxxx amount
SET @Counter = 0 
WHILE (@Counter > 0)
BEGIN
 INSERT INTO [Patients] ([Forename],[Surname],[Gender])
    SELECT TOP 1 #ForeNames.FirstName, (SELECT TOP 1 Name FROM #Name ORDER BY NEWID()), #ForeNames.Gender FROM #ForeNames ORDER BY NEWID()    
 SET @Counter=@Counter-1
END

DROP TABLE #Name
DROP TABLE #ForeNames

SELECT * FROM [Patients]

You can see for reading the files into the table I've used BULK INSERT which you can look up here since I find it a rather cool function.

Checking SQL Server Drive Space

Eak… Drivespace was down to 1MB on a customer’s server today (this was due to them deciding that changing passwords and not informing use or changing services settings was a good thing). Well I dialed in and trimmed log files, cleaned up unused file from the OS and such. In the office I have checks daily to make sure my databases are in tiptop condition but we usually leave the maintenance of the customer’s databases to themselves (we have other 400 of them so expecting a daily check is a little time consuming). This can lead to the odd occasion where the drive fills up or a backup script stop running and I’ve decided to do something about it, I created a web service on the sites with checked the most recent backups (see my backup scripts below) and also checks the disk space, I have then made a consuming website which connects to all the servers (eventually) and relays the space and last backup time, I can then from there take action if needed. Simple but very effective, I’ve posted the code below for how I record the drive space for any of you who which to know.

--Create the Database
CREATE DATABASE DBADatabase
GO
-- Switch to the new database for the rest of our setup
USE DBADatabase
GO
-- Create the table we’ll be using to hold the details on the drive space
CREATE TABLE dbo.DriveSpace (
 [ID] uniqueidentifier NOT NULL ROWGUIDCOL,
 [ServerName] varchar(50) NOT NULL,
 [Date] datetime NOT NULL,
 [Drive] char(1) NOT NULL,
 [SpaceMB] int NOT NULL )  ON [PRIMARY]
GO
-- add some indexes and such
ALTER TABLE dbo.DriveSpace ADD CONSTRAINT DF_DriveSpace_ID DEFAULT (newid()) FOR [ID]
GO

ALTER TABLE dbo.DriveSpace ADD CONSTRAINT PK_DriveSpace PRIMARY KEY CLUSTERED ([ID]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_DriveSpace ON dbo.DriveSpace ([Date]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE dbo.DriveSpace SET (LOCK_ESCALATION = TABLE)
GO

That’s all we need to store it.. bug how to fill it? Well there is a function that returns the information of the drives of which the server is located called xp_fixeddrives, try it for yourselves and see the two columns returned (Drive and MB free). Anywho we need to set up a stored procedure that will record the details so that you can later set up a job using the GUI or sp_add_job to execute the stored proc daily.

USE DBADatabase
GO
CREATE PROCEDURE dba_UpdateDriveInfo AS
BEGIN
 -- Temporary Table for our results
 DECLARE @DriveData AS TABLE ([Drive] CHAR(1), [MB Free] INT) 
 -- Get results
 INSERT INTO @DriveData EXEC xp_fixeddrives
 -- Dump them in our table
 INSERT INTO dbo.DriveSpace ([ServerName], [Date], [Drive], [SpaceMB])
  SELECT DB_NAME(), GETDATE(), [DD].[Drive], [DD].[MB Free] FROM @DriveData AS [DD]
END

Again all you need to do is set up a job to run this daily and if you wanted to maybe send a mail when you lower than a specific amount? Good Luck

Moving the TempDB

Got some very sexy Intel SSD drives in today for a new server, moved some databases across but also thought I'd move the temp table with it to increase the performance to the max!  Usually with databases I'd detach and then reattach the database but it's a little different for the tempDB, you need to issue the ALTER DATABASE command and tell it to issue a new location for the database.  Once you've done this though you need to restart the SQL Server instance for the changes to take effect.  The temp database has specific names for it's logical files namely tempdev and templog, so I've listed the command used below for you, enjoy speed freaks.

ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, FILENAME='e:\system\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME=templog, FILENAME='f:\system\templog.ldf')
GO

P.S. Don't forget to restart the instance

Stored procedure parameter caching in VB.NET

One of the annoyances of Stored Procedures is having to pass the parameters and know the parameter datatypes, it's a bit of a pain and one I addressed when I started to use .NET all those years ago. I was cleaning up some code and noticed this little nugget and thought I'd share it with you since most (if not all examples) on the net show parameters being declared and passed through programmatically.

To overcome this annoyance I decided to retrieve the parameter definitions direct from the SQL Server using the DeriveParameters command which will retrieve the SPs parameter definition so you can use these when passing the values through instead of declaring them yourself. This has the distinct advantage of making sure that the most up to date definitions are available to you program and also allows you to tweak the stored proc without having to change the code in your VB app. The only issue is that it causes additional calls to the SQL Server before each stored procedure call!!!  To help get around this the parameter definitions are cached in a collection after the first call so we only need that additional call to be made once for each stored proc. So here you go :

Private Sub LoadParams(ByRef dbCommand As SqlClient.SqlCommand)
   Dim strProcName As String = dbCommand.CommandText
   Dim dbparameter As Collection = Nothing
   Dim parameter As SqlClient.SqlParameter = Nothing
   Static dicParams As Collection

   ' If this is the first time in then initialise the holding collection.
   If (dicParams Is Nothing) Then
      dicParams = New Collection
   End If

   ' Check to see if the stored procedure has been called before, if so then retrieve the cached parameter list
   If (dicParams.Contains(strProcName)) Then
      dbparameter = dicParams(strProcName)
      If (Not dbparameter Is Nothing) Then
         ' Go through the parameters we have stored and add them to the command parameter list.
         For Each parameter In dbparameter
            dbCommand.Parameters.Add(CType(parameter, ICloneable).Clone)
         Next
      End If
   Else
      ' OK this is a never seen before stored procedure, get the current parameter declarations from the SQL Server
      SqlClient.SqlCommandBuilder.DeriveParameters(dbCommand)
      ' Now that we have the list of paramteres we can record them
      For Each parameter In dbCommand.Parameters
         ' Create a new parameter collection, we do this in the loop since some commands come back with no parameters
         If (dbparameter Is Nothing) Then
            dbparameter = New Collection
         End If
         ' Create a copy of the parameter information by cloning it, we have to force the clone method here.
         dbparameter.Add(CType(parameter, ICloneable).Clone)
      Next
      ' Record these paramteres and stored procedure name
      dicParams.Add(dbparameter, strProcName)
   End If
End Sub

Having pasted the code here I can see I have no errorhanding, that's because it's handled in the calling procedure which I'll also post so you can see how to use it, this function opens a stored procedure with the given parameters and then returns a dataset.

'///////////////////////////////////////////////////////////////////////////////

' Open the dataset a stored procedure
Friend Function OpenDataSetFromSP(ByVal StoredProcName As String, ByRef ReturnValue As Object, ByVal ParamArray Args() As Object) As DataSet
   Dim dbData As SqlClient.SqlDataAdapter
   Dim dbCommand As SqlClient.SqlCommand
   Dim parameter As SqlClient.SqlParameter
   Dim lngCounter As Long
   Dim ds As DataSet

   OpenDataSetFromSP = Nothing
   Try
      ' Create the command for this stored procedure
      dbCommand = New SqlClient.SqlCommand(StoredProcName, SQLDatabase)
      dbCommand.CommandType = CommandType.StoredProcedure
      ' Load the parameters definitions for this stored procedure
      LoadParams(dbCommand)
      lngCounter = 0
      If (dbCommand.Parameters.Count > 0) Then
         For Each parameter In dbCommand.Parameters
            ' Ignore the return paramter if the stored procedure has one
            If (Not parameter.Direction = ParameterDirection.ReturnValue) Then
               ' Make sure that we don't push in more params than the stored procedure has
               If (Args.Length >= lngCounter) Then
                  parameter.Value = Args(lngCounter)
               End If
               lngCounter = lngCounter + 1
            End If
         Next
      End If

      ' Execute what we need
      dbData = New SqlClient.SqlDataAdapter(dbCommand)
      ds = New DataSet
      ' Shove the data into a dataSet
      dbData.Fill(ds)
      OpenDataSetFromSP = ds

      ' Retrieve the Return parameter?
      For Each parameter In dbCommand.Parameters
         If (parameter.Direction = ParameterDirection.ReturnValue) Then
            ReturnValue = parameter.Value
         End If
      Next

     ' Exit
   Catch ex As Exception
      DB.GenerateError("OpenDataSetFromSP : " + StoredProcName, ex)
      OpenDataSetFromSP = Nothing
   End Try

   ' Clean up
   dbCommand = Nothing
   dbData = Nothing
End Function

You'll need to make sure you have the SQLConnection object named as SQLDatabase and also you'll need to sort out a different errorhandler, but the classes will hopefully save you a lot of time and heartache.

Forwarding Mail in Exchange Server 2007 / 2010

This should be pretty elementary to anyone who has used exchange and active directory before, but I got asked this morning so I thought I'd put it up.


Adding A Contact To Exchange 2007

1. Add a contact to Active Directory (E.G Tagtronics Support)
2. Open up exchange management console
3. Under "Recipient Configuration" you will see "Mail Contact" click on mail contact and you will see the mail contact wizard.
4. Under "Actions" click on "New Mail Contact"
5. Check the box "Existing Contact" and click on browse, select the contact you wish to add as a exchange forwarding address
6. Click on next and enter a valid SMTP address (E.G. me@myblog.com)
7. Click on next and then click Finnish

Configuring Mail Forwarding Exchange 2007

1. Under "Recipient Configuration" you will see "Mailbox"
2. Double click on the mailbox you would like to configure forwarding for
3. Click on the "Mail Flow Settings" tab and double click on "Delivery Options"
4. Tick Box "Forward To" and "Deliver Messages To Both Forwarding Address And Mailbox"
5. Click Browse and select the contact you added in section 1.
6. Click "OK", "Apply" and "OK" again.

Your email forwarding has now been set