CODEWITHSUNDEEP
sql-serverstored-proceduresado.netfull-text-search
Ross
Ross

Reputation: 4578

SQL Full Text Query - behaving differently depending on how I call a stored procedure

I've come across a confusing issue.

I have a Stored Procedure in my SQL Server 2008 R2 database which takes a single parameter @Query and runs a simple Full Text Search query:

CREATE PROCEDURE dbo.SearchMyTable
    @Query varchar(250)
AS
    SELECT
        ...
    FROM
        ...
        INNER JOIN FREETEXTTABLE (t, *, @Query) ft ON ( t.Id = ft.[Key] )
ORDER BY
    ft.Rank DESC

When I open a query window in SQL Server Management Studio, and test this by typing:

SearchMyTable 'truck'

It works great.

When I type the following, it still works, and returns the same results (presumably ignoring the noise word 'a')

SearchMyTable 'a truck'

But then I took into an ASP.NET page.

private SqlDataReader DoQuery(SqlConnection connection, string query) {
    SqlParameter parameter = new SqlParameter("Query", SqlDbType.VarChar);
    parameter.Value = query;

    SqlCommand command = connection.CreateCommand();
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "SearchMyTable";
    command.Parameters.Add(parameter);

    return command.ExecuteReader();
}

When I call DoQuery(connection, "truck"); it works, and gets the same results again, which I format and print out on my page.

But when I do the second version with the noise word, DoQuery(connection, "a truck");, the SqlDataReader comes back empty with no rows!

I'm very confused. I assume that my stored procedure is fine, as it works when calling it directly from a query window in SQL Management Studio.

So why does it seem to get different results depending on whether I call it as a raw query, or through the SQL Server ADO.NET provider in my ASP.NET page? Am I missing something?

EDIT: It would seem there is nothing wrong with the above. I found a solution, which may even be a bug in SQL/.NET. See my answer below.

Upvotes: 2

Views: 495

Answers (2)

benni_mac_b
benni_mac_b

Reputation: 8877

Try running SQL Server Profiler to see what SQL is actually executing when run from the code.

Upvotes: 1

Ross
Ross

Reputation: 4578

Found a solution.

The code I didn't show you was how I read my data reader in my ASP.NET page:

if (dr.HasRows) {
    ...
    while (dr.Read()) { ... }
    ...
} else {
    // no results
    ...
}

It turned out that dr.HasRows was returning false, even though there were rows! This has never happened to me before in 10 years of ADO.NET experience.

So I changed it to:

if (dr.Read()) {
    ...
    do { ... } while (dr.Read());
    ...
} else {
    // no results
    ...
}

And it works.

Upvotes: 1

Related Questions