Viewing 15 posts - 2,476 through 2,490 (of 7,619 total)
Instead of using WITH (NOLOCK) on every system table, use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED for the session. That way you don't have to worry about missing one...
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 26, 2020 at 5:15 pm
I tried to come up with something other than Extended Events because of this in OP's q:
Extended events is an option, but it's doubtful that my company will approve 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".
February 26, 2020 at 5:10 pm
I'd avoid INFORMATION_SCHEMA views completely. I've found them to be slow (I understand that the view definition doesn't look like it should be slow, but real-world use says otherwise; if...
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 25, 2020 at 9:36 pm
You can use "WITH (NOLOCK)" on system views, and I suggest you do so here.
Your join on the types table is incorrect, you should use user_type_id not system_type_id.
If you're worried...
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 25, 2020 at 9:08 pm
It's not really an instance-level trigger, it's a db level trigger for the master db, because all logins are created in the master db. The DDL event would be CREATE_LOGIN...
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 25, 2020 at 9:06 pm
To the point that others have made, ORMs will frequently make the mistake of using some form of unicode datatype and, if the underlying datatype doesn't match, then no...
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 25, 2020 at 6:51 pm
You need an index on the ProximityCardNumber column in that table, or SQL will have to scan the full table every time the query runs.
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 24, 2020 at 7:39 pm
SELECT Column1, COUNT(*) * Column1 AS SumColumn1
FROM dbo.table_name
WHERE Column2 = '1'
GROUP BY Column1
ORDER BY Column1
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 24, 2020 at 7:32 pm
When I run Scott's code, I get this error msg?
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'RowCount'.
Reserved word, just use a different name. I...
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 24, 2020 at 7:08 pm
This looks like a recursive update, so it's best to go row by row (or, if you are willing, use Moden's "special" update method using a specific clustered index structure).
But,...
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 21, 2020 at 10:58 pm
Corrected the code a bit, and added any leftover amount to the first work day of the month.
;WITH base_data AS (
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".
February 21, 2020 at 10:38 pm
I only filled in holidays / weekends for Jan, but this code should give you what you requested once you substitute in your own nonworkdays table.
;WITH base_data...
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 21, 2020 at 8:42 pm
For a direct, single table update, the alias shouldn't be needed.
If the FROM clause with the same table name appears, or especially if there any JOINs, it's absolutely critical to...
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 21, 2020 at 5:39 pm
I am not sure exactly what you mean.
What specifically do you/we need to do with column RE80_DATAMOV?
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 21, 2020 at 5:24 pm
FYI, you can do it without creating a table, like so:
SELECT
SQL_VARIANT_PROPERTY ( MyColumn2, 'BaseType' ) AS MyColumn2_BaseType,
SQL_VARIANT_PROPERTY ( today,...
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 21, 2020 at 3:59 pm
Viewing 15 posts - 2,476 through 2,490 (of 7,619 total)