November 15, 2007 at 2:45 am
I would like to execute the result of the following SQL in a job:
select
'alter database ' + sd.name + ' modify file (name=N''' + sm.name+''', filegrowth = 100MB)'
from sys.master_files sm
join sys.databases sd on sd.database_id=sm.database_id
where is_percent_growth=1 and sd.database_id>4
I can't get it into a job..... the quotes are driving me mad 🙁
Any help is very welcome!
November 15, 2007 at 3:15 am
The quotes are fine. However, if you're trying to execute this in a job, you probably need to use a cursor and then EXECute the returned SQL result.
K. Brian Kelley
@kbriankelley
November 15, 2007 at 4:52 am
what exactly you have put into the job?
November 15, 2007 at 5:29 am
I would like to execute the result of the following SQL in a job:
select
'alter database ' + sd.name + ' modify file (name=N''' + sm.name+''', filegrowth = 100MB)'
from sys.master_files sm
join sys.databases sd on sd.database_id=sm.database_id
where is_percent_growth=1 and sd.database_id>4
The result of the recursive SQL is what I want to execute. So for example:
alter database ALG_WS modify file (name=N'ALG_Ws', filegrowth = 100MB)
I want to use a construction like this:
begin
declare @STR varchar(500)
select @STR='select 'alter database ''' + sd.name
from sys.master_files sm
join sys.databases sd on sd.database_id=sm.database_id
where is_percent_growth=1 and sd.database_id>4'
print @STR
exec (@str)
end
@ Brian Kelley : Can you please support me with an example or a link to an example?
November 15, 2007 at 8:24 am
You're potentially getting back multiple rows. Therefore, to execute each row, you need a cursor to loop through them. Here's a good starting point, which also includes an example of how to loop through:
Books Online: Declare Cursor (T-SQL)
K. Brian Kelley
@kbriankelley
November 15, 2007 at 10:06 am
Try this
DECLARE @CMD NVARCHAR(500)
DECLARE STREXEC CURSOR FOR
select
'alter database [' + sd.name + '] modify file (name=N''' + sm.name+''', filegrowth = 100MB)'
from sys.master_files sm
join sys.databases sd on sd.database_id=sm.database_id
where is_percent_growth=1 and sd.database_id>4
OPEN STREXEC
FETCH NEXT FROM STREXEC
INTO @CMD
WHILE @@FETCH_STATUS=0
BEGIN
exec sp_executesql @cmd
FETCH NEXT FROM STREXEC INTO @CMD
END
CLOSE STREXEC
DEALLOCATE STREXEC
November 15, 2007 at 10:06 am
Sorry missed the last couple of posts.
November 15, 2007 at 10:14 am
Wow! Exactly what I had in mind!
It's very nice looking. I will adopt this construction, for sure!
EvilPostIT you helped me great with this! Thanks a lot!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply