TSQL - attempt to drop and create says 'object' already exists

SELECT * FROM sysobjects WHERE type = 'P' AND name = 'UNAdmin.unsp_EmployerInfoListViewSelectAdvanced'

returns 0 results

Then I run the following:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'UNAdmin.unsp_EmployerInfoListViewSelectAdvanced')
	DROP  Procedure  UNAdmin.unsp_EmployerInfoListViewSelectAdvanced
GO

CREATE PROCEDURE UNAdmin.unsp_EmployerInfoListViewSelectAdvanced


ETC....


ETC....

Open in new window



And I get:


Msg 2714, Level 16, State 3, Procedure unsp_EmployerInfoListViewSelectAdvanced, Line 102
There is already an object named 'unsp_EmployerInfoListViewSelectAdvanced' in the database.


And my Object Explorer tree for my Stored Procedures looks like this:

tree says it's there

What have I done to screw-up my database?  LOL  : )

Any help would be appreciated.



Tried this:

https://support.microsoft.com/en-us/kb/827448

No luck.


Tried SCRIPTED drop and create...  no luck....
LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?
 
Brian CroweDatabase AdministratorCommented:
sys.objects.name does not include the schema.  That is why you are not dropping the procedure.
0
 
dsackerContract ERP Admin/ConsultantCommented:
Try this:
IF OBJECT_ID('UNAdmin.unsp_EmployerInfoListViewSelectAdvanced') IS NOT NULL
BEGIN
    DROP PROCEDURE UNAdmin.unsp_EmployerInfoListViewSelectAdvanced
    PRINT 'Dropped procedure UNAdmin.unsp_EmployerInfoListViewSelectAdvanced'
END
GO

CREATE PROCEDURE UNAdmin.unsp_EmployerInfoListViewSelectAdvanced
...
...

Open in new window

Are you getting the message: 'Dropped procedure UNAdmin.unsp_EmployerInfoListViewSelectAdvanced'?

Is your CREATE statement still bombing?
0
 
Scott PletcherSenior DBACommented:
Btw, sysobjects is very much obsolete.  Use:
sys.objects.
0
 
Tom KnowltonWeb developerAuthor Commented:
Thanks everyone!

Brian was right.  UNAdmin is not part of the name:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'unsp_EmployerInfoListViewSelectAdvanced')
      DROP  Procedure  UNAdmin.unsp_EmployerInfoListViewSelectAdvanced
GO

Once I made the change above, it worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.