Error executing a sql query when using SearchService.Search

I am working on a process to update all tiff files to pdf files in content server using web services. There are millions of files need to be converted. I have everything working now except it only works on small amount of files, like 35,000 files. When I set the SingleSearchRequest.NumResultsToRetrieve more than 40,000, then "Error executing a sql query" exception happens. I am just wondering is there a settings in OpenText is preventing me to search more files? Or I should use some different method to retrieve all the tiff files.

Thanks

Find more posts tagged with

Comments

Ferdinand Prantl

You shouldn't ask for extreme number of results. Servers have throttling rules to reject such requests. The right aproach is to request the search results page by page in a loop. Have a look at this post to learn how to get all search results.

musiala

Thank you for your reply. So do you know what's the maximun result it could retrieve? so I can create paging with maximun capicity.

Ferdinand Prantl

I don't know. If nobody answers, I'd pick 1000. I don't think that it's important to maximize the returned page.

musiala

In my current case, I am converting searched Tiffs into PDF files. I am not sure how search service works by specifying the starting point and total return number. For example, I need convert 1,000,000 tiff files. In my search query, I search for 200,000 files and converting those tiff files to pdf files each time. When the next 200,000 files fetched, since I already have the first set of 200,000 files converted to PDF. If Content Server finishes some indexing already, does the starting point at 200,001 for Tiff images is still the same?

Hans Stoop

You need to look at the connect logs to find out why the sql query failed.

Hans

From: eLink Entry: Content Web Services Forum [mailto:otdncontentwebservices971forum@elinkkc.opentext.com]
Sent: Donnerstag, 5. Dezember 2013 19:31
To: eLink Recipient
Subject: Error executing a sql query when using SearchService.Search

Error executing a sql query when using SearchService.Search

Posted bymusi.ala@erissolutions.ca (Ala, Musi) On 12-05-2013 13:28

Thanks

[To post a comment, use the normal reply function]
Forum:	Content Web Services Forum
Content Server:	Knowledge Center

Kyle_Swidrovich

The search index is a living, breathing organism. One new change to the index could skew the ordering of results as each individual search operation is independent if you are sorting your results by “Score”.

If you sort your results by DataID, for example, then you should be able to keep the items in order.

However, if objects are deleted between runs, then you may miss some items.

If you’re looking to ensure that you hit every TIFF file, then perhaps using search for this is inappropriate.

You may want to call RunReport in the DocumentManagement service to run a LiveReport that queries the database and provides the information you need.

eLink User

I am not sure of the implication of time and indexing but am leaning to say the search results are cached somehow.If I really wanted to get a no miss application I would try to get all dataids thru a SQL query provided the mime type and file type info was right.That way I could rely on my own looping mechanism and not worry about the search intricacies the maximum I would have to do is get access to the live link database thru a live report or a actual db connection

Just some thoughts other than using search

Sent from my iPad

On Dec 6, 2013, at 11:24 AM, "eLink Entry: Content Web Services Forum" <otdncontentwebservices971forum@elinkkc.opentext.com> wrote:

eLink : Error executing a sql query when using SearchService.Search
Error executing a sql query when using SearchService.Search Posted bymusi.ala@erissolutions.ca (Ala, Musi)On 12-06-2013 12:06

In my current case, I am converting searched Tiffs into PDF files. I am not sure how search service works by specifying the starting point and total return number. For example, I need convert 1,000,000 tiff files. In my search query, I search for 200,000 files and converting those tiff files to pdf files each time. When the next 200,000 files fetched, since I already have the first set of 200,000 files converted to PDF. If Content Server finishes some indexing already, does the starting point at 200,001 for Tiff images is still the same?
[To post a comment, use the normal reply function]
Topic: Error executing a sql query when using SearchService.Search
Forum: Content Web Services Forum
Content Server: Knowledge Center