Viewing 15 posts - 6,361 through 6,375 (of 7,619 total)
The CROSS APPLY and SUBSTRING below should do what you need. Let us know if you need some clarification for getting it into an UPDATE statement.
DECLARE @leading_trailing_chars_to_eliminate varchar(10)
SET @leading_trailing_chars_to_eliminate...
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 15, 2013 at 4:14 pm
You'll need to format the hours separately, as below, since style "114" will only handle up to 23 hours:
SELECT
CAST(DATEDIFF(ms, ActualStartTime, EndTime) / 3600000 AS varchar(3)) +
...
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 15, 2013 at 3:38 pm
I think the code below is a reasonable interpretation, but whether it's correct or not is far from certain:
ALTER TABLE [Lkup].[ReceiptReasonCodes]
ADD [IsValidSap] [bit] NULL
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 15, 2013 at 3:28 pm
LutzM (10/14/2013)
ScottPletcher (10/14/2013)
...You would have to use a trigger to prevent sa activity....Every sa could disable the trigger and still perform updates.
I should have mentioned that. I was thinking...
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 15, 2013 at 8:02 am
You can't directly restrict sa (sysadmin) accounts from any activity in SQL Server. You would have to use a trigger to prevent sa activity.
For only non-sa, you could try:
DENY...
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 14, 2013 at 2:59 pm
Use LEFT OUTER JOIN, but don't cast datetimes in any data table to dates to do comparisons; instead, use a range check.
Here's a sample join for the tables you've described:
SELECT...
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 2, 2013 at 10:12 am
Be aware that a global temp table means the code must be single-threaded, as simultaneous executions of the code would stomp on each other.
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".
September 30, 2013 at 1:45 pm
Here's my version to do only a single pass of the table.
But I'm not sure why you want to list only "D" in the result, since both "B" and "D"...
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".
September 30, 2013 at 1:39 pm
Seems to me like it must be two different RAID5 sets ... wouldn't different drives on the same RAID set already be using both drives?!
Either way, I'd delay your #1...
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".
September 27, 2013 at 3:22 pm
Neither CAST nor CASE is actually necessary here:
SIGN(CHARINDEX('@', <string_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".
September 27, 2013 at 9:55 am
Btw, if weekdayid is unique (and it seems like it has to be for this table to make sense), get rid of the dopey IDENTITY column in this table! ...
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".
September 27, 2013 at 9:50 am
... will select count(*) get the keep changed record numbers even if (nolock) hint is on?
Actually, it will count uncommitted records if and only if (nolock) or the equivalent is...
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".
September 27, 2013 at 9:47 am
I prefer CROSS APPLY(s) to CTE(s) for aliasing. For example:
Select TOP 5 ID ,MID, RName,Pic1,FoodType.Descr AS FoodType,Average_P_PP,lat,lng,
ca1.Distance
From Member WITH(NOLOCK)
INNER JOIN FoodType WITH(NOLOCK) ON...
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".
September 27, 2013 at 9:43 am
Why mess with the DATEFIRST setting when it's not necessary? There are methods which can do the calculation simply without needing a specific datefirst setting, so why hassle with...
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".
September 27, 2013 at 9:38 am
As another kludge, you could probably do this:
COALESCE(
CASE WHEN @MonthUnits = 1 THEN GBAN01 ELSE NULL END,
CASE WHEN @MonthUnits = 2 THEN GBAN02 ELSE...
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".
September 19, 2013 at 3:42 pm
Viewing 15 posts - 6,361 through 6,375 (of 7,619 total)