Context Is Everything - Check Your Settings

  • Bill Talada

    SSChampion

    Points: 11956

    Comments posted to this topic are about the item Context Is Everything - Check Your Settings

  • Eirikur Eiriksson

    SSC Guru

    Points: 182356

    Thank you for this William, very useful!

    😎

  • CodedSteve

    SSC Veteran

    Points: 234

    Hi,

    I can't seem to find the

    "

    There is a script attached to this article"

    Regards

  • Bill Talada

    SSChampion

    Points: 11956

    The attachment is at the very bottom of the article. It is called Settings.sql

  • chudman

    SSCrazy

    Points: 2420

    Nice article, but you just made every support call I handle twice as long. Every user that sees this will be adding SQL DBA to their resume and business card. I can see it now:

    SCENARIO: I am called to discuss sudden performance problem on an ordinarily behaving server.

    USER: I did some troubleshooting of my own and I am concerned with this thing called 'COLLATION'. I read online that 'EBCDIC' is faster.... When can we make that change?'

    SQL DBA: <groan>

  • facemann

    Right there with Babe

    Points: 723

    I can only run this script from master else I get the following error.

    Msg 4104, Level 16, State 1, Line 2

    The multi-part identifier "objects.schema_id" could not be bound.

    How do I collect Database Properties from a specific database?

    I am guessing I need to edit

    insert into @S select 'DatabaseProperty', 'IsAnsiNullDefault', cast(DATABASEPROPERTYEX(db_name(), 'IsAnsiNullDefault') as varchar(300)), '1', 1, 0;

    to this

    insert into @S select 'DatabasePropertyTargetDB', 'IsAnsiNullDefault', cast(DATABASEPROPERTYEX('TargetDB', 'IsAnsiNullDefault') as varchar(300)), '1', 1, 0;

    and repeat this for every database I wish to monitor? That's fine as I don't have many databases to watch. Still a very useful script.

    Thanks!

  • Bill Talada

    SSChampion

    Points: 11956

    facemann (5/6/2014)


    I can only run this script from master else I get the following error.

    Msg 4104, Level 16, State 1, Line 2

    The multi-part identifier "objects.schema_id" could not be bound.

    I wrote the script for sqlserver 2008r2 but you may replace the DROP PROCEDURE statement at the top or adapt it for other versions.

    How do I collect Database Properties from a specific database?

    I am guessing I need to edit

    insert into @S select 'DatabaseProperty', 'IsAnsiNullDefault', cast(DATABASEPROPERTYEX(db_name(), 'IsAnsiNullDefault') as varchar(300)), '1', 1, 0;

    to this

    insert into @S select 'DatabasePropertyTargetDB', 'IsAnsiNullDefault', cast(DATABASEPROPERTYEX('TargetDB', 'IsAnsiNullDefault') as varchar(300)), '1', 1, 0;

    and repeat this for every database I wish to monitor? That's fine as I don't have many databases to watch. Still a very useful script.

    Thanks!

    My objective was to create a custom version for each database project I support since each database may have different requirements. The db_name() function will get the current setting for the database in which the procedure is created so no edit of that is necessary. I think there is an MsForEach proc that will iterate through all databases but I'm no expert on using that.

  • facemann

    Right there with Babe

    Points: 723

    Got it. Thanks Bill. 🙂

  • Wayne West

    SSC-Insane

    Points: 22586

    Thanks, Bill, definitely food for thought.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 9 posts - 1 through 9 (of 9 total)

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