Forum Replies Created

Viewing 15 posts - 2,656 through 2,670 (of 7,619 total)

  • Reply To: Best Practice to Query SQL Server from Another SQL Server

    Replication has far more headaches than a simple linked server.  I'm not against replication when it's really needed, of course, but I don't see that need here.  I, too, would...

    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 29, 2019 at 3:07 pm

    #3692777

  • Reply To: Transaction Isolation Levels

    Ok, for a fellow DBA.  I actually "stole" this from a Paul White article.  To be honest, I wasn't aware that the cursor setting prevented allocation scan reads until I...

    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 28, 2019 at 6:22 pm

    #3692501

  • Reply To: Transaction Isolation Levels

    You can get phantom reads and repeat reads using the default iso level of READ COMMITTED.  The only thing NOLOCK adds is dirty reads.  And you can greatly reduce 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".

    October 28, 2019 at 5:58 pm

    #3692496

  • Reply To: set datefirst question

    I do use SET DATEFORMAT as needed.  I agree, it's much easier to do that than to try to rewrite a script.  Then reset it asap to its original value...

    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 28, 2019 at 5:44 pm

    #3692491

  • Reply To: set datefirst question

    I'd strongly advise against messing with DATEFIRST setting.  The code below works under any/all DATEFIRST settings.

    declare @date_to_calc_week_of date
    set @date_to_calc_week_of = '20191028'

    ;with cte_date_calcs 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".

    October 25, 2019 at 3:01 pm

    #3691975

  • Reply To: Get Insert & Update counts from Merge $Action with outer query insert - SQL serv

    You don't need multiple counts.

    DROP TABLE IF EXISTS #actions;
    CREATE TABLE #actions ( action nvarchar(10) NULL )

    MERGE
    ...
    OUTPUT $ACTION into #actions
    ...

    DECLARE @insert_count int
    DECLARE @update_count int

    SELECT @insert_count = SUM(CASE WHEN...

    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 24, 2019 at 10:05 pm

    #3691824

  • Reply To: Clustered index - lots of locks

    Keys locks are normal with a clustered index (in fact, they must be key locks, since you can't get rid locks on a ci).  But I wouldn't expect that many...

    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 24, 2019 at 7:45 pm

    #3691755

  • Reply To: Case when then

    I think something like this is what you need:

    select @v1 = max(case when type = 1 then amt end),
    @v2 = max(case when type = 2 then amt...

    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 24, 2019 at 3:51 pm

    #3691701

  • Reply To: Clustered index - lots of locks

    No, doesn't sound normal.  How did you determine the locking that was occurring?  Does the table have a lot of partitions?

    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 24, 2019 at 3:47 pm

    #3691700

  • Reply To: How to know the sql server environment run time

    No, that is purely the physical name.

    On a cluster, SERVERPROPERTY('MachineName') shows the instance name.

    For a non-clustered named instance, if that doesn't show the instance name, then use:

    SERVERPROPERTY('InstanceName')

    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 22, 2019 at 5:59 pm

    #3691078

  • Reply To: How to know the sql server environment run time

    Isn't the physical machine name different?

    SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

    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 22, 2019 at 4:35 pm

    #3691058

  • Reply To: Inserting Data

    Is the report not able to do a LEFT JOIN instead of an INNER JOIN?  That would be the standard way to do that, without having to create a dummy...

    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 22, 2019 at 2:58 pm

    #3691016

  • Reply To: Selecting data from two columns

    You might want to consider assigning an alias to the modified value, so that if it ever changes later the definition of it is only in 1 place:

    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 21, 2019 at 8:01 pm

    #3690734

  • Reply To: SQL Query with CTE or other...!

    Maybe this will give you what you want:

    --** Data set up ***************************************************************
    CREATE TABLE dbo.questions (
        question_id int PRIMARY KEY,
        question varchar(1000)...

    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 21, 2019 at 5:19 pm

    #3690687

  • Reply To: Is SSIS really that bad?

    SSIS is a good (enough) tool, for what it does.  It is actually very good at moving data around, to/from SQL Server and other platforms as well.

    SSIS also has another...

    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 21, 2019 at 4:50 pm

    #3690674

    • Viewing 15 posts - 2,656 through 2,670 (of 7,619 total)