Technical information for Microsoft SQL Server 2005

This is the index for a collection of web pages containing difficult to find technical information about the initial release of Microsoft SQL Server 2005 Standard Edition.

Background:

The new Microsoft SQL Server 2005 is a fantastic product. It’s the perfect tool for my company’s data warehouse project. It contains every tool we need all in a single, cheap, well integrated package. I fully expect that these tools will give us a significant competitive advantage that is just not possible for a small company to achieve with any other combination of data warehousing and reporting tools. I have created this collection of web pages to document all the little details that we have discovered. Hopefully, you can benefit from the things we have learned!

Yes, this web site is UGLY. What can I say, I’m lazy. Plus, I would rather put my efforts into writing good articles! My main mathematics educational web site MathGV is much prettier. All of our testing servers are running Microsoft SQL Server 2005 Standard Edition and Windows 2003 Standard Edition unless other wise noted. Most articles should also apply to SQL Server 2005 Enterprise Edition as well.

Shameless Plug!

If you like this web site, click here to nominate me for a Microsoft SQL Server 2005 MVP award!

Sincerely,

Greg Van Mullem

Fell Free to contact me at: info@mathgv.com

Last updated: October 13, 2006

Links to major articles:

1. Error restoring database backup file in SQL Server 2005. I made a backup of all databases to a single file using the SQL 2005 Standard Edition maintenance plan wizard. I have been unable to restore databases from this type of backup file.

2. Errors using SSIS (SQL Server Integration Services) 2005 to extract records from an IBM AS400 to an SQL table (workaround)

3. Installing Microsoft SQL Server 2005 Reporting Services on a non-default web site. Reporting Services will be installed on a web server that is not running the SQL 2005 database engine. We will use a host header and a DNS name to connect to the web site.

4. Using the SSIS transformation script component in an ETL. This example shows how to ETL (Extract Translate and Load) customer records from a source (staging) table to a destination table. It uses a standard update or insert algorithm.

5. Veritas Backup Exec breaks SQL Server 2005 SP1 install. This article documents how Veritas Backup Exec (now owned by Symantec) causes the SQL Server 2005 Service Pack 1 install to fail. It also documents how to fix the problem even after a failed install attempt.

Smaller items:

1. The SQL Server 2005 Standard Edition install program will not let you upgrade from “SQL Server 2000 Enterprise Edition” to “SQL Server 2005 Standard Edition”. However, if you are not paying really close attention during the install it appears to work! Here is a Microsoft web page on version and edition upgrades http://msdn2.microsoft.com/en-us/library/ms143393.aspx

2. SQL Server 2005 Reporting Services will not install on the Web Server editing of Windows 2003 despite being a relatively standard ASP.NET web site. This is fully documented by Microsoft but it still caught me by surprise. Doho!

External resources:

1. I highly recommend Jamie Thompson’s “SSIS Junkie” blog for SSIS stuff. Wow does he have a lot of good information!

http://blogs.conchango.com/jamiethomson/default.aspx

2. Here is a good article on changing table and stored procedure ownership in SQL 2000.
http://weblogs.asp.net/owscott/archive/2004/01/30/65229.aspx

3. Here is an article that discusses how to fix problems accessing the diagrams in databases that have been restored to SQL 2005 from a SQL 2000 backup file. This problem is fixed in SP1.
http://mcfunley.com/cs/blogs/dan/archive/2005/12/23/899.aspx

4. Here is some really links that discuss calling SSIS from Reporting Services.

http://blogs.msdn.com/bimusings/archive/2006/07/19/671743.aspx

http://msdn2.microsoft.com/en-us/library/ms159215.aspx

http://msdn2.microsoft.com/en-us/library/ms156302.aspx

http://msdn2.microsoft.com/en-us/library/ms345250.aspx