Viewing 15 posts - 616 through 630 (of 7,613 total)
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER test_trg1
ON dbo.test
AFTER INSERT,UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(field_key)
BEGIN
INSERT INTO dbo.audit ( name, a_date, col, old_col, new_col )
...
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, 2022 at 3:42 pm
I'd go with:
SELECT CONCAT(@Route, '(' + NULLIF(@USRoute, '') + ')',
CASE WHEN LEN(@Route) + LEN(@USRoute) = 0 THEN '' ELSE ', ' END,...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 13, 2022 at 6:06 pm
I think you would have to add a GO, otherwise if the table already exists SQL Server won't be able to "compile" the SQL:
drop table if exists...
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 12, 2022 at 9:47 pm
CAST the column formula to the data type you want it to be, e.g.:
,CAST(Concat(Convert(varchar(10),Date,23) , ' ',REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar(8),time,100),7)),7),'AM',' AM'),'PM',' PM')) AS datetime) as DateTimeSQL 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 10, 2022 at 10:24 pm
Maybe this instead:
SELECT
DENSE_RANK() OVER (ORDER BY QuestionCategoryID) CategoryNumber,
...
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 10, 2022 at 3:26 pm
SELECT whatever
FROM dbo.table_name
WHERE date_column BETWEEN CAST(YEAR(GETDATE()) - 1 AS varchar(4)) + '07' AND
CAST(YEAR(GETDATE() AS varchar(4)) + '06'
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 10, 2022 at 2:11 pm
If you need absolutely sequential numbers, no gaps, then you could use ROW_NUMBER() to generate a value to be added to the MAX() value determined before the INSERTs.
DECLARE @max_ID int
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 6, 2022 at 2:17 pm
Select custnbr
from #plist
group by custnbr
having sum(case when prlist like 'pli%' then 1 else 0 end) = 1 and
sum(case when prlist like '[ac]b%'...
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 6, 2022 at 2:08 pm
Do you mean you can't use a SEQUENCE? I can't imagine why. I use those all the time when for whatever reason an identity is not appropriate. And there's a...
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 6, 2022 at 2:04 pm
First I would think would be to try this:
GRANT SELECT ON dbo.view_name TO [domain\account];
GRANT SELECT ON dbo.view_name2 ...
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 5, 2022 at 4:03 pm
Use a table to store the date ranges, then join to that.
DROP TABLE IF EXISTS #DATES;
CREATE TABLE #DATES (
VARIABLEA date NOT 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 5, 2022 at 3:03 pm
You're also going to need to do something about the:
ar.Account_B__c as Account_ID,
a.SalesOrg__c as SalesOrg,
columns. They are not in the GROUP BY and they are not within an aggregate function, so...
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 5, 2022 at 2:28 pm
Best would be not to shrink the main db at all. If possible, add a secondary filegroup, create the temp tables in there, then shrink only the file(s) in that...
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 3, 2022 at 6:23 pm
You're good there, so that's not part of the issue.
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 3, 2022 at 5:52 pm
Are you on the latest CU(patch-level)?
Have you verified that the threshold for parallelism setting is not too low?
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 3, 2022 at 3:37 pm
Viewing 15 posts - 616 through 630 (of 7,613 total)