| Author |
Topic |
|
elomon
Starting Member
37 Posts |
 Posted - 2003-07-22 : 11:16:19
|
| I've got a stored procedure that works 95% of the time. The other 5% of the time you get:Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'spname'The message is returned by the exact same execution code. The problem clears in 10-15 minutes by itself with absolutely no intervention in the SQL Server or the calling code.Any ideas what might cause this?TIA |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-22 : 12:15:25
|
| Damn miracle thing again...hold it...my dynamic sql alarm is going off...any chance your dynamically setting the procedure being called?Brett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-22 : 12:39:38
|
| It's either dynamic sql or someone is messing with you. Objects just don't disappear. So either someone is dropping and recreating them or an invalid object is being passed into a dynamic sql stored procedure.Tara |
|
|
|
elomon
Starting Member
37 Posts |
Posted - 2003-07-22 : 12:40:09
|
| Hmmm, no. The sp is coded into an ASP page that executes the same procedure every time. The sp itself is a simple Select, no dynamic SQL, just joins a few tables. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-22 : 12:41:21
|
| OK,We believe..Post the sproc and the asp code...Brett8-) |
|
|
|
elomon
Starting Member
37 Posts |
Posted - 2003-07-22 : 12:43:10
|
| And the SP is not being dropped and re-created. I'm sure b/c the create date on the SP is the same as always.I know objects don't dis/appear and I've gone through it with a bunch of people:1. Same code always executes the SP2. Standard SP with a simple Select only3. Problem clears without intervention4. Works 95% of the time. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-22 : 12:45:10
|
| Well to verify what is happening on your server just run SQL Profiler. Since you say that it isn't dynamic sql and that no one is dropping the objects, then are you sure that the code is hitting the correct database each time? Really, objects don't just disappear. Run SQL Profiler to determine what the cause is.Tara |
|
|
|
elomon
Starting Member
37 Posts |
Posted - 2003-07-22 : 12:49:20
|
| I'll try that the next the server cannot find the SP. I know it is difficult to believe but it is true - same db, same sp, same code every single time.Thanks for the suggestion. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-22 : 12:50:37
|
| You have to have SQL Profiler running during the time that the problem occurs in order to capture it. You can not go back in time and see what was running (well you can if you have Log Explorer by Lumigent) if you don't have SQL Profiler running. But keep in mind that SQL Profiler could cause performance degradation.Tara |
|
|
|
elomon
Starting Member
37 Posts |
Posted - 2003-07-22 : 14:30:17
|
| Ok, thanks for the tip. The problem usually occurs for 10-15 minutes, so I s/b able to fire up Profiler. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-22 : 14:33:15
|
| I'm think the odbc connection string is not being set correctly in one of the programs.I'll bet the sproc is being called from more than 1 place.Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-22 : 14:38:26
|
| Brett, I was thinking along those lines too. Elomon, is this a web application? If it is, do any other objects experience this problem? I am wondering if there is a DNS issue where the application gets directed to another server due to the DNS being incorrect. It's a long shot though.Also, I would add Errors and Warnings to the SQL Profiler events (keep the default and add this). If you do not see any errors and do not see the stored procedure being called, then you know that the problem isn't on the server, meaning the problem either resides on the web server/client machine or somewhere else such as in the network.TaraEdited by - tduggan on 07/22/2003 14:41:07 |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-07-22 : 15:40:26
|
| I think the guy that asked if the connection is in the same database everytime maybe onto something. One practice we have where we work is to prefix the database and owner along with the object name. That way, even if the connection is in a different database, the procedure will still be found. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-22 : 15:42:13
|
quote:
Brett, I was thinking along those lines too
Great minds think a like... I think the only time I saw anything with SQL Server and ODBC or OLE DB was DTS or linked servers...I would place the cause on the calling code.Brett8-) |
|
|
|
elomon
Starting Member
37 Posts |
Posted - 2003-07-23 : 10:16:54
|
| Thanks for the ideas.It is a web based app but when the problem occurs on the app, it also occurs through query analyzer. I'm not sure if that rules out a DNS problem or not.I changed the execute from spname to database.owner.spname and the problem has not occurred since the change. I've got the SQL Profiler running and no peeps after the change.Thanks to all. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-23 : 12:11:20
|
| Well it sounds like the default database for the user isn't correct. If it happens in Query Analyzer as well, then the problem is with the user or with someone messing with you. Since you said that no one is dropping the object, then the problem is with the user. If the default database isn't set for the user and you do not specify which database to use, then it assumes that the objects are in master. By specifying database.owner.objectname, you are telling it where to go even if you are in the master database.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-23 : 13:42:05
|
| Reminds me of the time I threw a script over the wall (after much testing) to the production dba, and he told me it failed..Being on the phone with him when he ran it, I couldn't see anything, but was shocked.Until he realized he was running in master[homer]dooooooooooooooooooooooooooh[/homer]Brett8-) |
|
|
|
|
Intermittent 'Could not find stored Procedure'