Viewing 15 posts - 2,806 through 2,820 (of 7,613 total)
You could do this using a stored procedure. The structure can be returned by the query itself, you don't have to pre-define it. In theory there are potential SQL injection...
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".
June 5, 2019 at 8:53 pm
I can see that, so another try with the code again. If I've done dup posts, admins please delete the extraneous post(s).
I've been extremely busy and so haven't had time...
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".
June 3, 2019 at 5:56 pm
I can't see my own last post(s), so I don't know if it(they) showed up or not.
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".
June 3, 2019 at 5:55 pm
I'd urge you not to use a temp table for that. If SQL goes down during the processing, you'll never be able to determine what the original key values were.
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".
June 3, 2019 at 5:18 pm
The function code is in the code box. To invoke it, do this:
SELECT * FROM dbo.GetMonthStartAndEndDates(GETDATE())
Or, if using with another table:
SELECT ...
FROM dbo.data_table dt
CROSS APPLY dbo.GetMonthStartAndEndDates(dt.some_date_column) gm
SET...
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 30, 2019 at 8:31 pm
DECLARE @day varchar(9)
DECLARE @end_date date
DECLARE @start_date date
SET @start_date = '20190525'
SET @end_date = '20190531'
SET @day = 'Wednesday'
SELECT DATEADD(DAY, -DATEDIFF(DAY, day_number, @end_date) % 7, @end_date) AS date_you_want
FROM (
...
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 29, 2019 at 6:22 pm
SELECT ca1.*
FROM #SomeTable st
CROSS APPLY (
SELECT Col1, Col2, Col3, Col4
WHERE Col4 <> 'ALL'
UNION ALL
...
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 23, 2019 at 9:26 pm
;with data as (
select 1 as ID,'Apple' as ProductName,'1' as serving, 'g' as unit union all
select 1 as ID,'Orange' as ProductName,'2' as serving, 'mg' as unit union...
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 23, 2019 at 5:35 pm
Yes, there is a penalty, because SQL bases its memory allocation on the column lengths. If you think about it, that actually makes perfect sense, as presumably longer columns 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".
May 20, 2019 at 7:20 pm
The biggest advantage to me of partitioning is being able to do separate compression, reorg, etc., on each partition.
But, I too did have some cases where partition elimination has made...
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 20, 2019 at 7:18 pm
If possible, put the full details in the "Description" in the backup file. That way it's always automatically/inherently available with the backup. The description can be retrieved prior to an...
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 20, 2019 at 7:15 pm
I'm dating myself here, but:
Bill or George! Anything but Sue!
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, 2019 at 5:47 pm
Oops, I left out one important line, to prevent run-away trigger recursion. In this case the UPDATE itself would prevent recursion, but it's safer to always check, in case 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".
May 17, 2019 at 5:34 pm
Typically you'd use an AFTER trigger to correct the data. If you wanted to, you could also have the trigger verify whether the value is a valid date or not. 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, 2019 at 2:26 pm
Here's a sample query that finds jobs that run a certain % of time over previous runs. Naturally you can tweak the percentages and prior run counts as you prefer...
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, 2019 at 8:11 pm
Viewing 15 posts - 2,806 through 2,820 (of 7,613 total)