Wednesday, March 29, 2006

SQL Server 2005 Gotcha #1

So, my company, SFA, jumped into SQL Server 2005 with both feet. We actually upgraded to it on February 3rd, so we've been running it almost 2 months now, and I've decided to share some gotchas that I ran into, in hopes that you won’t have to repeat my mistakes.

SQL Server 2005 Gotcha #1
Compatibility Mode 80 versus 90

When we upgraded from 2000 to 2005, we didn't realize that our databases compatibility modes would stay at 80. Newly created databases in SQL Server 2005 will be at Compatibility Mode 90, but upgraded databases stay at Compatibility Mode 80. I found out the hard way that this was for safety’s sake.

I discovered that my upgraded databases were still in 80 Compatibility Mode when I tried to run one of SSMS's cool new Summary Reports and it complained that it couldn’t display the report because the database's Compatibility Mode was 80 and it needed to be 90. Oh well, no biggie. I figured, I'll just pop in and change that. After all, it's a quick fix. Famous last words!

As soon as I switched it, apps started dying. Eeeek! Errors over here and errors over there. Errors everywhere! The error messages all said ‘Ambiguous column name’.

Here's what I found out.

When you switch to Compatibility Mode 90, you're basically telling SQL Server to recognize all the new keywords and such. You're also telling SQL Server to behave a little differently, i.e. as SQL Server 2005 would behave. One of the new behaviors of SQL Server 2005 is that it wants you to fully specify your columns in your ORDER BY clause if you select a column name more than once in a SELECT statement.

Here’s an example of a query that SQL 2005 didn’t like with the new 90 Compatibility Mode:

SELECT EmployeeName, EmployeeName
FROM (select 'John' as EmployeeName) tmp
ORDER BY EmployeeName;

A simple fix was to remove the extra column (it was in there twice by mistake anyway). If we really wanted to have it in there twice (you never know), we could have also just fully qualified the column name with the table name, like so:

SELECT EmployeeName, EmployeeName
FROM (select 'John' as EmployeeName) tmp
ORDER BY tmp.EmployeeName;

Interesting gotcha, especially because the error message didn’t exactly say, hey, you changed the Compatibility Mode and now I want you to be more precise with your column names in your ORDER BY clauses. Oh how I wish error messages would really tell me what’s wrong. But then, if they did, oh what fun I’d miss out on interpreting them!

More info on compatibility levels can be found here:
http://msdn2.microsoft.com/en-US/library/ms178653(SQL.90).aspx

I hope this gotcha helps someone. I’ve got plenty more to come!

Tuesday, March 28, 2006

INETA User Group Survey - Please fill out!

Below is a user group survey from INETA. If you fill it out, our user group may win a prize! Thanks!

_________________________________________________________________

Subject Line: IT and Developer Research Survey

Dear Member,

This is an invitation for you to participate in a technology-related survey conducted by Lieberman Research Worldwide.

This survey provides a forum for you to share your thoughts and opinions on subjects that are relevant to you and your job. Your participation in this project would be helping improve and create products, services and technologies that would be helpful to you.

We know your time is very valuable so with your participation, you will be entered into a raffle, and we will award prizes to winners at the end of the study.
To participate, please click on the link below to complete a brief survey.

http://surveycomplete3.com/cgi-bin/iagox.cgi?destination=yej0602&organization=1&group=447

If you cannot connect by clicking on the above link, please copy and paste the entire website address into your web browser.

Absolutely all information you provide to us will be kept strictly confidential and will never be tied back to you or your organization. If you have any questions at all, please feel free to email us at sphillips@lrwonline.com.

We look forward to providing you an opportunity to impact new technology developments.

Thank you in advance for your participation.

Tuesday, March 21, 2006

More Local .NET Jobs

Here's some recent job postings from our last sponsor. Enjoy!

APEX Professional Staffing, Inc. – Permanent .NET Opportunities in the Baltimore/DC/NOVA Area

Sr. Software Implementation Engineer Tysons Corner, VA - Permanent – Salary Range: $105,000 + Benefits

http://www.apexpro.com/jobdetail.cfm?jobID=1120


Mid/Senior ASP.NET/SQL Web Developer Timonium, MD - Permanent - Salary Range: $82,000 + Benefits

http://www.apexpro.com/jobdetail.cfm?jobID=1098



Mid-Level .NET Engineer Silver Spring, MD – Permanent – Salary Range: $70,000- $85,000 + Benefits

http://www.apexpro.com/jobdetail.cfm?jobID=1096



Mid/Senior .NET (w/some Open Source) Developer Columbia, MD – Permanent - Salary Range: $75,000-$82,000 + Benefits

http://www.apexpro.com/jobdetail.cfm?jobID=1139


Mid-Level VB.NET Developer Columbia, MD – Permanent – Salary Range: $75,000 + Benefits

http://www.apexpro.com/jobdetail.cfm?jobID=1140



Mid-Level C# Developer Hunt Valley, MD – Permanent – Salary Range: $75,000 + Strong Bonus + Benefits

http://www.apexpro.com/jobdetail.cfm?jobID=1119

Monday, March 20, 2006

New SQL Server 2005 Express Virtual Hands On Labs!

Check this out! You can try SQL Server 2005 for free really quick with no install! Just click this link and you'll be set up with a quick 20 minute lab that runs in your browser. No need to do a full SQL Server 2005 install and you'll be learning it in no time.

http://go.microsoft.com/?linkid=4659724

I love the push for more and more quickie learning resources. MS realizes that, for the most part, we are trying to grasp this stuff whenever we have a free moment and most of those moments are less than 1/2 hour, so these 10 minute "nuggets" presentations, Grok Talks, and now these Expresss HOLs are just the ticket! And did I mention they were free?!?

Up next (I wish), free, quick (like 5 minutes) e-learning modules? We'll see!

Saturday, March 04, 2006

Launch Meeting a Huge Success

Our Visual Studio 2005 and SQL Server 2005 Launch meeting was a hit! We had almost 30 people show up to participate in the party-filled atmosphere. We had a great time watching the geeks in the video get us pumped up about VS and SQL 2005 whilst we ate our pizza, we learned a bunch of some of the new features in both VS and SQL 2005, and we had a blast (literally!) during the finale where we collected the answers to Mega Prize Pack give-a-way questions and disributed the prizes (which were balloons that had to be sat on to be popped to find out what you had won).

What a night! Thanks to everyone who came out and made it such a great time!