May 23, 2008 at 2:27 am
Hi experts
When i try to create a procedure i get the error message that the procedure already exists. When i drop the same procedure i get the message that cannot drop the procedure because it does not exist.
i think a object with the same name as that of the procedure exists in some system table or the other. Cant find out the exact reason though.
Thankyou
"Keep Trying"
May 23, 2008 at 2:44 am
Can you please give the name of the proc which you are trying to create...
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
May 23, 2008 at 2:55 am
Hi
Name of the procedure is irrelevant but here its is
"PositionInsert_sp". To add i could successfully create a procedure called "PositionUpdate_sp" .
"Keep Trying"
May 23, 2008 at 7:46 am
Hi Chirag,
Run following query and see what is already exising.
select * from sys.objects where [name] = 'PositionInsert_sp'
It is not SP that is already exising. It could be a table, ....
Suresh
May 23, 2008 at 7:53 am
It might also help to see the code you are running to create/drop the procedure.
May 26, 2008 at 2:17 am
Hi
thanks for your response.
I have already queried sys.objects table and there is no object named "PositionInsert_sp".
even if i create the procedure with the following code i get the error "There is already an object named 'PositionInsert_sp' in the database."
create procedure PositionInsert_sp
as
begin
select 'd'
end
When i try to drop or alter the procedure i get the following error message "Cannot drop the procedure 'PositionInsert_sp', because it does not exist or you do not have permission."
This happens in only one database and the same procedure can be created successfully in another db. Atleast can anyone tell me which system tables does SQL check before creating a procedure. I have already checked the information_schema tables and sys.sql_modules.
"Keep Trying"
May 26, 2008 at 3:06 am
What permissions do you have in that DB? Are you sysadmin or db owner?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2008 at 5:37 am
Hi Gail
Iam a sysadmin. I login as SA.
Thanks
"Keep Trying"
May 26, 2008 at 5:44 am
Run a checkdb over that database, see if it gives any errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2008 at 11:27 am
Chirag (5/26/2008)
Hicreate procedure PositionInsert_sp
as
begin
select 'd'
end
Just for fun, have you tried to alter?
alter procedure PositionInsert_sp
as
begin
select 'd'
end
May 27, 2008 at 12:09 pm
Chirag (5/26/2008)
I have already queried sys.objects table and there is no object named "PositionInsert_sp".
When you did this, did you use "WITH [NOLOCK]"? If not, try it with NOLOCK, you may have a process hung in the middle of trying to create this same procedure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2008 at 12:15 am
GilaMonster (5/26/2008)
Run a checkdb over that database, see if it gives any errors.
This has happened to me before and something in the database got corrupted. I suggest you do what Gila Monster said and if that does not give errors, check for begin and commit transactions throughout all your SP's because you might have a begin transaction without a commit transaction. If that is not the problem then restart your database services and see if that works. If that does not work, I suggest that, just for now, ignore that SP and create another with a different name so you can continue with your work. As an after thought, not that I think that is the problem, but try to use the ALTER PROCEDURE as someone commented.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
May 28, 2008 at 4:50 am
Gail
Thanks for your response & Sorry for the delay in replying.
Your suggestion to run DBCC CHECKDB worked . It returned some errors. Mostly the error said that a row in sys.sql_dependencies does not have a matching row in sys.objects. The procs that iam unable to created already have a row in sys.sql_dependencies .. is that right?
I have attached the results of the DBCC CHECKDB statement. Please have a look and give your comments.
Bob
Even the alter does not work. Says the object does not exist. Think the issue has to do with system tables i have mentioned above.
Rbarry - No blocking issues i believe.
Thankyou
"Keep Trying"
May 29, 2008 at 2:51 am
Hi
There are many experts out there who can continue this thread... So keep the dice rolling....:cool:
"Keep Trying"
May 29, 2008 at 3:42 am
It seems that your database is not refreshing, stop all sql services and then restart it may solve your problem
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply