Copying Stored Procedures From one server to another

Posted At : August 26, 2008 9:52 PM | Posted By : Phillip Gagnon
Related Categories: ColdFusion,MSSQL

In our coding environment, like most, we have a development environment and a production environment. As we have been making the move to use more stored procedures and less queries, we have run into an issue where the procs do not get synchronized between the development (dev) and live server.

So in order to combat this, I wrote the following code snippet, that copies all the stored procs from one database, and copies them to another. All you have to do is update the two data sources to point to the correct locations. In the code, the dev server, is the server where the procs are copied from, and the live server, is the server where the procs are copied to.

<cfset devDsn="development_database_server">
<cfset liveDsn="live_database_server">

<cfquery name="qProcs" datasource="#devDsn#">
   SELECT o.*, c.text
   FROM sysobjects o LEFT JOIN dbo.syscomments c ON o.id=c.id
   WHERE xType='P' and category=0
</cfquery>
<cfoutput>
Starting loop now<br /><br /><br />
<cfflush>
<cfloop query="qProcs">

   <cfquery datasource="#liveDsn#">
      IF EXISTS(SELECT name FROM sysobjects WHERE name = '#name#' AND type = 'P')
         DROP PROCEDURE #name#
   </cfquery>
   
   <br /><br /><br />
   #text#
   <br /><br /><br /><br />
   <cfflush>
   <cftry>
   
      <cfquery datasource="#liveDsn#">   
         #preserveSingleQuotes(text)#
      </cfquery>
   <cfcatch type="any">
      <h1 style="color:red;">Error Writing #qProcs.name#</h1><br /><br /><br />
      #qProcs.text#<br /><br /><br /><br />
      <cfdump var="#cfcatch#">
   </cfcatch>
   </cftry>
</cfloop>
</cfoutput>



One problem that I did come across, was with procedures that have dynamic SQL statements within them. Other than that, seems to work fine.

Please let me know if you run into any issues!

Comments (29) | Print | Send | del.icio.us | Digg It! | Linking Blogs | 304 Views
Comments
[Add Comment]
world of warcraft power leveling's Gravatar The Warlock can be one of the best damage casters in World of Warcraft, but it's also a very complicated class with its damage-over-time (DoT) spells and minions for any occasion. You learn a variety of curses that you will use depending on your situation, and you also gain the ability to send your enemies running away from you in terror. Warlocks can be a difficult class to play, but if you can do it well you will be the envy of all other damage dealers. Most people will despise you in Player vs Player battles.
# Posted By world of warcraft power leveling | 4/3/10 1:29 AM
Frankie's Gravatar For active rapidshare users one of the most convenient ways of finding films/music at rapidshare is to use rapidshare SE( http://www.rapidsharemix.com/trends/ir/12-04-2010.... )
# Posted By Frankie | 4/13/10 3:13 PM
sf's Gravatar Aww, this was a really quality post. In theory I’d like to write like this too – taking time and real effort to make a good article… but what can I say… I procrastinate alot and never seem to get something done.
# Posted By sf | 5/17/10 2:16 AM
mbt shoes's Gravatar Good read. There is currently quite a lot of information around this subject on the net and some are most defintely better than others. You have caught the detail here just right which makes for a refreshing change – thanks.
# Posted By mbt shoes | 5/17/10 8:12 AM
mbt shoes's Gravatar This article has great reference value, thank you very much for sharing, I would like to reproduced your article, so that more people would see it.
# Posted By mbt shoes | 5/18/10 2:24 AM
coach outlet's Gravatar http://www.coachoutletmalls.com Coach outlet
http://www.coach-handbags-outlet.com coach handbags
http://www.coachoutletfactory.com coach factory outlet
http://www.chanelbags2010.com chanel bags 2010
# Posted By coach outlet | 5/22/10 3:35 AM
gucci bags's Gravatar http://www.thebest2u.net gucci bags
http://www.gucci-zone.com gucci outlet
# Posted By gucci bags | 5/22/10 11:06 AM
chanel bags's Gravatar http://www.chanelbags2010.com/ chanel bags 2010
http://www.chanelbags2010.com/ chanel bags
http://www.chanelbags2010.com/ cheap chanel bags
http://www.chanelbags2010.com/ chanel bags online
http://www.coachoutletmalls.com/ Coach outlet
# Posted By chanel bags | 5/29/10 3:30 AM
coach handbags's Gravatar http://www.coachoutletfactory.com coach factory outlet
http://www.coachoutletfactory.com coach outlet
http://www.coachoutletfactory.com coach bags on sale
# Posted By coach handbags | 5/30/10 10:17 PM
ed hardy clothing's Gravatar http://www.ed-hardy.cc ed hardy
http://www.ed-hardy.cc edhardy
http://www.ed-hardy.cc swimsuit trends 2010
http://www.ed-hardy.cc swimsuit bikini
http://www.ed-hardy.cc women swimsuit
http://www.ed-hardy.cc swimsuit girls
http://www.ed-hardy.cc swimsuit pics
# Posted By ed hardy clothing | 5/31/10 5:56 AM
ed hardy clothing's Gravatar http://www.ed-hardy.cc ed hardy
http://www.ed-hardy.cc edhardy

