full text enabling with stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

full text enabling with stored proc

Author

Topic

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-15 : 08:09:05

Hi all

we have written stored procedures to dynamically create database, these stored procedures are triggered from an asp page.
as well as creating the database i'm writing a stored procedure to enable full text indexing on this dynamically created database,
these stored procedures are stored in master, creating the databases works fine, the problem is when i'm trying to enable full text
indexing, I know I can't use (use dbNAme) in a stored procedure, so how do i tell my full text stored procedure to point to the new
database?

the only command I know of is: exec sp_fulltext_database 'enable'
so how do I tell it which database to enable as my stored proc is in master?

Thanks
Shem

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-15 : 08:26:28

Try this:

exec dbname.sp_fulltext_database 'enable'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-15 : 09:10:31

error:
Procedure sp_fulltext_contents incorrect syntax near '.'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-15 : 09:13:07

[code]exec dbname..sp_fulltext_database 'enable'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-15 : 09:24:13

changed to:
exec @strDB..sp_fulltext_database 'enable'

still getting error:
incorrect syntax near '.'

Shem

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-15 : 09:26:20

That's it.

You need to use dynamic SQL for this:

Declare @sql varchar(5000)
set @sql = @strDB + '..sp_fulltext_database ''enable'''
exec(@sql)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-15 : 09:42:50

can I do this

Declare @sql varchar(5000)
Set @sql = @strDB + '..spfulltext_database ''enable'''
exec(@sql)

and then re-assign a new value to @sql below that and execute it?

Shem

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-15 : 10:04:15

can anyone see something wrong with this?

Set @sql2 = @strDB + '..sp_fulltext_catalog ' + @strCatName + ', create'

i'm getting this error:
Incorrect syntax near the keyword 'create'.

Shem

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-03-15 : 10:09:36

getting a new error now:
Table or indexed view 'contents' does not have a full-text index or user does not have permission to perform this action.

my stored proc:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_Fulltext_Contents]    Script Date: 03/15/2007 15:58:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Fulltext_Contents] @strDB varchar(50)
AS
BEGIN
	DECLARE @strCatName varchar(4000)
	SET @strCatName = @strDB + '_contents_catalog'

		Declare @sql varchar(4000)
			Set @sql = @strDB + '..sp_fulltext_database''enable'''
				exec(@sql)
		
		Declare @sql2 varchar(4000)
			Set @sql2 = @strDB + '..sp_fulltext_catalog ''' + @strCatName + ''', ''create'''	
				exec(@sql2)

		Declare @sql3 varchar(4000)
			Set @sql2 = @strDB + '..sp_fulltext_table @tabname=''contents'', @action=''create'', @ftcat=[@strCatName], @keyname=[idkey]'
				exec(@sql3)
		
		Declare @sql4 varchar(4000)
			Set @sql4 = @strDB + '..sp_fulltext_column @tabname=''contents'', @colname=''contents'', @action=''add'''
				exec(@sql4)
		
		Declare @sql5 varchar(4000)
			Set @sql5 = @strDB + '..sp_fulltext_column @tabname=''contents'', @colname=''tags'', @action=''add'''
				exec(@sql5)

		Declare @sql6 varchar(4000)
			Set @sql6 = @strDB + '..sp_fulltext_column @tabname=''contents'', @colname=''hiddenTags'', @action=''add'''		
				exec(@sql6)
		
		Declare @sql7 varchar(4000)
			Set @sql7 = @strDB + '..sp_fulltext_column @tabname=''contents'', @colname=''title'', @action=''add'''
				exec(@sql7) 
		
		Declare @sql8 varchar(4000)
			Set @sql8 = @strDB + '..sp_fulltext_column @tabname=''contents'', @colname=''theDate'', @action=''add'''
				exec(@sql8)
		
		Declare @sql9 varchar(4000)
			Set @sql9 = @strDB + '..sp_fulltext_table ''contents'', ''start_change_tracking'''
				exec(@sql9)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources