Fulltext Index problems
 

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:

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:

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

Re: Fulltext Index problems
 

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

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)

It almost certainly won't be the best way to do this but it should get around the problem of using a variable name for your index.