Forum Replies Created

Viewing 15 posts - 316 through 330 (of 7,613 total)

  • Reply To: How to find second position after decimal 

    ;WITH cte_data AS (
        SELECT CAST(50.10 AS numeric(5, 2)) AS HRS
        UNION ALL
        SELECT 50.77
    )
    SELECT HRS
    FROM cte_data
    WHERE HRS...

    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".

    November 16, 2023 at 3:03 pm

    #4328946

  • Reply To: Convert Old School to current ANSI/ISO SQL Standards

    LineCount must exist in your table, not the system table; if so, then change the alias before the column name:

    ...

    INSERT INTO dbo.PrimaryHist

    SELECT 'UPDATE'

    ,s.login_name

    ,i.batch_id

    ,i.LineCount --<<--

    FROM INSERTED AS i

    CROSS JOIN sys.dm_exec_sessions 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".

    November 16, 2023 at 3:14 am

    #4328540

  • Reply To: Decrease size of database that is over 400 gb

    Page compression is generally very effective at reducing data size.  But you will need to either: (1) shrink the data file after compressing, which will take a long time and...

    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".

    November 16, 2023 at 3:11 am

    #4328539

  • Reply To: Convert Old School to current ANSI/ISO SQL Standards

    That's the equivalent of a CROSS JOIN:

    SELECT 'UPDATE'
          ,s.login_name
          ,i.batch_id
      FROM INSERTED AS i
      CROSS...

    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".

    November 15, 2023 at 9:44 pm

    #4328445

  • Reply To: Find a word from string

    Or, just in case there might be: '64.0 MB,restricted growth to 2097152.0 MB'

    SELECT * FROM #tbl_datafile_list WHERE Autogrow LIKE '%[^A-Za-z]restricted[^A-Za-z]%'

     

    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".

    November 7, 2023 at 3:01 pm

    #4323091

  • Reply To: Trigger to track changes

    Triggers don't necessarily suck at performance, it depends on how they are written.  Neither does CDC perform all that poorly; in fact, I think it's extremely unlikely you could write...

    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".

    November 2, 2023 at 9:03 pm

    #4319979

  • Reply To: Query to find latest full backup with all database except tempDB 

    ;WITH cte AS (
        SELECT @@SERVERNAME AS ServerName,
               ISNULL(b.database_name, d.name) AS DatabaseName,
      ...

    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".

    November 2, 2023 at 5:20 pm

    #4319837

  • Reply To: Avoiding Conversion errors

    Or make the value column a sql_variant data type.

    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".

    November 2, 2023 at 5:15 pm

    #4319835

  • Reply To: Avoiding Conversion errors

    SQL may not evaluate things in the order you expect it to, so something like that could indeed be happening in certain circumstances.

    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".

    November 2, 2023 at 3:14 pm

    #4319790

  • Reply To: Query to find latest full backup with all database except tempDB 


    ;WITH cte AS (
        SELECT @@SERVERNAME AS ServerName,
               b.database_name AS DatabaseName,
       ...

    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".

    November 2, 2023 at 1:47 pm

    #4319733

  • Reply To: Table scan shows too many records

    Still, be sure you do not have some type of accidental join / cross join that will generate extra rows.

    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".

    November 2, 2023 at 1:42 am

    #4319354

  • Reply To: Find the active Group

    I'm not sure I understand correctly, nor what result you expect to see from the sample data, but maybe this(?), based on my reading of your requirements 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".

    November 1, 2023 at 7:11 pm

    #4319191

  • Reply To: Execute AS and Impersonate

    Brandie Tarvin wrote:

    ScottPletcher wrote:

    I would think you would want to use "EXEC AS" on the proc itself rather than inside the proc.

    Sadly, without giving IMPERSONATE to the users, I cannot let...

    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".

    November 1, 2023 at 6:42 pm

    #4319168

  • Reply To: Execute AS and Impersonate

    Great, glad it helped.  You might want to mark my comment as the solution, to potentially help future readers.

    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".

    November 1, 2023 at 6:34 pm

    #4319165

  • Reply To: Unexpected deadlocks in test environment but not in production environment

    Yeah, in the test environment, that's not surprising.

    We're back to having to look at the query plans.

    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".

    October 31, 2023 at 5:24 pm

    #4318412

    • Viewing 15 posts - 316 through 330 (of 7,613 total)