Viewing 15 posts - 616 through 630 (of 7,614 total)
Are you sure you matched the original data type and length? For example, if the original columns was varchar(8000) and you specified varchar(4000), obviously data might get truncated.
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 8:02 pm
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
Viewing 15 posts - 616 through 630 (of 7,614 total)