May 26, 2009 at 1:47 am
Hi, I'm new to SQL and new to the forum. I have a stored procedure and have executed it.. I understand that once a stored procedure is executed it shoud go into the Programmability/Stored Procedures file. For some reason mine dont appear tp be going there. when I try to execute them again it tells me that the file name already exists. cam someone tell me what is/could be going on. I've tried refreshing the file but that didn't fix the problem!
May 26, 2009 at 5:52 am
Can you please post your actual code?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
May 26, 2009 at 6:10 am
Thanks for you reply, but I managed to sort it out! The procedure was executing but it was going into the master DB rather that one I specified. I was informed that by selecting the DB it would go to the right one, clearly not true!
May 26, 2009 at 7:18 am
execute sp_helptext 'ProcedureName' to get the script for Procedure
May 26, 2009 at 7:41 am
da.drew (5/26/2009)
I was informed that by selecting the DB it would go to the right one, clearly not true!
Definitely not true.
The DB selected in object explorer is not related to the DB that the query window is using. There's a drop down box at the top of the query window with a list of databases. The selected one is the one that your query is running in the context of
Or, if you have any USE statement in the query, the database specified in the USE statement will be the one that all queries after that run under.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2009 at 11:01 am
Start making it a habit to place a use statement before your create/alter statements for all objects. It will save you a lot of hassle in the future.
For example:
USE MyNewDatabase;
GO
CREATE PROCEDURE dbo.MyNewProcedure
@param1 int
AS
/* procedure code here */
GO
The other thing you should get in the habit of doing is schema qualifying all objects. That includes schema qualifying the object when you create it as I did in the above with 'dbo'.
And finally, you could specify the database name in the object name when creating/altering - but I don't recommend this just because you might have differently named databases:
Example: CREATE PROCEDURE MyNewDatabase.dbo.MyNewProcedure
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 26, 2009 at 1:01 pm
Thanks for the advise, but what is schema, ive seen it mentioned but I dont know what it is. SQL is really prettty new to me, (two day course).
Jeffrey Williams (5/26/2009)
The other thing you should get in the habit of doing is schema qualifying all objects. That includes schema qualifying the object when you create it as I did in the above with 'dbo'.
May 26, 2009 at 1:07 pm
You can think of the schema as the container that objects belong to. I would recommend reviewing books online on the subject because there is much more to it.
When you look at the objects in your database, I am assuming that all of the objects are named something like dbo.MyTable, dbo.MyProcedure, etc...
In this case, the schema is dbo and contains all of the objects. But, you could easily have objects named as drew.MyTable, drew.MyProcedure, etc... These objects could also live in the same database as the dbo objects, have the same name - but be different objects (not really recommended, just showing you the concept).
Again, please review BOL for further information.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply