When upgrading sql server 2000 to sql server 2008 changes affected by the current databases?

We are migrating the database of sql 2000 to sql 2008 with ANSI comparability mode 100.We are changing the old code =* and *= for left and right outer join and some order by changes instead of column name we added number for that respective column name.Live server having sql server 2000 database. we want to apply the changes in live server of the customer.current we testing the new back server with sql server 2008R2

The question is a. Apply all corrections in the live server with SQL 2000 and then upgrade the production server to SQL2K8


b. Upgrade the Production server to SQL2K8 and then apply the corrections.

Which one is best method.
more ▼

asked Nov 28, 2011 at 03:09 AM in Default

cstephen gravatar image

234 24 27 29

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Personally I would make a copy of the database, restore it to a test server, upgrade it, make the changes and test that it produces the same output as the production version and then repeat the process on the production database.

Why are you using the column number to sort the datasets. This is a path to causing yourself problems in the future.
more ▼

answered Nov 28, 2011 at 03:13 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

@Fatherjack, I was about to say the same. But I would like to ask what is the feasibility of restoring on a test server, make the corrections and test the output, then upgrade, test the upgrade? Feeling, this way can segregate the two processes. Thanks in advance.
Nov 28, 2011 at 03:21 AM Usman Butt
Absolutely 100% the right way to go. Testing either approach on the production system is a sure way to trouble.
Nov 28, 2011 at 03:23 AM Grant Fritchey ♦♦

We are adding the column number for repeating the same column in the final select statement of the procedure: For example

    host_id    = '1'

In this scenario for repeating columns we are just using order by and position of the column name:
Like order by 8
And your method is correct..we are also thinking this way only..
Nov 28, 2011 at 03:31 AM cstephen
in this case (and in every other one I can think of) I would wholly recommend against having a dataset that has columns with the same name. If you have to have the data twice, and I cant honestly see why, then name one vch_serial_no_orderby and then end your query with ORDER BY vch_serial_no_orderby.
Nov 28, 2011 at 03:53 AM Fatherjack ♦♦

Using Ordinals is not considered a best practice: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx

Don't return multiple columns with the same name. Differentiate them or you're going to lead to confusion down the road.
Nov 28, 2011 at 05:02 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 28, 2011 at 03:09 AM

Seen: 1065 times

Last Updated: Nov 28, 2011 at 03:33 AM