| Author |
 Topic  |
|
|
newbie16
Starting Member
8 Posts |
 Posted - 08/01/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
Posted - 08/02/2012 : 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 - 08/02/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
Posted - 08/02/2012 : 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 - 08/02/2012 : 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 |
Edited by - newbie16 on 08/02/2012 22:11:13 |
|
|
|
newbie16
Starting Member
8 Posts |
Posted - 08/03/2012 : 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
|
Edited by - newbie16 on 08/03/2012 01:14:41 |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1795 Posts |
Posted - 08/03/2012 : 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 |
Edited by - jackv on 08/03/2012 01:46:46 |
|
|
|
newbie16
Starting Member
8 Posts |
Posted - 08/03/2012 : 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. |
Edited by - newbie16 on 08/03/2012 02:43:12 |
|
|
| |
Topic |
|
Delete Stored Procedure for particular owner
