SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with ALTER PROCEDURE using SQL 6.5


Problem with ALTER PROCEDURE using SQL 6.5

Author
Message
daniel.aydar.arantes
daniel.aydar.arantes
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.
Elliott Whitlow
Elliott Whitlow
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44174 Visits: 5314
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
Peter Lavelle
Peter Lavelle
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 1497
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
daniel.aydar.arantes
daniel.aydar.arantes
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search