Cross Apply Data Source for SSRS R2 2008

I was working on SSRS 2008 R2 migration and needed a list of data sources to join to the ReportServer.dbo.Catalog table. Since a single report can have several datasources I needed a way to rollup the datasource Names to a single ItemID. I have used CTE's in the past but wanted to give the Cross Apply a go. Cross Applym, considerably easier than a CTE.

Cross Apply Data Source Name for SSRS:
SELECT DISTINCT
ds.ItemID ,
Name = LEFT(dsl.list, LEN(dsl.list) - 1)
FROM
dbo.DataSource AS ds
CROSS APPLY ( SELECT
ds2.Name + ',' AS [text()]
FROM
dbo.DataSource AS ds2
WHERE
ds.ItemID = ds2.ItemID
ORDER BY
ds2.ItemID
FOR
XML PATH('')
) dsl ( list )

WHERE
ds.ItemID IS NOT NULL
AND LEN(ds.Name) > 0

Ref Link:

Spatial Convert Latitude/Longitude (Lat/Long) to Geography Point

Convert Latitude/Longitude (Lat/Long) to Geography Point

Prior to SQL Server 2008, geography locations such as bank branches, restaurant locations and household locations, are usually stored as latitude and longitude coordinates in two separate columns. With the introduction of spatial data types in SQL Server 2008, particularly the GEOGRAPHY data type, this can now be stored as points in a single column stored as a spatial data object. This article will show the different ways of converting the latitude and longitude coordinates of geography locations into a geography POINT.

The GEOGRAPHY data type supports seven spatial data objects that you can create and work with, namely Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon and GeometryCollection. From these spatial data objects, we will be using the Point.



Let's say we have a table of famous international geographic landmarks where we store its latitude and longitude coordinates and we want to take advantage of SQL Server 2008's new geography data type. (Note: The latitude and longitude coordinates of these landmarks were taken from Google maps and not from Microsoft's Virtual Earth (or Bing Maps).)

CREATE TABLE [dbo].[Landmark] (
[ID] INT IDENTITY(1, 1),
[LandmarkName] VARCHAR(100),
[Location] VARCHAR(50),
[Latitude] FLOAT,
[Longitude] FLOAT
)
GO

INSERT INTO [dbo].[Landmark] ( [LandmarkName], [Location], [Latitude], [Longitude] )
VALUES ( 'Statue of Liberty', 'New York, USA', 40.689168,-74.044563 ),
( 'Eiffel Tower', 'Paris, France', 48.858454, 2.294694),
( 'Leaning Tower of Pisa', 'Pisa, Italy', 43.72294, 10.396604 ),
( 'Great Pyramids of Giza', 'Cairo, Egypt', 29.978989, 31.134632 ),
( 'Sydney Opera House', 'Syndey, Australia', -33.856651, 151.214967 ),
( 'Taj Mahal', 'Agra, India', 27.175047, 78.042042 ),
( 'Colosseum', 'Rome, Italy', 41.890178, 12.492378 )
GOUsing geography::STPointFromText

The first thing to do is to add the new column where we will store the geography point. This can easily be done by the ALTER TABLE statement and we will use the GEOGRAPHY data type for the new column.

ALTER TABLE [dbo].[Landmark]
ADD [GeoLocation] GEOGRAPHY
GO
To convert the latitude and longitude coordinates to a geography point, we will use the StPointFromText function. The STPointFromText returns a geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
GOAs you can see from this UPDATE statement, the longitude is specified first before the latitude. The reason is because in the Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation, the format is (x, y). Geographic coordinates are usually specified by Lat/Long but between these two, the X is the Longitude while the Y is the Latitude.

You may be wondering why the X-coordinate is the Longitude while the Y-coordinate is the Latitude. Think of the equator of the earth as the x-axis while the prime meridian is the Y-axis. Longitude is defined as the distance from the prime meridian along the x-axis (or the equator). Similarly, latitude is defined as the distance from the equator along the Y-axis.

If the latitude and longitude were accidentally interchanged in the parameter for the STPointFromText, you may get the following error message particularly for the Sydney Opera House landmark record:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
Using geography::STGeomFromText

Another way of creating a geography POINT is with the use of the STGeomFromText OGC static method. Just like the STPointFromText OGC static method, the STGeomFromText returns a geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::STGeomFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')', 4326)
GOUnlike the STPointFromText method, which only accepts a POINT, the STGeomFromText can accept any of the seven spatial data objects (POINT, POLYGON, LINESTRING, MULTIPOINT, MULTIPOLYGON, MULTILINESTRING and GEOMETRYCOLLECTION). If you try to pass any other spatial data object to the STPointFromText aside from a POINT, you will get the following error message:

Msg 6522, Level 16, State 1, Line 3
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24142: Expected "POINT" at position 1. The input has "LINES".
Using geography::Point

Yet another way of creating a geography POINT spatial data object is with the use of the geography::Point extended static geography method. The geography::Point method constructs a geography instance representing a POINT instance from the latitude and longitude values.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::Point([Latitude], [Longitude], 4326)
GO
Unlike the STGeomFromText and STPointFromText geography static methods, which accepts as parameter the point coordinates in the Open Geospatial Consortium (OGC) Well-Known Text representation, the Point geography static methods accepts as parameters the Latitude and Longitude coordinates of the point in that order.

Using geography::Parse

Yet another way of creating a geography POINT spatial data object is with the use of the geography::Parse extended static geography method. The geography::Parse static method is similar to the STGeomFromText static method in the sense that it returns a geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation.

UPDATE [dbo].[Landmark]
SET [GeoLocation] = geography::Parse('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' +
CAST([Latitude] AS VARCHAR(20)) + ')')
GOThe only difference between the geography::Parse method and the geography::STGeomFromText method is that in the Parse method, it assumes a spatial reference ID (SRID) of 4326, which is the second parameter of the geography::STGeomFromText method.


Reference link:

7 Free .NET Development and Architecture E-books

Who doesn't love free books? Take a look at this list of 7 useful free e-books for .NET programmers and budding architects


7 Free .NET Development and Architecture E-books

Sql Server Community Free Training Webinar Series Pragmatic Works

If you have not taken part in any of the free Webinars that Pragmatic Works and Sql Server Community have put together you are missing out on a great resource. These webinars are free and provide some information from some of the top industry experts on topics that can help your organization. Each month they put out a series of sessions that you can take part via live webinar or view the recorded sessions. Typically, the sessions are on Tuesday and Thursday of each week in the month. Once again make sure to check out the great resource.

Featured Session

Past Webinars

SSIS Expression Cheat Sheet

Over the past week I was watching some of the free Pragmatic Works Webinars and came across a SSIS Expression Cheat Sheet. If you work with ETL processes in SSIS I would highly recommend taking a look at this resource. It outlines some of the more common data transfers that are needed in the Data Flow Task.

Reference Link

Integration Sql Server 2008 R2 Reporting Service with SharePoint 2010

For those that are considering SSRS 2008 R2 integration mode with SharePoint 2010 I would recommend reviewing the latest article from MSSQL Tips. It provides a simple approach to setup with step by step photos.

Reference Link:

SSIS Script Task Email with SqlConnection Logging / VB.Net

On a earlier post I talked about using a Foreach loop container to send dynamic emails. I finally was able to wrap up the project and decided to take it a little bit more by adding a logging method in the script task. Below is my code sample for getting values from ForEach Loop Container, creating the email and logging the sent email into sql server via a SqlConnection.

Sample Code:

Public Sub Main()
Dim EmailTypeID As Integer
Dim EmployeeID As String
Dim SalesID As String
Dim SamAccount As String
Dim RehireDate As String
Dim ServiceDate As String
Dim FromAddress As String
Dim ToAddress As String
Dim Subject As String
Dim Body As String
Dim Name As String

Try
' Log properties
EmailTypeID = Dts.Variables("User::EmailTypeID").Value
EmployeeID = Dts.Variables("User::EmployeeID").Value.ToString
SalesID = Dts.Variables("User::SalesID").Value.ToString
SamAccount = Dts.Variables("User::SAMAccount").Value.ToString
RehireDate = Dts.Variables("User::RehireDate").Value.ToString
ServiceDate = Dts.Variables("User::ServiceDate").Value.ToString

' Email properties
FromAddress = Dts.Variables("User::MailFromAddress").Value.ToString
ToAddress = Dts.Variables("User::MailToAddress").Value.ToString
Subject = Dts.Variables("User::MailSubject").Value.ToString
Body = Dts.Variables("User::MailBody").Value.ToString
Name = Dts.Variables("User::MailName").Value.ToString

If SendMail(FromAddress, ToAddress, Subject, Body, Name) = True Then
LogEmail(EmailTypeID, EmployeeID, SalesID, SamAccount, ToAddress, RehireDate, ServiceDate)
Dts.TaskResult = ScriptResults.Success
End If

Catch ex As Exception
ErrorEmail(ex)
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub

Public Function SendMail(ByVal FromAddress As String, ByVal ToAddress As String, ByVal Subject As String, ByVal Body As String, ByVal Name As String) As Boolean
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Dim Body2 As String
Dim NewHireUrl As String

'MsgBox("Send, started")
NewHireUrl = "http://msn.com"

Body2 = "Dear: " & Name & _
vbNewLine & vbNewLine & _
" message "

myHtmlMessage = New MailMessage(FromAddress, ToAddress, Subject, Body2)
mySmtpClient = New SmtpClient("smtp.server.com")
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
'MsgBox("Send, completed")
Return True

End Function

Public Function LogEmail(ByVal EmailTypeID As Integer, ByVal EmployeeID As String, ByVal SalesID As String, ByVal SamAccount As String, ByVal ToAddress As String, ByVal RehireDate As String, ByVal ServiceDate As String) As Boolean
Dim oCnn As New Data.SqlClient.SqlConnection
Dim sSQL As String
oCnn.ConnectionString = "Server=xxxx;Database=xxxx;User ID=user;Password=pass;Trusted_Connection=False"
sSQL = "insert into dbo.EmployeeEmail (DimEmailTypeKey, EmployeeID, SalesID, SamAccount, EmailAddress, DateOfEmail, RehireDate, ServiceDate, AuditKey) "
sSQL += "Values (@EmailTypeID, @EmployeeID, @SalesID, @SamAccount, @EmaillAddress, @DateOfEmail, @RehireDate, @ServiceDate, @AuditKey) "
Dim oCmd As SqlCommand = New SqlCommand(sSQL, oCnn)
oCmd.Parameters.Add("@EmailTypeID", SqlDbType.Int).Value = EmailTypeID
oCmd.Parameters.Add("@EmployeeID", SqlDbType.VarChar).Value = EmployeeID
oCmd.Parameters.Add("@SalesID", SqlDbType.VarChar).Value = SalesID
oCmd.Parameters.Add("@SamAccount", SqlDbType.VarChar).Value = SamAccount
oCmd.Parameters.Add("@EmaillAddress", SqlDbType.VarChar).Value = ToAddress
oCmd.Parameters.Add("@DateOfEmail", SqlDbType.VarChar).Value = Date.Now.ToString()
oCmd.Parameters.Add("@RehireDate", SqlDbType.VarChar).Value = RehireDate
oCmd.Parameters.Add("@ServiceDate", SqlDbType.VarChar).Value = ServiceDate
oCmd.Parameters.Add("@AuditKey", SqlDbType.VarChar).Value = 1

oCmd.CommandType = CommandType.Text
oCmd.Connection = oCnn
oCnn.Open()
oCmd.ExecuteNonQuery()
oCnn.Close()
Return False

End Function

Public Sub ErrorEmail(ByVal Exception As Exception)
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient

myHtmlMessage = New MailMessage("admin@email.com", "admin@email.com", "Email Error", Exception.ToString())
mySmtpClient = New SmtpClient("smtp.server.com")
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
End Sub