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.
| Author |
Topic |
|
Hviezdoslav
Starting Member
5 Posts |
 Posted - 2009-03-24 : 15:57:45
|
| Greetings,I am a junior developer and only have experience using SQL Server 2000. Now I am using SQL Server 2005.In SQL Server 2000 at my first employer, I could go to Stored Proc section of a database and click to create a new stored proc. Assuming of course that the compilation was successful, I could see then the new stored proc in the Stored Proc section of the database in which I was working. Of course I could go to Query Analyzer to execute the stored proc to test. I never saved the stored proc to any place on my local drive but saved it to the database's tree, to the db itself within the Stored Proc section.So on SQL Server 2005 at my new employer, I created a new stored proc in the Stored Procedures section of a database. When saving, it prompted me to save to the Projects folder. I can see the stored proc that I saved sitting in C:\Documents and Settings\MWitt\My Documents\SQL Server Management Studio\Projects. I have read on the Internet that I need to execute the stored proc in order to see the stored proc in the Stored Procedures section of the database, in the tree on the left. This SQL Server 2005 stored proc is just to learn how to save a stored proc to the db and it just selects all from a table within that db. So I clicked New Query and typed the following: Exec a_test_only.sqlI got the following error:Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'a_test_only.sql'.I tried typing Exec smhDirectory.dbo.a_test_only.sql, but got the error: Msg 7202, Level 11, State 2, Line 1Could not find server 'smhDirectory' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. smhDirectory is the database in which I am creating the stored proc.I typed Exec dbo.a_test_only and got the error: Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'dbo.a_test_only'.I typed Exec smhDirectory.a_test_only.sql, but I got the error: Msg 2812, Level 16, State 62, Line 1Could not find stored procedure 'smhDirectory.a_test_only.sql'.Basically, I read on the Internet that I need to execute the stored proc to bring it into the database's tree, but all of the ways in which I am trying to execute the stored procedure do not work.I have only used SQL Server 2000 in the past. It was easy. Click to create new stored proc. Click OK to save, and if it compiles it saves to the Stored Proc section in the database's tree and I can open it in the future to continue working on it or whatever.In SQL Server 2005, how do I bring the stored procedure from this Projects folder into the tree of Management Studio so that I can see the stored proc and modify it? It seems strange that this is such an arduous task.I apologize if my ignorance about this issue seems appalling, but I must learn in SQL Server 2005 to do what I was doing at my last job in SQL Server 2000 (i.e., create a stored proc, see the stored proc in the tree of the db in the Stored Proc section, open up the stored proc to modify it, et cetera). Also I'll need to be able to execute stored procs within Management Studio to test 'em.Please help. I would be very appreciative if anybody could help me figure out in SQL Server 2005 how to do what would seem to be a simple task.Thanks in advance,Mike |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Hviezdoslav
Starting Member
5 Posts |
Posted - 2009-03-24 : 16:12:27
|
| This is me again. I posted this thread to ask the question.I tried the following:Exec smhDirectory.dbo.a_test_only.sqlThe name of the db is smhDirectory. The tables in the db have dbo in the beginning of their names. I get the following error though:Msg 7202, Level 11, State 2, Line 1Could not find server 'smhDirectory' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.So I typed the following and executed:Exec sp_addlinkedserverI got the following error:Msg 201, Level 16, State 4, Procedure sp_addlinkedserver, Line 0Procedure 'sp_addlinkedserver' expects parameter '@server', which was not supplied.So I executed the following using the server name:Exec sp_addlinkedserver(@devsql02)I got the following:Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '@devsql02'.So I tried executing the following:Exec sp_addlinkedserver(devsql02)I got the following:Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'devsql02'.Surely I am ignorant of something as this cannot be such an arduous task in SQL Server 2005. It was so easy in SQL Server 2000 as I needed only to create a new stored proc, save it, see it in the tree, modify it when necessary by opening it from within the database's tree inside the Stored Proc section, and test if desired within Query Analyzer. I must learn how to do the same in SQL Server 2005. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Hviezdoslav
Starting Member
5 Posts |
Posted - 2009-03-24 : 16:22:52
|
| Gregarious greetings Tara,I thank you much for your reply.I am not sure I understand what you mean when you write to run the CREATE PROC.I just right-clicked on the Stored Procedures folder within the Programmability folder of the db. I entered a name (a_TEST to be specific) after CREATE PROCEDURE. Then I entered a simple select to select all from a table within this db in the section for insert statement. I clicked Execute. I got the following:Command(s) completed successfully.But I do not see this new stored proc. I closed the tree for this db and opened up the tree again. I then opened the Programmability folder again and then opened again the Stored Procedures folder.I do not see this new stored proc though named a_TEST in the System Stored Procedures folder of the Stored Procedures folder or within the Stored Procedures folder.Do you have any ideas or suggestions, Tara? Do I need to type "dbo" when I am typing the name of the new stored proc after CREATE PROCEDURE? I'll try that approach and will post this reply at this time.Thanks again, Tara.Mike |
|
|
|
Hviezdoslav
Starting Member
5 Posts |
Posted - 2009-03-24 : 16:35:38
|
| No Tara, even if I create a stored proc with CREATE PROCEDURE dbo.a_A_TEST and execute it, even though it says that the Command Completed Successfully, I cannot find the stored proc anywhere in the tree of stored procs. Surely I do not need to logout of SQL Server Mgmt Studio and then login again.No way, Tara!!! I did get out of SQL Server Mgmt Studio and then login again; and lo and behold I see the two stored procedures named dbo.a_A_TEST and dbo.a_TEST within the Stored Procedures folder.Maybe my memory serves me incorrectly, but I think that in SQL Server 2000/Enterprise Mgr, after clicking New Stored Proc and then clicking OK to compile, instantly I could see the new stored proc in the db's tree without having to logout and logon. So I guess I will need to logout and login again in SQL Server 2005 in order to see the stored proc.I guess that I do not need to type "dbo" after CREATE PROCEDURE when I am creating the stored proc because I did not do so with one of these two stored procs yet I still see both of 'em after I logged back into SSMS.Thanks very much to you, Tara.Actually, before I read your kind post, I had tried clicking Execute after creating a new and simple test stored proc and it said that the command completed successfully, but since I had not logged off and logged in again I could not see the stored proc in the tree and thought that something was wrong.I could swear that in SQL Server 2000, after clicking OK the new stored proc showed instantly in the stored proc section of the db's tree (though maybe my ol' memory ain't rememberin' correctly).Anyhow, I thank you again.Have a decidedly delightful day, Tara!Mike |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Hviezdoslav
Starting Member
5 Posts |
Posted - 2009-03-24 : 18:14:24
|
| Okay, Tara.I thank you bunches upon bunches. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
How to save new stored procedure to database