Create a Job with the result of a Query ( Sql 2008 )

Question

Good morning friends.

is there a way to run a proc, with a result set of this query?

SELECT 
  'USE [' + d.name + N']' + CHAR(13) + CHAR(10) +  
  'ALTER DATABASE [' + d.name + N'] SET RECOVERY SIMPLE ' +
+ 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
+ 'ALTER DATABASE [' + d.name + N'] SET RECOVERY full'
FROM 
     sys.master_files mf 
JOIN sys.databases d 
    ON mf.database_id = d.database_id 
WHERE d.database_id > 4;

this query will list something like this:

USE [SEDI_4649] ALTER DATABASE [SEDI_4649] SET RECOVERY SIMPLE DBCC SHRINKFILE (N'SEDI_SQL_log' , 0, TRUNCATEONLY) ALTER DATABASE [SEDI_4649] SET RECOVERY full

USE [SEDI_4650] ALTER DATABASE [SEDI_4650] SET RECOVERY SIMPLE DBCC SHRINKFILE (N'SEDI_SQL_dat' , 0, TRUNCATEONLY) ALTER DATABASE [SEDI_4650] SET RECOVERY full

All i need to do is copy the result, and run in query window.

Greate, i have now all dbs Shrinked and logs.

BUT...is there a way to run this by a job??

i've been trying to create a proc that save the result in a .txt file, and the job execute this .txt but with no success.

thanks for the help.

SQL 2008

Answer 1

New job-->step-->new-->paste your query... and schedule it

change your query to use dynamic sql

and then use job

declare @sql varchar(max)
SELECT 
set @sql = 'USE [' + d.name + N']' + CHAR(13) + CHAR(10) +  
  'ALTER DATABASE [' + d.name + N'] SET RECOVERY SIMPLE ' +
+ 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
+ 'ALTER DATABASE [' + d.name + N'] SET RECOVERY full'
exec (@sql)
FROM 
     sys.master_files mf 
JOIN sys.databases d 
    ON mf.database_id = d.database_id 
WHERE d.database_id > 4;