Viewing 15 posts - 1,831 through 1,845 (of 7,613 total)
Is it possible to exit the proc without falling thru to the DROP TABLE? What about the start of the proc? If the temp table already exists, does...
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".
March 3, 2021 at 2:14 am
Is it possible to exit the proc without falling thru to the DROP TABLE?
What about the start of the proc?
If the temp table already exists, does the proc scratch (drop...
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".
March 2, 2021 at 9:20 pm
The other issue with IIF is that it can be nested only 10 deep. I do have some CASE conditions that have (far) more than 10 WHEN conditions. It 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".
March 2, 2021 at 9:15 pm
Make sure the proc is dropping or truncating the temp table every time at the start of the proc.
It sounds as if rows from the prior run are being left...
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".
March 2, 2021 at 8:56 pm
I think we are, and hooray! for that. From what I've read, at least, the STRING_AGG performs well.
I have only one box on 2017 right now, many on 2016 (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".
March 2, 2021 at 8:07 pm
I admit, I don't expect IIF to go away (ever), as bad an idea as it was to corrupt T-SQL with it. I'm just hoping they don't bring over things...
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".
March 2, 2021 at 8:02 pm
I was worried about potential performance issuing in the subquery methods, at least for larger numbers of rows.
For smaller numbers of rows, the subquery may in fact perform somewhat better.
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".
March 2, 2021 at 7:58 pm
by the way, since we already have touched the subject,
is it a good idea at all to execute anything like this on a remote server as EXEC (...) AT...
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".
March 2, 2021 at 7:55 pm
I don't know of one. And I don't see the value of it. First, if the statement appears in a loop, you'd have to multiply the cost. Second, not 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".
March 2, 2021 at 7:53 pm
exec ('CREATE OR ALTER PROCEDURE ins_tmp_proc AS
BEGIN
INSERT INTO dbo.tmp (job_id, name, ..., server)
SELECT job_id, name, ..., @@SERVERNAME from msdb.dbo.sysjobs
UNION ALL
SELECT job_id, name, ..., ''<remoteserver>'' from <remoteServer>.msdb.dbo.sysjobs
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".
March 2, 2021 at 7:47 pm
UPDATE CT --<<-- this MUST be CT, NOT CallTable
SET Fname = ISNULL(Fname_Max, Fname), Lname = ISNULL(Lname_Max, Lname)
FROM CallTable CT
INNER JOIN (
SELECT CallID, MAX(Fname)...
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".
March 2, 2021 at 7:41 pm
CONCAT and CONVERT do not work on conditions, i.e. comparisons. They work on expressions, as do all SQL functions.
That's why I used IF to compare to IIF -- it's 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".
March 2, 2021 at 3:14 pm
CASE, ISNULL and COALESCE are all an integral part of SQL Server and follow its general syntax. It's because IIF is so unlike other other functions and expressions...
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".
March 1, 2021 at 9:36 pm
A clustered index would be the best chance to avoid deadlocks. So there are roughly 60K rows for each column1 value? And you want to change all of them in...
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".
March 1, 2021 at 9:22 pm
If there's no index at all, might as well make it a clus index on column1. Yes, an index should help, since it will avoid scanning the whole table for...
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".
March 1, 2021 at 6:36 pm
Viewing 15 posts - 1,831 through 1,845 (of 7,613 total)