Post reply

could some one help me with the query below

  • October 14, 2017 at 9:05 pm

    #329165

    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

    #1963716

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 15, 2017 at 10:15 am

    #1963730

    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

    #1963754

    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

    #1963842

    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

    #1963848

    David Burrows - Monday, October 16, 2017 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')

    So, same thing as the normal dynamic SQL or so it would seem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 16, 2017 at 9:49 am

    #1963867

    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

    #1963909

    Lynn Pettis - Monday, October 16, 2017 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?

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply