This topic will cover how to export data from BLOB fields in SQL Server and save those files out to the filesystem. This is accomplished within VB.Net code, but could just as easily be ported over to C#.

DBServer: SQL Server 2008 SP2
Development Environment: Visual Studio 2008

Package Setup

1. Add an Execute SQL Task that retrieves the BLOB information from the Database
• ResultSet: Full result set
• Parameter Mapping: Add any necessary parameters for your stored procedure
• Result Set
• Result Name: 0 (zero)
• Variable Name: Doesn’t matter, so long as the variable is of type System.Object
2. Connect the Execute SQL Task to a For Each Loop Container (FELC)
1. Collection: Foreach ADO Enumerator
2. ADO object source variable: Result Set Variable Name from Step 1
3. Enumeration mode: Rows in the first table
4. Variable Mapping: Map out the columns from the Result Set to Package Variables (Index is 0-based)
1. NOTE: Be sure to setup the BLOB output variable as a System.Object
3. Add a Script Task to output the BLOB information to the file system

Script Code (VB.Net)

[code language=”vb”]

Public Sub SaveMemoryStream(ByVal buffer2 As Array, ByVal FileName As String, ByVal ms As MemoryStream)
Dim outStream As FileStream

outStream = File.OpenWrite(FileName)
outStream.Write(buffer2, 0, ms.Position)
outStream.Flush()
outStream.Close()
End Sub

Public Sub Main()
Dim Folder_Path As String
Dim File_Name As String
Dim s_File_Name As String
Dim buffer() As Byte
Dim oStream As System.IO.MemoryStream = Nothing
Dim oFileStream As System.IO.FileStream = Nothing
Dim buffer2() As Byte

Folder_Path = Dts.Variables("Working_Directory").Value.ToString() + ""
File_Name = Dts.Variables("File_Path").Value.ToString()

s_File_Name = Folder_Path & File_Name

Try
buffer = CType(Dts.Variables("Blob").Value, Byte())
oStream = New System.IO.MemoryStream(buffer)
oStream.Write(buffer, 0, buffer.Length)

buffer2 = oStream.ToArray()
SaveMemoryStream(buffer2, s_File_Name, oStream)

‘Close the stream
oStream.Flush()

Dts.TaskResult = ScriptResults.Success
Catch ex As Exception
Dts.Events.FireError(0, "Create Temp Image", ex.Message, "", 0)
Dts.TaskResult = ScriptResults.Failure

Finally
If Not oStream Is Nothing Then
oStream.Close()
End If

oStream = Nothing

If Not oFileStream Is Nothing Then
oFileStream.Close()
End If

oFileStream = Nothing
End Try
‘
Dts.TaskResult = ScriptResults.Success
End Sub
[/code]

Overview

This week brought me the unique challenge of extracting data from SAP within SSIS. My boss had already done some work on researching components, and the official word was that we were going to use a product from Theobald Software — Extract IS. After downloading a trial version and working on getting connected to SAP (which took waaaay longer than it should have, namely because of sparse documentation), I was disappointed to realize that the component was not returning all of the records within my dataset. No matter what date range I specified for my table extract, I still received only 2,476 records — FAR fewer than the multi-millions in the table.

That brings us to where we are today. While troubleshooting my problems with the Theobald solution, I ran across the Microsoft SAP ADO.Net connector. I figured it wouldn’t hurt to see if I had the same issues with this connector; that way, I could rule out if it was a Theobald issue or not. Turns out that I was able to successfully connect to SAP and extract my data!!

Now, onto the important stuff: How to make it work!

Making It Work

There were many moving parts involved in getting this working, from installing SAP Adapters on my local machine to installing custom RFC Functions on the SAP box. We will go over all of that in detail in this post.

Configuring Local Environment

We need to start with installing components on the local machine first, since we will use some of the output later on for installing necessary items in the SAP environment.

Installing Necessary Software

1. Visual C++ 2005 SP1 Redistributable: If you do not already have it installed, grab the download from here. Accept defaults for installation
2. WCF LOB Adapter SDK Install: Grab the install files from here, picking the appropriate download file for your system architecture (x86 vs x64). Under Choose Setup Type, select Custom. In the resulting window, ensure that Runtime and Samples are both selected to install to local disk. Accept remaining defaults.
3. BizTalk Adapter Pack install: Grab the install files from here, picking the appropriate download file for your system architecture (x86 vs x64). Under Choose Setup Type, select Custom. In the resulting window navigate to Base Adapters –> Microsoft BixTalk Adapter 3.0 for mySAP Business Suite, and ensure it is selected to install to local disk. Also, navigate to ADO Providers –> .Net Framework Data Provider for mySAP Business Suite, and ensure it is selected to install to local disk. Accept remaining defaults.

