Viewing 15 posts - 1,846 through 1,860 (of 7,619 total)
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
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 in SQL...
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:30 pm
Does the table have an index with column1 in it, or preferably where column1 is the first column in an index? If no, SQL would have to scan the table...
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:17 pm
If the main table is really that narrow (few bytes), then compress the non-clus index to reduce the pages more:
CREATE UNIQUE NONCLUSTERED INDEX IX1_IattribINT ON dbo.AttribINT (...
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 4:07 pm
I'd suggest first try creating a non-clustered index on ( OID, AID ). Cluster the temp table on ( OID /*and AID if available*/ ).
CREATE UNIQUE NONCLUSTERED...
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 3:07 pm
Need to see the DDL for AttribINT, including all index definitions.
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 2:36 pm
I dislike IIF ... its "familiar" for people that use similar functions in Excel, but I think CASE is better in SQL
IIF only has two outcomes, true/false, and 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 28, 2021 at 3:38 am
Yeah, that code should do it.
But if you're using the column name "ClientNotes" directly in a SELECT you will get the original column value, not the one after the REPLACEs. ...
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 28, 2021 at 3:19 am
Most typically:
select name,count(*) as name_count
from dbo.table_name
where name like '%ra[jm]%' and P = 1
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 27, 2021 at 5:47 pm
IIF([var1Title] != '', [var1Title] + ISNULL(' - ' + [var1Topic], '') + ISNULL(' - ' + [var1Name], ''), '') AS Item1
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, 2021 at 4:28 pm
Viewing 15 posts - 1,846 through 1,860 (of 7,619 total)