Periodically(roughly every month to 6 weeks) and from differing sources ( Windows Service Applications and Web Service applications) in our production environment we are falling into a situation where some of our applications cannot see a specific database.
The General exception that is thrown is as follows :-
System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'prcODMSelStudyDataExists'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection, Action`1 wrapCloseInAction)
Error Number:2812,State:62,Class:16
The strange thing we are observing here is that this only affects the application thread itself and if we log into SSMS with the same username and password as the login used by the application we can access all areas.
The only thing that appears to solve the issue is for use to flip to our failover servers. This essentially restarts the SQL service. Once we do this the application continues as per normal and we don't see the error again for a few more weeks.
Has anyone comes across anything like this before - we do use a third party encryption software called DBDefence and we have gone via their support teams but they haven`t seen anything like this before.
Thanks
Si