SQL Server 2000 Upgrade

• Alan Cornell

  Grasshopper

  Points: 15

  More actions

  March 23, 2004 at 9:01 am

  #101930

  I want to upgrade to SQL 2000, but I have several databases set to 65 compatibility.  Does anyone have any scripts to help me identify the changes needed to make everything  2000 compatible?

   

  Alan

• Prasad Bhogadi

  SSCrazy Eights

  Points: 9235

  More actions

  March 24, 2004 at 12:38 am

  #500159

  Well the SQL SERVER 2000 Upgrade Wizard should do it for you with ease, however you may want to take a look at UPGRADING SQL SERVER in BOL and study the steps in

  Preparing to Upgrade from SQL Server 6.5,

  Upgrading Databases from SQL Server 6.5 (Upgrade Wizard),

  Thanks

   

  Prasad Bhogadi
  www.inforaise.com

• Eoin The Forge

  SSCrazy

  Points: 2932

  More actions

  March 24, 2004 at 2:31 am

  #500165

  We came up with this (I think the comment could be a problem down the line that is why it is in here)

  System Stored Procedures (Tasks) (Level 1)

  Replace the following unsupported Microsoft® SQL Server™ 6.x task-related system stored procedures with the corresponding SQL Server 2000 job-related system stored procedures.

  SQL Server 6.x	SQL Server 2000
  sp_addalert	sp_add_alert
  sp_addnotification	sp_add_notification
  sp_addoperator	sp_add_operator
  sp_dropalert	sp_delete_alert
  sp_dropnotification	sp_delete_notification
  sp_dropoperator	sp_delete_operator
  sp_helpalert	sp_help_alert
  sp_helphistory	sp_help_jobhistory
  sp_helpnotification	sp_help_notification
  sp_helpoperator	sp_help_operator
  sp_purgehistory	sp_purge_jobhistory
  sp_runtask	sp_start_job
  sp_stoptask	sp_stop_job
  sp_updatealert	sp_update_alert
  sp_updatenotification	sp_update_notification
  sp_updateoperator	sp_update_operator

  Suser_name()

   

  suser_name() will return null in sql2000 and is included only for backward compatibility. suser_sname() should be used.

   

  Use

  Select suser_sname()

   

  Instead of

  Select suser_name()

     

  Quoted Identifiers

   

  Because QUOTED_IDENTIFIER is ON by default in SQL 2000 it reads strings within double quotes as identifiers, e.g. column names. Scripts used in SQL 6.5 that use double quotes to delimit character strings will have to be modified to use single quotes.

   

  E.G.

   

  Use

   

  AND Convert(char(255), pan) LIKE RTrim(e.CardNumberPrefix) + ‘%’

   

  Instead of

   

  AND Convert(char(255), pan) LIKE RTrim(e.CardNumberPrefix) + "%"

   

   

   

  Query hints

   

  Table hints must be specified following the FROM clause using a WITH clause. Table hints must be enclosed in parentheses.

   

  SQL 6.5

  SELECT count(*) FROM t1 (TABLOCK HOLDLOCK)

  SQL2000

  SELECT count(*) FROM t1 WITH (TABLOCK HOLDLOCK)

   

   

   

   

   

  Index hints

   

  In sql 6.5 the INDEX = syntax specified one or more indexes to use for a table hint.

   

  SELECT lname, au_fname, phone

          FROM authors (INDEX = aunmind)

                  WHERE au_1name = 'Smith'

   

  In sql2000 Supported for backward compatibility only.

  Consider removing all references to INDEX = and replacing (when using multiple index hints) with references to INDEX(index, index...)

   

   

  e.g.        SELECT lname, au_fname, phone

            FROM authors WITH (INDEX(aunmind))

                  WHERE au_1name = 'Smith'

  SELECT lname, au_fname, phone

            FROM authors WITH (NOLOCK INDEX(aunmind))

                  WHERE au_1name = 'Smith'

   

   

   

   

  Non ANSI joins

   

  It is recommended that you remove all references of the left outer join (*=) and right outer join (=*) operators in all SELECT statement FROM clauses and replace with references to the SQL-92-standard syntax RIGHT OUTER JOIN and LEFT OUTER JOIN.

  Left outer join (*=) and right outer join (=*) syntax may return incorrect results.

  Future versions of SQL Server will support only the SQL-92-standard syntax.

   

  Non ANSI syntax

  SELECT employee.emp_id, employee.job_id

  FROM employee,jobs 

     WHERE employee.job_id *= jobs.job_id

     AND (jobs.job_id IS NULL)

  SQL-92-standard syntax

  SELECT employee.emp_id, employee.job_id

  FROM employee LEFT OUTER  JOIN jobs

     ON employee.job_id = jobs.job_id

  WHERE (jobs.job_id IS NULL

    

   

  IS NULL / IS NOT NULL

  Any code that uses the equality operators "=" or "<>" to reference NULL produces different results from those returned under SQL Server 6.5.  Always use the IS NULL or IS NOT NULL construct when you're dealing with NULL.

  Only use …

  SELECT employee.emp_id

  FROM employee

  WHERE jobs.job_id IS NULL

  or

  SELECT employee.emp_id

  FROM employee

  WHERE jobs.job_id IS NOT NULL

   

   

   

  Comments

   

  In SQL 6.5, the upgrade changes the -- by removing it. This allows that command to execute in the stored procedure.

  All comments should be changed to */ instead to avoid any problems in the future.

   Replace

  -- this is a comment

  with

  /* this is a comment */

    

  Order by

   

  An explicit ORDER BY clause for a SELECT statement is required to ensure any useful ordering of data.

   

  SELECT emp_id, job_id

  FROM employee

  ORDER BY emp_id

   New reserved words

  80	COLLATE, FUNCTION, OPENXML
  70	BACKUP, CONTAINS, CONTAINSTABLE, DENY, FREETEXT, FREETEXTTABLE, PERCENT, RESTORE, ROWGUIDCOL, TOP

   

   

• ckempste

  SSCoach

  Points: 17983

  More actions

  April 5, 2004 at 9:13 am

  #501770

  Great post, thanks for the tips - will prove very handy indeed.

  Cheers

  Ck

  
  Chris Kempster
  www.chriskempster.com
  Author of "SQL Server Backup, Recovery & Troubleshooting"
  Author of "SQL Server 2k for the Oracle DBA"