Whats the best to run sql scripts stored in a text column?

Question

I've got the task to automate the database modification scripts in our simulation server.

Each simulation generates a few databases, all based from an common base DB, which is empty.

But now is needed to do the same to get the data from last year's simulation database.

I devised to create an clone of that DB based on a backup from the moment we need. And apply all database changes from the moment the DB was created till now.

So the mechanism I imagine is something that can run an script (like the ones generated either by ER/Studio or SSMS) stored in a version table - which will be called from a SQL Agent job step.

EDIT: What is the simplest way to do that - running an script stored in a table from an SQL Agent job step?

P.S.: if the solution can avoid xp_cmdShell or sql_cmd or even preprocessing the script either in Delphi or in a SQLCLR function, it's better - but if's the only way I can cope with that.

score 0 · Answer 1 · answered Mar 13 '15 at 18:35

0

You can run a proc that does this:

create table jobs (step varchar(max))
insert into jobs  values ('select * from sys.objects')
insert into jobs values ('select * from sys.databases')
--create a proc from this code
declare @curjob nvarchar(max)
declare @prevjob nvarchar(max)
select top 1 @curjob= step from jobs order by step
while @curjob is not null
    begin
        exec sp_executesql @curjob
        set @prevjob=@curjob 


select top 1 @curjob = step from jobs 
         where step > @prevjob        
         order by step
   if @@ROWCOUNT =0
                break

        end

answered Mar 13 '15 at 18:35

benjamin moskovits

5,261
1
12
22

But 'sp_executesql' will understand 'GO' and such without any preprocessing? – Fabricio Araujo Mar 13 '15 at 18:54
Go is not SQL- its a SSMS Query window command. Do you have go statements in you scripts? Create a proc from what I coded and from SQL Agent execute the proc as a job step. – benjamin moskovits Mar 13 '15 at 18:59
So, I'll have to preprocess the modification script before running. – Fabricio Araujo Mar 13 '15 at 19:03
All you have to do is create a proc (stored procedure) that SQL Agent can execute. In most senses of the word SQL Server does not preprocess, or compile, procs. – benjamin moskovits Mar 13 '15 at 19:17
Does not comply, I don't know beforehand how many scripts will run in a given database make it conformant with the current structure. – Fabricio Araujo Mar 16 '15 at 16:04

Whats the best to run sql scripts stored in a text column?

1 Answers1