WCF or ASP.NET Web API – Which to use?!

WCF or ASP.NET Web API – Which to use?!

Found this interesting article when deciding whether to go with WCF or the newer ASP.NET Web API framework when creating new web services. the Web API looks good as the way to go when creating RESTful services, without all the complex and verbose configuration that comes with WCF. However, along with that you lose the extra configuration WCF provides you with, such as multiple transport types, duplex communication, message queues etc. So it’s definitely worth considering what the service will be…

Read More Read More

Repeating Matrix Column Headers in Reporting Services

Repeating Matrix Column Headers in Reporting Services

Today my afternoon was spent going through a set of standard reports used by some of the customers that I work with, checking layout, formatting etc. I’d noticed that when a matrix spans over multiple pages, the column headers are not repeated. Foolishly I thought this will be a simple fix, surely just right clicking the group on the group selection at the bottom of report builder, then selecting repeat header… However, sometimes this isn’t the case, turns out you…

Read More Read More

COM woes – Retrieving COM class factory failed due to the following error: 80040154

COM woes – Retrieving COM class factory failed due to the following error: 80040154

I spent ages trying to figure out this problem, when trying to reference Microsoft Office Interop libraries in my project (For manipulating an Excel file programmatically). Turns out, the dlls I was trying to reference were 32-bit, and the platform my project was set to run on was 64-bit. As I was building this project on a 64-bit machine, Visual Studio set the default platform to be 64-bit. This is a simple fix: Go to the “Project” menu, select “Properties”, then click…

Read More Read More

Create SQL Table from .NET Datatable in C#

Create SQL Table from .NET Datatable in C#

In one of my recent projects I needed to generate a datatable in C# whose fields could vary depending on which properties of a variable needed to be exported from the system. It needed to be able to generate the SQL CREATE TABLE script on the fly and create the table in the database, and then populate the table with the data to export, either using a SqlBulkCopy object or on a row-by-row basis. The tutorial below provides a good…

Read More Read More

Dynamically Build SQL MERGE statement in SSIS

Dynamically Build SQL MERGE statement in SSIS

http://www.sqlservercentral.com/articles/EDW/77100/ I came across this the other day, when I was looking at using the SQL MERGE statement to replicate the functionality of the Slowly Changing Dimension task in SSIS. There are many benefits to using MERGE instead of the SSIS task, one of my favourites being only having to run 1 SQL statement for the whole task, instead of one for each Lookup, and then subsequent UPDATE or INSERT statement. This link however, takes it one step further by…

Read More Read More

Debugging MDX Queries from SSRS using Profiler and SQL Management Studio

Debugging MDX Queries from SSRS using Profiler and SQL Management Studio

At work recently I came across the need to assess the MDX queries used by some of the reports on our reporting server, along with the parameters that were passed into it (as most of the queries are powered by parameters that are passed into the MDX by parent reports) Unfortunately, you can’t use SSMS to run parameterised MDX queries like you can in SSRS, but the tutorial below gives a good example using SQL Server Profiler, capturing traces of executed…

Read More Read More

Identifying cause of duplicate attribute keys in SSAS

Identifying cause of duplicate attribute keys in SSAS

http://ms-olap.blogspot.com/2009/11/duplicate-attribute-key-has-been-found.html At work today, I was a bit stumped when SSAS reported the cube wouldn’t build, due to a duplicate key found in the source database, although having checked this there was none to be found. However after consulting with a colleague, and finding this article on the internet, it turns out that there was a white space after the value of an attribute in the source DB. After SSAS runs the DISTINCT query on the source DB, it trims…

Read More Read More