Linked Server migration from sql server 2000 to 2005

A couple of methods of migrating linked servers from sql server 2000 to 2005\2008

Method 1
---------------
Use this script template to create a script for migrating linked servers. Depending on the amount of linked servers , I either copy and paste
or sometimes place in a loop and build dynmaically.

EXEC sp_addlinkedserver '<MY_LINKED_SERVER_NAME>', '', '<MY-PROVIDER>', '<MY_DATA_SOURCE>', NULL, NULL, '<MY_CATALOG>'
EXEC sp_serveroption '<MY_LINKED_SERVER_NAME>', 'rpc', 'FALSE'
EXEC sp_serveroption '<MY_LINKED_SERVER_NAME>', 'pub', 'FALSE'
EXEC sp_serveroption '<MY_LINKED_SERVER_NAME>', 'sub', 'FALSE'
EXEC sp_serveroption '<MY_LINKED_SERVER_NAME>', 'dist', 'FALSE'
EXEC sp_serveroption '<MY_LINKED_SERVER_NAME>', 'dpub', 'FALSE'
EXEC sp_serveroption '<MY_LINKED_SERVER_NAME>', 'rpc out', 'FALSE'
EXEC sp_serveroption '<MY_LINKED_SERVER_NAME>', 'data access', 'TRUE'
EXEC sp_serveroption '<MY_LINKED_SERVER_NAME>', 'collation compatible', 'FALSE'
EXEC sp_serveroption '<MY_LINKED_SERVER_NAME>', 'use remote collation', 'TRUE'
EXEC sp_serveroption '<MY_LINKED_SERVER_NAME>', 'lazy schema validation', 'FALSE'
EXEC sp_addlinkedsrvlogin @rmtsrvname = '<MY_LINKED_SERVER_NAME>', @useself = 'FALSE', @locallogin = NULL, @rmtuser = '<MY_REMOTE-USER>', @rmtpassword  = <REMOTE_USER_PASSWORD>'

Method 2
--------------------
--use this script to to copy linked server profiles

use master
set nocount on
select 'exec master..sp_addlinkedserver
@server = ''' + srvname + ''',
@srvproduct =', + '''' + srvproduct + ''',
@provider =', + '''' + providername + ''', @datasrc = ' , '''' +
datasource + '''' from sysservers

Source:Jack Vamvas (http://www.sqlserver-dba.com)

Performance Myth : Replacing a CURSOR with a WHILE LOOP and think you are not processing with a row

Switching from using a CURSOR to a WHILE loop will mean they are not processing by a row. This is not true. The following examples illustrates this myth. Both examples are processing row by row :
Example 1 - CURSOR

create table #mytemp
(ID INT ,
avalue VARCHAR(100),
status INT
)
INSERT INTO #mytemp (ID,avalue,status) VALUES(1,'Mick Jagger',0)
INSERT INTO #mytemp (ID,avalue,status) VALUES(2,'Keith Richards',0)
INSERT INTO #mytemp (ID,avalue,status) VALUES(3,'Ron Wood',0)
declare @cnt INT,@tmpINT INT
SET @cnt = 1
DECLARE my_cursor CURSOR FOR
SELECT ID FROM #mytemp
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @tmpINT
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @cnt
UPDATE #mytemp SET status = 1 WHERE ID = @cnt
SET @cnt = @cnt + 1
FETCH NEXT FROM my_cursor
INTO @tmpINT;
END
CLOSE my_cursor;
DEALLOCATE my_cursor;

Example 2 - WHILE loop

create table #mytemp
(ID INT ,
avalue VARCHAR(100),
status INT
)
INSERT INTO #mytemp (ID,avalue,status) VALUES(1,'Mick Jagger',0)
INSERT INTO #mytemp (ID,avalue,status) VALUES(2,'Keith Richards',0)
INSERT INTO #mytemp (ID,avalue,status) VALUES(3,'Ron Wood',0)
declare @cnt INT
SET @cnt = 1
WHILE (SELECT COUNT(*) FROM #mytemp WHERE status = 0) 0
BEGIN
UPDATE #mytemp SET status = 1 WHERE ID = @cnt
SET @cnt = @cnt + 1

END
SELECT * FROM #mytemp
DROP TABLE #mytemp

Source:Jack Vamvas (http://www.sqlserver-dba.com)