October 1, 2009 at 1:03 pm
I have some 110 new SPs and Functions in a Development Database; they need to be moved to the testing Database in another server. I have the list of which ones I need. The total number of SPs and Functions are around 250 (including the new ones).
what is the best/fastest way to get this done?
Thanks
Dan
October 1, 2009 at 1:06 pm
Do you have the create scripts for all of them? If so, put all of those into one big document, and then run that.
If not, then I recommend you check out the tools that RedGate and ApexSQL sell for comparing and merging database structures.
Failing that, select the definitions from sys.sql_modules, and use those as create scripts.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 1, 2009 at 2:00 pm
Hey GSquared, Cant you use SSIS to transfer the SPs? I am not sure if it can be done 2005 but it can be done in 2008. Or am I mistaken?
-Roy
October 1, 2009 at 4:33 pm
you can used SSMS to script them
October 1, 2009 at 6:39 pm
Can I select (Cherry pick) just the SP's I need from SSMS 2005?
October 2, 2009 at 7:30 am
repent_kog_is_near (10/1/2009)
I have some 110 new SPs and Functions in a Development Database; they need to be moved to the testing Database in another server. I have the list of which ones I need. The total number of SPs and Functions are around 250 (including the new ones).what is the best/fastest way to get this done?
Thanks
Dan
there's not going to be a fast way today. However, tomorrow you could have a very fast way in place if, you take the time today to move your procedures into individual scripts and check those scripts into source control. Then, always edit all procedures through the script, not on the database. Check it out of source control, do the edit, test it on the database, and then check it in to source control.
THEN, label your source controls versions. Every time you do a build to the QA environment, put a label on first. Any good source control system will allow you to capture just those procedures that have been changed since the last label or version and you can check them out and run them, usually through an automated process.
With enough work, and/or with the right tools, you can make this completely automatic, quick and easy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 2, 2009 at 12:01 pm
The Grant's way it the right way.
repent_kog_is_near (10/1/2009)
Can I select (Cherry pick) just the SP's I need from SSMS 2005?
But as a fast an durty solution you can just script all of your SPs with 5 clicks.
Under YourDB_name expand Programmability-> Stored Procedures.
Then click on Stored Procedures, and you will get list of all of them in "Object Explorer Details" pane.
Click anywhere at that pane, then CTRL+A and you will highlight all of them.
Then CTRL+click on System Procedures folder to unhighlight it (you don't need them).
Then Right-Click on in highlighted area script all of your SPs as "CREATE TO.."
October 2, 2009 at 12:06 pm
I forgot to say you have to grab this script and run it on a new server, and all of your scripted SPs will be transferred to the new server.:-)
October 2, 2009 at 12:08 pm
Grant Fritchey (10/2/2009)
repent_kog_is_near (10/1/2009)
I have some 110 new SPs and Functions in a Development Database; they need to be moved to the testing Database in another server. I have the list of which ones I need. The total number of SPs and Functions are around 250 (including the new ones).what is the best/fastest way to get this done?
Thanks
Dan
there's not going to be a fast way today. However, tomorrow you could have a very fast way in place if, you take the time today to move your procedures into individual scripts and check those scripts into source control. Then, always edit all procedures through the script, not on the database. Check it out of source control, do the edit, test it on the database, and then check it in to source control.
THEN, label your source controls versions. Every time you do a build to the QA environment, put a label on first. Any good source control system will allow you to capture just those procedures that have been changed since the last label or version and you can check them out and run them, usually through an automated process.
With enough work, and/or with the right tools, you can make this completely automatic, quick and easy.
Holy, I did not know that Sergey! When I read through your comment, I was like.. "how does he selects all the procs" and I read again, and the damn object explorer details that I always close sounds like the way to go! Damn I learn something new everyday.
I will not use that really often, since I use source control, but it can turn out to be useful sometimes!
Thanks for that,
Have a nice day,
Cheers,
J-F
October 2, 2009 at 1:07 pm
J-F Bergeron (10/2/2009)
Grant Fritchey (10/2/2009)
repent_kog_is_near (10/1/2009)
I have some 110 new SPs and Functions in a Development Database; they need to be moved to the testing Database in another server. I have the list of which ones I need. The total number of SPs and Functions are around 250 (including the new ones).what is the best/fastest way to get this done?
Thanks
Dan
there's not going to be a fast way today. However, tomorrow you could have a very fast way in place if, you take the time today to move your procedures into individual scripts and check those scripts into source control. Then, always edit all procedures through the script, not on the database. Check it out of source control, do the edit, test it on the database, and then check it in to source control.
THEN, label your source controls versions. Every time you do a build to the QA environment, put a label on first. Any good source control system will allow you to capture just those procedures that have been changed since the last label or version and you can check them out and run them, usually through an automated process.
With enough work, and/or with the right tools, you can make this completely automatic, quick and easy.
Holy, I did not know that Sergey! When I read through your comment, I was like.. "how does he selects all the procs" and I read again, and the damn object explorer details that I always close sounds like the way to go! Damn I learn something new everyday.
I will not use that really often, since I use source control, but it can turn out to be useful sometimes!
Thanks for that,
Have a nice day,
If you want to do the functions AND procs in one shot, simply right-click on the database name, pick tasks, "generate scripts", and follow the prompts.
You then control the "select all" vs cherry-picking, whether to include permissions, include drop statements, etc....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2009 at 1:10 pm
Just keep in mind that, if you have anything you DON'T want to transfer, the script all objects method will need some editing after the fact. So either choose just the ones you want, or redact the ones you don't from the resulting script.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2009 at 7:34 am
Holy, I did not know that Sergey! When I read through your comment, I was like.. "how does he selects all the procs" and I read again, and the damn object explorer details that I always close sounds like the way to go! Damn I learn something new everyday.
I will not use that really often, since I use source control, but it can turn out to be useful sometimes!
Thanks for that,
Have a nice day,
Cheers,
J-F
You are very welcome. It is always good to know something new, right?;-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply