Need to periodically drop and recreate stored procedure?
<p>Here is my issue.  I'm using SQL Server 2014.  I have an ASP.Net web-application that accepts a string from a user.  That string is passed to a stored procedure (SP), which queries a view, which queries a linked server.  My data-access layer returns a datatable to my business object.  If there IS data - the inputted string is considered valid (it has a match on the linked server).  If there is NO data, the inputted string is considered invalid (there is no match on the linked server).</p>

<p>So simply, here is the path of info:</p>

<p>WebApp - Stored Procedure - View - Linked Server - Table with data</p>

<p>Obviously this path is reversed once the query executes (and the data is passed from the table and ultimately to the web-app).</p>

<p>Here is where it gets weird.  Twice in the last 13 months since the SP was created (today being the second time), no data was returned to the web-app when VALID strings were entered.  To be clear, this SP has ALWAYS worked (other than these two times).  But, once it fails, it NEVER works until it's dropped and recreated.</p>

<p>So, here is what WORKS:</p>

<ul>
<li>Querying the linked server, directly, and adding the string to the
WHERE clause. </li>
<li>Querying the view, directly, and adding the string to    the WHERE
clause.</li>
</ul>

<p>Here is what DOESN'T work:</p>

<ul>
<li>Entering the string into the web-app.</li>
<li>Querying the SP, directly, and passing the string as a parameter.</li>
</ul>

<p>And the really, really weird part:</p>

<p>BOTH TIMES that this has happened, dropping and recreating the stored procedure solved the problem.  How is it that a SP will work for months, and then just stop working?  And then how come the fix is to drop and recreate the SP?  </p>

<p>Firstly, why is this happening?  There are lots of other web-apps that call SPs, that call views, that call the SAME linked server that have never failed.  But twice in a year ONE SP fails without apparent reason - and deleting and recreating it solved the problem?</p>

<p>Please help - this is really confusing...And please let me know if you need more information.  </p>

<p><strong>EDIT</strong></p>

<p>In response to alroc's comment:  </p>

<ul>
<li>The script that creates the stored procedure doesn't have anything to
do with security.  Any user that can access the DB can access the SP.</li>
<li>I'm the only DBA here, so no one else "should" be changing any
security or permissions on the DB.  However, if they were doing
that, I would expect these problems elsewhere, since the web-app
using the same SQL login (same connection string) that works for
every single other process - and there are hundreds of them.</li>
</ul>

<p><strong>SECOND EDIT</strong></p>

<p>In response to beercohol's comment:</p>

<ul>
<li>The query is not timing out - it returns no results almost instantly.
The timeout is set to 30 seconds and this isn't coming close to
approaching that threshold.</li>
<li>I ran DBCC CheckDB and no errors were found.</li>
<li>I will try recompiling the SP next time this happens, but why would the execution plan periodically go bad for ONE SP out of hundreds or thousands?</li>
</ul>