Problem with ALTER PROCEDURE using SQL 6.5
-
Hello,
I am part of a dev group that works with ASP.Net/C#, and we are having a problem. We create lots of stored procedures in the datacenter (they run SQL 2000, in 6.5 compatibility mode), and they work as they should.
But sometimes we need to alter these procedures (successfully), including the number of parameters that they receive. If the procedure was executed several times, and the number of parameters that it receive was changed the application returns an error:
We have a workaround to it, drop the old procedure and recreate it, with a new name. It works, but it's not practical and the client don't like to wait.
The developer of the "Connection" class says that it's a problem with the database (some kind of strange unknown cache).
Somebody have a clue to what is the problem and how to solve it? It's definitelly a problem with the database or the application is the one to blame?
Thanks.
-
I have had this happen as well..
Do a drop, call an exec of the proc, then recreate it.
The call forces the engine to forget about anything it has cached for it.
CEWII
-
Hi Daniel,
I think the problem is not about caching.
I still support a SQL 6.5 database and so far as I know there is no ALTER PROCEDURE command in SQL 6.5.
You have to do:
DROP PROCEDURE ...
CREATE PROCEDURE ...
The fact that you are running from SQL 2000 in 6.5 Comptability Mode may allow the ALTER PROCEDURE to execute but it will fail.
Peter Lavelle
-
Peter, the problem occurs even if I DROP and CREATE the procedure. If I choose a different number of parameters while recreating it, the problem will happen.
Elliot, I'll try an EXEC between the DROP and the CREATE commands when the problem happen again.
Thanks for the help to you two!
[/URL]Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply