Browsing the blog archives for October, 2009

INSERT to a table with an IDENTITY Column

I’ve been a SQL Practitioner since 1996 when 6.5 was the hot item.  Along the way I’ve learned a bunch of stuff about SQL Server.  Without a doubt, the more I learn, the more I know what I don’t know.  Being humbled on a regular basis is probably a good thing, but sometimes it’s just […]


Speed Up Startup of SQL Server Management Studio

Have you ever noticed how long it takes to start up SQL Server Management Studio (SSMS)?  I always assumed it was because it is the biggest and most powerful tool in my toy box and it deserves the load time. You can SIGNIFICANTLY reduce the load time by avoiding Microsoft’s marketing message at startup, the […]

Table Partitioning via the Clustered Index

Today’s post will demonstrate how to partition an existing table without disturbing the existing data in the table by taking advantage of it’s clustered index. SQL Server Books Online (BOL) is laced with interesting tidbits of information but sometimes falls short of delivering on the specifics.  Let’s face it, there’s no way to demonstrate every […]

7-Zip Output Argument

This is a real quickie but it cost me dearly this weekend.  My task was to call 7-zip from within SSIS and extract the contents. Here’s the basic example that’s all over the Internet: 7z e -oC:\Doc Where: 7z is the executable e is the argument to perform an extract is the file […]


Column DIFF between two tables

I recently had a situation where I needed to compare table columns between the production table and a test/development table.  Chances are if I needed to do it someone else will to. I like using the EXCEPT SET OPERATOR.  Some people might prefer to use an OUTER JOIN to determine the column differences.  With the […]

Use a View to Control the Size of UPDATE Transactions

In several of my production databases I have to deal with tables that have hundreds of millions of records.  Querying these tables are challenging; doing updates to them can be nightmarish. I recently had to add a column to a table that had 65 million records.  The table is an aggregate table so the new […]

Download a file from the web using SSIS

There are some tasks, no matter how seemingly simple, I just would not want to have to do again.  Today’s post is about one of them… At work we use a data cleansing tool that purchased from the USPS (Post Office) called NCOA (National Change of Address).  All of the big list brokers use the […]

1 Comment

Write Row Count to Flat File Using SSIS: Part III

This is the third and final part to this thread on how to capture the number of rows being processed in SSIS to a flat file. In Part I we learned how not to do it and why it didn’t work. In Part II we showed how to use the ROW COUNT transformation to populate […]

Write Row Count To Flat File Using SSIS: Part II

In yesterday’s post we pulled a Thomas Edison and showed one of 10,000 ways how not to write the number of records being processed using the ROW COUNT transformation. We thought the process was straight forward, but it didn’t work.  Our approach was: 1.  Create a query that processed some records. 2.  Send the result […]


Writing Row Count to Flat File in SSIS: Part I

Yesterday I had a simple enough task at hand.  An existing EXTRACT that loads a result set to a DATA file needed to have a corresponding COUNT flat file that contained the number of rows in the extract’s data file.  Not being the most savvy at SSIS I did what anyone else would do get […]


My Five Reasons for Blogging

My wife of 24 years recently asked me “Why blog?  Are you looking for a new job?” At first I didn’t have a good answer for her other than “If I were looking for a new job it would be too late for blogging”.  After all, nobody blogs their way into a job overnight.  If […]


My Contribution to the TSQL Challenge #14

What does one do on a Friday night after going out with some friends and having a couple of mugs of suds?  How about doing a T-SQL puzzle? Jacob Sebastian and a team of MVPs and high-powered database gurus are running a series of challenges.  It’s the closest thing to getting an assignment in college.  […]


How Long Are Your Jobs Running?

Today’s post is going to be short and sweet.  About a year ago I put together a procedure that allows me to see the run times of all of the SQL Agent jobs on a database server.  Some might argue that I got a little carried away when I added the standard deviation and variance […]