Hi everyone,

I got this situation, I got a stored procedure that for some reason
after using it for some period of time (maybe a day or two) stops
sending results, but if I change the position of the condition (where)
on the first line then begins to work ok. This solution works a day or
two but the issue arise again and I must change the line to it's
original version, completing this weird cycle. have you ever seen this
kind of behavior before?
The first commented line is what i need to change time to time...


--Where (Cr.ID_Credito = @ID_Credito Or @ID_Credito = 0)
Where (@ID_Credito = 0 Or Cr.ID_Credito = @ID_Credito)
And (Co.Rut_Cliente = @Rut_Cliente Or @Rut_Cliente = '')
And (Cr.Estado_Credito = @Estado_Credito Or @Estado_Credito = '')
And (Cr.Confirmado= @Confirmado or @Confirmado='')
And (Cr.Estado_Credito in
('CP','AN','CE','CA','EO','AC','CO','CR','CD','JU' ))
And (Cr.Rut_Empresa = @Rut_Empresa Or @Rut_Empresa = '')
and (Co.Codigo_Local=@Codigo_Local or @Codigo_Local=0)
and (Cr.Automotora=@Rut_Automotora or @Rut_Automotora='')
and (PE.Rut_persona=@Rut_Persona or @Rut_Persona='')
and (Cr.Confirmado = @Confirmado or @Confirmado='')
and (Cr.Garantizado = @Garantizado or @Garantizado='')


Thanks.
Diego.

ASP, Com+
SQL Server 2005 SP3

Wishmaster ("sysadmin.rock[SINESTO]"@gmail.com) writes:
What more exactly do you mean with stop sending results? Does it not
produce a result set at all? Does it only produce an empty result set?
Is there an error message?

What happens if you run the procedure from Management Studio? If you run
the procedure from Mgmt Studio, after first having executed SET ARITHABORT
OFF?

Anyway, I would suggest that you add this at the end of the query:

OPTION (RECOMPILE)

Since I assume that this is somehow related to the query plan, I expect that
this should resolve the issue. I would also expect it to improve the overall
performance.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 05-05-2011 18:56, Erland Sommarskog wrote:

what i'm trying to say is there not any result set on my website and no
error message as well. But if i run the stored procedure from Mgnt
Studio takes arround 3 minutes to produce a result set.
I'm going to check your suggestions.
Thank you!

Diego.

Wishmaster ("sysadmin.rock[SINESTO]"@gmail.com) writes:
Three minutes is a tad long for a search from a web page. Since the default
timeout in most client API is 30 seconds, I suspect that you get a timeout.

I have an article on my web site that discusses various strategies
for these kind of searches, including the one you are using.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 06-05-2011 17:52, Erland Sommarskog wrote:

This morning it happened again, but we research it about the recompile
option that you suggested and we used it. Now i'm going to track this
particular issue. I will tell you how it goes later.
Thanks,

PD: what is the link of your website?

Wishmaster schreef op 09-05-2011 20:55:
http://www.sommarskog.se/

On 09-05-2011 15:20, Henk van den Berg wrote:
thanks Henk.

Wishmaster ("sysadmin.rock[SINESTO]"@gmail.com) writes:
Oops! Forgot to paste the link: http://www.sommarskog.se/dyn-search.html

My sincere apologies.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx