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

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

Has No-one got any idea the above Question ? Is the above problem that complicated ?

|||

savvy wrote:

Has No-one got any idea the above Question ? Is the above problem that complicated ?

You question is not complicated but it is not valid implementation because SQL Server can perform what you want back in SQL Server 7.0 in 1999. Now if you can interested in valid solution post again and I can give you some links.

The reason Information Schema Views and Openquery are ANSI SQL for inter RDBMS( relational database management system) communication not for SQL Server and IIS index server. Hope this helps.

|||

Thanks for your help. So, Is my analogy wrong ? I have implemented this because i need to link SQL server and Index Server so that i can grab the data in the SQL Server and i had no idea of other ways of achieving this .

If you got any information to get around this problem that will be really great as I've been trying to solve this problem since a week.

Thanks in Advance

|||

Try these links the first deals with using both Image and text columns to get what you want and the second is SQL Server Full Text Blog, he was with the Microsoft SQL Server Full Text team. If you cannot find your solution in his blog he will answer your post at SQL Server Central forums. Hope this helps.

http://forums.asp.net/949146/ShowPost.aspx

http://spaces.msn.com/members/jtkane/?partqs=cat%3DSQL+Server+2000+Full-Text+Search&_c11_blogpart_blogpart=blogview&_c=blogpart

|||

Thanks for your help

Can you tel me is there any way to read the Word or PDF documents and store the text it in a database field (ntext) . Is this is possible?

Thanks in advance

|||

Savvy,

I think you have skipped design and is coding so you are complicating simple problems. The create table statement below comes from Microsoft new sample database AdventureWorks, you can store the files as Word or PDF on image columns but also use text to store the same files so you can use the Microsoft Full Text Index and do the key word search you want. What design do for you is look for alternative implementations which takes the complications out of the problem. Run a search for the AdventureWorks database on Microsoft site install it run tests and take the tables you need for your application. Hope this helps.

