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.

Online Reindex Build Perfomance Issues

We are in the process of testing online reindex rebuilds on a 260 Gig
database on a table with 40 gig of data and an index size of 42 gigs.
We are finding that this process is taking up a substantial amount of
resources (processor and Disk IO) so much so that at time even simple
queries are locking. Does any one know of any means to improve the
performance of the reindexing or perhaps lower it priority to reduce it
impact?
Thanks in advance,
-Bob"Bob Sills" <bobsills@.gmail.com> wrote in message
news:1163779525.169860.225690@.b28g2000cwb.googlegroups.com...
> We are in the process of testing online reindex rebuilds on a 260 Gig
> database on a table with 40 gig of data and an index size of 42 gigs.
> We are finding that this process is taking up a substantial amount of
> resources (processor and Disk IO) so much so that at time even simple
> queries are locking. Does any one know of any means to improve the
> performance of the reindexing or perhaps lower it priority to reduce it
> impact?
>
That's a gotcha with online rebuilds. They don't use locks, but they may
run in parallel and chew up a lot of resources. Try setting the MAXDOP in
the ALTER INDEX statement to reserve processors for your other workloads.
David|||David Browne wrote:
> "Bob Sills" <bobsills@.gmail.com> wrote in message
> news:1163779525.169860.225690@.b28g2000cwb.googlegroups.com...
> > We are in the process of testing online reindex rebuilds on a 260 Gig
> > database on a table with 40 gig of data and an index size of 42 gigs.
> > We are finding that this process is taking up a substantial amount of
> > resources (processor and Disk IO) so much so that at time even simple
> > queries are locking. Does any one know of any means to improve the
> > performance of the reindexing or perhaps lower it priority to reduce it
> > impact?
> >
> That's a gotcha with online rebuilds. They don't use locks, but they may
> run in parallel and chew up a lot of resources. Try setting the MAXDOP in
> the ALTER INDEX statement to reserve processors for your other workloads.
>
> David
Thanks! This is helping greatly.
-Bob

Online Reindex Build Perfomance Issues

We are in the process of testing online reindex rebuilds on a 260 Gig
database on a table with 40 gig of data and an index size of 42 gigs.
We are finding that this process is taking up a substantial amount of
resources (processor and Disk IO) so much so that at time even simple
queries are locking. Does any one know of any means to improve the
performance of the reindexing or perhaps lower it priority to reduce it
impact?
Thanks in advance,
-Bob
"Bob Sills" <bobsills@.gmail.com> wrote in message
news:1163779525.169860.225690@.b28g2000cwb.googlegr oups.com...
> We are in the process of testing online reindex rebuilds on a 260 Gig
> database on a table with 40 gig of data and an index size of 42 gigs.
> We are finding that this process is taking up a substantial amount of
> resources (processor and Disk IO) so much so that at time even simple
> queries are locking. Does any one know of any means to improve the
> performance of the reindexing or perhaps lower it priority to reduce it
> impact?
>
That's a gotcha with online rebuilds. They don't use locks, but they may
run in parallel and chew up a lot of resources. Try setting the MAXDOP in
the ALTER INDEX statement to reserve processors for your other workloads.
David
|||David Browne wrote:
> "Bob Sills" <bobsills@.gmail.com> wrote in message
> news:1163779525.169860.225690@.b28g2000cwb.googlegr oups.com...
> That's a gotcha with online rebuilds. They don't use locks, but they may
> run in parallel and chew up a lot of resources. Try setting the MAXDOP in
> the ALTER INDEX statement to reserve processors for your other workloads.
>
> David
Thanks! This is helping greatly.
-Bob

Online Reindex Build Perfomance Issues

We are in the process of testing online reindex rebuilds on a 260 Gig
database on a table with 40 gig of data and an index size of 42 gigs.
We are finding that this process is taking up a substantial amount of
resources (processor and Disk IO) so much so that at time even simple
queries are locking. Does any one know of any means to improve the
performance of the reindexing or perhaps lower it priority to reduce it
impact?
Thanks in advance,
-Bob"Bob Sills" <bobsills@.gmail.com> wrote in message
news:1163779525.169860.225690@.b28g2000cwb.googlegroups.com...
> We are in the process of testing online reindex rebuilds on a 260 Gig
> database on a table with 40 gig of data and an index size of 42 gigs.
> We are finding that this process is taking up a substantial amount of
> resources (processor and Disk IO) so much so that at time even simple
> queries are locking. Does any one know of any means to improve the
> performance of the reindexing or perhaps lower it priority to reduce it
> impact?
>
That's a gotcha with online rebuilds. They don't use locks, but they may
run in parallel and chew up a lot of resources. Try setting the MAXDOP in
the ALTER INDEX statement to reserve processors for your other workloads.
David|||David Browne wrote:
> "Bob Sills" <bobsills@.gmail.com> wrote in message
> news:1163779525.169860.225690@.b28g2000cwb.googlegroups.com...
> That's a gotcha with online rebuilds. They don't use locks, but they may
> run in parallel and chew up a lot of resources. Try setting the MAXDOP in
> the ALTER INDEX statement to reserve processors for your other workloads.
>
> David
Thanks! This is helping greatly.
-Bob

