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.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Automating the Database Patch process

Author  Topic 

nnara530
Starting Member

1 Post

Posted - 2012-12-04 : 02:35:17
Hi

We are into application development and everytime we release the product there are large number of database patches that needs to be run.

Right now these .sql files are being created by the individual developers and the release manager discuss with development team and ordering them and then finally execute them manually.

This is becoming very combursome procedure and trying to automate this process.

Is it possible to write a Stored Procedure that will accept the block of SQL statements as a parameter and then run these block of sql statements?

Generally the .sql statements will have ALTER scripts, INSERT Statements and Update statements etc..

One e.g. is given below

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RULES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table [dbo].[RULES]
IF @@ERROR = 0
print 'Table RULES dropped'
ELSE
print 'Drop of table RULES failed'
END

INSERT INTO [dbo].[MY_CONFIG_WORK]
SELECT [CONFIG_SECTION], [CONFIG_KEY],[CONFIG_VALUE_TYPE], [CONFIG_VALUE] FROM [dbo].[MY_CONFIG]
GO



Kind Regards

Narayanan

srimami
Posting Yak Master

160 Posts

Posted - 2012-12-18 : 04:15:21
Hi,

Use Sqlcmd scripts to execute these scripts and schedule them to run using windows schedular.

Thanks,
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-12-20 : 11:17:36
Check this link - which also include an output option - http://www.sqlserver-dba.com/2012/09/sql-server-sqlcmd-with-screen-output-and-output-file.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -