SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to save new stored procedure to database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hviezdoslav
Starting Member

USA
5 Posts

Posted - 03/24/2009 :  15:57:45  Show Profile  Reply with Quote
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.sql

I got the following error:

Msg 2812, Level 16, State 62, Line 1
Could 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 1
Could 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 1
Could 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 1
Could 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

USA
36834 Posts

Posted - 03/24/2009 :  16:01:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
To "save" it to the database, just run the CREATE PROC for it in SSMS. You do not need to save it to a file, saving to a file is for source control reasons.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

Hviezdoslav
Starting Member

USA
5 Posts

Posted - 03/24/2009 :  16:12:27  Show Profile  Reply with Quote
This is me again. I posted this thread to ask the question.

I tried the following:

Exec smhDirectory.dbo.a_test_only.sql

The 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 1
Could not find server 'smhDirectory' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

So I typed the following and executed:

Exec sp_addlinkedserver

I got the following error:

Msg 201, Level 16, State 4, Procedure sp_addlinkedserver, Line 0
Procedure '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 1
Line 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 1
Line 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.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36834 Posts

Posted - 03/24/2009 :  16:15:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
You are going about this incorrectly. Open your sql file in a "New Query" window inside SQL Server Management Studio and hit F5 (click the green arrow) to execute ("save") the stored procedure to the database.

This is the same procedure you'd use in Query Analyzer too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36834 Posts

Posted - 03/24/2009 :  16:17:06  Show Profile  Visit tkizer's Homepage  Reply with Quote
What are you trying to do with sp_addlinkedserver? That is used to add a linked server and has nothing to do with saving a stored procedure to the database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

Hviezdoslav
Starting Member

USA
5 Posts

Posted - 03/24/2009 :  16:22:52  Show Profile  Reply with Quote
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
Go to Top of Page

Hviezdoslav
Starting Member

USA
5 Posts

Posted - 03/24/2009 :  16:35:38  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36834 Posts

Posted - 03/24/2009 :  17:08:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
You just need to refresh the tree. You do not need to logout.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

Hviezdoslav
Starting Member

USA
5 Posts

Posted - 03/24/2009 :  18:14:24  Show Profile  Reply with Quote
Okay, Tara.

I thank you bunches upon bunches.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36834 Posts

Posted - 03/24/2009 :  19:25:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000