Help! Need to migrate Windows 2000 Server/SQL Server to Windows 2003 Advanced Server/SQL A

We have existing machine runing Windows 2000 Server and SQL Server
2000. We will be buying a larger machine and would like to migrate all
data/processes/DTSs/jobs into new machine which will be running
Windows 2003 Advanced Server + SQL 2000 Advanced Server edition.
Our goal is not only preserve all jobs/data/packages but also leve the
same name of the SQL server.
How can we do that? are there any reliable recepies?
Thanks in advance!
Gene.See if this helps:
http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Gene Fichtenholz" <gfichten@.hotmail.com> wrote in message
news:6c807745.0402231156.2abf1586@.posting.google.com...
We have existing machine runing Windows 2000 Server and SQL Server
2000. We will be buying a larger machine and would like to migrate all
data/processes/DTSs/jobs into new machine which will be running
Windows 2003 Advanced Server + SQL 2000 Advanced Server edition.
Our goal is not only preserve all jobs/data/packages but also leve the
same name of the SQL server.
How can we do that? are there any reliable recepies?
Thanks in advance!
Gene.

Help! Create index with substring

Good day!

We had decided to migrate from Oracle to SQL Server, so faced some problems. Using Oracle we could create indexes like that

create index obj_id_cnum on obj_id (substr(cnum,1,2))

But Microsoft SQL Server doesn't allow this code. How can we do the same using SQL Server. Thanks you.

dynamic sql...

declare @.SQL nvarchar(100)

set @.SQL = 'create index [obj_id_cnum] on object_id(' + substring(cnum,1,2) + ')'

exec sp_executesql @.SQL

|||

Is obj_id a table, I am assuming? If so, then you are right, you cannot apply an index on a function directly. Two ways you can approach this:

1. If using enterprise edition, you can index a view and it will be used:

create view obj_id_indexed
as
select obj_id_key, cnum, substring(cnum,1,2) as cnumSub,
from obj_id

create unique clustered index on obj_id_cnum(obj_id_key, cnumSub)

Now your queries will see this index on the the view and apply it just like the

2. You can however add a computed column to your table and then index it:

alter table obj_id
add cnumSub as substring(cnum,1,2)

I would suggest that all of these techniques are probably the "wrong" way to go about this sort of thing. Almost any time you need to use a substring on a value in a SQL table there is a problem with normalization. Better would be to break the column into two parts, and then you have a much better chance of making the indexes work for you.

|||Thanks you very much! I do appreciate your help!|||

Derek Comingore - RSC wrote:

dynamic sql...

declare @.SQL nvarchar(100)

set @.SQL = 'create index [obj_id_cnum] on object_id(' + substring(cnum,1,2) + ')'

exec sp_executesql @.SQL

set @.SQL = 'create index [obj_id_cnum] on object_id(' + substring(cnum,1,2) + ')' gives an error 'Invalid column name 'cnum'.

|||Note that we are considering adding function / expression based indexes for a future version of SQL Server. For now, the easiest workaround is to use an indexed computed column.