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 Re-indexing vs Offline Re-indexing

Hi,

The other day we tried online re-indexing feature of SQL 2005 and it’s performing faster than offline re-indexing. Could you please validate if it’s supposed to do be this way? I always thought offline should be faster than online.

Thanks,

Ritesh

Generally offline should be faster than online. Offline doesn't have to handle any changes that might be occurring on the table while the re-indexing is taking place, etc., so there's less work that needs to be done. If it seems the online form is faster than the offline, then perhaps it's a matter of caching or disk speed or some concurrent workload that is making it seem faster. Here's a few things that might make your timings invalid:

First, is the table whose index is being rebuilt of good size so that the difference isn't just a couple of seconds so that we're really just talking about 'noise' in the timing?

Has SQL Server's cache been cleared so that you're not including disk reads in one case and not including them in another?

Are they both going to the same disk(s), or is one benefiting from faster drives?

Are you sure one isn't benefiting from a data file that's already been grown to a sufficient size while the other had to wait for the files to be automatically grown? Or perhaps it's the log file that had to grow during the slower test but not during the faster one?

Was some other query running during the slow test that wasn't running during the faster one? Or some other process that was running on the same machine so that less memory or CPU was available?

Don

|||

Both were ran on same system utilizing the same drives. The index on each tables takes more than an hour hence we can really see the difference. The difference on one of the table is huge from 14 hours in offline to 5 hours in online.

Is there a possibility that if indexes are not fragmented it will take lesser time. We are just running rebuild index on tables currently there's no logic to eliminate certain indexes with less fragmentation.

The online re-indexing was ran after offline re-indexing does the sequence can make the difference?

Thanks,

Ritesh

|||

I'd bet the offline (first) test had to do a lot more work than the online (second) test. It's likely that you are continually having to auto-grow your datafile or log by a small amount at a time so that in the first test it's continually stopping to grow the file. Then the second test just uses that already-grown file and never has to wait like the first test did. Growing a data or log file can have a significant impact. Try growing the file to an appropriate size before you do either re-index operation and don't rely on auto-grow to size your files correctly.

When comparing operations like this you should start from exactly the same starting position. I'd recommend you restore from a backup before each re-index test .. it's very important to compare apples to apples when trying to analyze something like this.

Don

|||

We tried online re-indexing by restoring the same database and still we got the better results as compared to offline indexing. Timings have almost double, is there anything that data engine does internally so that online re-indexing is not a bottleneck on the application.

Thanks,

Ritesh

|||

It seems that to get to the bottom of this we'd have to know a lot more about the table(s), indexes, fragmentation, initial file sizes, concurrent workload, etc., which unfortunately I would likely not be able to dig into as deeply as might be needed.

Offline should generally be faster .. it has exclusive access to the table and indexes so there's less overhead.

I still think there must be something else that's adding extra work for the offline form, like having to grow data or log files where the online rebuild doesn't have to do this work. Can you confirm that you've increased the datafile and logfile sizes before you start the test so that it doesn't have to wait for autogrow?

Don

Online Re-indexing vs Offline Re-indexing

Hi,

The other day we tried online re-indexing feature of SQL 2005 and it’s performing faster than offline re-indexing. Could you please validate if it’s supposed to do be this way? I always thought offline should be faster than online.

Thanks,

Ritesh

Generally offline should be faster than online. Offline doesn't have to handle any changes that might be occurring on the table while the re-indexing is taking place, etc., so there's less work that needs to be done. If it seems the online form is faster than the offline, then perhaps it's a matter of caching or disk speed or some concurrent workload that is making it seem faster. Here's a few things that might make your timings invalid:

First, is the table whose index is being rebuilt of good size so that the difference isn't just a couple of seconds so that we're really just talking about 'noise' in the timing?

Has SQL Server's cache been cleared so that you're not including disk reads in one case and not including them in another?

Are they both going to the same disk(s), or is one benefiting from faster drives?

Are you sure one isn't benefiting from a data file that's already been grown to a sufficient size while the other had to wait for the files to be automatically grown? Or perhaps it's the log file that had to grow during the slower test but not during the faster one?

Was some other query running during the slow test that wasn't running during the faster one? Or some other process that was running on the same machine so that less memory or CPU was available?

Don

|||

Both were ran on same system utilizing the same drives. The index on each tables takes more than an hour hence we can really see the difference. The difference on one of the table is huge from 14 hours in offline to 5 hours in online.

Is there a possibility that if indexes are not fragmented it will take lesser time. We are just running rebuild index on tables currently there's no logic to eliminate certain indexes with less fragmentation.

