We are constantly implementing new tasks and developing web sites for about 12 customers. We have a development server at our office and our live servers are at our datacenter. Many times we have to rewrite or modify stored procedures and then after testing, we need to copy them to the live server.

To accomplish this task we could normally script the SPs and copy the scripts to the live server, but that would take us a lot of time. The solution was written by our colleague Phillip Gagnon, who wrote a piece of code to automate that task of copying from dev to live.
I found it interesting and with his permission I’m posting it here.

Here is the code,you may call it pushToLive.cfm:

{code type=xhtml}<cfparam name=”url.devDsn” default=”myDevDSN”>
<cfparam name=”url.liveDsn” default=”myLiveDSN”>
<cfparam name=”url.output” default=”false”>
<cfquery name=”qProcs” datasource=”#devDsn#”>
SELECT o.name, c.text
FROM sysobjects o LEFT JOIN dbo.syscomments c ON o.id=c.id
WHERE xType=’P’ and category=0
</cfquery>
<cfoutput>
There are #qProcs.recordCount# procedures to overwrite<br /><br /><br />
<cfflush>
<cfset i=1>
<cfloop query=”qProcs”>
<cfquery datasource=”#liveDsn#”>
IF EXISTS(SELECT name FROM sysobjects WHERE name = ‘#name#’ AND type = ‘P’)
DROP PROCEDURE #name#
</cfquery>
<cfif url.output>
<br /><br /><br />
#text#
<br /><br /><br /><br />
<cfflush>
</cfif>
<cftry>
#i#. Writing #name#<br />
<cfflush>
<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>
<cfset i=i+1>
</cfloop>
<br />
Done!<br />
</cfoutput>{/code}