locked
Deleting stored procedures through query analyzer RRS feed

  • Question

  • Hi All!

    I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database? Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:

    1. Production database comes into test database
    2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
    3. Remove user
    4. Add user (this one brings login name since the restored one didn't)
    5. Have a nice day

    I've got everything except removing the stored procedure so I will really appreciate the help.

    Thank you all!

    Friday, October 20, 2006 8:27 PM

Answers

  •  Rijckewaert wrote:

    Hi All!

    I know this must be a very silly question but, what is the PLSQL string I have to use to delete a stored procedure in a database?

    Transact-SQL ... here Oracle is notwelcome

    the actual traditional code is

    IF OBJECT_ID('[schema/owner].[object]') IS NOT NULL
    	DROP PROCEDURE [schema/owner].[object];
    

    Essentially I have to remove a stored procedure that comes from a database backup every night because it belongs to a user and that user has to be recreated in the new SQL Server 2000. Simply put:

    1. Production database comes into test database
    2. Remove copy of stored procedure since it can not be set to dbo user because there is another copy with the same name that belongs to dbo.
    3. Remove user
    4. Add user (this one brings login name since the restored one didn't)
    5. Have a nice day

    I've got everything except removing the stored procedure so I will really appreciate the help.

    Thank you all!

    this "design" is not clear to me... why would you always drop an object to be recreated (if the inner code remains the same)?

    is this becouse of the well known "orphaned users" problem?
    this problem (perhaps the problem you are experimenting) is involved when a "restored" database user(s) is no longer mapped to a corresponding server's standard SQL Server login..
    in SQL Server 2000 this means the relationship between master.dbo.syslogins is broken with database.dbo.sysusers...
    actually the JOIN on master.dbo.syslogins.sid ~ database.dbo.sysusers.sid

    if this is the case, you do not need to delete the object and the user... you just need to resync it/them, via the sp_change_users_logins system stored procedure http://msdn2.microsoft.com/en-us/library/ms174378.aspx ... please have a look at http://msdn2.microsoft.com/en-us/library/ms175475.aspx as well..

    if this is not the case... please expand

    regards

    Friday, October 20, 2006 8:55 PM