+ Reply to Thread
Results 1 to 8 of 8

strange behavior with SP

  1. strange behavior with SP

    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

  2. Re: strange behavior with SP

    Wishmaster ("sysadmin.rock[SINESTO]"@gmail.com) 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, esquelatsommarskogdotse

    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

  3. Re: strange behavior with SP

    On 05-05-2011 18:56, Erland Sommarskog wrote:
    > Wishmaster ("sysadmin.rock[SINESTO]"@gmail.com) 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.

  4. Re: strange behavior with SP

    Wishmaster ("sysadmin.rock[SINESTO]"@gmail.com) 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, esquelatsommarskogdotse

    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

  5. Re: strange behavior with SP

    On 06-05-2011 17:52, Erland Sommarskog wrote:
    > Wishmaster ("sysadmin.rock[SINESTO]"@gmail.com) 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?



  6. Re: strange behavior with SP

    Wishmaster schreef op 09-05-2011 20:55:
    > Thanks,
    >
    > PD: what is the link of your website?
    >
    >
    >

    http://www.sommarskog.se/

  7. Re: strange behavior with SP

    On 09-05-2011 15:20, Henk van den Berg wrote:
    > Wishmaster schreef op 09-05-2011 20:55:
    >> Thanks,
    >>
    >> PD: what is the link of your website?
    >>
    >>
    >>
    >
    > http://www.sommarskog.se/

    thanks Henk.

  8. Re: strange behavior with SP

    Wishmaster ("sysadmin.rock[SINESTO]"@gmail.com) writes:
    > PD: what is the link of your website?

    Oops! Forgot to paste the link: http://www.sommarskog.se/dyn-search.html

    My sincere apologies.


    --
    Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

    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

+ Reply to Thread
« Previous Thread | Next Thread »