SEO

Hi,

I wrote the following in the SQL Server 2005 Express Management Studio
Query Analyzer
and hit execute. Even though Query Analyzer indicated success when I
hit refresh on the stored procedure folder the procedure does not show
up in the stored procedure list. When I entered 'exec Test' I get an
error indicating that SQL Server 2005 can't find the procedure. I
executed the following code 'select * from sys.all_objects where name
like '%Test%'' and the object is not in the returned result. So I tried
to execute the code again, and lo and behold, SQL Server gives me the
following error - There is already an object named 'Test' in the
database. What the hell?

Thanks,
Frustrated

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE dbo.Test
-- Add the parameters for the stored procedure here
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> =
<Default_Value_For_Param1, , 0>,
--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> =
<Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
-- SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

-- blah, blah
END
GO

Responding to my own post - I created a store procedure and deleted it
then couldn't locate it. I guess if you don't specify the database and
owner, by default the stored procedure is copied into the Master
database under system stored procedures (!), so if you can't find your
stored procedure, this is probably a good place to look.

Still perplexed about why users are allowed to store stuff in the
System Procedure folder and why this is where procedures with
unqualified names are stored by default.

Crazy Cat (danbredy@hotmail.com) writes:
> Responding to my own post - I created a store procedure and deleted it
> then couldn't locate it. I guess if you don't specify the database and
> owner, by default the stored procedure is copied into the Master
> database under system stored procedures (!), so if you can't find your
> stored procedure, this is probably a good place to look.
>
> Still perplexed about why users are allowed to store stuff in the
> System Procedure folder and why this is where procedures with
> unqualified names are stored by default.

First or all, there is no System Procedure folder, really. That is
just a visualization in Mgmt Studio.

Users are indeed permitted to create objects in the master database
if they have permissions. And, assuming that you have SQL Express on
your own machine, you presumably belong to BUILTIN\Administrators on
the box, and thus you have sysadmin when you log in with Windows
Authentication. And then you may indeed create procedures in the master
database.

Mgmt Studio has a dropdown which tells you the current database, and
this also displays on the tab, although it may be compressed. The Object
Explorer is not related to the query window.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx