DB tells me SP is already in it, but it is not!

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 2005 Forums

Transact-SQL (2005)

DB tells me SP is already in it, but it is not!

Author

Topic

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 06:09:34

Hi,

creating a Stored Procedure results in the message that the SP is already in the DB. But I can not find it in the DB Explorer, even not after clicking on "Refresh". My Desktop App using this SP via DAL also throws a "SP not found" exception.

I already tried to DROP (Command completed sucessfully) the SP, but the following CREATE Command results in the error message ('Msg 2714, Level 16, State 3, Procedure GetUsersWhere, Line 26
There is already an object named 'GetUsersWhere' in the database.')

Where is the problem (except the one between chair and keyboard) ?

kr
flotschie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 06:14:52

Are you using a different schema?
Try adding schema name to your objects.

E 12°55'05.63"
N 56°04'39.26"

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 07:17:10

I am using CREATE like

CREATE Procedure [dbo].[securityGetUsersWhere]

Where can I check under wich schema I'm on the DB?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-24 : 07:20:28

There is already an object named 'GetUsersWhere' in the database

No, you're never too old to Yak'n'Roll if you're too young to die.

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 07:25:44

Ok if it's a SP then the error message would be like There is already an procedure named 'GetUsersWhere' in the database.'?

Thanks for the hint, I still don't know how to solve this prob.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 07:26:39

The drop also need the schema.

E 12°55'05.63"
N 56°04'39.26"

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 07:27:29

I already use the schema also in the DROP Commands.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-24 : 07:29:13

It is possible for example a table with same name already exists so you can't create the stored procedure.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-24 : 07:30:53

And it is confusing you're talking about GetUsersWhere and sometimes securityGetUsersWhere...

No, you're never too old to Yak'n'Roll if you're too young to die.

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 07:31:30

I am afraid not, but thanks for the hint ;-)

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 07:32:24

@webfred you are completely right, sorry for that. the correct name is [dbo].[securityGetUsersWhere] and i will try to edit my first post.

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 07:33:11

Editing not possible?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-24 : 07:34:34

Maybe your stored procedure will try to create a table GetUsersWhere...?

No, you're never too old to Yak'n'Roll if you're too young to die.

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 07:41:19

this is the exact Script:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/* Create Header Stored Procedures */

CREATE Procedure [dbo].[securityGetUsersWhere]
(
@UserID int,
@UserGroupID int,
@Active varchar(10),
@PW nvarchar(50)
)
AS

Declare @xUserID varchar(10), @xUserGroupID varchar(10)

IF (@UserID = -1)
Set @xUserID = '%'
Else
Set @xUserID= cast(@UserID as varchar(10))

IF (@UserGroupID = -1)
Set @xUserGroupID = '%'
Else
Set @xUserGroupID = cast(@UserGroupID as varchar(10))

SELECT *
FROM dbo.XSecUser
WHERE
(@UserID is NULL or UserID = @UserID)
AND (@UserGroupID is NULL or UserGroupID = @UserGroupID)
AND (@Active is NULL or Active = @Active)
AND (@PW is NULL or PW= @PW)

GO

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 07:44:17

The Remove Script:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/* Remove Header Stored Procedures */

DROP Procedure [dbo].[securityGetUsersWhere]
GO

Maybe the Remove Script causes all the trouble.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-24 : 07:51:49

What is the result of following query in the database where you want to create the SP?
select name,xtype from sysobjects where name like '%GetUsersWhere%'

No, you're never too old to Yak'n'Roll if you're too young to die.

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 07:55:32

Result of select name,xtype from sysobjects where name like '%GetUsersWhere%' is 0 rows...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 08:00:58

Is your database set to CaSe SENSiTIvE?

E 12°55'05.63"
N 56°04'39.26"

flotschie
Starting Member

14 Posts

Posted - 2009-06-24 : 08:02:06

Just managed to create the SPs.

Old Way of creation: I have a *.sql file and double click it in the normal Windows Explorer. A SQL connection Window pops up, I Connect, have the SQL Script in a Window in the MS SQL Server Management Studio, and there I click on "Execute".

And that's what i just did: In the MSSQL SMS, I right click on the "Stored Procedures" Folder in my DB -> "New Stored Procedure" -> Cope the SQL Script from the file mentioned above -> "Execute" -> SP are in the DB!

Can somebody explain that to me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 08:05:23

When "double click" the file, you get the login window and you are transferred to your default database, most often MASTER.
Do you have an USE clause in one of the script files?
When "right click" stored procedures folder, you already are in a specific database.

E 12°55'05.63"
N 56°04'39.26"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-24 : 08:07:59

No, you're never too old to Yak'n'Roll if you're too young to die.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources