asked
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 OR b. Upgrade the Production server to SQL2K8 and then apply the corrections. Which one is best method.
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.
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.
@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.
Absolutely 100% the right way to go. Testing either approach on the production system is a sure way to trouble.
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..
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`.