The online re-indexing was ran after offline re-indexing does the sequence can make the difference?

Thanks,

Ritesh

|||

I'd bet the offline (first) test had to do a lot more work than the online (second) test. It's likely that you are continually having to auto-grow your datafile or log by a small amount at a time so that in the first test it's continually stopping to grow the file. Then the second test just uses that already-grown file and never has to wait like the first test did. Growing a data or log file can have a significant impact. Try growing the file to an appropriate size before you do either re-index operation and don't rely on auto-grow to size your files correctly.

When comparing operations like this you should start from exactly the same starting position. I'd recommend you restore from a backup before each re-index test .. it's very important to compare apples to apples when trying to analyze something like this.

Don

|||

We tried online re-indexing by restoring the same database and still we got the better results as compared to offline indexing. Timings have almost double, is there anything that data engine does internally so that online re-indexing is not a bottleneck on the application.

Thanks,

Ritesh

|||

It seems that to get to the bottom of this we'd have to know a lot more about the table(s), indexes, fragmentation, initial file sizes, concurrent workload, etc., which unfortunately I would likely not be able to dig into as deeply as might be needed.

Offline should generally be faster .. it has exclusive access to the table and indexes so there's less overhead.

I still think there must be something else that's adding extra work for the offline form, like having to grow data or log files where the online rebuild doesn't have to do this work. Can you confirm that you've increased the datafile and logfile sizes before you start the test so that it doesn't have to wait for autogrow?

Don

Online Re-indexing vs Offline Re-indexing

Hi,

The other day we tried online re-indexing feature of SQL 2005 and it’s performing faster than offline re-indexing. Could you please validate if it’s supposed to do be this way? I always thought offline should be faster than online.

Thanks,

Ritesh

Generally offline should be faster than online. Offline doesn't have to handle any changes that might be occurring on the table while the re-indexing is taking place, etc., so there's less work that needs to be done. If it seems the online form is faster than the offline, then perhaps it's a matter of caching or disk speed or some concurrent workload that is making it seem faster. Here's a few things that might make your timings invalid:

First, is the table whose index is being rebuilt of good size so that the difference isn't just a couple of seconds so that we're really just talking about 'noise' in the timing?

Has SQL Server's cache been cleared so that you're not including disk reads in one case and not including them in another?

Are they both going to the same disk(s), or is one benefiting from faster drives?

Are you sure one isn't benefiting from a data file that's already been grown to a sufficient size while the other had to wait for the files to be automatically grown? Or perhaps it's the log file that had to grow during the slower test but not during the faster one?

Was some other query running during the slow test that wasn't running during the faster one? Or some other process that was running on the same machine so that less memory or CPU was available?

Don

|||

Both were ran on same system utilizing the same drives. The index on each tables takes more than an hour hence we can really see the difference. The difference on one of the table is huge from 14 hours in offline to 5 hours in online.

Is there a possibility that if indexes are not fragmented it will take lesser time. We are just running rebuild index on tables currently there's no logic to eliminate certain indexes with less fragmentation.

The online re-indexing was ran after offline re-indexing does the sequence can make the difference?

Thanks,

Ritesh

|||

I'd bet the offline (first) test had to do a lot more work than the online (second) test. It's likely that you are continually having to auto-grow your datafile or log by a small amount at a time so that in the first test it's continually stopping to grow the file. Then the second test just uses that already-grown file and never has to wait like the first test did. Growing a data or log file can have a significant impact. Try growing the file to an appropriate size before you do either re-index operation and don't rely on auto-grow to size your files correctly.

When comparing operations like this you should start from exactly the same starting position. I'd recommend you restore from a backup before each re-index test .. it's very important to compare apples to apples when trying to analyze something like this.

Don

|||

We tried online re-indexing by restoring the same database and still we got the better results as compared to offline indexing. Timings have almost double, is there anything that data engine does internally so that online re-indexing is not a bottleneck on the application.

Thanks,

Ritesh

|||

It seems that to get to the bottom of this we'd have to know a lot more about the table(s), indexes, fragmentation, initial file sizes, concurrent workload, etc., which unfortunately I would likely not be able to dig into as deeply as might be needed.

Offline should generally be faster .. it has exclusive access to the table and indexes so there's less overhead.

I still think there must be something else that's adding extra work for the offline form, like having to grow data or log files where the online rebuild doesn't have to do this work. Can you confirm that you've increased the datafile and logfile sizes before you start the test so that it doesn't have to wait for autogrow?

Don