Who Locked My Table?

I have a couple of big stored procs that process data to a data warehouse. Sometimes they lock up the destination table and makes the reports hang. So I need to find out who locked the table and kill the process. Our DBA is kind enough to provide a query to find that informatoin:

select CASE WHEN tl.resource_type = 'OBJECT' THEN object_name(tl.resource_associated_entity_id)
WHEN tl.resource_associated_entity_id = 0 THEN 'n/a'
ELSE object_name(p.object_id) END as 'entity_name'
, tl.*
from sys.dm_tran_locks as tl
LEFT JOIN sys.partitions as p
on p.partition_id = tl.resource_associated_entity_id
where resource_type <> 'DATABASE'

I love DBAs.

T-SQL Error Handling

http://www.sommarskog.se/error-handling-II.htmlc

Merged colums in Excel exporting

I ran into a situation today that when I export a report to Excel there are unwanted merged columnns. After some digging I came across this post. It was very helpful.

http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx

Compare Strings in MDX

"using the MDX filter () function and the vba text functions you have plenty of search possibilities

filter (DimName.children, instr(DimName.currentmember.Properties("Name"),"MyValue")>0"

The full post is at:
http://www.developmentnow.com/g/112_2004_3_0_0_407650/MDX-equivalent-to-SQL-LIKE-operator.htm

Warehouse and Cube Changes in TFS 2010

The over view:
http://blogs.msdn.com/sunder/archive/2009/05/16/team-foundation-server-2010-relational-warehouse-and-cube-schema-changes.aspx

How the existing reports are affected:
http://blogs.msdn.com/aaronbjork/archive/2009/05/18/team-foundation-server-2010-where-are-my-reports.aspx

Reports update from 2008 to 2010:
http://www.socha.com/blogs/john/2009/05/upgrading-visual-studio-team-foundation.html

Pass Parameters via URL

I had to do some research to find out why my URLs did work. In short, the URL should point to http://localhost/ReportServer/ instead of http://localhost/Reports/, which is the Report Manager. Here is the post that helped:
http://dobrzanski.net/2008/08/11/reporting-services-problem-with-passing-parameters-directly-in-the-url/

Here is another good post about passing multivalued parameters:
http://bobp1339.blogspot.com/2007/10/passing-ssrs-report-parameters-in-url.html

Execution Policy Issue When Running a PowerShell Script from Team Build

I was trying to run a Powershell script as a step in a Team Build. To do that, I created the Powershell script, and called it in Team Build via an EXEC task. The execution policy was set to Unrestricted on the build server manully. I kept running into the permission error when the build ran ("cannot be loaded because the execution of scripts is disabled on this system. ").

After strugging with it for two days, I decided to put the set-executionpolicy in the Team Build to set the execution policy on the fly. And that fixed the problem.

Why did that work? After some digging (basically adding another powershell command to find out what the current user is.), the Team Build uses the same service account as the one we remote desktop in and set the policy manually. So the only theory that can explain that is the same account has different security settings in team build and remote desktop in. I still need to prove that.