October 14, 2017 at 9:05 pm
ExecuteSql"(varchar(max),"USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64),
@logfile varchar(128)
declare c1 cursor for
SELECT d.name, mf.name as logfile--, physical_name AS current_file_location, size
FROM sys.master_files mf
inner join sys.databases d
on mf.database_id = d.database_id
where recovery_model_desc <> 'SIMPLE'
and d.name not in ('master','model','msdb','tempdb','ReportServer')
and mf.type_desc = 'LOG'
open c1
fetch next from c1 into @dbname, @logfile
While @@fetch_status <> -1
begin
select @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'
exec(@isql)
fetch next from c1 into @dbname, @logfile
end
close c1
deallocate c1")"
Returning error ":
make sure string constants are enclosed in single quotes and facet properties are prefixed with '@' sign.
This query i am using to force policy to run the query and set the correct recovery model. Usin ExecuteSQL()
msdn - https://blogs.msdn.microsoft.com/sqlpbm/2008/07/03/executesql/
@JayMunnangi
October 14, 2017 at 9:14 pm
Where are you trying to run this query from? I don't believe it'll run in SSMS because the EXECUTESQL isn't a part of the T-SQL language. One of the .Net lanaguages? Maybe but not T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2017 at 10:15 am
Hello Jeff, thats rite its not a direct TSQL , ExecuteSQL () statement is from the Policy manager code, this code will be executing as a part of Policy under Policy Management.
@JayMunnangi
October 16, 2017 at 1:38 am
At a guess, maybe because you have two sets of double quotes: ExecuteSql"(varchar(max),"USE MASTER. Thus the second one is closing your first; making your entire query un-encapsulated.
I'm not familiar with ExecuteSql either, but opening a second, non-escaped, quoted string seems like that's going to cause an issue.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2017 at 8:27 am
I have never used it but according to the spec referenced you are using the wrong quotes, you need to use single quotes as described and two of them within the query
something like this
ExecuteSql('varchar(max)','USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64),
@logfile varchar(128)
declare c1 cursor for
SELECT d.name, mf.name as logfile--, physical_name AS current_file_location, size
FROM sys.master_files mf
inner join sys.databases d
on mf.database_id = d.database_id
where recovery_model_desc <> ''SIMPLE''
and d.name not in (''master'',''model'',''msdb'',''tempdb'',''ReportServer'')
and mf.type_desc = ''LOG''
open c1
fetch next from c1 into @dbname, @logfile
While @@fetch_status <> -1
begin
select @isql = ''ALTER DATABASE '' + @dbname + '' SET RECOVERY SIMPLE''
exec(@isql)
fetch next from c1 into @dbname, @logfile
end
close c1
deallocate c1')
Far away is close at hand in the images of elsewhere.
Anon.
October 16, 2017 at 8:39 am
David Burrows - Monday, October 16, 2017 8:27 AMI have never used it but according to the spec referenced you are using the wrong quotes, you need to use single quotes as described and two of them within the query
something like this
ExecuteSql('varchar(max)','USE MASTER
declare
@isql varchar(2000),
@dbname varchar(64),
@logfile varchar(128)
declare c1 cursor for
SELECT d.name, mf.name as logfile--, physical_name AS current_file_location, size
FROM sys.master_files mf
inner join sys.databases d
on mf.database_id = d.database_id
where recovery_model_desc <> ''SIMPLE''
and d.name not in (''master'',''model'',''msdb'',''tempdb'',''ReportServer'')
and mf.type_desc = ''LOG''
open c1
fetch next from c1 into @dbname, @logfile
While @@fetch_status <> -1
begin
select @isql = ''ALTER DATABASE '' + @dbname + '' SET RECOVERY SIMPLE''
exec(@isql)
fetch next from c1 into @dbname, @logfile
end
close c1
deallocate c1')
So, same thing as the normal dynamic SQL or so it would seem.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2017 at 9:49 am
So, your policy is that all databases are to use the SIMPLE recovery model? Does this meet your users RPO and RTO requirements in the event of failure and database(s) need to be restored from backups?
October 16, 2017 at 11:53 am
Lynn Pettis - Monday, October 16, 2017 9:49 AMSo, your policy is that all databases are to use the SIMPLE recovery model? Does this meet your users RPO and RTO requirements in the event of failure and database(s) need to be restored from backups?
Hmmm... I didn't even consider that because I thought no one would be crazy enough to do such a thing in production. Hopefully, this is for a test box only. I don't even set everything to SIMPLE in Dev, Staging, or UAT. I treat all 3 of those the same way I treat prod.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply