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

SSRS ReportExecution2005.asmx call with code/ C# / https

As stated in my prior post I have been working on InfoPath Forms Services. During the development of a form I wanted to attach a pdf version of the form in an email during submission. I have researched several ways to accomplish this task and the solution I used was to call reporting services. This call would allow me to return a memorystream and attach it to my email in code. In my case the difficult part was to make sure the call worked from both https and http calls. One key note to take into consideration is to make the web service url call directly from the Forms Collection your are currently in. This will ensure the proper creds are passed and "DefaultCreds" will work. Below I have provided a code sample on how to make the call and return a pdf in a memory stream.

Report Server Web Service Endpoints

Sample of Code:
private MemoryStream FormSubmitGetReportAttachment()
{
try
{

// Render arguments
byte[] result = null;
string reportPath = "http://moss/Reports/Sales/Review.rdl";
string format = "PDF";
string historyID = null;
string devInfo = @"False";
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name = "ApptKey";
parameters[0].Value = ApptKey.ToString();

//DataSourceCredentials[] credentials = null;
//string showHideToggle = null;
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
//ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;

ReportExecutionService rs = new ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
//rs.Credentials = new NetworkCredential(user, pass, domain);
rs.Url = "https://moss/Forms/_vti_bin/ReportServer/ReportExecution2005.asmx";
rs.ExecutionHeaderValue = new ExecutionHeader();

ExecutionInfo execInfo = rs.LoadReport(reportPath, historyID);
String SessionId = rs.ExecutionHeaderValue.ExecutionID;
rs.SetExecutionParameters(parameters, "en-us");

result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

MemoryStream ms = new MemoryStream();
ms.Write(result, 0, result.Length);
ms.Position = 0;
return ms;
}
catch (SoapException e)
{
ErrorMessage(e, true, e.Detail.OuterXml);
return null;
}
catch (Exception ex)
{
ErrorMessage(ex);
return null;
}

}

UserProfileService.asmx in MOSS 2007/ https

Over the past month I have been working with InfoPath Forms Services and managed code. I noticed something on calling a the UserProfileService that I wanted to share. The use of a local DataConnection in the InfoPath works on both https and http calls. But I wanted to strickly use a code solution for this call. For some reason my code solution would only work on http calls. I then came across this article and the recommendation of changing the webservice url from the main site to the site of the form itself.

The solution was to change the url in the web service call from :
https://moss/_vti_bin/userprofileservice.asmx

to:

https://moss/sites/forms/_vti_bin/userprofileservice.asmx

Code Examples of Local and Code only solutions:

Local Data Connection inside Form:
private void GetProfileInfo2()
{
try
{
XPathNavigator Label = Root.SelectSingleNode("/my:myFields/my:Label", NamespaceManager);
XPathNavigator Name = Root.SelectSingleNode("/my:myFields/my:UserName", NamespaceManager);
XPathNavigator ErrorMesageLabel = Root.SelectSingleNode("/my:myFields/my:MessageLabel", NamespaceManager);

WebServiceConnection UserSvc = (WebServiceConnection)DataConnections["GetUserProfileByName"];
XPathNavigator xnDoc = DataSources["GetUserProfileByName"].CreateNavigator();
UserSvc.Execute();
XPathNavigator xnAcctEmail = xnDoc.SelectSingleNode("dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values[../s0:Name = \"WorkEmail\"]", NamespaceManager);
XPathNavigator xnAcctName = xnDoc.SelectSingleNode("dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values[../s0:Name = \"PreferredName\"]", NamespaceManager);
if (xnAcctEmail != null)
{
Label.SetValue(xnAcctEmail.Value);
Name.SetValue(xnAcctName.Value);
}
}
catch (Exception exception)
{
ErrorMessage(exception);
Root.SelectSingleNode("/my:myFields/my:MessageLabel", NamespaceManager).SetValue(exception.ToString());
}
}

Code Only Call:
private void GetProfileInfo()
{

try
{
XPathNavigator RemoteEmail = Root.SelectSingleNode("/my:myFields/my:RemoteEmail", NamespaceManager);
XPathNavigator RemoteName = Root.SelectSingleNode("/my:myFields/my:RemoteName", NamespaceManager);


UserProfileService Profile = new UserProfileService();
Profile.Credentials = new System.Net.NetworkCredential("User", "Pass", "Domain");
//Profile.UseDefaultCredentials = true;
//Profile.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
Web_Service_Validation.UserProfile.PropertyData[] properties = Profile.GetUserProfileByName(UserName);

if (properties.Length > 0)
{
RemoteEmail.SetValue(properties[36].Values[0].Value.ToString()); // WorkEmail
RemoteName.SetValue(properties[4].Values[0].Value.ToString()); //name
}


}
catch (Exception exception)
{
ErrorMessage(exception);
Root.SelectSingleNode("/my:myFields/my:MessageLabel", NamespaceManager).SetValue(exception.ToString());
}
}

SSIS Execute SQL Task into an object variable / For each ADO Enumerator

I ran across the need to dynamically send out 2000+ emails based on a Execute Sql Task in SSIS. I knew I needed to use the For Each Loop Container but wasn't sure how to link multiple rows to a single action in the For Each Loop Container.

I ended up using the Execute Sql Task with a Result Set of "Full result set" and returned the Result Set to a single Variable Object. This allows you to process multiple columns/rows into a single variable. Just need to make sure its a Object variable. Then in the For Each Loop Container Collection I used the Foreach ADO Enumerator. This allowed me to reference the Object variable I setup earlier. Next, I mapped each of the variables in the mapping section based on the order of the columns in the extract proc. Very similar to an array in .net. Finally, I used a script task to fire off an email for each row.

I have used the For Each Loop Containers in the past for processing/imports of data files. This is the first time I have worked with the Object variable in SSIS and agree this is a very power trick to have in your toolbox.

Thanks
Bryan

SSRS 2008 Inscope / Percent to Total

Over the past couple of weeks I have been asked several questions about InScope function in SSRS 2008. It is a very useful function that allows for aggergations at different group levels in a report. This is helpful when trying to calculate a dynamic percent to total by a parent group.

Here is an example of how to use Inscope:
Territory Total of 2500 appts
District Total of 125 appts
what is the percent total?

=iif(iif(InScope("Territory", count(Fields!.Appts.Value,"Territory"),0) = 0 , 0,
count(Fields.Appts.Value) / iif(InScope("Territory"), count(Fields.Appts.Value, "Territory"),0))

Result = 5%

SharePoint Gantt Chart Formatting

The SharePoint default Gantt chart doesn't provide any formatting but with a Content Editor Web Part and a little bit of CSS you can change the layout and formatt.

What this code does:
- change the images width from 16 to 2 pixels.
- remove the display of weekdays
- rotate the dates to display them vertically


Reference: Link

If you are looking for a zoom slider with JQuery:Link

Submit InfoPath 2007 with Code (SharePoint list & Email)

Create a SharePoint Library Submit data connection and an Email Submit data connection and then programmatically execute the data connections in the FormEvents_Submit event handler of the InfoPath form, and perform a Close the form action as part of the Submit Options on the InfoPath form.

1. In InfoPath, design a browser-compatible InfoPath form template
2. On the Data Source task pane, add a Text Field node named formName under the myFields node.
3. On the Tools menu, choose Data Connections.
4. On the Data Connections dialog box, click Add, and create a new Submit data connection As an e-mail message. Fill in any email address in the To field; you will be changing this in code anyway. Accept the default name of Email Submit for the data connection.
5. On the Data Connections dialog box, click Add, and create a new Submit data connection To a document library on a SharePoint site. Fill in a valid URL to a Document Library. Select the formName node from the Main data source to be the File name for the form. Accept the default name of SharePoint Library Submit for the data connection.
6. On the Tools menu, choose Submit Options.
7. On the Submit Options dialog box, select Allow users to submit this form, deselect the Show the Submit menu item and the Submit toolbar button check box, click Advanced and select Close the form from the After submit drop-down list box, select Perform custom action using Code, and click Edit Code.
8. This will add a FormEvents_Submit event handler to the InfoPath form template.
9. In Microsoft Visual Studio Tools for Applications, add the following C# code to the FormEvents_Submit event handler:

// Get a reference to the main data source

XPathNavigator root = MainDataSource.CreateNavigator();

// Generate a name for the form to be saved in SharePoint

string formName = Guid.NewGuid().ToString();

// Set the name of the form on the data connection

root.SelectSingleNode(

"//my:formName", NamespaceManager).SetValue(formName);

// Submit the form to SharePoint

DataConnection spConn =

DataConnections["SharePoint Library Submit"];

spConn.Execute();

// Set the properties for the email

string toAddress = root.SelectSingleNode(

"//my:sendTo", NamespaceManager).Value;

EmailSubmitConnection emailConn =

(EmailSubmitConnection)DataConnections["Email Submit"];

emailConn.To.SetStringValue(toAddress);

emailConn.Subject.SetStringValue("This subject was set from code");

emailConn.Introduction = "This email was generated by code.";

emailConn.EmailAttachmentType = EmailAttachmentType.None;

// Send the email

emailConn.Execute();

// Indicate success

e.CancelableArgs.Cancel = false;

Save your work and build the code.

Reference:

Split Comma Delimited String

Andy has put together a really good video on Comma Delimited String parsing in sql. Personally I am a big fan of the CLR functionality on this issue. Make sure to check out the video he has put together.

Link to video:
http://www.sqlshare.com/SplittingDelimitedStrings_774.aspx

Link to Andy's example:
http://www.sqlshare.com/references.774.zip

Here is another example of the split function:

GO
/****** Object: UserDefinedFunction [dbo].[fn_Split] Script Date: 06/02/2010 17:05:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_Split]
(
@sText VARCHAR(8000) ,
@sDelim VARCHAR(20) = ' '
)
RETURNS @retArray TABLE
(
idx SMALLINT PRIMARY KEY ,
value VARCHAR(8000)
)
AS
BEGIN
DECLARE
@idx SMALLINT ,
@value VARCHAR(8000) ,
@bcontinue BIT ,
@iStrike SMALLINT ,
@iDelimlength TINYINT

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTRIM(RTRIM(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ( ( @iDelimlength = 0 )
OR ( @sDelim = 'Empty' )
)
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText) > 0
BEGIN
SET @value = SUBSTRING(@sText, 1,
CHARINDEX(@sDelim,
@sText) - 1)
BEGIN
INSERT @retArray
( idx, value )
VALUES
( @idx, @value )
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value)
+ @iDelimlength
SET @idx = @idx + 1
SET @sText = LTRIM(RIGHT(@sText,
DATALENGTH(@sText)
- @iStrike))

END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray
( idx, value )
VALUES
( @idx, @value )
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue = 1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText) > 1
BEGIN
SET @value = SUBSTRING(@sText, 1, 1)
BEGIN
INSERT @retArray
( idx, value )
VALUES
( @idx, @value )
END
SET @idx = @idx + 1
SET @sText = SUBSTRING(@sText, 2,
DATALENGTH(@sText) - 1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray
( idx, value )
VALUES
( @idx, @sText )
SET @bcontinue = 0
END
END

END

RETURN
END

InfoPath 2007 Bind Drop Down List with C#.Net

I have seen several ariticles on how to bind a list in InfoPath 2007 so this isn't anything new but I wanted to share the method that worked best for me. I found it best to make a generic method to bind the list so you can hook it into multiple event handlers. This prevented me from writing the same bit of code twice. If you notice prior to binding the list I clear the list. On a prior post I have posted the code for the ClearListBox method. If you don't clear the list prior to you may end up just appending your values.

private void BindDistrictList(string username, string HomeDistrict)
{
string xpListBox = "/my:myFields/my:listDistrict/my:listDistrictItem";
ClearListBox(xpListBox, Root);

try
{
string data = string.Empty;
using (SqlConnection conn = new SqlConnection(_SqlCon))
{
SqlCommand command = new SqlCommand("IPGetDistrictbyUser", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@UserName", SqlDbType.Char).Value = UserName;
command.Parameters.Add("@hd", SqlDbType.Char).Value = HomeDistrict;
conn.Open();

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
XmlDocument doc = new XmlDocument();
XmlNode group = doc.CreateElement("listDistrictItem", NamespaceManager.LookupNamespace("my"));
XmlNode field = doc.CreateElement("listDistrictItemName", NamespaceManager.LookupNamespace("my"));
XmlNode node = group.AppendChild(field);
node.InnerText = reader["DistrictName"].ToString();

field = doc.CreateElement("listDistrictItemValue", NamespaceManager.LookupNamespace("my"));
node = group.AppendChild(field);
node.InnerText = reader["DistrictID"].ToString();

doc.AppendChild(group);

MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:listDistrict", NamespaceManager).AppendChild(doc.DocumentElement.CreateNavigator());

}
conn.Close();
}
}
catch (Exception ex)
{
ErrorMessage(ex);
}
}

InfoPath 2007 Clear Databound Drop Down List with C#.Net

Over the past couple weeks I have been working with InfoPath 2007 and cascading drop down list. The issue is once a user decides to go out of the normal flow selection you need to clear the drop down list and re-bind the list. I couldn't find good examples on how to clear a list so I decided to share my code.

Call from your method:
ClearListBox("/my"myFields/my:listLocations/my:listLocationsItem", Root);

Here is the ClearListBox method:
public void ClearListBox(string xp_ListBox, XPathNavigator xNav)
{
//clear list box of items
XPathNodeIterator lstClear = xNav.Select(xp_ListBox, NamespaceManager);
if (lstClear.Count > 0)
{
for (int i = lstClear.Count; i > 0; i--)
{
XPathNavigator reList = MainDataSource.CreateNavigator();
XPathNavigator reListItems = reList.SelectSingleNode(xp_ListBox + "[" + i + "]", NamespaceManager);
reListItems.DeleteSelf();
}
}
}

I am using a Property for Root:
private XPathNavigator Root
{
get
{
return MainDataSource.CreateNavigator();
}
}

InfoPath - Get the current user without writing code

Recently I started working on InfoPath forms in Moss2007. One common item that is needed on each of the forms is the current user. I came accross a method of getting the current user in InfoPath 2007 via the UserProfileService web service. Below is a really good article that provides a step by step example.

http://blogs.microsoft.co.il/blogs/itaysk/archive/2007/04/05/InfoPath-_2D00_-Get-the-current-user-without-writing-code.aspx

Active Directory using SSIS, LDAP, ADHelper.dll and C#.Net

Recently I have been task with a project to get up to date info from Active Directory. My goal was to stay within the scope of SSIS, MOSS, SSRS and SQL. The problem I faced was two very specific fields in LDAP, description and memberof. Since these two fields are arrays you can't use LDAP or SSIS Active Directory Services. I did come across the ADHelper dll that would allow SSIS to return the memberof field. The proplem is that the query is focused on users and not group. In order to get group description I had to write a console app that was kicked off by a SSIS package.

Here is a good article on the basic fields that can be queried using SSIS Active Directory Services.

http://agilebi.com/cs/blogs/jwelch/archive/2007/04/07/retrieving-information-from-active-directory-with-ssis.aspx

Here is a good article on the ADHelper.dll that can be referenced in SSIS
http://www.mssqltips.com/tip.asp?tip=1657

I will post the code to query the Group Description infomation.

Week of Business Intelligence with Pragmatic Works Webinars

This week Pragmatic Works put togther a "Week of Business Intelligence" webinars. It was a lot of material to cover in such a short amount of time but each session is a must. If you didn't get a chance to view them make sure to check out the recorded sessions at the below link. Most of the webinars have code samples you can download.

Some of my favorite:

1. Designing a Data Warehouse
   
2. Loading a Data Warehouse with SQL Server Integration Services (SSIS)
   
3. Report Design Recipes for BI Solutions

http://www.pragmaticworks.com/resources/webinars/March2010Webinar.aspx

SSAS 2008 Time Calculation Dimension (Calculated Members)

Over the past couple of weeks I have been working on setting up a PPS 2007 dashboard and came across a Time Calculation Dimensions in SSAS 2008. At first I didn't truly understand the true purpose or functionality but after time I gained respect as I created new Time Calculation Calculated Members. This article will explain the steps needed to setup a Time Calculation dimension and add multiple Calculated Members that are stored in the Time Dimension.

1. Go to your dsv of the cube and add a new Named Calculation field to your time dimension, Make sure to call it PeriodID and the expression should be " N'Current Period' " (make sure to drop the double quotes
   
2. Create a new Dimension with the Dimension Wizard
3. Select use an existing table
4. tap into your current time dimension, in my case I am using DimWeek
5. Make sure to have the primary key of the dimension selected and no other fields
6. Name the new dimension "Time Calculation"
7. Now that the dimension is created bring it up
8. Drag over the PeriodID (the Name Calculation you created on step 1) to the Attributes section of the Dimension Structure tab
9. Rename the field to Time Calculation and you should have something like this
10. Now open the cube up and go to the Calculation
11. Create a new Calculated Member
12. Name: [Prior Year]
13. Parent hierarchy: Time Calculation. Time Calculation
14. Expression: (parallelperiod([Week].[FiscalCalendar].[Fiscal Year], 1, [Week].[FiscalCalendar].CurrentMember), [Time Calculation].[Time Calculation].&[Current Period])
15. 
16. Now you need to setup your Dimension Usage
17. Setup the Measure Group, Referenced, Intermediate Dimension (Week in this case), Reference Dimension attribute (Time Calculation), Intermediate Dimension attribute (PeriodID)
18. 
19. Build and deploy cube
20. Now in your cube browser you will see a new dimension call Time Calculation
21. Add the new dimension and drag a metric and the time dimension and you will see the power of the Time Calculation Dimension

Here is a list of some other Time Calculations:
CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior Year]
AS (parallelperiod([Week].[FiscalCalendar].[Fiscal Year], 1, [Week].[FiscalCalendar].CurrentMember), [Time Calculation].[Time Calculation].&[Current Period]),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Date]
AS aggregate(crossjoin([Time Calculation].[Time Calculation].&[Current Period], PeriodsToDate([Week].[FiscalCalendar].[Fiscal Year], [Week].[FiscalCalendar].CurrentMember))),
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Year Change]
AS [Time Calculation].[Current Period] - [Time Calculation].[Prior Year],
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Year Change %]
AS Case
When ISEMPTY([Time Calculation].[Prior Year]) Or [Time Calculation].[Prior Year] = 0
Then IIF([Time Calculation].[Current Period] > 0, 1, 0)
Else
[Time Calculation].[Year to Year Change] / [Time Calculation].[Prior Year]
End,
FORMAT_STRING = "Percent",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior 3 Periods]
AS Aggregate({[Week].[FiscalCalendar].CurrentMember.Lag(3) :
[Week].[FiscalCalendar].CurrentMember.Lag(1)}, [Time Calculation].[Current Period]),
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Prior 6 Periods]
AS Aggregate({[Week].[FiscalCalendar].CurrentMember.Lag(7) :
[Week].[FiscalCalendar].CurrentMember.Lag(1)}, [Time Calculation].[Current Period]),
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Time Calculation].[Time Calculation].[Year to Date %]
AS [Time Calculation].[Time Calculation].&[Current Period] /[Time Calculation].[Time Calculation].[Year to Date],
FORMAT_STRING = "Percent",
VISIBLE = 1;

Cross Site List RSS Feed with SharePoint DataView (No need for Anonymous Access)

1. Lets start out by pulling up SharePoint Desinger 2007


2. Open up your site (in my case I am using MySite)


3. On the Data Source Library tab on the right portion of the designer
   


4. Connect another library in the lower right
   


5. Now that you have created a connection to another library you need to "Refresh library" to get the objects to appear
   


6. Now your Data Source Library should look like this (image)


7. We now move on to the list you are trying to hook into. On the Date Source Library select "Connect to a script or RSS feed.."


8. On the General tab give it a name, in my case "BI Calendar"


9. On the Source tab the default values are good


10. Just add the URL for the List RSS feed you want to view
11. Go to the list, select the action, select "View RSS Feed"
    


12. Take the URL and drop it into the "Enter the URL to a server-side script" field
    
13. At this
    point if you a specific view that you want to reference you are going to need to
    add the GUID to the view in the above step as well. This was difficult step to figure
    out and I hope I can help save someone else the time I spent on it. Open up the
    view, modify the view and pull out the "&View=" from the URL. This parameter will be used in the server-side script connection to the List RSS Feed. The next step is to add a parameter to the DataView properties. A URL with out the View will look like this: "http://MyServer/_layouts/listfeed.aspx?List=a4a88bd0%2Ddf29%2D4f54%2Da924%2D195be47346ed" and a URL with the View will look like this: "http://MyServer/_layouts/listfeed.aspx?List=a4a88bd0%2Ddf29%2D4f54%2Da924%2D195be47346ed&View=%7B9F3ACEAB%2D75DF%2D4C04%2D83C2%2D3A5733A6CA31%7D"
14. 
    Go to the the Login tab, in my case we are using the Kerberos so "Use Windows Auth"
    works, not sure what other cases this will work. You may have to play this depending
    on the type of Auth you have setup on your farm. Keep in mind if you enter your
    creds they will be stored in plain text. This is the point in which you get around
    the Anonymous Access part of a List RSS Web Part .


15. Now we need to add a SharePoint DateView to the page. Insert, SharePoint Controls,DateView
    


16. The next step is to link the Server-Side Scripts to the SharePoint DataView web part we just
    added. Right click on the server side scrip you just created in the Data Source
    Library and select "Show Data". This will allow you to do two things, validate the
    the connection is working correctly and view the list of RSS fields that come over
    in the feed
    


17. Now scroll down on the Data Source Details tab to the "Item" section. Then select "title &
    pubDate" by holding down your CRTL key.
18. Then select the "Insert Selected Fields
    as.." drop down at the top of the Date Source Details tab
    
    This step binds the fields from the RSS feed to the DataView



19. Go to the DataView and right click edit properties. We want to edit the title and
    convert it into a hyper link.
    
    Make sure the "Text to display" is {title} and the "Address" is {link}.
    


20. Next I clean up the headers on the webpart and the columns
21. Finally save and deploy.
    You may first see an error page but just select the "refresh page" link and you
    are done (image)

SharePoint Data View Web Part

Here is a simple step by step on how to setup a DataView web part in SharePoint Designer. This is a quick and easy way to use current datasources and integrate them into a current page. Link to pdf

Link to article

Deploying a Business Intelligence Solution Using SharePoint, Reporting Services, and PerformancePoint Monitoring Server with Kerberos

Over the past month I have taken part in a deployment of MOSS 2007, Performance Point Server 2007, SQL Server Reporting Services 2008 on Windows Server 2008 with Kerberos authentication. This is a pretty complex configuration and I found an ideal resource by Carl Rabeler that I wanted to share. The article covers the design requirements for this business intelligence solution, its logical architecture, the challenges we faced in architecting and implementing this solution, and our resolutions to these challenges.

Make sure to check out the original article from sqlcal.com: Link

Sharepoint Wakeup/Sharepoint List Adapters

Currently we have a nightly process that loads some records into a staging WSS list with Sharepoint List Adapters in SSIS 2008. Loading Moss/WSS lists with list adapters is a quick and easy solution that everyone needs to check out (list adapters). But I started noticing timeout failures in the mornings. After some additional research I came to the conclusion the site is trying to wake up at the same time I am loading 10,000+ records into a wss list.

So I decided to setup a Sharepoint WakeUp process to warm the site prior to loading the records. If you have not explored this simple process you need to look into it. By setting up a simple scheduled task on the web front end of your site you can wakeup miltiple sites. The process even has the ability to exclude some sites like "mysites"

The only catch is that you must setup the exe on the actual MOSS/WSS webfront end. It references a sharepoint.dll that is needed to wake the site.

Simple command:
spwakeup.exe -site:http://portal.mycompany.com -exclude:http://portal.mycompany.com/MySites -log:c:\log.txt

Concatenating rows as a alternative to using a cursor

As I was reading the "question of the day" on SqlServerCentral.com I came across a simple but very effective alternative solution to using a cursor by concatenating rows.

declare @table table (id int, [name] varchar(10), gender char(1))
declare @MaleNames varchar(255)

insert into @table values (1,'John','M')
insert into @table values (2,'Sue','F')
insert into @table values (3,'Jane','F')
insert into @table values (4,'Mark','M')
insert into @table values (5,'Bill','M')

select @MaleNames = IsNull(@MaleNames + ', ','') + [name] from @table where gender = 'M'

Select @MaleNames as MalePatients

DBA Check List

Below is an outline of the items that need to be considered for a DBA role. Being a DB Developer/DBA I find this list to be a great starting point. Depending on the type of environment you have some of these items may be overkill or just not needed.

Link to full pdf version

• General DBA Best Practices
• Best Practices for Becoming an Exceptional SQL Server DBA
  
• Day-to-Day
  
• Installation
  
• Upgrading
  
• Security
  
• Job Maintenance
  SQL Server Configuration Settings
  
• Database Settings
  
• Replication


• High Availability Best Practices
  
  • General High Availability
    
  • Disaster Recovery
    
  • Backup
    
  • Clustering
    SQL Server Mirroring
    
  • Log Shipping
  
  
• Performance Tuning Best Practices
  
  • Performance Monitoring
    
  • Hardware Performance Tuning
    
  • Indexing
    
  • SQL Server 2008 Compression Best Practices
    
  • SQL Server 2008 Data Collector Best Practices
    Best Practices for Resource Governor
  
  
• Application Design and Coding Best Practices
  
  • Database Design
    
  • Queries and Stored Procedures
    
  • Transact-SQL
    
  • SQL Server CLR
    
  • XML
  
  
• SQL Server Component Best Practices
  
  • SSIS
    
  • Reporting Services
    
  • Analysis Services
    
  • Service Broker
    
  
  

Source article

XML Web Part/ RSS / Content Query Web Part Replacement in Windows SharePoint Services

Part 1

• Add an XML Web Part to a page.
• Edit the XML Web Parts properties
• In the XML Link field, enter the URL of the RSS feed.
• In the XSL Link field, enter the URL of an XSL file that you've written to transform the feed, or alternatively, in the “XSL Editor“, enter the following XSL code:

<?xml version="1.0"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:template match="/">
<table width="100%" border="0" cellspacing="0" cellpadding="1">
<tr>
<td class="tablebg"><table border="0" cellpadding="3" cellspacing="0" bgcolor="#FFFFFF">
<tr>
<td><img src="images/spcacer.gif" width="163" height="1" /></td>
</tr>
<tr valign="middle">
<td><h2>Recent Articles</h2>
</td>
</tr>
<xsl:for-each select="rss/channel/item">
<tr>
<td><a href="{link}"><xsl:value-of select="title"/></a></td>
</tr>
</xsl:for-each>
<tr>
<td><a href="/allarticles.aspx">More...</a></td>
</tr>
</table>
</td>
</tr>
</table>
</xsl:template>
</xsl:stylesheet>

Part 2
It wasn't too long ago that I was trying to get my favorite Web Part (Content Query Web Part) to pull an RSS feed form a SharePoint list. Web Part was giving me a lot of grief, repeatedly giving me the error:

Cannot retrieve the URL specified in the XML Link property. For more assistance, contact your site administrator.

It took me a while to piece together that this Web Part does NOT run in the context of my given SharePoint credential when it comes to fetching this RSS. In fact I don't know of ANY RSS able Web Parts that support authenticated feeds.

As a work around I had to enable Anonymous Access for the given list. I was quite surprised when after some research I discovered that SharePoint allows you to turn on annonymous access for any of the following:

• Everything on a site (ie. 3 sites out of a site collection)
• Any combination of Lists and Libraries on a given site
• No anonymous access at all

For my needs I wanted to turn it on for just ONE list (the one I wanted to expose and consume the RSS feed for). Here's how to do it.

Enabling Anonymous Access for a Single List

1. The first thing you need to do is turn on anonymous access for the web application, this is done through the Central Administration UI. Open up the Central Administration web page, go to the Application Management tab and click on Authentication Providers under Application Security (see below).
2. Now that this application allows anonymous access we need to turn it on for the given site. Go to the site you want to turn on anonymous access on, navigate to Site Actions->Site Settings->Modify All Site Settings. From here click on Advanced Permissions. Open up the Settings menu on the tool bar and then click Anonymous Access. Finally choose (for this example) "Lists and libraries". We're going to turn on anonymous access for exactly one list.
3. Finally go to the list you want to turn on anonymous access for and in the List Settings, Permissions for this List and then expand the Settings menu and click Anonymous Access. From here you simply select the rights you want to give anonymous users for this list. In this case we simply choose View Items.
4. That's it, that RSS feed should now be working!
   

Synchronise SQL table with SharePoint List

I wanted to share how I was able to get synchronization of a SharePoint List through
SSIS. Below I have provided an outline of the steps I took.

• First, I had an OLE DB Source, then a Data Conversion to convert those strings and
  then Sort it on its key.
  
• Second, I had the SharePoint List Source that has to be synchronized, Data Conversion
  to match the key type and then Sort it on the key.
• Third, Merge Join this 2 sorted data using Full Outer Join.
• Fourth, add a Conditional Split with the ff output:
  
  • Output Name: Create Condition: ISNULL( ID )
  • Output Name: Delete Condition: ISNULL([Your Key])
  • Default output name: Update
    
• Lastly, Add 3 SharePoint List Destination for Create Delete and Update with the
  ff note:
  
  • For Create, do not map the column "ID" and Batch Type should be "Modification"
  • For Update, map the column ID to the ID you got from merging, Batch Type should
    be "Modification"
  • For Delete, you only need to map the ID, and Batch Type should be "Deletion"
  
  
  How the layout looks, sometimes a pictures can help:
  

Free Tools for the SQL Server DBA

This article has come across my desk serveral times so I want to make sure everyone is aware of it. The article outlines some key tools that can help perform a DBA/Developer role.

http://www.sqlservercentral.com/articles/Tools/64908/

Excel 2007 connection to SSAS 2008: "The message or signature supplied for verification has been altered

I have seen some issues with Vista/Office2007/Server2008/Sql2008/SSAS cubes connections. An error message when trying to connect to a SSAS cube. The error message reads, " The message or signature supplied for verification has been altered" It seems that "AES algorithm" is not a default in Windows Server 2003 but is a default in Windows Server 2008.

Installed the hotfix and the issue has been resolved:
http://support.microsoft.com/kb/969083

Link to similar issues:
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/f285e2dd-bd3f-455c-b8b3-808d73a25a75/

Applied Microsoft SQL Server 2008 Reporting Services

Applied Microsoft SQL Server 2008 Reporting Services

Repeat Header / Keep Header Visible in Tables in RS 2008

You selected "Repeat header rows on each page" or "Keep header rows visible while scrolling" in the tablix properties dialog, but it doesn't seem to work as expected? You might want to try the following four steps if you are using a "table"-style layout:

1. in the grouping pane, click on the small triangle and select "Advanced Mode" to show static members:
   
   
2. verify that you have the properties grid turned on (in BI Development Studio use F4, in Report Builder go to the "View" tab and select "Properties")
   
3. select the corresponding (static) item in the row group hierarchy
   
4. in the properties grid:
   - set KeepWithGroup to After
   - set RepeatOnNewPage to true for repeating headers
   - set FixedData to true for keeping headers visible

Reference link:

Automated Index Maintenance

I recently went to the OPASS meeting and watched a demo on Automated Index Maintenance. I am currently in the process of testing the logic on a dev box but wanted to share the link. I will follow up with updates through the testing process.

Link