upgrading from 2000 to 2003 2

[jisaacson]

I would like any advice on upgrading sql server 2000 to sql server 2003. Specifically, I would like to know if there are any problems with vb6 code that are affected by the upgrade.

I also use stored procedures. Are they affected by the upgrade? If so how?

Did your upgrade go smoothly, or where there problems. If so, what were the problems?

I would appreciate all responses as I would like to make the upgrade as painless as possible.

 

[RiverGuy]

There is no SQL Server 2003. There is SQL Server 2005 however. When I upgraded our main database from SQL 2000 to SQL 2005, our main departmental VB.Net app worked without a hitch. I've never used VB6 and SQL Server 2005 together however.

That being said, you should do some research. Here is a good place to start:

http://msdn.microsoft.com/en-us/library/ms143179(SQL.90).aspx

 

[gmmastros]

First, there is no SQL 2003. There's SQL2000, SQL2005, and SQL2008.

I have a VB6 application that can use SQL2000 or SQL2005 and it works very well.

After upgrading your database, make sure you run sp_updatestats (because statistics are different between the 2 databases and you will certainly notice a difference). This only needs to be done once (although updating statistics on a regular basis isn't a bad idea, either.)

I don't know how you are connecting vb6 to the database, but I would encourage you to use the SQLNative driver instead of the SQL2000 OLEDB provider because you will get slightly better performance.

I have not had any problems with anything. However, if you do run in to problems, you can always set the compatibility mode back to sql2000 compatibility.

There is a gotcha that is relatively easy to avoid. Obviously, SQL2005 is a different instance of SQL server than SQL2000. When you install SQL 2005, you can set the default collation of the instance (which affects the system databases, Master, Model, TempDB, and MSDB). I would encourage you to identify the collation of your existing instance and make sure you install sql2005 with the same collation that you are already using. If the collations don't match, and you use temp tables and/or table variables, and you join on string columns (like varchar, char, etc...), you will have serious problems. It's best to avoid these problems by making the collations match.

Stored procedures are unaffected by the upgrade. Performance may be an issue, but running sp_updateStats fixes those.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom