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 |
|
Rupa
Posting Yak Master
123 Posts |
 Posted - 2007-06-05 : 09:52:35
|
| Hey allI have restored a database that was held on the SQL Server 2000 onto SQL Server 2005 Express Edition.All the stored procedures have been restored as well but when I try to modify it and then save it, it will save it as '.sql' in a folder that I can choose but how wld that wrk? Can't I save it as a stored procedure like on SQL Server 2000? I'm new to SQL Server 2005 so any response will be highly appreciated.Many thanksRupa |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-05 : 09:58:11
|
| "Can't I save it as a stored procedure like on SQL Server 2000"Presumably you "Executed" the CREATE PROCEDURE or ALTER PROCEDURE statement under SQL 2000. This will work OK under SQL 2005. However, better practice is to save each Sproc in a separate file, and use a Version Control system to "manage" the files.Kristen |
 |
|
|
Rupa
Posting Yak Master
123 Posts |
Posted - 2007-06-05 : 10:04:07
|
| Thx KristenWhat I meant to say is that when I restored the database, the procedures got stored as well. I would now like to amend (edit) the procedure so when I right click on the procedure and click on modify, I can modify it to my liking but when I click on 'save', it will save it as a '.sql' so when I refresh the procedures and open the one that I had just amended, it will show the previous one again. So the changes weren't saved, if you know what I mean?? Any suggestions? Many thanksRupa |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-05 : 10:26:28
|
| You can apply modified script to the db, that means you need execute it instead of saving it. |
|
|
|
Rupa
Posting Yak Master
123 Posts |
Posted - 2007-06-05 : 10:41:30
|
| Thx rmiao..Does that mean that I can't save it? I have to modify it every time I want to execute it?ThanksRupa |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-05 : 11:29:26
|
I think its only the change of terminology that is catching you out.1) The way used used to work on SQL 2000 was to "Execute" the Create/Alter Procedure syntax. This probably wasn't apparent in Enterprise Manager - I expect you just did RightClick : Properties on the Procedure, edited it, and then press OK and that was that! Fact is that E.M. Executed the script for you.  2) You can do this in SQL 2005 too - but "Save" is trying to save the Procedure Script to a file for you instead3) IMHO you would be much MUCH better off having your Procedures in files. Of course you also need them in the database, so when you modify them you need to Execute them (so the database is updated) and Save them (so the file is updated)4) When you want to Rollout your latest creation you can just "Execute" all the files that have changed. So you can develop against a DEV database, test against a QA database, and then Rollout to a PRODUCTION database. (And if your application is used by multiple clients or multiple servers/databases it becomes easy to run the appropriate Scripts to bring a given database up to the current version. Plus you can safeguard yourself by storing your script files in a version control system.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-05 : 22:55:46
|
| Don't have to execute more than once dor single change, it's saved in to db once you execute it. |
|
|
|
Rupa
Posting Yak Master
123 Posts |
Posted - 2007-06-06 : 04:44:17
|
| Aaahh!!! That makes sense..Thx Kristen, thx rmiao.Much appreciated!!!Rupa |
|
|
|
|
|
|
|
|
Migrating database