SharePoint ULS Logging for Better Troubleshooting

I started running into problems with Correlation IDs in SharePoint 2010 not showing up in my ULS logs.  Here's how I ended up logging all of the details for a repeatable issue.

• Go to Central Administration
• Select Monitoring
• Select Configure Diagnostic Logging from the Reporting area
• Check the ALL checkbox at the top
• From the drop-downs select:
• Least event to report to the event log: Error (you can play with Critical vs. Warning as well)
• Least critical event to report to the trace log: Verbose (this will give you the full trace log for that Correlation ID)
• Replicate your issue and note the Correlation ID
• Before you do anything else, go back into the Configure Diagnostic Logging screen
• Check the ALL checkbox at the top
• From the drop-downs select:
• Least event to report to the event log: Reset to default
• Least critical event to report to the trace log: Reset to default
• This will make sure that your logs don't explode while you're troubleshooting.

Hope that helps!

An Old Tablet, VPN, OneNote and SharePoint begins...

Some background... I have an old tablet, at least five years old running XP Tablet Edition, so it falls into the BYOD (Bring Your Own Device) category at work.  The only way I can connect is via F5 Networks (VPN).  I have Office 2010 installed on it with OneNote.  We have SharePoint 2010 at work with MySites.

Where it begins... I finally got around to getting Office 2010 installed on an old XP tablet I had lying around.  I fired it up, attached to my work network from home via F5 (VPN) and started playing around with OneNote and my SharePoint personal site.  I had already created a few OneNote notebooks a while back, so I opened these up and started playing around.  Some pretty cool stuff!  The script to text function really grabbed my attention.  Starting tomorrow at work, I'm going to see how well a BYOD (Bring Your Own Device) works via VPN and if entering passwords a bunch of times per day is going to be a major pain (I hope to figure a better way to cache credentials).  Plus, I'll need to see how long this battery is going to last in the real world.  I think this tablet is at least 5 years old...

I will be posting my travels on here about my "ancient" tablet, VPN, OneNote, SharePoint and how it's all working together.  I hope my results are good, because OneNote has been pretty neat from the short amount of time I was able to look at it.

Keep checking back for updates!

SharePoint Integrated SSRS Report Filtered on Logged-in User

I ran into a requirement for some charting on a SharePoint site that the various chart parts out there just were not solving.  I had already created some integrated SSRS reports for the site, so I figured I would just keep going down that path and keep things consistent.  However, this chart posed a new problem for me... pass through the current user and filter the report based on that.  In other words, I'm logged in as Cory Swartz.  I only wanted to see Cory Swartz's results on the chart.  After a few days of searching, tweets on #SPHelp, etc., I finally figured it out.

First, I started out with the Data Source on the SharePoint Report Library:

The key with the data source is that it is using Windows authentication.  This will allow us to match up the IDs later on.














Once we have the data source configured, we can build our report.  I used Report Builder for this.  One thing that I learned a while back with IE9 is that you need to change your Browser Mode to launch Report Builder from the browser.  Just a tidbit for those new to this.  To switch the Browser Mode, hit F12 and then change over to Internet Explorer 8.  Then you'll be able to open Report Builder directly from the browser.

Ok, so back to the report. 

You'll need to create your Data Source.  For this, you will select the Use a shared connection or report model option.  You will need to browse out to your SharePoint site via the Data Source dialog box and select the Data Source we created in the Report Library (or other location).

Next up is where the rubber meets the road.  Create a new Dataset.  You can name it whatever you'd like.  I used the Use a dataset embedded in my report option.  Select the Data source we created in the above step.  Then I used the Text Query type.

Here you can see the details.  The real key for this report is in the Query.  I highlighted the starting point of the <Query> region.  This is what's going to determine the User-filtered results.












Here's the detail of the entire Query:

In this example, my Assigned To field is a user lookup in SharePoint.  Using LookupId='TRUE' it will find the ID of that user.  Then, using the <Eq> operator, it will match it to the <UserID/> and only return those results.

Then I was able to build my chart all nice and pretty in Report Builder and publish it like any other SSRS report in SharePoint.  Now that I know what steps to take, I'll be able to meet their report needs very quickly and produce nice looking charts and graphs.

Thanks to @VanVlaenderenP for taking time to answer all of my #SPHelp tweets!

On to SharePoint!

So as you can tell by the new name, I'm going to start posting about my adventures in SharePoint.  I've been working with SharePoint for about 8 months now.  I'm diving into a 2007 to 2010 migration, Project Server, and rolling out the BI stack (Excel Services, PowerPivot, PerformancePoint, SSRS, etc).  It's going to be a busy year, and I will be trying to post things here and there that I find useful.  I hope those things are useful to you as well.

Thanks for reading!

Create a dynamic incremented folder in SSIS

So I have a SSIS package that needs to put files it generates in a new, incremented folder.  Here's what I ended up doing:

• Created a counter table with one column "dfgcounter"
• Created a variable in my package named DFGCounter
• Created a "New DFG" File Connection
• Created a ConnectionString expression: ''\\\\servername\\subfolders\\CCMSI"+ @[User::DFGCounter]
• Created an Execute SQL Task
• Selected MAX dfgcounter and put it into Result Set mapped to Variable User::DFGCounter
• Note: the value will not show in the variables window... caused me to think there was something wrong with the SQL Task
• Created a File System Task
• Operation: Create Directory
• Source Connection: "New DFG" File Connection that was created earlier
• File System Tasks moving the file(s) to the New DFG File Connection
• Data Flow Task
• Derived column taking the DFGCounter + 1 and inserting it into the dfgcounter table

Missing ODBC System DSN Connections

So today at work, we ran into a problem with System DSNs just walking off of some computers.  After checking out the interwebs, I came across some forum posts covering the issue.  Here's what we had to do to restore the System DSNs that went missing:

Open up regedit and browse to
HKLM\SOFTWARE\ODBC\ODBC.INI
Then look for ODBC Data Sources key... if it's not there, that's what your problem is.
Recreate the ODBC Data Sources key then add Strings for each of the ODBC connections that is showing under the ODBC.INI key.  You will have to assign the driver name to each of the Strings you just created.  For example, I created a TESTDB String (which is also a key under ODBC.INI) in the ODBC.INI key.  Then the value I assigned to it was SQL Server.  The SQL Server value is the driver name.  The driver names can be found under the ODBCINST.INI key right below ODBC.INI.

Adding Parameters to the Report Title in SSRS 2005

So in SSRS 2005 I needed to add my parameters to my report title.  In Crystal Reports it was pretty simple, just drag and drop the parameter into the report title and it would do the work for me.  In SSRS I needed to build an expression.

So click on the Report Title and in the Properties, click on Value and the option.  In the expression type something like this:

="Report on Stuff between" + Parameters!Start_Date.Value + " and " + Parameters!End_Date.Value

If my Start Date was 02/01/2010 and End Date was 02/18/2010 the report title would be:

Report on Stuff between 02/01/2010 and 02/18/2010


Works great once I learned how to do it.