Random "Could not find stored procedure" error
-
I am connecting to a SQL server 2000 database from an ASP.NET website. On occasions I get the error message "Could not find stored procedure 'xxx'". xxx is always the same stored procedure, however out of hundreds of calls a day, and no alterations to the db or web app, the error message pops up randomly. It's happened about 3 times in the last 5 months and never coincides with any application upgrades or environment changes.
The connection uses SQL server authentication with the uesrname and password in the connection string and the connection string never changes, so I'm fairly sure this is not a problem with connecting to the wrong database or the user not having the permissions set to execute the procedure (I could be wrong, maybe not looking at something I should, but that wouldn't explain randomness of the error).
Does anyone know of any reasons why this error could come up randomly with no configuration or code changes?
-
is connection pooling on/off?
We had troubles with connection pooling off resulting in error messages like server does not exists... because the machine couldn't generate connections fast enough (or something alike)
-
Connection pooling is on.
-
It may not be connection related the stored proc maybe recompiling all the time so it is not in the procedure cache, which means your application must wait for it to recompile. Run it through your application in profiler and check sp statement start and statement close, then run it in Management Studio/Query Analyzer and click on show execution plan so you can fix it. Try the link below for stored proc tricks from T-SQL expert Ken Henderson. Hope this helps.
http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
This might be obvious but I would double check the default database of your logins. It sounds like a database context problem somewhere.
-
Thanks for that - solved the same problem I was having from an ASP.NET 2.0 application connecting to SQL Server 2000.
Despite the connection string specifying the correct database, it could not find the stored procedure. Changing the default database for the login being used solved this. Changing the default database back, and explicitly changing the database against the connection, resulted in the same error.
Resolved by setting up a new user for the required database, with that database set as the default. Bizarre; many thanks!
-
Still doesn't seem quite right. I always set the default db to equal master and explicitly set the dbname in my connection strings. Saves me aggravation if I restore and the dbid changes, and forces good standards too. Also makes it easy to point to a 'test' datababase without having to switch logins. Sure you don't have a bad connect string or bad dbname in the connect string?
-
This is my connection string:
"Provider=SQLOLEDB;Data Source=SHED\FINANCE;initial catalog=SOP;User ID=SOP;Password="
If I change "initial catalog=SOP" to any other db it can't see the procedures within that DB. Not sure if it's a bug with the OLEDB provider.
I'd prefer to use the one user in the way you mention, which is how I'd normally work, but it seems I can't!
-
Initial Catalog means that you are by default connecting to that DB. When you change your initial catalog to some other DB, and then try to execute the same SP, it will fail as the SP will be looked for in the new DB that you specified. In that case you should call the SP with the fully qualified name: [a.k.a. database.dbowner.spname]
-
i've seen this many times ; it has to do with the default database for the login in question...change the default login's database from whatever it is now (probably master) to SOP; cant' exactly explain why, but even though the connection string clearly says which database, sometimes items evaluate to the user's default instead of that provided in the connection string.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
That's exactly the issue. I can only assume it's a bug as it doesn't make any sense otherwise. It's annoying that I have to use more than one login but at least it's working for now!
-
We have same peroblem. We have one stred proc wich is called couple of thousand times a day. Randomly it generates error:
Could not find stored procedure 'sp_viestimaaran_paivitys'.
stored procedure is there all the time and nothing has changed. Can anyone help us how I could remove this anoying proplme from our system
I call strored from .asp (vbscript) page like this
cn="Provider=SQLOLEDB;Server=000.000.000.000;Database=Keskustelufoorumi;initial catalog=Keskustelu;UID=44444444user;PWD=55555555"
Set vpaivitys= Server.CreateObject("ADODB.Recordset")
sqllause = "sp_viestimaaran_paivitys " & viestiketjuid & ",0,1"
vpaivitys.Open sqllause, cn
Set vpaivitys = Nothing
stored proc is quite simple:
CREATE PROCEDURE sp_viestimaaran_paivitys
(@viestiketjuid int,
@viestimaara int,
@lukukerrat int)
AS
BEGIN TRANSACTION
Set Nocount on
if (@viestiketjuid <> 0)
begin
if (@viestimaara = 1)
begin
-- päivitetään
update dbo.viestitilastot
set viestimaara = (SELECT COUNT(*) AS maara FROM dbo.Viestit WITH (NOLOCK) WHERE (aktiivinen = 1) AND (viestiketjuid = @viestiketjuid) GROUP BY viestiketjuid, viestiketjuid)
where viestiketjuid = @viestiketjuid
-- p'ivitetään viestien järjestysnumerot kohdileen
UPDATE dbo.Viestinumeroiden_paivitys WITH (ROWLOCK)
SET viestinnumero = number
WHERE viestiketjuid = @viestiketjuid
end
if (@lukukerrat = 1)
begin
-- päivitetään
update dbo.viestitilastot WITH (ROWLOCK)
set luettumaara = luettumaara + 1
where viestiketjuid = @viestiketjuid
end
end
COMMIT TRANSACTION
RETURN
GO
-
there's a combination of issues; You'll probably remember that if aany object starts with the letters "sp_", the query engine looks in the master database first....if not found, it looks in the database you are connected to.
Most people say that yeah technically, a non-system proc starting with sp_ adds a bit of performance overhead, but this is another reason not to...i start mine with pr_ myself, and only procs i want avaiable for all db's get sp_
sometimes, the query does not know which database you are refering to, and uses your default database instead.
you can fix this easily by confiming whihc database is the default on this screen in Enterprise Manager.
you can also explictly say the dbname in your queries, by calling dbname..dbo.sp_viestimaaran_paivitys
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks...
Newer really thought that it defaults to master becouse my connection string should tell the default database. My default was master.
I will try to call stored by full name. Let's see...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply