Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #288

Re: strange behavior with SP

Show all headers | View raw

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, esquel@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 comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

strange behavior with SP Wishmaster <"sysadmin.rock[SINESTO]"@gmail.com> - 2011-05-05 11:54 -0300
  Re: strange behavior with SP Erland Sommarskog <esquel@sommarskog.se> - 2011-05-05 23:56 +0200
    Re: strange behavior with SP Wishmaster <"sysadmin.rock[SINESTO]"@gmail.com> - 2011-05-06 17:22 -0300
      Re: strange behavior with SP Erland Sommarskog <esquel@sommarskog.se> - 2011-05-06 23:52 +0200
        Re: strange behavior with SP Wishmaster <"sysadmin.rock[SINESTO]"@gmail.com> - 2011-05-09 14:55 -0400
          Re: strange behavior with SP Henk van den Berg <hvandenberg@xs4all.nl> - 2011-05-09 21:20 +0200
            Re: strange behavior with SP Wishmaster <"sysadmin.rock[SINESTO]"@gmail.com> - 2011-05-09 16:45 -0400
          Re: strange behavior with SP Erland Sommarskog <esquel@sommarskog.se> - 2011-05-09 23:52 +0200

csiph-web