Showing posts with label mydb. Show all posts
Showing posts with label mydb. Show all posts

Monday, March 26, 2012

I can't see my Procedure. Why? This is my first Stored Procedure.

Hello,
I created the "MyDb" database using Microsoft SQL 2005 Server Management
Studio and I added the table "dbo.Surveys".
Then I right clicked on Store Procedures and created a new procedure.
When I close it I am asked to save it. I save it and gave the file a
name.
However my stored procedure doesn't show in the Stored Procedures list.
I can only access it by loading the file (File > Open > File). Why?
When I open the File the Connect Window shows again.
I believe the procedure is created because I used CREATE.
When I execute it twice I got the message that there is already another
procedure with that name so I change CREATE to ALTER.
Could you explain to me how to make my procedure visible in:
Databases/MyDb/Programmability/StoredProcedores
How to run it with dbo.Surveys table and see all the records that
returns?
Thank You,
Miguel
My Stored Procedure code is:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.Surveys_GetSurveyBySurveyName
-- Procedure Parameters
@.SurveyName nvarchar
AS
BEGIN
-- Prevent extra result sets from interfering with SELECT statements
SET NOCOUNT ON;
-- Procedure statements
SELECT m.SurveyName
FROM dbo.application_Surveys
WHERE @.SurveyName = u.SurveyName
IF ( @.@.ROWCOUNT = 0 ) -- Survey Name not found
RETURN -1
RETURN 0
END
GO> When I close it I am asked to save it. I save it and gave the file a name.
That is a file.

> However my stored procedure doesn't show in the Stored Procedures list.
> I can only access it by loading the file (File > Open > File). Why?
You need to *RUN* the CREATE PROCEDURE script to apply the procedure to the
database. This has nothing to do with a file you save on your hard drive.

> I believe the procedure is created because I used CREATE.
> When I execute it twice I got the message that there is already another
> procedure with that name so I change CREATE to ALTER.
Then maybe you created it in the wrong database? Just because you created a
database does not necessarily mean that your current query window is in that
database's context.

> Could you explain to me how to make my procedure visible in:
> Databases/MyDb/Programmability/StoredProcedores
Did you right-click and hit refresh? Did you try EXEC
dbo.Surveys_GetSurveyBySurveyName?

> My Stored Procedure code is:
What happens when you insert this here:

> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
USE MyDB
GO

> ALTER PROCEDURE dbo.Surveys_GetSurveyBySurveyName
...|||Well, have you hit F5 after creation the SP? Have you refresh on stored
procedure--system stored procedure folder?
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:u$nJopbTGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I created the "MyDb" database using Microsoft SQL 2005 Server Management
> Studio and I added the table "dbo.Surveys".
> Then I right clicked on Store Procedures and created a new procedure.
> When I close it I am asked to save it. I save it and gave the file a name.
> However my stored procedure doesn't show in the Stored Procedures list.
> I can only access it by loading the file (File > Open > File). Why?
> When I open the File the Connect Window shows again.
> I believe the procedure is created because I used CREATE.
> When I execute it twice I got the message that there is already another
> procedure with that name so I change CREATE to ALTER.
> Could you explain to me how to make my procedure visible in:
> Databases/MyDb/Programmability/StoredProcedores
> How to run it with dbo.Surveys table and see all the records that returns?
> Thank You,
> Miguel
> My Stored Procedure code is:
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE dbo.Surveys_GetSurveyBySurveyName
> -- Procedure Parameters
> @.SurveyName nvarchar
> AS
> BEGIN
> -- Prevent extra result sets from interfering with SELECT statements
> SET NOCOUNT ON;
> -- Procedure statements
> SELECT m.SurveyName
> FROM dbo.application_Surveys
> WHERE @.SurveyName = u.SurveyName
> IF ( @.@.ROWCOUNT = 0 ) -- Survey Name not found
> RETURN -1
> RETURN 0
> END
> GO
>

I cant see my Procedure. Why? This is my first Stored Procedure.

Hello,

I created the "MyDb" database using Microsoft SQL 2005 Server Management Studio and I added the table "dbo.Surveys".

Then I right clicked on Store Procedures and created a new procedure.
When I close it I am asked to save it. I save it and gave the file a name.
However my stored procedure doesn't show in the Stored Procedures list.
I can only access it by loading the file (File > Open > File). Why?

