Add a new Data Source to an RDL

How To Connect SSRS Report Builder to a PowerPivot Excel file (Sharepoint)

So, you’ve finally got around to setting up that SQL Server 2012 environment and you’re playing about with PowerPivot and SQL Server Analysis Services.  You’ve set up your sharepoint site and you’ve created and uploaded a new spreadsheet using PowerPivot that contains all that tasty cube data.  However, in your haste, you haven’t set up SQL Server Reporting Services Report Builder on the Sharepoint site, so when you open Report Builder on a separate server (or from a local install), and want to connect into your new quasi-cube (via your PowerPivot Excel file), how do you do it?  The Report Builder side of things is not too dissimilar to using a traditional SSAS cube as a Data Source.

The answer, is deceptively simple if you know where to look. Continue reading…

SQL Saturday #105 Dublin Review

As I mentioned in my last post, I was recently over in Dublin attending SQL Saturday 105, a SQL Server conference and networking event hosted by PASS.  For anyone considering attending a future SQL Saturday event, I can definitely recommend it for developers, Database Administrators (DBA) and Business Intelligence (BI) developers of any level, from enthusiastic amateurs all the way to expert consultants.  Given the fortunate timing on Saturday’s event, it also doubled as the European technical launch for SQL Server 2012 (codenamed Denali during development).

Featuring a total of six sessions covering 3 tracks (Developer, DBA and BI), the event was packed, as over 200 SQL Server professionals from all over Ireland (as well as our intrepid 3 from Edinburgh) flocked to learn about some of the new features of SQL Server 2012 or just to pick up some tips and tricks from some of the industry’s best. Continue reading…

SQL Saturday #105 In Dublin

As it stands, I’m currently getting organised to head to SQL Saturday #105 which takes place tomorrow (March 24th) in Dublin.  This is the closest SQL Saturday event to Edinburgh so far, so I jumped at the chance to go.  SQL Saturday is a regular event, created by the Professional Association for SQL Server (PASS) to provide SQL Server professionals and enthusiasts with opportunities to network with colleagues and experience some top quality training, while also being able to grow their own membership.

Continue reading…

SQL Server Job Agent fails SSIS package with error: Option “/CALLERINFO” is not valid

I was editing some pre-existing SQL Server Agent jobs the other day, adding some interconnectivity and changing steps around as I’ve done hundreds of times before.  These job steps are used to execute SSIS packages (SQL Server Integration Services) on the server and have been running for months (years?) without fail.  However, after I made my changes they started failing on the steps I had edited, throwing the bizarre error:

Option “/CALLERINFO” is not valid.  The command line parameters are invalid.

Continue reading…

Reverse the order of words in a string in SQL Server

I recently found myself with a situation in SQL Server where I had a string consisting of a series of values (constructed using the CLR function for concatenating values that I wrote about last week) that I needed to reverse.  Unfortunately, it’s not as simple as using SQL Server’s built-in REVERSE function.  That actually reverses each individual character in a string, rather than switching the order of words. Continue reading…