Copying Stored Procedures From one server to another
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 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!
There are no comments for this entry.
[Add Comment]