Friday, March 30, 2012

OPENQUERY from ASP.NET Page Problem?

I'm performing a particular word Search in MS Word, Text, PDF docs and
displaying the results through Index Server linked to SQL Server when
it is matched. For which I'm using Openquery in the stored procedure
which works fine in Query Analyzer of the SQL Server but doesn't work (
shows none of the results) when i call it from the ASP.NET Page. I am
not able to figure out Where and What is the problem?
The Stored Proc which is i'm using is shown below
Any help will be greatly appreciated. Thanks for your time and help in
Advance
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
which works with followin stat in Query Analyzer
Exec SelectIndexServerCVpaths
@.searchstring = 'The Search text'
but doesn't work when i connect it to a Datagrid in my ASP.NET Page
objcmd = new SqlCommand("SelectIndexServerCVpaths", objConn);
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.Add("@.searchstring",strsearchstrings);
objConn.Open();
objRdr = objcmd.ExecuteReader();
dgcvs.DataSource=objRdr;
dgcvs.DataBind();
objRdr.Close();
objConn.Close();Did you try it with impersonation on?
http://support.microsoft.com/kb/323293/en-us
Also why don't you just query indexing services directly through ixsso, or
msidxs?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"savvy" <johngera@.gmail.com> wrote in message
news:1137934280.281102.175800@.o13g2000cwo.googlegroups.com...
> I'm performing a particular word Search in MS Word, Text, PDF docs and
> displaying the results through Index Server linked to SQL Server when
> it is matched. For which I'm using Openquery in the stored procedure
> which works fine in Query Analyzer of the SQL Server but doesn't work (
> shows none of the results) when i call it from the ASP.NET Page. I am
> not able to figure out Where and What is the problem?
> The Stored Proc which is i'm using is shown below
> Any help will be greatly appreciated. Thanks for your time and help in
> Advance
>
> 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
> which works with followin stat in Query Analyzer
> Exec SelectIndexServerCVpaths
> @.searchstring = 'The Search text'
> but doesn't work when i connect it to a Datagrid in my ASP.NET Page
> objcmd = new SqlCommand("SelectIndexServerCVpaths", objConn);
> objcmd.CommandType = CommandType.StoredProcedure;
> objcmd.Parameters.Add("@.searchstring",strsearchstrings);
> objConn.Open();
> objRdr = objcmd.ExecuteReader();
> dgcvs.DataSource=objRdr;
> dgcvs.DataBind();
> objRdr.Close();
> objConn.Close();
>|||Thanks for your time and help
I'll try out the above|||Thanks for your time Hillary
I tried with impersonation with both true and false as well
it didn't make any difference
At present i'm getting some results which are static not changing with
the search word
I can't query just Indexing Services as you can see in my stored
procedure i'm linking my SQL Server Database table with the Index
Server Results
and displaying the results
Is there any problem in my Connection String which is shown below
SqlConnection objConn = new
SqlConnection(" Server=MISC\\MISC;Database=sapresources;
User
ID=sap;Password=sapres;");
that's it i'm not using any provider name, catalog name nothing of that
sort, Is that right ?sql

No comments:

Post a Comment