Srikanthsubramanian
asked on
How to migrate sql 200o full text to 2005 server
Hi,
How to migrate ms sql 2000 full text index to ms sql 2005 server?
I want script all full text indexes in the sql 2000 server and I want execute in sql 2005 server.
How to migrate ms sql 2000 full text index to ms sql 2005 server?
I want script all full text indexes in the sql 2000 server and I want execute in sql 2005 server.
ASKER
Actually I have more than 40 dbs and more than 100 full text indexes, so I cant create full text individually. The script will easy my job.
Ok, I see. Well, you can write a script on the target 2005 server to populate the indexes and so on. If you look in Books Online for sp_fulltext_table for example, you can take it from there. 2005 still supports these system stored procedures but the new method is to use CREATE, ALTER. Take your pick.
AustinSeven
AustinSeven
You could write a select statement on the 2000 server to return the list of databases and FT indexes. Then you could copy that info to the 2005 target server and write another select that generates the required list of t-sql commands to carry out the required actions. This would be much quicker than hand-crafting the script of course. Do you see what I mean?
AustinSeven
AustinSeven
Here, I wrote an example for you to select the info into a table on the 2000 server. From this, you can write a SELECT statement that inserts the command syntax to do whatever you want to your FT indexes on the 2005 server.
AustinSeven
AustinSeven
create table FTList (
dbname varchar(50),
FTIndex varchar(50)
)
set nocount on
declare dbcur cursor for
select name from master..sysdatabases where name not in ('master','tempdb','model','msdb')
open dbcur
declare @dbname varchar(50), @cmd varchar (256)
fetch next from dbcur into @dbname
while (@@fetch_status <> -1)
begin
select @cmd = 'insert into FTList select ' + quotename(@dbname,'''') + ', name from ' + @dbname + '.dbo.sysfulltextcatalogs'
exec (@cmd)
fetch next from dbcur into @dbname
end
close dbcur
deallocate dbcur
select * from ftlist
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great AustinSeven,
I need more... Where can i put table name, field names, filename.
EM we can select table name, field name, output file...
The table sysfulltextcatalogs do not have that much of informations....
I need more... Where can i put table name, field names, filename.
EM we can select table name, field name, output file...
The table sysfulltextcatalogs do not have that much of informations....
You can use the SQL Server 2000 sp_help_fulltext_* system stored procedures. Look in Books Online for a good description of what info they return. For example, sp_help_fulltext_catalogs will give you the path to the output file. sp_help_fulltext_tables gives you the table related info. Experiment with these sprocs to see which ones you need to return the required info. Then, when you have that understood, just dump the output of these sprocs into the table or a staging/ temp table.
You should have all the info to complete now, I think.
AustinSeven
create table #ft_cats (
ftcatid tinyint,
name varchar(50),
path varchar (100),
status tinyint,
number_fulltext_tables tinyint)
insert into #ft_cats
exec sp_help_fulltext_catalogs
-- can 'insert into' another table to collect the data.
-- or assign path to a variable @path = path... to use later in the script
select path from #ft_cats
drop table #ft_cats
AustinSeven