When I open the File the Connect Window shows again.

I believe the procedure is created because I used CREATE.
When I execute it twice I got the message that there is already another procedure with that name so I change CREATE to ALTER.

Could you explain to me how to make my procedure visible in:
Databases/MyDb/Programmability/StoredProcedores

How to run it with dbo.Surveys table and see all the records that returns?

Thank You,
Miguel

My Stored Procedure code is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.Surveys_GetSurveyBySurveyName
-- Procedure Parameters
@.SurveyName nvarchar
AS
BEGIN
-- Prevent extra result sets from interfering with SELECT statements
SET NOCOUNT ON;

-- Procedure statements
SELECT m.SurveyName
FROM dbo.application_Surveys
WHERE @.SurveyName = u.SurveyName

IF ( @.@.ROWCOUNT = 0 ) -- Survey Name not found
RETURN -1
RETURN 0

END
GO

USE MyDb

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.Surveys_GetSurveyBySurveyName
-- Procedure Parameters
@.SurveyName nvarchar
AS
BEGIN
-- Prevent extra result sets from interfering with SELECT statements
SET NOCOUNT ON;

-- Procedure statements
SELECT m.SurveyName
FROM dbo.application_Surveys
WHERE @.SurveyName = u.SurveyName

IF ( @.@.ROWCOUNT = 0 ) -- Survey Name not found
RETURN -1
RETURN 0

END
GO

this one can create on sp on my machine.

Limno

|||

You must have created the stored proc in some other database.

Here's a script from SQLServerCentral.com that you can use to find any object: Compile the script in master database and exec it with the stored proc name.

/*
Purpose- Search object across database server.
Author-Vidyadhar P.
Email-vidya_pande@.yahoo.com
Date-6th-Feb-2006
Location- Pune,India
Input Parameters
@.object_name= Name of object to be searched part of object nameto be searched
@.ExactORLikeSearch= If no parameters are passed Sp will search for exact object names.
if 'L' is passes as parameter it will to like seach.
*/

create procedure dbo.sp_find_object -- 'product_master','L'
@.object_name varchar(100),
@.ExactORLikeSearch char(1)=E --E/L
as
begin
set nocount on
declare @.databases table (colid int identity ,dbname varchar(50))
create table ##object_db (dbName varchar(100), objectName varchar(100),objectType varchar(100))
insert into @.databases (dbname) select name from sysdatabases where dbid>4 and name not in ('pubs','northwind', 'holding_tank')
declare @.max_dbs int
select @.max_dbs=max(colid) from @.databases
declare @.current_db varchar(100)

declare @.Qstr nvarchar(2000)
declare @.Qstr1 nvarchar(2000)
set @.Qstr='insert into ##object_db select '
set @.Qstr1=''

declare @.i int
set @.i=1

while @.i<=@.max_dbs
begin
select @.current_db=dbname from @.databases wherecolid=@.i
--------------------------
if @.ExactORLikeSearch='E'

set @.Qstr1=@.Qstr+''''+@.current_db+''''+',name, case xtype when '+''''+'U'+'''' +' then ' +''''+'table'+''''+' when ' +''''+'P'+''''+' then '+''''+'procedure'+''''+' when '+''''+'F'+''''+' then '+''''+'function'+''''+' when '+''''+'V'+''''+' then '+''''+'view'+''''+ ' end as ObjectType
from '+@.current_db+'.dbo.sysobjects wherename='+''''+@.object_name+''''
--------------------------
if @.ExactORLikeSearch='L'

set @.Qstr1=@.Qstr+''''+@.current_db+''''+',name, case xtype when '+''''+'U'+'''' +' then ' +''''+'table'+''''+' when ' +''''+'P'+''''+' then '+''''+'procedure'+''''+' when '+''''+'F'+''''+' then '+''''+'function'+''''+' when '+''''+'V'+''''+' then '+''''+'view'+''''+ ' end as ObjectType
from '+@.current_db+'.dbo.sysobjects where namelike'+''''+'%'+@.object_name+'%'+'''' +'and xtype in ('+''''+'U'+''''+','+''''+'P'+''''+','+''''+'F'+','+''''+''''+'V'+''''+')'
--------------------------

exec sp_executesql @.Qstr1
set @.i=@.i+1
end

select * from ##object_db
drop table ##object_db

end

sql