Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!aioe.org!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: strange behavior with SP Date: Thu, 05 May 2011 23:56:09 +0200 Organization: Erland Sommarskog Lines: 33 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx03.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="29132"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX182MGza0lJUQmqbAKQOA9qW" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:ITXAHTMJsdPmaA0ho8y1tOmAqGc= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:288 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