Tag Archives | SQL Server 2005

Upgrading from SQL Server 2000 to 2005

This is part one in a series of upgrading from SQL Server 2000 to SQL Server 2005. The first question I want to address is why would you want to upgrade in the first place. It’s important to know why you are doing something if you are going to be able to truly understand what you are doing. Most of my opinion and experience comes from the admin DBA side of the fence so you won’t find a ton of developer jargon thrown around because that’s not a strong point for me. I’m in the process of upgrading from SQL Server 2000 to SQL Server 2005 as well, so I hope I can chronicle the journey and help others if they are in the same situation.

It goes without saying that the biggest reason for upgrading is that the product is basically End of Life. While you can still get extended support through 2013 it would still be wise to start the process of moving forward. There are plenty of resources on the internet about the new features that were introduced or approved upon with SQL Server 2005. Since I am more of an administrator I get a little more warm and fuzzy about Table and Index Partitioning, All Permissions Grantable, Mirroring, Snapshots, Online Restores, and Performance DMV’s.  While the new data types can be very useful and I’m sure CLR has its place it is hard for me to get as excited about those items. A full list of features can be found by digging through Microsoft’s full list or an abbreviated Top 30.

For us, SQL Server Integration Services this was one of the biggest hurdles to overcome because of the number of DTS packages that we are dependent upon. Microsoft has completely redone the way that data is moved from the ground up and there is a learning curve. We eventually decided to purchase a few licenses of a third-party piece of software to help with the package transition.

There’s also no more Enterprise Manager, and for the most part that’s a good thing. Visually, it’s much more appealing and it’s worth running a client to access SQL 2000 instances to do most things. This late in the game I’ve even been running a SQL 2008 client because of the added features that it gives me as an administrator.

Coming in part 2, Upgrade Education and Fact Finding.

Resources:

Landing page for SQL Server 2005
Microsoft whitepaper on “Why Upgrade to SQL Server 2005”
FAQ: Why Upgrade to SQL Server 2005 now
What’s new in SQL Server 2005
SQL 2005 Books Online : What’s New
Top 10 new features in SQL Server 2005
Consider what’s new in Microsoft SQL Server 2005
New T-SQL Features in SQL Server 2005 Part 1
New T-SQL Features in SQL Server 2005 Part 2
The Differences Between SQL Server 2000 and 2005
The Differences Between SQL Server 2000 and 2005 – Part 2

Comments { 2 } Posted on September 16, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication

Problems with SQL 2005 Upgrade Advisor

For those of us that are still in situations where we deal primarily with SQL Server 2000, there should come a time when we have to run the Microsoft SQL Server 2005 Upgrade Advisor. Some of you may be going straight to SQL Server 2008 and this issue may be resolved in the SQL Server 2008 Upgrade Advisor.

During the initial run of the wizard, an error will pop up without giving you many details. In my case, I look at the event viewer and see the following:

.Net 2 Runtime Error

.Net 2 Runtime Error

If you browse the internet you’ll find various ideas to fix it. Some suggest removing all .Net versions and reinstalling them. Some suggest not putting on .Net 2 SP1 until you run it. Another suggestion is to change the Aspnet.config file to read <legacyUnhandledExceptionPolicy enabled=”true” />. None of these suggestions worked for me.

The fix that got me working was to copy all of the files from within the \BPA\Bin folder and paste them into \BPA\.

I think I would try this first before going through all that other trouble. If this doesn’t work you can always explore the other options.

Resources:
http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/2a5f460b-8684-4510-8fcb-1e9d9786baff/
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/390e8a17-bd66-4426-b24c-d0a5cc009053
http://www.sqlservercentral.com/Forums/Topic561810-5-1.aspx
http://support.microsoft.com/kb/911816
http://sqllearnings.blogspot.com/2009/04/microsoft-sql-server-2005-upgrade.html
http://devel.virtage.com/2009/01/en-crashing-sql-server-2005-upgrade-advisor-error/

Comments { 1 } Posted on August 5, 2009 in SQL Server 2000, SQL Server 2005, SQLServerPedia Syndication

SQL Server 2005 won’t install

A colleague of mine had tried to install SQL Server 2005 several times onto a x64 server. He kept gettting the following error:

The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, “How to: View SQL Server 2005 Setup Log Files” and “Starting SQL Server Manually.”

After searching through the logs and trying multiple items I finally ran accross a fix for our case:
http://support.microsoft.com/kb/954835/en-us

From Microsoft, this can happen when the ratio between logical processors and physical sockets is not a power of 2.

So how do you know if you are affected by this?

This was on a Dell PowerEdge R900, so if you are working with that then you may be in the same situation.

You could download and run WinAudit or cpuz to see if you can determine anything from the results. You could also possibly query wmi.

Comments { 0 } Posted on June 26, 2009 in SQL Server 2005

Duplicate ErrorCode Columns in SSIS

If you are using SSIS to handle a column that has the name of ErrorCode you could run into an issue. You can find more information on the specifics @ Microsoft Connect.

Your error may look similar to this:
Validation error. Data Flow Task: DTS.Pipeline: The package contains two objects with the duplicate name of “output column “ErrorCode” (180)” and “output column “ErrorCode” (13)”.

In my case, here’s how I fixed it:
On the task(OLE DB Source) that gave me the error, I went into the advanced editor. Go to the “Input and Output Properties” tab and select the Error Output Columns. I simply changed the name of the second instance to ErrorCode2 and it worked for me. It may be better to actually rename your column in the table but that requires more work. I guess it’s possible that this could affect your error logging but for my case it did not.

Comments { 1 } Posted on June 19, 2009 in SQL Server 2005, SQLServerPedia Syndication, SSIS

Execute SQL Task too big

I ran into an issue last night where copying selected text into the Execute SQL Task within SSIS would get truncated at a certain point. I really believe that I’ve successfully pasted larger text without an issue before, but I haven’t tested that theory as of yet.

One fix is to save the file as .sql and browse to it from within the Execute SQL task. Once I tried this I noticed that my last few lines were finally there. If you know of other ways around this please feel free to share.

Comments { 2 } Posted on March 10, 2009 in SQL Server 2005, SQLServerPedia Syndication, SSIS