I can't find my stored proc in Query Analyser

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

General SQL Server Forums

New to SQL Server Programming

I can't find my stored proc in Query Analyser

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-04 : 23:46:50

1/
I create stored procedure in Query Analyser using:
ALTER PROCEDURE dbo.unshippedtotal
@name char,
@ytd int output
AS
select * from vwaccount
select @ytd=sum(AccAccnnmb) from vwaccount
return
GO

But When I try to expand the list of stored procedures under the DB and the server name, I can t see my Stored Proc called "unshippedtotal"

Any ideas

2/ Another question pls:
Can we use Query Analyser to do the same tasks we do with Entreprise Manager like creating and modifying tables, Creating Stored Procedures, Modifying Views... (I m more familiar with Entreprise Manager but somebody told me it s better to use Query Analyser)

Thanks for coaching :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-04 : 23:56:57

1)
If you are in the correct server & database, the stored procedure should be there.
Also try the following. It will list the routines (SP, Functions etc) in your DB.

select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = 'unshippedtotal'

2)
Yes. You should use Query Analyser to perform these function. EM provides you a GUI for creating / modifying tables. In Query Analyser you have to use the DDL command to do this. QA is preferred as you can save the script for creating table / views, Stored Procedure / Functions etc a Query File (.SQL). This allows you perform version control and also when you need to deploy the database, you only need to run the saved scripts.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-05 : 01:12:05

(i like the idea of storing the scripts for deploymnt)
1/DDL command is it like: create table, Drop table.....and so on?
2/u mean we can t create tables as we do in etntreprise manager

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-05 : 01:39:36

1) Yes. DDL is Data Definition Language.
2) No. You have to use DDL.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-06-05 : 16:09:15

Thanks Khtan

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-06-05 : 20:30:43

Did you ever find your stored procedures? You have to also expand the database and Stored Procedures folder they should be in. If you can't see them after creating them, you will need to right-click on one of the levels and refresh.

BTW, if you need help with all these statements, you can find them all in Books Online. There are tons of great examples.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-06 : 02:05:02

refreshing the object browser usually does the trick