Forum Replies Created

Viewing 15 posts - 1,576 through 1,590 (of 7,619 total)

  • Reply To: LATCH_EX when creating a clustered index

    Hmm, interesting.  For the nonclustered index(es)?  For a clus index create, I could see delays as SQL would have to sort the entire contents of the table.

    What is the 'cost...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 17, 2021 at 3:00 pm

    #3884444

  • Reply To: LATCH_EX when creating a clustered index

    1.       The source is another table in another database and I am using a dataflow task to move it from one table to another. The source is a Stored procedure

    There...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 17, 2021 at 2:51 pm

    #3884440

  • Reply To: prevent certain characters being inserted into table

    Best would be a CHECK constraint if that is possible.  You wouldn't have to write the code and SQL would guarantee that it gets run and works without error (given,...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 15, 2021 at 3:32 am

    #3883679

  • Reply To: Limit database restores to specific drive locations

    I guess you have to fall back on querying the msdb.dbo.restorehistory table every nn minutes and looking up the details on all newly restored dbs, since the last check nn...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 14, 2021 at 6:46 pm

    #3883561

  • Reply To: Limit database restores to specific drive locations

    Rats!  Apparently RESTORE does not trigger a CREATE DATABASE event and there is no RESTORE DATABASE event, at least from what I've found so far.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 14, 2021 at 6:38 pm

    #3883560

  • Reply To: Limit database restores to specific drive locations

    If a RESTORE DATABASE triggers a "CREATE DATABASE" event, and I'm not sure about that although it seems logical, then you could use a DDL trigger to check for the...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 14, 2021 at 2:58 pm

    #3883475

  • Reply To: VLOOKUP Equivalent in SQL 

    SELECT t1.ID, t1.Amount, t2.Amount_VLOOKUP
    FROM table1 t1
    CROSS APPLY (
        SELECT CASE
            WHEN t1.Amount < t2.col1 THEN 0
     ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 14, 2021 at 2:47 pm

    #3883471

  • Reply To: Removing prefix and suffix from a column 

    UPDATE yt
    SET name = LTRIM(RTRIM(name3))
    FROM your_table yt
    CROSS APPLY (
        SELECT CASE WHEN yt.name LIKE 'ZZZZ%' THEN STUFF(yt.name, 1, 4, '') ELSE yt.name END AS...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 14, 2021 at 2:45 pm

    #3883470

  • Reply To: high CPU

    Jeff Moden wrote:

    For my databases, I've adopted the "If it moves, shoot it" philosophy for statistics updpates.  In other words, if the RowModCntr is not zero and the stats haven't been...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 12, 2021 at 8:49 pm

    #3882752

  • Reply To: roles needed to execute Stored procedures/function in SQL server

     

    leo_dec wrote:

    Hi

    Thanks for your reply.

    Does that mean that I need to run below grant execute SP SQL Script for each SP for user?

    GRANT create procedure ON DATABASE::[db name] TO...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 11, 2021 at 2:53 pm

    #3881933

  • Reply To: SELECT column based on parameter

    tshad wrote:

    Is there a way in the same SELECT statement to show different columns without using dynamic SQL?

    No.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 11, 2021 at 2:48 pm

    #3881932

  • Reply To: SQL 2016 - Need help with complex calculations based on paycodes !

    Some of the amounts in the original didn't seem to add up, and the rules weren't specific for FTYPE and FCODE, but here's my best guess so far:

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers".  Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 10, 2021 at 7:14 pm

    #3881617

  • Reply To: Minimum permission for user to grant execute on a stored procedure they created

    I think a separate schema(s) is a valid approach.  Indeed, it has some advantages.  For one, the developer-created objects are in the db to which they apply.  If you create...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 10, 2021 at 4:00 pm

    #3881530

  • Reply To: SQL 2016 - Need help with complex calculations based on paycodes !

    Very busy right now, as soon as I get time, I'll follow up on this ... unless, of course, someone else has already jumped in and solved it.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 10, 2021 at 3:52 pm

    #3881527

  • Reply To: LATCH_EX when creating a clustered index

    Create the clustered index only before loading the table.  Create the non-clustered indexes after loading the table.

    If this is a permanent table, seriously consider using data compression.  Although it takes...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    May 10, 2021 at 3:49 pm

    #3881526

    • Viewing 15 posts - 1,576 through 1,590 (of 7,619 total)