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

Question

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

OR

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

Which one is best method.

Answer 1

0

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.

answered Nov 28 '11 at 03:13 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

@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 '11 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 '11 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

 SELECT  
    company_code,  
    location_code,          
    tran_type,  
    tran_grp_tmp,   
    tran_no_tmp,
    act_inc_date,   
    curr_code, 
    vch_serial_no,   
    vch_serial_no   
FROM    
    gl_bank_postings_temp
WHERE   
    host_id    = '1'
ORDER BY 
    vch_serial_no

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 '11 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 '11 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 '11 at 05:02 AM Grant Fritchey ♦♦

(comments are locked)

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

Follow this question

Related Questions