Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with ALTER PROCEDURE using SQL 6.5 Expand / Collapse
Author
Message
Posted Thursday, July 23, 2009 9:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 16, 2009 8:19 PM
Points: 2, Visits: 4
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.
Post #758356
Posted Wednesday, August 12, 2009 11:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
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
Post #769866
Posted Sunday, August 16, 2009 8:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 8:03 PM
Points: 54, Visits: 1,497
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


Post #771667
Posted Sunday, August 16, 2009 8:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 16, 2009 8:19 PM
Points: 2, Visits: 4
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!
Post #771669
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse