I have spent the last few weeks thinking about what the new year and the new decade will bring. I am happy to leave 2009, which was a strange year, at least I felt strange about it. Not much was accomplished in my civilian career because I spent much of the year deployed to Kosovo with the National Guard. It was an interesting experience, but it put me behind in building new skills for my civilian career. So….2010 is the year to jump start my career. Fortunately, I do have a good job, one that will provide excellent experience for me in the coming year.
So other then doing my day to day work at my job, what I am going to do to jump start and beef up my SQL Server skill set? I will focus on the following 3 items:
- Increase my blogging. My goal this year is to post at least once per week, 52 times this next year. Writing about a subject is a good learning experience, at least for me.
- Take and pass the two exams for SQL Server 2008 development, 70-433 & 70-451.
- Attend at least 4 in-person events relating to SQL Server. This could be a code camp, PASS, local user group, or some classroom training. It just has to be in person and related to SQL Server in some way.
So, here’s to a great new year!
Now that I am back home from my deployment to Kosovo, I’ve gone back to work. My first day back was Monday, December 7th. It was nice to come back, but I was a little nervous that first day thinking about what changed while I was gone. Now that I’ve been back for 4 days now, I feel a bit silly that I was nervous last Monday…there are many good changes. Most of our servers are now running SQL Server 2005 and we have a major upgrade project that is being developed on SQL Server 2008. It’s nice to be moving off SQL 2000.
It feels good to be back. 2010 will be a great year!
Just the other day I received a call from a developer with a database problem. He was receiving timeouts when he called a particular stored procedure from his application. So I took a look at the proc, reviewed the code, and tried to run it. It just ran and ran and ran, no error messages, nothing. Then I tried to recompile the proc, same thing, just hung. So I stopped it, looked at the amount of data it needed to run, not much data involved, so I cut the proc into pieces and ran it a chuck at a time. Finally, I got to the problem part of the code, the part that just hung. In reading the code, I noticed that it was referencing a linked server, hmmm, where does this point to? So, I go into the Client Network Utility and here we are, an old server that no longer exists! Change the server name and port number and yes! The proc complies and runs! Weird, wonder why it just didn’t just return an error message. Oh well, all in a day’s fun!
Notice the lack of table aliases for the select list:
Select FirstName,
LastName,
c.ID,
Address1,
Address2,
State,
Zip,
Phone1,
Phone2,
Email,
RegionName,
EmployeeName
FROM Customers c
JOIN Location l
ON c.ID = o.ID
JOIN Contact c
ONc.ID = o.id
WHERE c.ID = @ID
Don’t do this!
If this was a database you just inherited from the last guy (or gal), it might not be obvious which column goes to which table. So you have to take the time to figure that out before you can make the necessary changes. And that is just wasted time.
If you are writing a multi-table query, please use table aliases on every column in the select. The next person to work on the code will thank you.
Here is another difference between DTS & SSIS that will trip you up if you are not expecting it. The issue deals with loading flat files with varying numbers of columns. For example the 1st row has 5 columns, then the 2d row has 3 columns, and then the 3d row has 4 columns. DTS handled this by always looking for the row delimiter first, whereas SSIS expects to always have the same number of column delimiters in each row. Here are some great posts that can explain this better than I can:
http://blogs.conchango.com/jamiethomson/archive/2006/07/14/SSIS-Nugget_3A00_-Extracting-data-from-unstructured-files.aspx
http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_–Flat-File-Connection-Manager-issues.aspx
http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
