|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?doc which i've done through Index Server and linked the results to SQL Server. Part of my stored proc is shown below in which for a FREETEXT keyword search i'm using a variable "@searchstring", which i have to, is not working. I know it works with hard text but Is there any way i can use a Variable in OPENQUERIES or is this my DEAD END? Can anyone please guide me how to use a variable in FREETEXT Thanks in Advance DECLARE @searchstring varchar(22) SET @searchstring = 'aspnet' SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName, DocAuthor, Size, Create, Write, Path FROM SCOPE('' "c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE FREETEXT(''@searchstring'')') Hi,
You need to resort to dynamic SQL, i.e..... SET @sql = 'SELECT TOP 50 * FROM ( SELECT DISTINCT kba.idKBArticle, [Rank], Characterization FROM ( SELECT DISTINCT TOP 50 [FileName], [Rank], Characterization FROM OPENQUERY( lsIndexServer, ''SELECT FileName, Rank, Characterization FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE WHEN @OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END + '( ''''' + @SearchKeywords + ''''' )'' ) WHERE LEFT( Characterization, 12 ) <> ''vti_encoding'' ) AS qry INNER JOIN KBArticle kba ON kba.ArticleFileName = qry.[FileName]' REMEMBER!!!!!! ====>>>>>>> To prevent injection make absolutely sure you replace any single quotes with 2 single quotes... -- this one fails and is subject to injection... declare @searchtext varchar(100) set @searchtext = 'tony''s injection' exec( 'print ''' + @searchtext + '''' ) go -- this one works because prevent injection... declare @searchtext varchar(100) set @searchtext = 'tony''s injection' set @searchtext = REPLACE( @searchtext, '''', '''''' ) exec( 'print ''' + @searchtext + '''' ) go Show quote "savvy" <johng***@gmail.com> wrote in message news:1137755654.791956.239920@z14g2000cwz.googlegroups.com... > I'm writing a stored procedure for a keyword search in a Word or PDF > doc which i've done through Index Server and linked the results to SQL > Server. > Part of my stored proc is shown below in which for a FREETEXT keyword > search i'm using a variable "@searchstring", which i have to, is not > working. > I know it works with hard text but > Is there any way i can use a Variable in OPENQUERIES or is this my DEAD > END? > Can anyone please guide me how to use a variable in FREETEXT > Thanks in Advance > > DECLARE @searchstring varchar(22) > SET @searchstring = 'aspnet' > > SELECT * FROM OPENQUERY(FileSystem,'SELECT Directory, FileName, > DocAuthor, Size, Create, Write, Path FROM SCOPE('' > "c:\inetpub\wwwroot\sap-resources\Uploads" '') WHERE > FREETEXT(''@searchstring'')') > Thanks for your help
i tried using above idea and some other examples. The code shown below is working perfectly in the analyzer. I want to create a view with the results Is it possible ? Thanks in Advance DECLARE @searchstring varchar(22) SET @searchstring = 'aspnet' declare @strSQL varchar(244) select @strSQL='select FileName,Path from scope('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains (' select @strSQL=@strSQL +char(39)+ char(39)+ @searchstring +char(39)+ char(39)+')' select @strSQL='select * from openquery(FileSystem,'+ char(39)+ @strSQL+ char(39)+ ')' exec (@strSQL) Something like CREATE VIEW FileSearchResults AS (@strSQL) which is not working Hi Savvy,
Sorry - you won't be able to create a view for that unless your search string is hard-coded and never changes. You could write a stored procedure that accepts the search string as a parameter. Tony. Show quote "savvy" <johng***@gmail.com> wrote in message news:1137764139.455350.325970@o13g2000cwo.googlegroups.com... > Thanks for your help > i tried using above idea and some other examples. > The code shown below is working perfectly in the analyzer. I want to > create a view with the results > Is it possible ? > Thanks in Advance > > > DECLARE @searchstring varchar(22) > SET @searchstring = 'aspnet' > declare @strSQL varchar(244) > select @strSQL='select FileName,Path from scope('''' > "c:\inetpub\wwwroot\sap-resources\Uploads" '''') where contains (' > select @strSQL=@strSQL +char(39)+ char(39)+ @searchstring +char(39)+ > char(39)+')' > select @strSQL='select * from openquery(FileSystem,'+ char(39)+ > @strSQL+ char(39)+ ')' > exec (@strSQL) > > > Something like > CREATE VIEW FileSearchResults AS (@strSQL) > which is not working > Thank you very much for your help and time Tony Rogerson
This is my complete stored procedure which is perfectly working when i hardcore the @searchstring with the word which doesn't change. I just want to use a variable working over there. Can u please help me in this Thanks in Advance CREATE PROCEDURE SelectIndexServerCVpaths ( @searchstring varchar(100) ) AS IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'FileSearchResults') DROP VIEW FileSearchResults EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM OPENQUERY(FileSystem,''SELECT Directory, FileName, DocAuthor, Size, Create, Write, Path FROM SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE FREETEXT(''''@searchstring'''')'')') SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path = F.PATH AND C.DefaultID=1 GO CREATE PROCEDURE SelectIndexServerCVpaths
( @searchstring varchar(100) ) AS SET @searchstring = REPLACE( @searchstring, '''', '''''' ) IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'FileSearchResults') DROP VIEW FileSearchResults EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM OPENQUERY(FileSystem,''SELECT Directory, FileName, DocAuthor, Size, Create, Write, Path FROM SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE FREETEXT(''''' + @searchstring + ''''')'')') SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path = F.PATH AND C.DefaultID=1 GO Show quote "savvy" <johng***@gmail.com> wrote in message news:1137766282.280852.185740@g14g2000cwa.googlegroups.com... > Thank you very much for your help and time Tony Rogerson > This is my complete stored procedure which is perfectly working when i > hardcore the @searchstring with the word which doesn't change. > I just want to use a variable working over there. Can u please help me > in this > Thanks in Advance > > CREATE PROCEDURE SelectIndexServerCVpaths > ( > @searchstring varchar(100) > ) > AS > IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS > WHERE TABLE_NAME = 'FileSearchResults') > DROP VIEW FileSearchResults > EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM > OPENQUERY(FileSystem,''SELECT Directory, FileName, > DocAuthor, Size, Create, Write, Path FROM > SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE > FREETEXT(''''@searchstring'''')'')') > SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path = > F.PATH AND C.DefaultID=1 > GO > Thanks for your Great help Tony
I have a strange problem its above code is working in the Query Analyzer but not working if execute the stored procedure as shown below i tried but i'm not able to figure out where the problem is Thanks in Advance Exec SelectIndexServerCVpaths @searchstring = 'aspnet' whats the error?
Show quote "savvy" <johng***@gmail.com> wrote in message news:1137770531.573885.223780@f14g2000cwb.googlegroups.com... > Thanks for your Great help Tony > I have a strange problem its above code is working in the Query > Analyzer but not working if execute the stored procedure as shown below > i tried but i'm not able to figure out where the problem is > Thanks in Advance > > Exec SelectIndexServerCVpaths > @searchstring = 'aspnet' > I'm sorry Tony
i didn't copy the code properly in my stored procedure this part exactly FREETEXT(''''' + @searchstring + ''''')'')') when i copied again its working perfectly Tony You dont know how much your help is worth to me I cant just express in words I needed to complete project today which i did with your help Thank you very very very much Tony Rogerson Not a problem Savvy - glad to help.
If you are in the UK try and get to one of my meetings - online or off. Tony. Show quote "savvy" <johng***@gmail.com> wrote in message news:1137772266.218968.66780@g47g2000cwa.googlegroups.com... > I'm sorry Tony > i didn't copy the code properly in my stored procedure this part > exactly FREETEXT(''''' + @searchstring + ''''')'')') > when i copied again > its working perfectly Tony > You dont know how much your help is worth to me > I cant just express in words > I needed to complete project today which i did with your help > Thank you very very very much Tony Rogerson > I'm in UK mate
tell me address of your meetings or further details I'll definately come Thanks
http://sqlserverfaq.com.
:) Tony.Show quote "savvy" <johng***@gmail.com> wrote in message news:1137773845.586175.69910@z14g2000cwz.googlegroups.com... > I'm in UK mate > tell me address of your meetings > or further details > I'll definately come > Thanks > I am attempting to do something similar in a function. But I keep getting a
syntax error My code fragment is: RETURNS @StatusTab TABLE (ITPMHealth CHAR(1), BusHealth CHAR(1)) As begin insert into @StatusTab SELECT [ITPMHealth], [BusHealth] FROM openquery(edpportallink,'Select substr(stoplight,1,1) as "ITPMHealth", substr(bus_stus_cd,1,1) as "BusHealth" from view_stoplight_report where sr_no = ''' + @srnum + '''') return @srnum is passed to the function. Can this not be done in a function? Thanks. Ed Show quote "Tony Rogerson" wrote: > CREATE PROCEDURE SelectIndexServerCVpaths > ( > @searchstring varchar(100) > ) > AS > SET @searchstring = REPLACE( @searchstring, '''', '''''' ) > > IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS > WHERE TABLE_NAME = 'FileSearchResults') > DROP VIEW FileSearchResults > EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM > OPENQUERY(FileSystem,''SELECT Directory, FileName, > DocAuthor, Size, Create, Write, Path FROM > SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE > FREETEXT(''''' + @searchstring + ''''')'')') > SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path = > F.PATH AND C.DefaultID=1 > GO > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "savvy" <johng***@gmail.com> wrote in message > news:1137766282.280852.185740@g14g2000cwa.googlegroups.com... > > Thank you very much for your help and time Tony Rogerson > > This is my complete stored procedure which is perfectly working when i > > hardcore the @searchstring with the word which doesn't change. > > I just want to use a variable working over there. Can u please help me > > in this > > Thanks in Advance > > > > CREATE PROCEDURE SelectIndexServerCVpaths > > ( > > @searchstring varchar(100) > > ) > > AS > > IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS > > WHERE TABLE_NAME = 'FileSearchResults') > > DROP VIEW FileSearchResults > > EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM > > OPENQUERY(FileSystem,''SELECT Directory, FileName, > > DocAuthor, Size, Create, Write, Path FROM > > SCOPE('''' "c:\inetpub\wwwroot\sap-resources\Uploads" '''') WHERE > > FREETEXT(''''@searchstring'''')'')') > > SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path = > > F.PATH AND C.DefaultID=1 > > GO > > > > >  |
|||||||||||||||||||||||
Other interesting topics