January 6, 2010 at 3:50 am
Hello,
We encounter a very strange issue on a x64 SQL 2005 Standard SP3 Server, sometimes we receive an error saying that a stored procedure could not be found.
We have a .NET application for online sales which is quite used (over 2000 transactions per day). We have error notification in the .NET application and sometimes we receive a strange SqlException :
Process information:
Process ID: 7200
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE
Exception information:
Exception type: SqlException
Exception message: Could not find stored procedure 'GetPagesByIdParentForMenu'.
The issue is really intermitent because this stored procedure is called 10'000 a day and the error happens only once or twice a day. Notice that it doesn't happen always on the same stored procedure.
EDIT : I checked also that creationdate of the stored procedure is not changing to ensure that sp is not deleted/created somewhere in the application and the creation date is not changing.
I checked the SQL logs and event log on both server and didn't notice any other error which could explain this. Nothing special happens in the same timeframe as this sql exception.
I thought to let the profiler run to have more information but I would like to filter the trace and I am not sure how to narrow it to this error and which information I should include in the trace.
Any idea or advice on what I should check is welcome
Best regards
Gilles Faessler
January 6, 2010 at 4:34 am
How does your .net application connect to sqlserver
or
how is the webservice you are calling connecting to sqlserver ?
If it is tunneling credentials, chances are a user ( or some users ) are using another authorization level than the rest, and may have a differende default schema. If that schema doesn't have the supposed sproc, you will get that error.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 6, 2010 at 4:48 am
My initial instinct is to believe that the stored procedure(s) cannot be found.
So really that would only leave that the connection is pointing to another server / database.
You could add a server side trace to capture all calls to your stored procedures that are not in the expected database.
January 6, 2010 at 4:49 am
The .NET application accesses on the database trough a database access library which uses always the same credentials/connection string to connect on the database
January 6, 2010 at 6:28 am
I believe i've seen this condition when a users credentials has a default database other than the one being connected to.
What I mean is if i created a new user "webdev" that had a default database of 'master', but access to 'SandBox', sometimes after connecting to 'SandBox', the context somehow jumps to the default 'master', and objects cannot be found.
Maybe due to connection pooling or something, but the procedures/tables can't be found in master.
Lowell
January 6, 2010 at 7:34 am
Ok I found it.
The "Stored procedure could not be found" error message was effectively due to the fact that the .NET application was not connected to the correct database.
It was a bit tricky to find because the database access layer class uses static methods with a static connectionstring. At one location in the code the connectionstring was changed and then reset to original connectionstring but during this time the entire application was connecting to the wrong database.
Thank you for the hints, it helped me to figure it out !
June 11, 2015 at 7:05 am
Guys
I got the same issue in our web application and we have checked almost everything but this issue persists
We are using jquery and rest framework and get this error intermittently
All connection strings are checked and are pointing to correct database
Our application gets at least 10000 hits daily and we get 40-50 such issues daily. Below is a sample error:
Could not find stored procedure 'Validate_User_Token'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Xavient.ProductSuite.DBHelper.DataAccess.ExecuteReader(CommandType commandType, String commandText, IDataParameter[] commandParameters)
June 11, 2015 at 7:34 am
the easiest fix is a simple change in the application call, so that it explicitly uses DatabaseName.SchemaName.ObjectName.
that resolves any possibilities of ambiguity where the query engine tries it's best to resolve objects using an unknown database context..
so change the code to say EXECUTE Production.dbo.Validate_User_Token insteadl of EXECUTE Validate_User_Token
Lowell
June 11, 2015 at 7:42 am
Thanks for your swift response!
Though we have 3000 odd procedures yet i'll get this changed and will update on the results here
June 11, 2015 at 7:47 am
Could not find stored procedure 'Validate_User_Token'
Indeed, at least Schema qualify all objects !
Exec [YourSchema].Validate_User_Token
Accounts can have their own default schema set !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 11, 2015 at 7:49 am
i think the case you have, where it's a high volume of calls to a specific proc, and it's intermittent, relaly makes me think connection pooling is the culprit, so if you can, modify the call to that proc first.
you could also be smart, with the side benefit of zero application changes, you can make a synonym in master that points to that procedure, and modify the procedure to explicitly use three part calls inside the code to handle the code in the correct database.
that makes maintenance a little more of a headache, since not everyone thinks of synonyms, but it would fix it fast and easy.
Lowell
June 11, 2015 at 7:53 am
The big downside of using three part naming is during "same instance different DB-name recovery" !
This mostly happens to correct a mishap.
Hush hush repair may cause more damage due to execution sprocs as if they were local, but actually they are messing up your "production" db !
That's the reason I don't advocate three part naming IN the sprocs, ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply