Delete Stored Procedure for particular owner

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete Stored Procedure for particular owner

Author

Topic

newbie16
Starting Member

8 Posts

Posted - 2012-08-01 : 23:06:49

Hello,

I am creating and testing a stored procedure through remote using JUnit , my user name is test and this is my code,


String sql = "create procedure test.firstProc3 AS select GETDATE()";
cursor.execute(sql);

ANd to drop the procedure:


String dropSQL = "DROP PROCEDURE test.firstProc3";cursor.execute(dropSQL);

The first time I run this , its fine , but the second time ( in a new session), i get the following error,

There is already an object named 'firstProc3' in the database.

When I gave sp_help on the server side,
the table had the row with the value
firstProc3 test stored procedure

However when I give
DROP PROCEDURE test.firstProc3 in the Query analyzer, the row is getting deleted from the table.

What could be the issue in trying to do the same operation through Junit?

Are there any permissions to be set?

PS - the user test has the db_ddladmin enabled.

Please help.
Thanks.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-02 : 01:28:17

When you run the DROP procedure the first time via :
String dropSQL = "DROP PROCEDURE test.firstProc3";cursor.execute(dropSQL);

Can you confirm , the procedure is dropped at that point? You could do this through a SELECT ... from sys.sysobjects

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

newbie16
Starting Member

8 Posts

Posted - 2012-08-02 : 01:53:46

Hello,

No, it does not get deleted from the table.

I tried giving

select test.firstProc4 from sys.sysobjects

AND

select firstProc4 from sys.sysobjects

The output was,

"Invalid Object name sys.sysobjects"

Finally I did sp_help and I was able to see that the entry is still present in the table.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-02 : 11:44:38

Sorry , just realised you're on SQL Server 2000 , try : sysobjects
In other words :
select * from sysobjects where xtype = 'P' and name = '<place stored procedure name>'

is the logon account using JUnit - the same as the logon account when going direct tgrouigh Query Analyzer?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

newbie16
Starting Member

8 Posts

Posted - 2012-08-02 : 21:56:30

Hello,

I changed it to:

cursor.parse("drop procedure firstProc3");
cursor.execute();

and when I give,
select * from sysobjects where xtype = 'P' and name = 'firstProc3'

This is returning a row of data containing information on the procedure.

Drop procedure not working

newbie16
Starting Member

8 Posts

Posted - 2012-08-03 : 01:10:13

Hello,

I managed to solve my problem by using the the following

declare @object_id int
select @object_id = object_id('test.firstProc5')
EXEC sp_MSdrop_object @object_id
GO

THis is removing the data from the table too.

Thanks for your help :)

This is the link I referred to:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-03 : 01:46:00

You're welcome. That is a useful undocumented proecedure. Did you get that code to run through the JUnit?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

newbie16
Starting Member

8 Posts

Posted - 2012-08-03 : 02:39:49

Yes, I declared it as a .sql file separately and then read from it and executed it.

List<String> sqls = SPUtil.readSql("dropSQL.sql");
for (String sql: sqls) {

cursor.parse(sql);
cursor.execute(sql);
}

The readSQL method parses the .sql file and returns executable SQL statements.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources