Strange...Cannot create procedure.

Question

Strange...Cannot create procedure.

ChiragNS

One Orange Chip

Points: 26137
More actions
May 23, 2008 at 2:27 am

#122348

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"

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

alimcitp SSC Eights! Points: 930 More actions · Answer 1

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

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"

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

It might also help to see the code you are running to create/drop the procedure.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

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"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

Hi Gail

Iam a sysadmin. I login as SA.

Thanks

"Keep Trying"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bob Fazio SSChampion Points: 10794 More actions · Answer 9

Chirag (5/26/2008)
Hi
create 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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

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]

Manie Verster SSCertifiable Points: 7152 More actions · Answer 11

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)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 12

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"

ChiragNS One Orange Chip Points: 26137 More actions · Answer 13

Hi

There are many experts out there who can continue this thread... So keep the dice rolling....:cool:

"Keep Trying"

vikas_behl2004 Newbie Points: 9 More actions · Answer 14

It seems that your database is not refreshing, stop all sql services and then restart it may solve your problem