The Power of Stupid...

Stupid mistakes can just make you feel like an idiot sometimes. At my new job I wasted nearly 2 hours trying to figure out why some very simple C# code was not working properly. The idea was to execute the code inside of an SSIS package so that it updates a WSS 3.0 Issue Tracker list. It was simple stuff, but it took me far too long to realize that I was logged in on the wrong domain.


It was not until I wrote the same code in PowerShell that I actually got an error that made sense: “Access Denied.” I mentioned this to my boss who asked me which domain I was logged in under. Then I typed whoami and quickly realized what a dummy I had been.

Chinook Database on CodePlex

The other day I was browsing around CodePlex and found a wonderful tool for learning SQL Server or for integrating your applications with SQL Server. The Chinook database, while small, has some cool appeal to it. It has all the major components you need. You can work on reporting for invoicing, sales, inventory, just about anything. You can download it at http://chinookdatabase.codeplex.com/

How to Remove Some Thing from a Column’s Data

Here was a problem I was faced with today. A friend of mine was experiencing issues with two databases not syncing properly because one had a column (City) that contained a special character (a single quote). We needed to craft a query that would remove the single quote without changing any other portion of the column's data. There were two considerations that needed to be made:

1. Ensuring the single quote was properly escaped so that it did not cause an error at run time.
2. How to properly place the REPLACE function inside the UPDATE command.

It took me about 30 minutes to figure it out but here is what I came up with.

UPDATE [ARCUSTOMERS] SET City = REPLACE(City, '''', '') FROM [ARCUSTOMERS] WHERE City LIKE '%''%'

Getting Back to Basics

In any art or profession one can never review the fundamentals too frequently. For this reason I am looking at doing some posts on the basics of relational theory. Not so much for the sake of my readers' (Hi, mom!) but more for my own sake. Here is what I currently have planned:

Fundamentals of Relational Theory and Database Design
Fundamentals of T-SQL Querying (2005 and 2008)
T-SQL Queries and DMVs

The third item is really one of my favorites, but I feel I have not put as much effort into it as I should.

Excel Services and PowerPivot for BI

I am really impressed with the enhancements in Excel services and the addition of PowerPivot to SharePoint 2010.  I'm going to have to write some sort of top 10 article on the two of them. Here is an example of a dashboard I wrote for the Shceduling Department and the Production supervisors published in Excel Services. The data is driven via T-SQL queries. The datasource can even be refreshed. I'm just amazed.

Stupid SharePoint 2010 Error:The Installation of this Package Failed

If you are trying to install SharePoint 2010 and you get the error message shown in the following image, you will need to redownload the installation file and try again.

I wasted an entire afternoon trying to figure out why I was getting the error above. I had installed all of the prereq's and my Server 2008 R2 was up-to-date. The executable would not even fully extract, so there was no error log or anything to help me troubleshoot. I hope this post saves someone some time.

Using sp_executesql for a Script Based “Function”

On the SQL Team forums a user was requesting a way to have a function run from inside a script, because he was unable to create a UDF as he lacked the required permissions to do so. My suggestion was to use sp_executesql. The syntax is a little hard to understand. Here is an example.

DECLARE @myVar AS INT DECLARE @test AS nvarchar(500) DECLARE @paramDef nvarchar(500) SET @paramDef = N'@myParam INT' SET @myVar = 10248 SET @test = N'USE Northwind; SELECT * from dbo.Orders where dbo.Orders.OrderID = @myParam' EXEC sp_executesql @test, @paramDef, @myParam = @myVar;

The syntax is explained below.

EXEC sp_executesql @variable,                    --This represents the SQL code to be executed.                    @parameterDifinition,                    --This describes the parameter. Is it a varchar,                    -- int, or some other type.                    @valuePassed                    --This holds the value you wish to pass to the                    -- parameter.