Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?

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'')')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
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.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.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.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
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.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?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"savvy" <johngera@.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|||I'm really grateful to you Tony
Thanks onceagain

cant use functions on linked servers??

Hello

I'm having some problems with a couple of linked SQL servers. Basically I can get queries and SPs to work just fine, aklthough there is a little overhead. But I can't get functions to work! Can it really be that linked servers don't support functions? And if so, just out of curiosity, why on earth is it so? Linked servers only handle result sets, not scalar values?

I hope somebody can help me with this
MNJCan you explain "can't get functions to work" in more detail? I've never had it fail, so I'm probably missing something really basic here.

-PatP|||I thought you could never access the SPs and UDFs through a linked server. If you could, I would be interested to know how. Is this syntax valid. Can I execute this from Server 2
linkedserver1.database1.owner1.SP1|||EXECUTE linkedserver.master.dbo.sp_who

Works just fine for me. Does anyone else have trouble with it?

-PatP|||This is working for me:

select * from OPENQUERY ( linkedserver , 'select * from testDB2.dbo.testfunction()' )

but not working:

select * from linkedserver.testDB2.dbo.testfunction()|||Pat,

I get

Server: Msg 7411, Level 16, State 1, Line 1
Server 'QA' is not configured for RPC.|||Well that was easy..

Just go to properties in EM and select that you want to do RPC's

Why would that even be an option...

Wonder what the code is to enable it...|||Originally posted by snail
This is working for me:

select * from OPENQUERY ( linkedserver , 'select * from testDB2.dbo.testfunction()' )

but not working:

select * from linkedserver.testDB2.dbo.testfunction() Can you elaborate just a bit on what constitutes "not working" ?

-PatP|||I think you can't use the next for a function

select * from any_function(params)

Shouldn't this be:

select any_function(params)

Maybe this will solve the problem?|||Welcome Johan!

It depends. If they are using a table valued function, the original syntax would be fine as long as it had at least a two-part name.

-PatP|||Just one question from me ...

though from your post it seems the linked server is to a SQL server ...

is it really to a SQL server or some other db.|||Originally posted by Pat Phelan
Can you elaborate just a bit on what constitutes "not working" ?

-PatP
I got:

Server: Msg 170, Level 15, State 31, Line 1
Line 2: Incorrect syntax near '('.|||Pat: an off-topic question: what's a table valued function?|||Originally posted by jora
Pat: an off-topic question: what's a table valued function? You can think of a table valued function as a view that takes parameters. You can read about them in the CREATE FUNCTION (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp) documentation, or an article at SQL Team (http://www.sqlteam.com/item.asp?ItemID=1955).

-PatP|||Thanks for the input everybody, and sorry I didn't respond until now. I've been looking further into the thing, and has come to the conclusion that my problem is really about linked servers and INSERTS.

I've started a new thread here:
http://www.dbforums.com/showthread.php?p=3673457#post3673457

Thanks
MNJ

Can't update linked server table

Dear Sir,
I have a script which update a linked server table.
e.g.
Update <LinkedServer>.DB1.Tbl1
Set col1 = B.col1
From <LinkedServer>.DB1.Tbl1 A
inner join <local>.DB0.Tbl1 B
On A.id = B.id
It function for several month and suddently I started to recevive error:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation genera
ted
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=60
0
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Any advise to make it function again?
HenryMost times If saw this error it was based on the absence of a unique
key (e.g. primary key). Examine the table if such a key exsist. if not
create on, that SQL Server is able to look up the row that currently
should be updated.
HTH, Jens Suessmeyer.|||Did you tryed to use OPENQUERY():
http://msdn.microsoft.com/library/d...br />
5xix.asp
"Henry" wrote:

> Dear Sir,
> I have a script which update a linked server table.
> e.g.
> Update <LinkedServer>.DB1.Tbl1
> Set col1 = B.col1
> From <LinkedServer>.DB1.Tbl1 A
> inner join <local>.DB0.Tbl1 B
> On A.id = B.id
> It function for several month and suddently I started to recevive error:
> Server: Msg 7306, Level 16, State 2, Line 1
> Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' from OLE DB provid
er
> 'SQLOLEDB'. The provider could not support a row lookup position. The
> provider indicates that conflicts occurred with other properties or
> requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation gene
rated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=
600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Any advise to make it function again?
> Henry
>

Can't update linked server table

Dear Sir,
I have a script which update a linked server table.
e.g.
Update <LinkedServer>.DB1.Tbl1
Set col1 = B.col1
From <LinkedServer>.DB1.Tbl1 A
inner join <local>.DB0.Tbl1 B
On A.id = B.id
It function for several month and suddently I started to recevive error:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Any advise to make it function again?
HenryMost times If saw this error it was based on the absence of a unique
key (e.g. primary key). Examine the table if such a key exsist. if not
create on, that SQL Server is able to look up the row that currently
should be updated.
HTH, Jens Suessmeyer.|||Did you tryed to use OPENQUERY():
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_5xix.asp
"Henry" wrote:
> Dear Sir,
> I have a script which update a linked server table.
> e.g.
> Update <LinkedServer>.DB1.Tbl1
> Set col1 = B.col1
> From <LinkedServer>.DB1.Tbl1 A
> inner join <local>.DB0.Tbl1 B
> On A.id = B.id
> It function for several month and suddently I started to recevive error:
> Server: Msg 7306, Level 16, State 2, Line 1
> Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' from OLE DB provider
> 'SQLOLEDB'. The provider could not support a row lookup position. The
> provider indicates that conflicts occurred with other properties or
> requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Any advise to make it function again?
> Henry
>

Can't update linked server table

Dear Sir,
I have a script which update a linked server table.
e.g.
Update <LinkedServer>.DB1.Tbl1
Set col1 = B.col1
From <LinkedServer>.DB1.Tbl1 A
inner join <local>.DB0.Tbl1 B
On A.id = B.id
It function for several month and suddently I started to recevive error:
Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
Any advise to make it function again?
Henry
Most times If saw this error it was based on the absence of a unique
key (e.g. primary key). Examine the table if such a key exsist. if not
create on, that SQL Server is able to look up the row that currently
should be updated.
HTH, Jens Suessmeyer.
|||Did you tryed to use OPENQUERY():
http://msdn.microsoft.com/library/de...oa-oz_5xix.asp
"Henry" wrote:

> Dear Sir,
> I have a script which update a linked server table.
> e.g.
> Update <LinkedServer>.DB1.Tbl1
> Set col1 = B.col1
> From <LinkedServer>.DB1.Tbl1 A
> inner join <local>.DB0.Tbl1 B
> On A.id = B.id
> It function for several month and suddently I started to recevive error:
> Server: Msg 7306, Level 16, State 2, Line 1
> Could not open table '"ABC_Sale_Rpt"."dbo"."Sale_Data"' from OLE DB provider
> 'SQLOLEDB'. The provider could not support a row lookup position. The
> provider indicates that conflicts occurred with other properties or
> requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
> Any advise to make it function again?
> Henry
>