You should now have an ADO.NET SAP connector available for your use in SSIS:

You will need to secure the following information from your SAP Administrator in order to connect to SAP from SSIS:

• Application Server Host
• System Number
• Client
• Language
• Username
• Password

All the options under Messaging Server and Destination can be left blank in the SAP ADO.Net connection manager configuration page. Finally, click Test Connection to ensure that everything is good to go.

Configuring SAP Environment

The details of the SAP Environment configuration are beyond the scope of this blog post [especially since someone else installed the custom RFC function and set permissions, not me =)]. However, in the attached PPT Presentation, you can find detailed instructions for SAP configuration, from slides 28-39. Also, reference the attached help file for more detailed information regarding SAP configuration and setup.

Usage

Now that we have both pieces of the puzzle working together, it’s time to get some data! Fire up BIDS, and create a new Integrations Services Project. In that new project, add a Data Flow Task to the Control Flow. Within the Data Flow Task, drag over ADO NET Source from your toolbox. Having already configured the SAP Connection Manager above, select it as the ADO.NET connection manager from the drop down box. Under Data access mode, select  SQL command. Then, proceed to type in your SQL Command.

NOTE: SQL Syntax for SAP is ever so slightly different from normal T-SQL syntax. Have a look here for some important information. Pay particular attention to dates, if you are using those as part of your WHERE clause.

Click OK to exit the ADO.NET Source Editor. Next, connect up an OLE DB Destination to dump your data; open the destination and map the columns appropriately. If you have not already done so, create the table into which you will dump your data.

Hit F5 on the keyboard and let the good times roll!!

Conclusion

In this post, you learned how to install the SAP ADO.Net Adapters, configure the necessary RFC Functions on the SAP side, and extract data from a SAP System. I’ve uploaded the files referenced in this post to my SkyDrive. If you have any questions, please feel free to leave a comment!

[sourcecode language=”csharp”]
using System.Xml;
using System.Linq;
using System.Xml.Linq;
[/sourcecode]

The flow of the package basically as follows: For each file in the input folder, read the contents into a StreamReader, pick out the pieces of XML that we care about, and repeat.

The XML files that I’m working with have three levels. For illustrations’ sake, we will call them Grandfather, Father, and Child. The Grandfather (Route) has multiple children (Trips), who then have multiple children (Stop(s)).

Assign Values to Variables

Now that we have added the necessary references to our script task, the next task is to code the task.

[sourcecode language=”csharp”]
foreach (var routes in (from route in StreamReader(outfilename, "ExtRoute")
select new
{
//Route-level attributes
RouteKey = (string)route.Attributes("RouteKey").FirstOrDefault(),
ActualStartTime = (string)route.Attributes("start").FirstOrDefault(),
ActualStopTime = (string)route.Attributes("end").FirstOrDefault(),
PlannedStartTime = (string)route.Attributes("DepartDate").FirstOrDefault(),
PlannedRunTime = (string)route.Attributes("PlannedElapsedTime").FirstOrDefault(),
ActualDriverName = (string)route.Attributes("driver").FirstOrDefault(),
ActualDriverID = (string)route.Attributes("driverid").FirstOrDefault(),
ActualTruckID = (string)route.Attributes("truck").FirstOrDefault(),
RouteUnPlanned = (string)route.Attributes("unplanned").FirstOrDefault(),
RoutingDate = (string)route.Attributes("RoutingDate").FirstOrDefault(),
Route = (string)route.Attributes("id").FirstOrDefault(),
Branch = (string)route.Attributes("group").FirstOrDefault(),
Trips = (from trip in route.Elements()
select new
{
//Trip-level attributes
TripKey = (string)trip.Attributes("TripKey").FirstOrDefault(),

//Stop-level attributes
Stops = (from stop in trip.Elements()
select new
{
LocationType = (string)stop.Attributes("LocationType").FirstOrDefault(),
Longitude = (string)stop.Attributes("Longitude").FirstOrDefault(),
Latitude = (string)stop.Attributes("Latitude").FirstOrDefault(),
CustomerName = (string)stop.Attributes("locationName").FirstOrDefault(),
CustomerNumber = (string)stop.Attributes("LocationRefNumber").FirstOrDefault(),
ActualSequence = (string)stop.Attributes("actualSequence").FirstOrDefault(),
GeoStop = (string)stop.Attributes("geoStop").FirstOrDefault(),
ArrivalTime = (string)stop.Attributes("arrivalTime").FirstOrDefault(),
DepartureTime = (string)stop.Attributes("departureTime").FirstOrDefault(),
StopDuration = (string)stop.Attributes("stopDuration").FirstOrDefault(),
ActualDistance = (string)stop.Attributes("ActualDistance").FirstOrDefault(),
DeltaDistance = (string)stop.Attributes("DeltaDistance").FirstOrDefault(),
DeltaTimeDriven = (string)stop.Attributes("DeltaTimeDriven").FirstOrDefault(),
WorkTime = (string)stop.Attributes("WorkTime").FirstOrDefault()
})
})
}
))
[/sourcecode]

