Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Saved sprocs not going where I need them.

Status
Not open for further replies.

willydude

Programmer
Oct 24, 2006
123
US
After making changes to sprocs in my DB “Payroll” and then saving them, I am directed to:
C:\Documents and Settings\Administrator\My Documents\SQL Server Management Studio Express\Projects where the sproc and the changes are saved.

But when I go back into the sproc in the Payroll DB, the changes are not saved there. How can get management studio to save the sproc in the Payroll DB and not in the Projects folder?

I know I can change the above path thru Tools/Options to QueryResults, but that is not my DB “Payroll”. Plus, I am not wanting to save the results. I am wanting to save the changed query in my DB “Payroll”

I have found my Payroll file in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data, but when I try to view it I get the message that it being used, even when Mgnt Studio is not open.

I have tried to look in the Master DB, but it gives me an error message also.

TIA.

Bill
 
So when I press F5, the sproc is saved in my Payroll DB and I do not need to use File/Save which saves it to the Projects folder?

I've been doing way too much clicking and saving!

Thanks.
 
It's best to do it both ways. I mean, you have to save it to the database (F5) in order to use the sproc. But it's best to save it to the local drive so that you can ALSO put the sproc in to source safe (or some other version control system).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
In order to put the procedure onto the SQL Server you have to execute the ALTER PROCEDURE script against the database. Without doing this all you are doing is saving the code which makes the scripts to your local hard drive.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog
 
Greetings,

I am a stored procedure newbie.

I came across this thread and thought I had struck gold, but I am still messing something up.

When I click F5 or Execute it says it completes successfully.

However I cannot see the sp in the list of others sps.

I see in the last post where it is recommednded to execute the ALTER PROCEDURE script?

Can someone pls help?

Thanks!
 
It may be a simple as refreshing the list.

Right click on 'Stored Procedures' and click refresh.

You can also verify that the stored procedure is created by checking the information_schema views. Like this...

[tt][blue]Select Specific_Name From Information_Schema.Routines Order By Specific_Name[/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top