Archives

Writing T-SQL Merge Statements the Right Way

In a previous article, I discussed Merge statement basics. However, in extensive testing I’ve come to realize that my article, like most articles I’ve read about Merge leaves out or mis-handles several important aspects. Rather than edit that article, I’ve decided to publish a series of articles which I hope will clear up some of […]

Presenting Tonight at North Texas SQL Server User Group

Hey folks, just a quick message that I’ll be presenting this evening at North Texas SQL Server User Group. I’m presenting an updated version of “Have you got the Urge to Merge?” which I gave last month in Fort Worth. I’ve made good progress with my code which generates T-SQL Merge Statements and will be […]

State Name and Abbreviation Lookup Table Script

This is a quick script which creates a table consisting of State Names and their corresponding Abbreviations. I use this table when doing basic data quality checking and also standardizing state designations.

CREATE TABLE dbo.StateLookup
(
StateID INT IDENTITY (1, 1),
StateName VARCHAR […]

Helpful Date Queries Which I Commonly Use

As I’ve mentioned before, I think I’m the most avid reader of my blog. I use it to document my work so that I don’t have to constantly re-invent the wheel. In this case, I find myself repeatedly searching for these scripts, often to populate SQL Reporting Services parameter defaults, so I thought I would […]

User Defined Data Type Definition Script

Recently I worked on a project where every data type was user defined. In case you weren’t aware, SQL Server supports the use of User Defined Data Types. These are custom data types which are based on the standard types. For example, if your data always uses a two character string for states, you may […]

Interesting Integration Services (SSIS) Error and Solution.

While troubleshooting an existing 2008 R2 package the other day I received the following error.

Error HRESULT E_FAIL has been returned from a call to a COM component.

The error occurred when I was using breakpoints and watch windows to diagnose problems in the package. The breakpoint behavior was off because even when there wasn’t a […]

How to Pass GetDate() as a Stored Procedure Parameter

The Problem

When you pass GetDate() as a parameter to a stored procedure, the following error is returned:

EXEC dbo.ProcDate GetDate()

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’

When I ran into this problem, I found this helpful article which suggests that I should assign the value of GetDate() to variable and then pass […]

Creating a Date Table/Dimension on SQL 2008.

In a previous article, I listed the benefits of using a dedicated date table and included a customizable script which enables you to quickly create your own version. One of my readers pointed out that he uses the date datatype, rather than using the smart integer key method, when working with SQL 2008+ databases. The […]

SQL Rally 2012 is Coming to Big D!

Last week PASS announced that Dallas and the North Texas SQL Server User Group have been awarded SQLRally 2012. This is great news for all of the nearby PASS User Groups and DBA’s.

We’ve put on three successful SQL Saturday events in 18 months, and I’m proud to have been on the planning […]

Date vs Integer Datatypes as Primary Key for Date Dimensions

Every Kimball Group book I’ve read, as well as every Data Warehouse class I’ve attended, has indicated that a Date Dimension Primary Key should be a smart integer key in the format YYYYMMDD (20110518) so I’ve always built my Date tables that way. However, Barnaby (Blog/Twitter) pointed out that I should be using a Date […]