 |
|
|
|
Next: Progress Programmer Opportunity- Great company-Mi..
|
| Author |
Message |
External
Since: May 05, 2011
Posts: 4
|
(Msg. 1) Posted: Thu May 05, 2011 11:25 am
Post subject: strange behavior with SP
Archived from groups: comp>databases>ms-sqlserver (more info?)
|
|
|
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 |
|
| Back to top |
|
 | |
External
Since: May 30, 2004
Posts: 2061
|
(Msg. 2) Posted: Thu May 05, 2011 6:25 pm
Post subject: Re: strange behavior with SP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Wishmaster ("sysadmin.rock[SINESTO]" ) writes:
> 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...
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.DeleteThis@sommarskog.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 |
|
| Back to top |
|
| |
External
Since: May 05, 2011
Posts: 4
|
(Msg. 3) Posted: Fri May 06, 2011 4:25 pm
Post subject: Re: strange behavior with SP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 05-05-2011 18:56, Erland Sommarskog wrote:
> Wishmaster ("sysadmin.rock[SINESTO]" ) writes:
>> 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...
>
> 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,
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. >> Stay informed about: strange behavior with SP |
|
| Back to top |
|
| |
External
Since: May 30, 2004
Posts: 2061
|
(Msg. 4) Posted: Fri May 06, 2011 6:25 pm
Post subject: Re: strange behavior with SP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Wishmaster ("sysadmin.rock[SINESTO]" ) writes:
> 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.
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.DeleteThis@sommarskog.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 >> Stay informed about: strange behavior with SP |
|
| Back to top |
|
| |
External
Since: May 05, 2011
Posts: 4
|
(Msg. 5) Posted: Mon May 09, 2011 2:55 pm
Post subject: Re: strange behavior with SP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On 06-05-2011 17:52, Erland Sommarskog wrote:
> Wishmaster ("sysadmin.rock[SINESTO]" ) writes:
>> 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.
>
> 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,
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? >> Stay informed about: strange behavior with SP |
|
| Back to top |
|
| |
External
Since: May 09, 2011
Posts: 1
|
(Msg. 6) Posted: Mon May 09, 2011 4:25 pm
Post subject: Re: strange behavior with SP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: May 05, 2011
Posts: 4
|
(Msg. 7) Posted: Mon May 09, 2011 4:45 pm
Post subject: Re: strange behavior with SP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: May 30, 2004
Posts: 2061
|
(Msg. 8) Posted: Mon May 09, 2011 6:25 pm
Post subject: Re: strange behavior with SP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
| Related Topics: | Bizarre case behavior. - I'm doing a select which includes the following: case when (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null) then '' else convert(datetime,left(replace(replace(T464.COMMENT_4,' QTR: ',''),' - ',''),10)) end as QuarterStartDate The COMMENT_...
don't understand the following locking behavior - can't figure out why the following locking scenario works the way it does: spid 1: start transaction select a row from table T1 with updlock result: see an Update lock for the row and index key spid 2: query for the same row as in session 1 ..
Strange SQL Question - Ok here goes, another odd SQL Question ....as always.. How do I get ... The value of a paramater passed to say a Trigger OR The SQL Itself Like this , say I have a Trigger set on delete, Now this trigger will add a row to another table (a history..
strange Problem - Hello, hope u can help or point me in the right direction I have 2 Services on our production System 1 Service is Vb6 (not the problem) 2 nd service is on another maching c# (not the problem) these service insert and update a common Table that links 2...
Strange select - I have the following query, but the issue is that there are multiple results of IP Address with different labels in computer name. I only want the first result, but distinct doesn't work. select distinct computername as [Computer Name], ipAddress as [IP... |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