The code above uses the IEnumerable C# function to quickly and efficiently query only the XML nodes that we care about. The following code is used for this purpose:

[sourcecode language=”csharp”]
{
foreach (var trip in routes.Trips)
foreach (var stop in trip.Stops)
{
DefaultBuffer.AddRow();
DefaultBuffer.TripKey = trip.TripKey;
DefaultBuffer.RouteKey = routes.RouteKey;
DefaultBuffer.CustomerName = stop.CustomerName;
DefaultBuffer.CustomerNumber = stop.CustomerNumber;
//etc…

}
[/sourcecode]

Be sure to notice the beginning and ending curly brackets. Don’t forget them — they’re important!!

Conclusion

I hope that these two articles have given you some insight into a quick and efficient way to import XML data into a SQL database. Any thoughts, suggestions, or criticisms are welcomed!

XML is becoming increasingly popular in the workplace. Many systems are using it as a way to transmit data efficiently across the network. While XML certainly has its place, consuming it and deconstructing it can be a real headache.

Unfortunately, the native XML Source components in SSIS are lousy for such a purpose as this. Before I get into the details, I’ll provide a high-level overview.

I was provided roughly 1,000 XML files that I had to import into a SQL database. Sounded easy enough. However, the XML files had no in-line schema, and I was not provided a stand-alone schema so I had to derive one, again using the XML Source components. This would seem fine; however, the schema makes a “best-guess” with relation to the XML file that is presented. It guesses on the datatypes; so in one file, you could have an attribute such as filevalue=”1″ and the schema parsing says that that attribute’s datatype is a Bit. However, the next XML file has filevalue=”2432″. In this case, the import fails because 2432 is obviously not a Bit.

Removing Unwanted Data

Any and all users will tell you “My data is clean!”. But reality often proves different. Eventually, as the project morphed, these individual XML files were rolled into one BIG XML file, formatting, metadata and all. As such, I needed a way to strip all of the superfluous junk out of the file on the fly. Enter — C# StringExtension! I just add this as a script task inside my For Each Loop Container to strip out the junk from each file as it’s processed.

[sourcecode language=”csharp”]
public static class StringExtension
{
private static Dictionary<string, string> _replacements = new Dictionary<string, string>();
static StringExtension()
{
_replacements["<?xml version="1.0" encoding="ISO-8859-1"?>"] = "";
_replacements["<parmExtRoute>"] = "";
_replacements["</parmExtRoute>"] = "";
_replacements["<ReportCacheID>"] = "";
_replacements["</ReportCacheID>"] = "";
// etc…

}
public static string clean(this string s)
{
foreach (string to_replace in _replacements.Keys)
{
s = s.Replace(to_replace, _replacements[to_replace]);
}
return s;
}
}
public void Main()
{
string filename = Dts.Variables["_FileName"].Value.ToString();
string outfilename = Dts.Variables["_FileName"].Value.ToString();
try
{
StreamReader sr = new StreamReader(filename);

string content = sr.ReadToEnd();
content = content.clean();
sr.Close();

StreamWriter sw = new StreamWriter(outfilename);
sw.WriteLine("<Root>");
sw.WriteLine(content);
sw.WriteLine("</Root>");

sw.Close();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
[/sourcecode]

All that needs to be modified is the key dictionary pairs, adding additional pairs for whatever needs to be parsed out. Then, simply call the string extension on any string variable that needs to be cleansed, as I’ve done above. I’ve also taken the liberty to surround the entire XML fragment with a singular <root></root> node, so that everything will be happy.

Now, onto importing the data into our table!

First off, we need some sample data:

[sourcecode language=”sql”]
DECLARE @Students TABLE (
FirstName nvarchar(50),
LastName nvarchar(50),
DisplayName nvarchar(100)
)
INSERT INTO @Students
SELECT ‘Jim’ as FirstName, ‘Bob’ as LastName, ‘Jim Bob’ as DisplayName
UNION
SELECT ‘John’, ‘Doe’, ‘John Doe’
UNION
SELECT ‘Jane’, ‘Doe’, ‘Jane Doe’
UNION
SELECT ‘Yuri’, ‘Tao’, ‘Yuri Tao’
[/sourcecode]

Now that we have our test data, we can attempt to create an XML string for each distinct row. Ultimately, we want our table to end up like the sample below:

We could try our first option:

[sourcecode language=”sql”]
select * from @Students FOR XML RAW
[/sourcecode]

However, this option returns an XML fragment for the entire dataset – not what we’re looking for here. We need an individual XML fragment for each row.

After searching around on the net and a few forums, I was finally able to get an answer:
[sourcecode language=”sql”]
SELECT *
FROM @Students s
CROSS APPLY
(
SELECT
(
SELECT *
FROM @Students t
WHERE t.DisplayName = s.DisplayName
FOR XML RAW
) x1
)x
[/sourcecode]

The CROSS APPLY function basically creates a Cartesian product. In this case, it iterates over each row in our table and produces the desired XML result. However, if you were to run this query as-is on our sample data, you would notice that the XML output, while formatted as XML, isn’t of an XML datatype.

To fix this, simply convert the column, like so:
[sourcecode language=”sql”]
SELECT s.*, CONVERT(XML,x.x1) as ErrorData
FROM @Students s
CROSS APPLY
(
SELECT
(
SELECT *
FROM @Students t
WHERE t.DisplayName = s.DisplayName
FOR XML RAW
) x1
)x

[/sourcecode]

That’s all there is to it! We now have an XML fragment for each row of data in our sample. As always, test the example for performance bottlenecks. I’ve utilized this 50,000 records and it returns in about 1 minute. Not too bad, given that the records are very wide (40-50 columns).

Later, I will write a post detailing querying and shredding XML data into a relational dataset – good stuff indeed!!

Additional Resources:

• Implementing XML in SQL Server: http://msdn.microsoft.com/en-us/library/ms189887.aspx
• Constructing XML using FOR XML: http://msdn.microsoft.com/en-us/library/ms178107.aspx

Error #1:

Failed to create the configuration database.
An exception of type System.Security.Cryptography.CryptographicException was thrown.  Additional exception information: The data is invalid.

Well…great. I received this error after I had successfully installed Sharepoint 2010 and was just starting up the Sharepoint 2010 Configuration Wizard.

After searching around for awhile, I came across a post that said this error is related to permissions on the Sharepoint directory. The Network Service account needs Full Control permissions on it.

So, navigate to %commonprogramfiles%Microsoft SharedWeb Server Extensions14 and give the Network Service user Full Control on that folder and all related subfolders and files.

OK. Got that error out of the way. Now to rerun the Sharepoint 2010 Configuration Wizard. And…

BOOM!! Error #2:

What the heck?! This error proved to be more of a pain to diagnose. But again, Google to the rescue. A little background first though.

My computer is part of our domain here at the office. We have two wireless networks which do not talk to one another (for reasons not important). I found that this little error is related to the fact that the Configuration Wizard is trying to use my user credentials from AD to try and reach out to the Domain Controller for verification/authorization.

However, I was not on the network that had the Domain Controller on it. (My computer likes to randomly change wireless networks without telling me about it). So, all that I needed to do was to hop onto the wireless network with the Domain Controller and rerun the setup.

Woot!! Success!

I finished the Sharepoint Configuration Wizard, error free, and now have Sharepoint 2010 installed and working on my Windows 7 Laptop!

What if we take a step back and look at the code itself? Maybe the code is the problem, not the server performance. Since running across SQLServerCentral in the early days of my BI experience, there were a few blog posts and articles which have stuck with me throughout. One such article, More RBAR and “Tuning” UPDATEs, has been of great help to me.

This article opened up my eyes to a completely different way of thinking when it comes to performance problems within SQL Server. I highly suggest reading it before continuing with the rest of my post here.

I ran into this “tuning” problem the other day when working with some Fact records that I was trying to tie to a Type 2 Dimension. I have about 37,000 school enrollment records for which I need to find the appropriate Student ID surrogate key among 273,000 different student records. It seemed pretty simple enough:

• Link the Fact record to the Dimension record using the Student Number
• Based upon the Fact record’s registration date column, place the record with the correct version of the Student

Act 1

There are two different ways to construct our SQL statement to get this job accomplished: either set-based or row-by-row (see RBAR above) Obviously, one is much more preferred above the other method. Take, for example, the code below (RBAR):

UPDATE [PreStage_FactSchoolEnrollment]
SET [AlternateStudentID] =
(SELECT
    (SELECT TOP 1 DS.[StudentID]
    FROM [DimStudent] DS
    WHERE DS.[EffectiveEndDate] >= [FactSchoolEnrollment].[RegistrationDate]
        AND DS.[StudentSISID] = [FactSchoolEnrollment].[Pupil_Number]
    ORDER BY DS.[EffectiveEndDate],DS.[StudentID])
FROM [FactSchoolEnrollment]
WHERE [PreStage_FactSchoolEnrollment].[ID] = [FactSchoolEnrollment].[ID])

Seems innocent enough, right? However, there is a huge performance issue with this query. Below is a screenshot of one particular piece of the actual execution plan from this query above. Remember our record counts: ~37,000 Fact records and ~273,000 Dimension records.

That’s right…that number circled above is over 8 BILLION rows that were created in memory!! (8,465,578,262 to be exact). This is the base problem with RBAR queries. In essence, this query, as it is currently structured, queried and stored the ENTIRE dimension (all 273,000 records) for EACH of the incoming Fact records (37,000). That is where the 8.4 Billion records are created. Notice that this update took over 48 minutes run. There isn’t an index in the world that is going to help this type of performance monster.

Act 2

Enter set-based SQL. How about we reconstruct this query as a set-based query instead? Look at the differences in the SQL below:

UPDATE PreStage_FactSchoolEnrollment
SET PreStage_FactSchoolEnrollment.AlternateStudentID = ISNULL(b.StudentID,-1)

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY StudentSISID ORDER
BY EffectiveEndDate ASC) as rownum, Pupil_Number, sub_b.StudentID
FROM FactSchoolEnrollment sub_a
INNER JOIN DimStudent sub_b ON
    sub_a.Pupil_Number = sub_b.StudentSISID
WHERE (sub_b.EffectiveEndDate >= sub_a.RegistrationDate)) b
WHERE PreStage_FactSchoolEnrollment.Pupil_Number = b.Pupil_Number

This end result of this query is EXACTLY the same as the above query; the only difference is that this query took all of 9 seconds to return data. Now that’s a performance gain!

Followup

Now, understandably, it may not be feasible to rewrite your SQL code because of different constraints. But, if you can, at all, (and I’m pleading with you here), PLEASE try to rewrite the code itself. You will be surprised at how much of a difference syntax can make!

If this had been any other situation, I would have just gone over to my coworker and asked him to give me a copy of the Log file as well (after having chastised him for giving me a bum backup). However, when this situation occurred, we were at two different physical locations, and I didn’t have a way to get over to where he was.

After googling around for a little bit, I ran across a TERRIFIC post that saved my bacon.

Basically what the guy did is created a dummy database, took SQL offline and swapped the MDF files out, brought SQL back online (effectively putting the database into SUSPECT mode), then put the database into EMERGENCY mode, and had SQL re-create the LDF file. Genius!

I can attest to the fact that this works on SQL Server 2008. My database was 27GB in size, so it may take a while to create the LDF file. For me it took about 30 minutes to recreate the file.

Hopefully this post will help someone else out with the issue that I was facing!

On one report, I knew that my column headers were always going to be the same, no matter what group of data I was running the report against. In this instance, I let SQL Server do the pivot operation.

In another report, I knew that my column headers were going to change depending on the parameters that were passed into the report. In this case, I had no choice but to let SSRS do the pivot.

If I had a choice, I would do the pivot in SQL Server instead of SSRS, if not for simplicity’s sake. I’m more of a coding type of guy. However, there are instances where you cannot avoid a pivot in SSRS. This tutorial will walk you through the steps of setting up a report in SSRS to pivot data on the fly.

First, we need some data to operate with. The data below is what we are going to start with:

CREATE TABLE tmpTable
(
grp INT
,SortOrder INT
,AssessmentTitle NVARCHAR(50)
,AssessmentLevelLabel NVARCHAR(5)
,LevelScaleLow INT
)

INSERT INTO tmpTable
SELECT 1 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘I’ AS AssessmentLevelLabel
,110 AS LevelScaleLow
UNION
SELECT 2 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘II’ AS AssessmentLevelLabel
,138 AS LevelScaleLow
UNION
SELECT 3 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘III’ AS AssessmentLevelLabel
,147 AS LevelScaleLow
UNION
SELECT 4 AS grp
,1 AS SortOrder
,‘Biology’ AS AssessmentTitle
,‘IV’ AS AssessmentLevelLabel
,159 AS LevelScaleLow
UNION
SELECT 1 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘I’ AS AssessmentLevelLabel
,116 AS LevelScaleLow
UNION
SELECT 1 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘II’ AS AssessmentLevelLabel
,153 AS LevelScaleLow
UNION
SELECT 3 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘III’ AS AssessmentLevelLabel
,174 AS LevelScaleLow
UNION
SELECT 4 AS grp
,2 AS SortOrder
,‘Math’ AS AssessmentTitle
,‘IV’ AS AssessmentLevelLabel
,198 AS LevelScaleLow

This is how your table should appear:

This is ultimately how we want our data to appear in the Report:

Since this data can change depending on the Assessment that we pass to the stored procedure, a Matrix is going to be needed in the report in order to facilitate dynamic columns. As I have discussed in an earlier post, a Matrix is needed for situations where dynamic columns are required. Else, you can get by with using a static table.

Now that our data is setup for our tutorial, it’s time to start working with pivoting the data in SSRS.

1. We need to create a new report and add a Matrix dataset to it. Here, I’m assuming that you have already created a data source and populated it with the data from the table above.
2. Grab the Matrix from the Toolbox and drag-n-drop it onto the Report. The Matrix data region provides helpful clues as to where you need to place your fields, specifying Rows, Columns, and Data.
   1. If we look in our table above we can tell that our Rows are going to be the AssessmentLevelLabels with their associated LevelScaleLow values, and the columns are going to be the AssessmentTitles.
3. So, for Rows, select AssessmentLevelLabel, for Columns, select AssessmentTitle, and for data, select LevelScaleLow.

Grouping and Sorting is automatically done when you assign the fields to the Row Grouping and the Column Grouping (AssessmentLevelLabel and AssessmentTitle in our case).

Now, run the report, and VIOLA! Notice that your data is now displayed in the pivoted format!

This approach of pivoting the data in SSRS instead of in SQL Server has a couple of advantages:

• You don’t have to design the report to accommodate every single column. This isn’t such a big deal in our example, since we ultimate only have two columns that end up as output. But imagine if you have the possibility of having 30 columns at any one time. You have to design the report and place out every single one of the 30 columns. What a pain!
• Portability – If you decide to drive your pivoted data from a stored procedure, and you are using this pivot across multiple reports, then you only need to go to one central location to make your changes, and they will all be propagated to each report.

That’s all there is to it! If you have any questions, feel free to leave a comment.

To make sure that you don’t have to unwillingly test your disaster recovery plan, there is one easy setting to change in SSMS that will (hopefully) prevent you from making such a mistake.

A co-worker (other than the one who deleted the tables inadvertently) showed this feature of SSMS one day in a weekly code review that we have.

SSMS provides you information in the status bar regarding the server to which you are currently connected. The only bad thing about this is that no matter what server you connect to, there is no real POP of anything to differentiate between environments. You have to physically look down and see what server you are connected to. See the picture below:

In my job, I connect to Localhost, a Sandbox environment, and a QA environment. It would be nice to have the ability to, at a quick glance (ideally out of peripheral vision), be able to tell to which server I’m currently connected. To accomplish this, follow the steps below.

• In the Object Explorer pane, click Connect —> Database Engine
• Click Options. The Connection Properties tab is now selected.
• Check Use custom color, then click Select to choose your custom color.
  • As a rule of thumb, I use Green for Localhost and Red for anything else (Green = Good; Red = CAREFUL!!)

Now, connect to a new instance of your SQL Server, and open a new query window. Notice the status bar at the bottom of the screen:

And, the nice thing is that these preferences are saved until you change them, even if SQL Management Studio is closed. So, as has been famously coined before, “Set it and forget it!!” Hopefully this little trick will save you from yourself. I know that it’s saved me a time or two!