No results returned to a reader but the same query works thru SQL Management Studio

Question

Hello,

I'm experiencing weird behavior with some of full text search queries, especially those with multiple words. These are working fine when executed thru Management Studio but returning no results when called from a code. I did a SQL Trace to see what commands are being sent from my app and exactly same command come with results when executed from Management Studio but come with no results when called from my app using ExecuteReader method.

This call:

exec dbo.FullTextSearch_Articles @ftsQuery=N' FORMSOF (INFLECTIONAL, moravec) '

will return data for both my app and Management Studio while this command:

exec dbo.FullTextSearch_Articles @ftsQuery=N'( FORMSOF (INFLECTIONAL, jan)  AND  FORMSOF (INFLECTIONAL, moravec) )'

will only return data when executed from Management Studio. I copy/pasted these queries directly from a trace log.

On a code side, I'm using Enterprise Library, but overall my DB call is really simple:

using (var dataReader = (SqlDataReader)db.ExecuteReader(cmd))
{
   if (dataReader.HasRows)
   {
       var results = new List<IFullTextSearchItem>();
       while (dataReader.Read())
       {
          results.Add(CreateArticleSearchFromReader(dataReader));
       }
       return results;
    }
    return null;
}

In the second case, dataReader.HasRows is false for some reason, but again, when those queries are executed from a Management Studio, both returns some data.

I thought it might be due to a number of rows returned (second query returns much bigger set of results) but then sucessfully tested single word search with even more rows returned.

Any idea why DataReader would behave different from a simple Management Studio query execution would be appreciated.

Thanks,

Antonin

Answer 1

OK, this is interesting. I did a bit more debugging and found that dataReader.HasRows property is not accurate - for some reason this is set to false in some case even when there are data in Results View collection.

I wonder whether reader.HasRows is somehow dependable on the amount of data being transferred between client app and server - so in the case of lot of data, this property is not updated immediately once ExecuteReader method is called.

Anyway, simply by removing check for reader.HasRows solves this issue.