http://www.ed-hardy.cc e d hardy
http://www.ed-hardy.cc hardy ed
http://www.ed-hardy.cc authentic ed hardy

http://www.ed-hardy.cc buy ed hardy
http://www.ed-hardy.cc cheap ed hardy
http://www.ed-hardy.cc discount ed hardy
http://www.ed-hardy.cc cheap ed hardy
http://www.ed-hardy.cc ed hardy outlet
http://www.ed-hardy.cc wholesale ed hardy
http://www.ed-hardy.cc ed hardy for sale
http://www.ed-hardy.cc ed hardy on sale
http://www.ed-hardy.cc sale ed hardy
http://www.ed-hardy.cc don ed hardy
http://www.ed-hardy.cc ed hardy designs

http://www.ed-hardy.cc ed hardy shop
http://www.ed-hardy.cc ed hardy store
http://www.ed-hardy.cc cheap ed hardy clothing
http://www.ed-hardy.cc ed hardy christian au
http://www.ed-hardy.cc ed hardy accessories
# Posted By ed hardy clothing | 6/6/10 11:18 PM
coach bags outlet's Gravatar I really like your article, I support your point of view
# Posted By coach bags outlet | 6/18/10 3:20 AM
coach outlet's Gravatar http://www.coachoutletmalls.com Coach outlet
http://www.coachoutletmalls.com coach bags outlet
http://www.guccihandbagsoutlet.com gucci handbags outlet
http://www.guccihandbagsoutlet.com gucci outlet
# Posted By coach outlet | 6/20/10 10:49 PM
gucci bags's Gravatar http://www.thebest2u.net gucci bags
http://www.gucci-zone.com gucci outlet
http://www.gucci-zone.com gucci outlet online
http://www.gucci-zone.com gucci outlet store
# Posted By gucci bags | 7/5/10 11:35 AM
gucci's Gravatar <a href="http://www.gucciusaoutlet.com"; target="_blank"><strong>Gucci Messenger Bag</strong></a>

<a href="http://www.gucciusaoutlet.com"; target="_blank"><strong>Gucci Shoulder Bag</strong></a>

<a href="http://www.gucciusaoutlet.com"; target="_blank"><strong>Gucci Tote</strong></a>

<a href="http://www.gucciusaoutlet.com"; target="_blank"><strong>cheap gucci sale</strong></a>


http://www.gucciusaoutlet.net/index.php?main_page=...

http://www.gucciusaoutlet.net/index.php?main_page=...

http://www.gucciusaoutlet.net/index.php?main_page=...

mickey-ruan@hotmail.com
# Posted By gucci | 7/6/10 10:59 PM
Jaty's Gravatar http://www.eluxuryblog.com
http://www.bigbigwatch.com/B.R.M-Watches-157.html