online indexing on standard edition

Can you create an online index on SQL 2005 standard edition ?
No, they are available only on the Enterprise Edition.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> Can you create an online index on SQL 2005 standard edition ?
>
>

online indexing on standard edition

Can you create an online index on SQL 2005 standard edition ?No, they are available only on the Enterprise Edition.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> Can you create an online index on SQL 2005 standard edition ?
>
>

online indexing on standard edition

Can you create an online index on SQL 2005 standard edition ?No, they are available only on the Enterprise Edition.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> Can you create an online index on SQL 2005 standard edition ?
>
>

online index rebuild Problem

Hi

Please advice whether i have to do update stats after i do online index rebuild

I use Alter index with (options as below)

ONLINE = ON

SORT_IN_TEMPDB = ON

STATISTICS_NORECOMPUTE =OFF

Should i be doing update stats after this online index rebuild?

After doing online index rebuild when clients connecting to DB the Response time has increased ..

During Online INDEX REBUILD page file usage rises from 3 Gb to 10 GB and stays at 10 Gb even after online index rebuild

i have no idea on this ..

pls Advice ...I Need help..........................

Thanks in advance

By rebuilding the index, the default behavior of SQL Server 2005 is to autoupdate statistics

So no extra work is needed by you

|||

Pagefile is increased, then check the memory settings on SQL Server.

Also if there are tables with frequent inserts and updates then its better to schedule an intermittent UPDATE stats for optimum performance.

online index rebuild and update statistics

Hi ..

Please advice whether i have to do update statistics along with Index rebuild (online /off line)

Thanks in advance

This depends on the traffic on your DB. IF its an OLTP/heavily used DB you might keep this for off peak use. Your Index scripts could be scripted to either update or do not update stats. You dont *have to* update stats. Its optional. But generally its good to keep updated stats. Again, depending on your Db size and usage, you can turn Auto Statistics ON or OFF.|||

Manual statistics update is preferable in off-peak hours

|||To reword his question a little bit, if you perform Alter Index .... Rebuild, do you need to also perform an Update Statistics with full scan afterwards? The Alter Index ... Rebuild updates stats based on sampling rates. Is there any real advantage to updating the stats with a full scan afterwards?

online index rebuild and update statistics

Hi ..

Please advice whether i have to do update statistics along with Index rebuild (online /off line)

Thanks in advance

This depends on the traffic on your DB. IF its an OLTP/heavily used DB you might keep this for off peak use. Your Index scripts could be scripted to either update or do not update stats. You dont *have to* update stats. Its optional. But generally its good to keep updated stats. Again, depending on your Db size and usage, you can turn Auto Statistics ON or OFF.|||

Manual statistics update is preferable in off-peak hours

|||To reword his question a little bit, if you perform Alter Index .... Rebuild, do you need to also perform an Update Statistics with full scan afterwards? The Alter Index ... Rebuild updates stats based on sampling rates. Is there any real advantage to updating the stats with a full scan afterwards?

Online index defrag i SQL 7 ?

I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
are doing the same thing online ? DBCC dbreindex is not online or ?
I don't want the operation to interfer with the users.
Sincerely
\\Jonas BI'm sorry, Jonas, but INDEXDEFRAG was introduced in SQL2K. Not much we can do about that, there
isn't anything similar in 7.0.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
news:uG%232EVEhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
> are doing the same thing online ? DBCC dbreindex is not online or ?
> I don't want the operation to interfer with the users.
> Sincerely
> \\Jonas B
>

Online index defrag i SQL 7 ?

I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
are doing the same thing online ? DBCC dbreindex is not online or ?
I don't want the operation to interfer with the users.
Sincerely
\\Jonas B
I'm sorry, Jonas, but INDEXDEFRAG was introduced in SQL2K. Not much we can do about that, there
isn't anything similar in 7.0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
news:uG%232EVEhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
> are doing the same thing online ? DBCC dbreindex is not online or ?
> I don't want the operation to interfer with the users.
> Sincerely
> \\Jonas B
>

Online index defrag i SQL 7 ?

I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
are doing the same thing online ? DBCC dbreindex is not online or ?
I don't want the operation to interfer with the users.
Sincerely
\\Jonas BI'm sorry, Jonas, but INDEXDEFRAG was introduced in SQL2K. Not much we can d
o about that, there
isn't anything similar in 7.0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
news:uG%232EVEhEHA.3520@.TK2MSFTNGP10.phx.gbl...
> I have used dbcc indexdefrag i sql2000 but are there one in sql 7.0 which
> are doing the same thing online ? DBCC dbreindex is not online or ?
> I don't want the operation to interfer with the users.
> Sincerely
> \\Jonas B
>