timeout exipired vs StoreProc vs ado.net

[OLAPFOREVER]

SQL Server 2005 sp2 64 bits
cluster
on a San technologie
pooling connection is activate
--------------------------------

error: Timeut Expired. The timeout period elapsed prior to completion of the
operation or the server is not responding

Hi!

We have a very strange problem.

We write a store procedure who use fulltext search "contains" sentence

When the programer use this store proc from an ".net SQLClient data
provider" connection, all run well but sometime he receive a timeout expired
error

In the same time, If I run the same store proc from SQL server managment
studio (query) the store proc run well and never return a timeout expired...
never (under 1 sec response time)



I rewrite the store proc for dont use fulltext search but in place use a
basic "select ... from ... where" without "contains" sentence:
Same thing .. with ".net SQLClient data provider" = sometime return a
timeout.... from SQL server managment studio no timeout (under 1 sec
response time)


When then problem occurs, I monitor the connection in activity monitor I
See in "Wait type" column:
-when the store proc use fulltext search "contains" sentence : wait type=
MSSEARCH
-when the store proc dont use fulltext search "contains" sentence : wait
type = LCK_M_RS_S (Key range Shared/Shared)



Very strange....Could you help us

 

[Tom Dacon]

When you run a query from management studio, it sets its own timeout
interval, which is different from the default SqlCommand timeout interval in
the framework (only 15 seconds). This puzzled me too for a while.

The solution is to increase the timeout by setting the SqlCommand's
CommandTimeout property to a higher value (in seconds).

Tom Dacon
Dacon Software Consulting

 

[OLAPFOREVER]

increase the timeout is not the solution

the query return the result under 1 second (in ssms) but
in ado.net a timeout occurs