http://www.eluxuryblog.com
http://www.eluxuryblog.com/louis-vuitton-bags-c-1.html
http://www.eluxuryblog.com/chanel-bags-c-48.html" target="_blank">http://www.eluxuryblog.com/chanel-bags-c-48.html
http://www.eluxuryblog.com/gucci-bags-c-61.html" target="_blank">http://www.eluxuryblog.com/gucci-bags-c-61.html
http://www.eluxuryblog.com/chloe-bags-c-57.html" target="_blank">http://www.eluxuryblog.com/chloe-bags-c-57.html
http://www.eluxuryblog.com/hermes-bags-c-62.html
http://www.eluxuryblog.com/jimmy-choo-bags-c-63.html
http://www.eluxuryblog.com/prada-bags-c-53.html
http://www.eluxuryblog.com/news/comprar-tienda-chanel-bolsos-de-imitación-online-shop-a-12043.html
http://www.eluxuryblog.com/news/comprar-bolsos-louis-vuitton-de-imitación-online-shop-a-12044.html
http://www.eluxuryblog.com/news/comprar-bolsos-gucci-de-auténticos-o-bolsos-gucci-de-imitación-a-12045.html
http://www.eluxuryblog.com/news/ridefinire-di-lusso-con-una-borsa-gucci-replica-mini-totes-a-3112.html
http://www.eluxuryblog.com/news/todo-el-mundo-impresionar-con-hermes-réplica-bolsos-a-3186.html
http://www.eluxuryblog.com/news/hermes-replica-borse-strutturato-canvas-un-sogno-venire-vero-a-3099.html
http://www.eluxuryblog.com/news/redefinir-de-lujo-con-un-bolso-de-gucci-mini-réplica-totes-a-3190.html
http://www.eluxuryblog.com/news/trova-sconto-borse-louis-vuitton-a-107.html
http://www.eluxuryblog.com/news/suggerimenti-su-come-trovare-sconto-replica-borse-a-495.html
http://www.eluxuryblog.com/news/trova-sconto-borse-louis-vuitton-a-107.html
http://www.eluxuryblog.com/news/compra-scontati-chanel-borse-online-shop-a-30.html
http://www.eluxuryblog.com/news/definire-la-propria-identità-su-gucci-replica-borse-a-3123.html
http://www.eluxuryblog.com/news/how-to-buy-designer-louis-vuitton-replica-borse-a-502.html
http://www.eluxuryblog.com/news/compra-scontati-chanel-borse-online-shop-a-30.html
http://www.eluxuryblog.com/news/trouver-discount-louis-vuitton-sacs-a-102.html
http://www.eluxuryblog.com/news/acheter-discounted-chanel-sacs-boutique-en-ligne-a-33.html
http://www.eluxuryblog.com/news/finden-sie-ein-discountlouisvuittonhandtaschen-a-104.html
http://www.eluxuryblog.com/news/wie-zu-erkennen-replica-gucci-handtaschen-a-183.html
http://www.eluxuryblog.com/news/chanel-nr-5-kommerzielle-ursache-scorsese-shutter-island-a-11950.html
http://www.eluxuryblog.com/news/kaufen-Günstige-chanel-handtaschen-online-shop-a-32.html
http://www.eluxuryblog.com/news/louis-vuitton-replika-torebki-popularne-i-gdzi-kupi?-a-531.html
# Posted By Jaty | 7/16/10 2:44 AM
Jaty's Gravatar http://www.top50watches.com/
http://www.top50watches.com/rolex-c-137.html" target="_blank">http://www.top50watches.com/rolex-c-137.html
http://www.top50watches.com/breitling-c-21.html" target="_blank">http://www.top50watches.com/breitling-c-21.html
http://www.top50watches.com/bvlgari-c-22.html" target="_blank">http://www.top50watches.com/bvlgari-c-22.html
http://www.top50watches.com/cartier-c-23.html" target="_blank">http://www.top50watches.com/cartier-c-23.html
http://www.top50watches.com/original-boxes-franck-muller-c-4_188.html" target="_blank">http://www.top50watches.com/original-boxes-franck-...
http://www.top50watches.com/original-boxes-iwc-c-4_192.html" target="_blank">http://www.top50watches.com/original-boxes-iwc-c-4...
http://www.top50watches.com/officine-panerai-c-158.html" target="_blank">http://www.top50watches.com/officine-panerai-c-158...
http://www.top50watches.com/omega-c-150.html" target="_blank">http://www.top50watches.com/omega-c-150.html
http://www.top50watches.com/tag-heuer-c-64.html" target="_blank">http://www.top50watches.com/tag-heuer-c-64.html
http://www.top50watches.com/replica-mobile-phone-c-238.html
http://www.top50watches.com/news/rolex-price-list-current-usa-a-12545.html" target="_blank">http://www.top50watches.com/news/rolex-price-list-...
http://www.top50watches.com/news/edición-relojes-especiales-negro-mr-jones-cíclope-a-12505.html
http://www.top50watches.com/news/rolex-dayton-precio-descuento-venta-a-12548.html" target="_blank">http://www.top50watches.com/news/rolex-dayton-prec...
http://www.top50watches.com/news/rolex-oyster-perpetual-de-precios-en-españa-a-12549.html
http://www.top50watches.com/news/rolex-segunda-mano-en-españa-a-12550.html
http://www.top50watches.com/news/montres-de-luxe-en-france-achat-en-ligne-a-12551.html
http://www.top50watches.com/news/breitling-replica-orologi-a-447.html
http://www.top50watches.com/news/acquisto-di-questi-orologi-uomo-può-essere-il-modo-più-intellige-a-12552.html" target="_blank">http://www.top50watches.com/news/acquisto-di-quest...
http://www.top50watches.com/news/i-migliori-suggerimenti-di-fare-acquisti-orologi-uomo-a-1096.html
http://www.top50watches.com/news/orologi-breil-lo-stile-italiano-con-movimento-svizzero-a-12553.html
http://www.top50watches.com/news/relojes-suizos-rolex-réplica-estilo-de-moda-a-1175.html" target="_blank">http://www.top50watches.com/news/relojes-suizos-ro...
http://www.top50watches.com/news/por-qué-comprar-imitación-relojes-omega-a-12570.html
http://www.top50watches.com/news/comprar-breitling-relojes-de-imitación-de-top50watches-a-12572.html
http://www.top50watches.com/news/comprar-relojes-audemars-piguet-de-imitación-top-50-de-relojes-d-a-12573.html" target="_blank">http://www.top50watches.com/news/comprar-relojes-a...
http://www.top50watches.com/news/comprar-relojes-cartier-imitación-online-a-12574.html" target="_blank">http://www.top50watches.com/news/comprar-relojes-c...
http://www.top50watches.com/news/relojes-suizos-rolex-réplica-estilo-de-moda-a-1175.html" target="_blank">http://www.top50watches.com/news/relojes-suizos-ro...
# Posted By Jaty | 7/16/10 3:39 AM
Jaty's Gravatar http://www.trtime.com/
http://www.trtime.com/rolex-c-1.html
http://www.trtime.com/audemars-piguet-c-6.html
http://www.trtime.com/bell-ross-c-8.html
http://www.trtime.com/breitling-c-11.html" target="_blank">http://www.trtime.com/breitling-c-11.html
http://www.trtime.com/bvlgari-c-13.html
http://www.trtime.com/cartier-c-14.html
http://www.trtime.com/chopard-c-17.html
http://www.trtime.com/corum-c-21.html" target="_blank">http://www.trtime.com/corum-c-21.html
http://www.trtime.com/franck-muller-c-100.html
http://www.trtime.com/graham-c-27.html" target="_blank">http://www.trtime.com/graham-c-27.html
http://www.trtime.com/hublot-c-31.html
http://www.trtime.com/iwc-c-32.html
http://www.trtime.com/omega-c-39.html
http://www.trtime.com/panerai-c-42.html" target="_blank">http://www.trtime.com/panerai-c-42.html
http://www.trtime.com/patek-philippe-c-44.html
http://www.trtime.com/tag-heuer-c-56.html" target="_blank">http://www.trtime.com/tag-heuer-c-56.html
http://www.trtime.com/u-boat-c-58.html
http://www.trtime.com/ulysse-nardin-c-250.html
# Posted By Jaty | 7/16/10 3:47 AM
Jaty's Gravatar http://www.etrade8888.com
http://www.etrade8888.com/louis-vuitton-bags-c-17.html
http://www.etrade8888.com/louis-vuitton-wallets-c-302.html
http://www.etrade8888.com/news/lauren-conrad-opts-for-beige-colored-chanel-255-bag-a-2739.html
http://www.etrade8888.com/chanel-bags-c-147.html
http://www.etrade8888.com/gucci-bags-c-237.html" target="_blank">http://www.etrade8888.com/gucci-bags-c-237.html
http://www.etrade8888.com/miu-miubags-c-140.html
http://www.etrade8888.com/hermes-bags-c-138.html
http://www.etrade8888.com/prada--bags-c-154.html
http://www.etrade8888.com/jimmy-choo-bags-c-162.html
http://www.etrade8888.com/christian-dior-bags-c-167.html
http://www.etrade8888.com/burberry-bags-c-173.html
http://www.etrade8888.com/ugg-boots-c-210.html" target="_blank">http://www.etrade8888.com/ugg-boots-c-210.html
http://www.etrade8888.com/watches-c-232.html
http://www.etrade8888.com/shoes-boots-c-281.html
http://www.etrade8888.com/abercrombie-fitch-c-9.html
http://www.etrade8888.com/ed-hardy-c-33.html" target="_blank">http://www.etrade8888.com/ed-hardy-c-33.html
http://www.etrade8888.com/christian-audigier-c-39.html
http://www.etrade8888.com/crystal-rock-c-46.html
http://www.etrade8888.com/lacoste-c-3.html
http://www.etrade8888.com/paul-smith-c-96.html
http://www.etrade8888.com/ralph-lauren-c-6.html
http://www.etrade8888.com/sinful-c-114.html" target="_blank">http://www.etrade8888.com/sinful-c-114.html
http://www.etrade8888.com/smet-c-117.html
http://www.etrade8888.com/juicy-couture-c-75.html
http://www.etrade8888.com/fred-perry-c-97.html
# Posted By Jaty | 7/16/10 4:00 AM
Jaty's Gravatar http://www.etrade8888.com
http://www.etrade8888.com/news/
http://www.etrade8888.com/louis-vuitton-wallets-c-302.html
http://www.etrade8888.com/chanel-bags-c-147.html
http://www.etrade8888.com/gucci-bags-c-237.html" target="_blank">http://www.etrade8888.com/gucci-bags-c-237.html
http://www.etrade8888.com/miu-miu-bags-c-138.html
http://www.etrade8888.com/prada-bags-c-140.html
http://www.etrade8888.com/hermes-bags-c-154.html
http://www.etrade8888.com/christian-dior-bags-c-167.html
http://www.etrade8888.com/burberry-bags-c-173.html
http://www.etrade8888.com/mulberry-bags-c-179.html
http://www.etrade8888.com/ugg-boots-c-210.html
http://www.etrade8888.com/watches-c-232.html
http://www.etrade8888.com/abercrombie-fitch-c-9.html
http://www.etrade8888.com/ed-hardy-c-33.html
http://www.etrade8888.com/christian-audigier-c-39.html
http://www.etrade8888.com/crystal-rock-c-46.html" target="_blank">http://www.etrade8888.com/crystal-rock-c-46.html
http://www.etrade8888.com/lacoste-c-3.html
http://www.etrade8888.com/ralph-lauren-c-6.html" target="_blank">http://www.etrade8888.com/ralph-lauren-c-6.html
http://www.etrade8888.com/juicy-couture-c-75.html
http://www.etrade8888.com/fred-perry-c-97.html
http://www.etrade8888.com/sinful-c-114.html" target="_blank">http://www.etrade8888.com/sinful-c-114.html
http://www.etrade8888.com/smet-c-117.html
# Posted By Jaty | 7/16/10 4:07 AM
mbt schuhe's Gravatar http://www.cheap-mbt-shoes.net posture shoes
http://www.cheap-mbt-shoes.net mbt shoes clearance
http://www.cheap-mbt-shoes.net mbt walking shoes
http://www.cheap-mbt-shoes.net mbt outlet
# Posted By mbt schuhe | 7/28/10 11:12 PM
handyking's Gravatar eFox-shop.com puts quality at the top of business plan. Our Quality Control team carries out various testing processes to ensure only quality products are sold on the web. No counterfeits or reimbursed items would appear on our selling lists.
www.efox-shop.com
<a href="http://www.efox-shop.com"> chinahandy</a>
<a href="http://www.efox-shop.com"> dual sim handy</a>
<a href="http://www.efox-shop.com"> touchscreen handy</a>
<a href="http://www.efox-shop.com"> Chinesische Handys</a>
<a href="http://www.efox-shop.com"> chinesische handymarken</a>
<a href="www.efox-shop.com">china" target="_blank">http://www.efox-shop.com">china handy hersteller </a>
# Posted By handyking | 8/3/10 10:24 PM
timberland shoe company's Gravatar We are traveling by train. Out timberland hiking boots windows, we drink in the passing scene of cars on nearby highways, of children timberland shoe company waving at a crossing, of cattle grazing on a distant timberland boots hillside, of smoke pouring from a power plant, of row upon row of corn and wheat, of flatlands and timberland wheat shoes valleys, of mountains and rolling classic 3 eye timberland boat hillsides, of city skylines and village halls.But uppermost in our black timberland boots minds is the final destination.
# Posted By timberland shoe company | 8/27/10 9:17 PM
Wholesale NFL Jerseys's Gravatar http://www.jersey-fanatics.com
http://www.prodirectjerseys.com
<p>Jersey-fanatics.com is the leading online <a href="http://www.jersey-fanatics.com">Wholesale NFL Jerseys</a> trade marketplace since 2007.</p>
http://www.jersey-fanatics.com/prolist-id-26.html
http://www.jersey-fanatics.com/prolist-id-27.html
http://www.jersey-fanatics.com/prolist-id-42.html" target="_blank">http://www.jersey-fanatics.com/prolist-id-42.html
http://www.jersey-fanatics.com/prolist-id-43.html
http://www.jersey-fanatics.com/prolist-id-44.html
http://www.jersey-fanatics.com/prolist.php?id=286
# Posted By Wholesale NFL Jerseys | 9/1/10 4:09 AM
[Add Comment]
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.