CREATE TABLE [ProductPhoto] (
[ProductPhotoID] [int] IDENTITY (1, 1) NOT NULL ,
[ThumbNailPhoto] [image] NULL ,
[ThumbnailPhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LargePhoto] [image] NULL ,
[LargePhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_ProductPhoto_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_ProductPhoto_ProductPhotoID] PRIMARY KEY CLUSTERED
(
[ProductPhotoID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

Thanks Caddre for your help. It was a really long journey after i got your reply, when i got shifted totally from linked servers to FULL TEXT INDEXING in MICROSOFT SEARCH SERVICE. I had many problems to solve such as my FULL TEXT INDEXING was grayed out so i need to install DEVELOPER Edition on my System coz my O/S is Win XP Prof. I read many of your Posts regarding this topic. I personally thank u alot for your help which u have rendered in this field.

Thank you very much

|||What error are you getting?|||

Savvy,

Thanks for the complement and I am glad I could help.

Open SqlCeConnection from an ASP.NET Web Service?

Help!

I am trying to implement a web service that creates and populates a SQL Mobile database file, then returns the created database to a mobile device as a byte array. The database size could be in excess of 500,000 rows, which is why I want to do as much of this preprocessing on the server before it gets to the mobile device. I can't use replication since I have to do some shaping of the data before I can use it on the mobile device.

Unfortunately, the web service is throwing the following exception when I try to instantiate a SqlCeConnection object:

"System.NotSupportedException: SQL Server Everywhere Edition is not intended for ASP.NET development."

Are there any suggestions as to how I can get around this potential limitation? If I refactor out the code that actually performs the SqlCe operations to a separate assembly, but still call that assembly from within the ASP.NET process, will I get the same error?

I know that you can work with Sql Mobile databases from the deskop, and I suppose I could invoke a console application to create the database, but that seems like such a hack.

Thanks for any advice,

Matthew

This limitation was built into the SQL Everywhere engine because it is supposed to be used as an embedded database (running in-proc with your application), and not in a client-server scenario. The engine will actually test if there are any IIS processes in the calling stack (not sure about the terminology here), so you are out of luck.

Maybe someone smarter than me has a solution - I would also love to know it.

|||

I can understand the reasoning behind that decision; SQL Express is positioned perfectly for that case, but I am definitely not trying to use the SQL Everywhere engine in a client/server scenario.

I am just trying to let a server with significantly more resources pre-build a database for use on a mobile device, then ship the database down to the client. I'd hate to have to write some process that I can "shell" out to from the web app, generate the .sdf file, then read it in by the web service, but I really don't want to if I don't have to. Replication is not really an option, unfortunately.

Thanks,

Matthew

Open .RDL in ASP.NET without ReportServer

Is it possible to simply add an .RDL to an asp.net project, and access the
report dynamically without any report url or anything.
I just want to pass the database authentication to the .RDL [reportViewer?]
and a few parameters, and run the report. But I do not want the report to
exist in a subweb or http://reportServer etc. I just want to drop it right
into my project, and go.
Thanks for any links or advice.This is totally possible with the new controls that ship with VS 2005. You
can develop the report and then view it in your app using the controls. Note
that this is more work. You have to handle subreports, drill through etc.
Also, I found the design environment in VS 2005 that comes with VS to design
reports nowhere as near as nice as the report designer that comes with SQL
Server. Note that if you have SQL Server, the designer comes with it. You
can develop and preview the reports without deploying them, then just change
the extension and bring them into your project.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <cmill575@.hotmail.com> wrote in message
news:u5QFgtdBGHA.3604@.TK2MSFTNGP09.phx.gbl...
> Is it possible to simply add an .RDL to an asp.net project, and access the
> report dynamically without any report url or anything.
> I just want to pass the database authentication to the .RDL
> [reportViewer?] and a few parameters, and run the report. But I do not
> want the report to exist in a subweb or http://reportServer etc. I just
> want to drop it right into my project, and go.
> Thanks for any links or advice.
>|||Thanks for your response. That's good news. I hope MS improves the designer
in VS.
Cant you just design the report using the SQL Server report designer and
then drop the rdl into your web project?
Anyway, thanks again.
BTW: Does anyone have any walkthrough's using this new functionality? I
havent used RS since it first came out.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:u1R684jBGHA.3292@.TK2MSFTNGP09.phx.gbl...
> This is totally possible with the new controls that ship with VS 2005. You
> can develop the report and then view it in your app using the controls.
> Note that this is more work. You have to handle subreports, drill through
> etc. Also, I found the design environment in VS 2005 that comes with VS to
> design reports nowhere as near as nice as the report designer that comes
> with SQL Server. Note that if you have SQL Server, the designer comes with
> it. You can develop and preview the reports without deploying them, then
> just change the extension and bring them into your project.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chris" <cmill575@.hotmail.com> wrote in message
> news:u5QFgtdBGHA.3604@.TK2MSFTNGP09.phx.gbl...
>> Is it possible to simply add an .RDL to an asp.net project, and access
>> the report dynamically without any report url or anything.
>> I just want to pass the database authentication to the .RDL
>> [reportViewer?] and a few parameters, and run the report. But I do not
>> want the report to exist in a subweb or http://reportServer etc. I just
>> want to drop it right into my project, and go.
>> Thanks for any links or advice.
>|||You can use the SQL Server report designer and then rename the extension and
then use that. That is really the easiest. The issue with VS is that there
is no preview. You have to deploy and use either your web page or your
winform to view the report.
Just dropping the rdl into your web project makes no sense unless there is
something to render the report. That is where the new controls come in.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <cmill575@.hotmail.com> wrote in message
news:%23Xt1UVpBGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Thanks for your response. That's good news. I hope MS improves the
> designer in VS.
> Cant you just design the report using the SQL Server report designer and
> then drop the rdl into your web project?
> Anyway, thanks again.
> BTW: Does anyone have any walkthrough's using this new functionality? I
> havent used RS since it first came out.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:u1R684jBGHA.3292@.TK2MSFTNGP09.phx.gbl...
>> This is totally possible with the new controls that ship with VS 2005.
>> You can develop the report and then view it in your app using the
>> controls. Note that this is more work. You have to handle subreports,
>> drill through etc. Also, I found the design environment in VS 2005 that
>> comes with VS to design reports nowhere as near as nice as the report
>> designer that comes with SQL Server. Note that if you have SQL Server,
>> the designer comes with it. You can develop and preview the reports
>> without deploying them, then just change the extension and bring them
>> into your project.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Chris" <cmill575@.hotmail.com> wrote in message
>> news:u5QFgtdBGHA.3604@.TK2MSFTNGP09.phx.gbl...
>> Is it possible to simply add an .RDL to an asp.net project, and access
>> the report dynamically without any report url or anything.
>> I just want to pass the database authentication to the .RDL
>> [reportViewer?] and a few parameters, and run the report. But I do not
>> want the report to exist in a subweb or http://reportServer etc. I just
>> want to drop it right into my project, and go.
>> Thanks for any links or advice.
>>
>