OPENQUERY from the ASP.NET page problem?

[savvy]

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 (
displays 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();

 

[Daniel Fisher\(lennybacon\)]

Error details?

--Daniel
http://staff.newtelligence.com/danielf/




-----Original Message-----
From: savvy [mailto:[email protected]]
Posted At: Monday, January 23, 2006 10:17 AM
Posted To: microsoft.public.dotnet.framework.aspnet
Conversation: OPENQUERY from the ASP.NET page problem?
Subject: OPENQUERY from the 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 (
displays 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();

 

[savvy]

Thanks for your time Danie
At present i'm getting some results which are static not changing with
the search word
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;");
i'm not using any provider name, catalog name nothing of that
sort in my connection string, Is that right ?
but if i use this in my stored procedure , it gives the error saying
"USER DOESNOT HAVE PERMISSION TO DO THIS ACTION"
Exec sp_addlinkedserver FileSystem,
'Index Server',
'MSIDXS',
'Web'
Thanks in Advance