MS SQL

Fulltext Index problems

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Oct 2008

Posts: 1

Reputation: ecejahv is an unknown quantity at this point

Solved Threads: 0

ecejahv

Offline

Newbie Poster

Fulltext Index problems

Oct 26th, 2008

Hi,

I am a beginner in Microsoft SQL. I want to create a fulltext index from a UNIQUE key. For that purpose I have the following code:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE FULLTEXT INDEX ON sample_table
	(data)
	KEY INDEX UQ__sample_table__59FA5E80
		ON Catalog_data
		WITH CHANGE_TRACKING OFF, NO POPULATION
CREATE FULLTEXT INDEX ON sample_table
	(data)
	KEY INDEX UQ__sample_table__59FA5E80
		ON Catalog_data
		WITH CHANGE_TRACKING OFF, NO POPULATION

This works fine. The problem is that the unique key index name is not known in advance (I got the name from the object browser) so I made a stored procedure, basically using the system "sp_helpindex" stored procedure, to get the dynamic index name. Then, if I try the follwing code:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
DECLARE @ui_name CHAR (100)
EXECUTE S_UNIQUE_INDEX_NAME @ui_name OUTPUT
CREATE FULLTEXT INDEX ON sample_table
	(data)
	KEY INDEX @ui_name
		ON Catalog_data
		WITH CHANGE_TRACKING OFF, NO POPULATION
DECLARE @ui_name CHAR (100)
EXECUTE S_UNIQUE_INDEX_NAME @ui_name OUTPUT
CREATE FULLTEXT INDEX ON sample_table
	(data)
	KEY INDEX @ui_name
		ON Catalog_data
		WITH CHANGE_TRACKING OFF, NO POPULATION

...it does not work (Error message "Incorrect syntax near '@ui_name' ").

I have tested to print the value of the stored procedure and it is ok (same value, "UQ__sample_table__59FA5E80") and also different casting methods. Now I start to think that the variable could not be visible within the "CREATE FULLTEXT..." command context but I am not sure. Can anybody help me ?

Thanks in advance and best regards,

ecejahv

Last edited by peter_budo; Oct 29th, 2008 at 6:22 pm. Reason: Keep It Organized - please use [code] tags

•

Join Date: Dec 2008

Posts: 1

Reputation: ed_ward_graham is an unknown quantity at this point

Solved Threads: 0

ed_ward_graham ed_ward_graham is offline

Offline

Newbie Poster

Re: Fulltext Index problems

Dec 19th, 2008

I'm a bit of a novice myself but have you tried

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
DECLARE @ui_name CHAR (100)
EXECUTE S_UNIQUE_INDEX_NAME @ui_name OUTPUT
DECLARE @SqlCmd NVARCHAR(MAX)
SET @SqlCmd =
'CREATE FULLTEXT INDEX ON sample_table
	(data)
	KEY INDEX ' + @ui_name +
		' ON Catalog_data
		WITH CHANGE_TRACKING OFF, NO POPULATION';
-- now create index as a dynamic SQL command
EXEC (@SqlCmd)
DECLARE @ui_name CHAR (100)
EXECUTE S_UNIQUE_INDEX_NAME @ui_name OUTPUT
DECLARE @SqlCmd NVARCHAR(MAX)
SET @SqlCmd =
'CREATE FULLTEXT INDEX ON sample_table
	(data)
	KEY INDEX ' + @ui_name +
		' ON Catalog_data
		WITH CHANGE_TRACKING OFF, NO POPULATION';
-- now create index as a dynamic SQL command
EXEC (@SqlCmd)