Viewing 15 posts - 7,516 through 7,530 (of 7,618 total)
You should be able to use DROP USER or sp_dropuser to get rid of users in the db, whether they are linked to a login or not.
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".
December 1, 2011 at 3:58 pm
INSERT INTO foundTable ( note_#, product_code )
SELECT n.note_#, p.product_code
FROM notesTable n
INNER JOIN productsTable p ON
n.notes LIKE '%' + p.product_code + '%'
...
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".
December 1, 2011 at 3:52 pm
Any constraints will be verified before your trigger ever gets control. Thus, you cannot handle those types of errors in a trigger.
In general, you use BEGIN TRY ... 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".
November 25, 2011 at 2:30 pm
Please post the valid formatting chars the existing function accepts and the expected output from sample formats.
#0.00, ### ##0.00
What specifically does that mean? I'm guessing the # is zero-suppressed...
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".
November 25, 2011 at 2:22 pm
You don't actually need PATINDEX for that, LIKE will work just as well:
WHERE string LIKE ' [^ ]%' OR string LIKE '%[^ ] [^ ]%'
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".
November 25, 2011 at 1:26 pm
This does not work if two or more people have the same total.
To paraphrase Clinton, it depends on the definition of "work". If two people have the same total,...
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".
November 21, 2011 at 10:33 am
Don't see how you can do this w/o an ORDER BY, but it's trivial with one:
SELECT TOP 1 Name, SUM(Consumption) AS Total_Consumption
FROM tablename
GROUP BY Name
ORDER BY SUM(Consumption) DESC
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".
November 21, 2011 at 9:40 am
Interesting code.
But can't use just do either of these:
DECLARE @result TABLE (value decimal(24, 4))
INSERT INTO @result
EXEC('SELECT 3.0 / 4 + 3.2')
SELECT * FROM @result
Or:
DECLARE @value decimal(24, 4)
EXEC sp_executesql N'SELECT @value...
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".
November 21, 2011 at 9:37 am
Try something like:
REPLACE(REPLACE(column, CHAR(13), ''), CHAR(10), '')
Of course, CHAR(13) = cr, CHAR(10) = lf
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".
December 21, 2010 at 12:12 pm
Ah, you mean exactly as I stated in the initial comment to your q? 🙂
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".
December 9, 2010 at 1:03 pm
I don't think you have to do all that. Just change the first SELECT ... INTO statement:
...
PTS_QA_CE = CAST(Null AS decimal(10, 2)),
...
Btw, don't use decimal(10, 2) unless you really...
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".
December 7, 2010 at 4:56 pm
Instead of a "CROSS JOIN" on the tally table, use an INNER JOIN, specifying the WHERE conditions relating to tally values as ON conditions instead.
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".
December 7, 2010 at 1:46 pm
I don't know... you have 4 instances of CHARINDEX vs just 2
Quite true. A valid point.
3 of them are exactly the same function. Unfortunately, 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".
December 7, 2010 at 12:44 pm
Technically, I think the first calc can be reduced ever so slightly 🙂 :
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1) AS LastDayOfLastMonth
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".
December 6, 2010 at 4:32 pm
I think you can do that more easily, like so:
SELECT SUBSTRING(col1, (CHARINDEX('Website is', col1) + 11),
CHARINDEX(' because', col1, (CHARINDEX('Website is', col1) + 11)) -
...
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".
December 6, 2010 at 3:47 pm
Viewing 15 posts - 7,516 through 7,530 (of 7,618 total)