Viewing 15 posts - 2,656 through 2,670 (of 7,620 total)
But my god MSDTC can play havoc with you when you do remote updates.
Yes, quite true. Thus, when you can, with relatively low amounts of data, don't do remote updates. ...
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 4:39 pm
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Viewing 15 posts - 2,656 through 2,670 (of 7,620 total)