Viewing 15 posts - 1,006 through 1,020 (of 7,620 total)
You need to step back and do proper data modeling on all your entities. That is the most important step in proper design.
Then, as you convert the entities to physical...
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".
February 7, 2022 at 4:41 pm
SQL Server does not all "FROM @FILENAME", only "FROM 'data_file_name'". Google the syntax of the command.
You'll need to use dynamic SQL if you want to feed in a FROM file...
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".
February 2, 2022 at 11:29 pm
I'm guessing you don't already have a row_num.
;WITH cte_add_row_num AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE DESC) AS row_num
...
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".
February 1, 2022 at 5:39 pm
Would need to see the tables' DDL and the actual query plan. Can't troubleshoot air.
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".
February 1, 2022 at 1:02 am
Yeah, really should have mentioned that Pervasive thing to begin with :-). That means there are multiple rows being returned for each lookup. Which one you get will be random,...
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".
January 31, 2022 at 9:06 pm
Another possibility is a bit shorter, uses only LIKE instead of LIKE and the LEN() function, and does the same thing is...
IIF(D.Zip LIKE '[0-9][0-9][0-9][0-9][0-9]', d.Zip,...
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".
January 31, 2022 at 6:37 pm
--...
ALTER DATABASE [DBNameToRestoreHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [DBNameToRestoreHere]...
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".
January 31, 2022 at 4:41 pm
I say don't use IIF. It's a completely foreign coding approach to other SQL. And there's no genuine need for it, since CASE can do the same thing, and IIF...
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".
January 31, 2022 at 4:15 pm
If you want to rely on the existing zip always being fully padded on the left with zeros, you can do this:
CASE WHEN LEN(D.Zip) = 5 AND...
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".
January 28, 2022 at 10:04 pm
You could probably use TRIGGER_NESTLEVEL to do that.
I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>'). If you want more info on this...
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".
January 28, 2022 at 8:21 pm
Don't use functions in a WHERE clause, that's a very bad habit. Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some...
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".
January 27, 2022 at 9:00 pm
'19 Nov 2022' is converts to the datetime value of '19 Nov 2022 00:00:00.000', which is earlier (less than) '19 Nov 2022 15:19:12.000'.
Try:
WHERE [...]
AND CONVERT(date, TransferredDate)...
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".
January 27, 2022 at 7:13 pm
And the view did not exist before the proc was called and still did not exist after it was called?
If so, put the SQL in a variable and print it...
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".
January 27, 2022 at 6:44 pm
The best way is to use > the next day rather than <= the end day. Like this:
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '18 Nov 2021'
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".
January 27, 2022 at 6:40 pm
I would think it would be. Why not try it with a small table and see?
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".
January 27, 2022 at 6:38 pm
Viewing 15 posts - 1,006 through 1,020 (of 7,620 total)