Viewing 15 posts - 4,966 through 4,980 (of 7,613 total)
Luis is correct.
But also quit using sysobjects and syscolumn views, as they are very obsolete and slow (and possibly even buggy).
Try this code instead:
SELECT @COLUMNS = @COLUMNS + c.name +...
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".
July 16, 2015 at 10:05 am
This will be more complex than just what you've shown, if this is trying to strip prefixes/suffixes off a name, as it appears to be.
For example, the letters "JR" 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".
July 16, 2015 at 9:55 am
Yeah, I can't think of a really clean way to do it now either, other than brute force:
SELECT table_name.*, derived2.row_num
FROM table_name
INNER JOIN (
SELECT colb,ROW_NUMBER() OVER(ORDER BY...
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".
July 15, 2015 at 1:29 pm
If you typically query by datetime, cluster the table by datetime first. No need to use kludges just to keep load_id as the lead key column.
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".
July 15, 2015 at 10:57 am
A User table, a Group table and a GroupUser -- or UserGroup -- table.
User ( User_Id, ...other_user_columns... )
Group ( Group_Id, ...other_group_columns... )
GroupUser ( Group_Id, User_Id, ...other_columns_related_only_to_the_COMBINATION_of_Group_and_User only]...
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".
July 15, 2015 at 10:55 am
Typically the ROW_NUMBER() method will outperform other methods. It also guarantees only a single result row, which means that duplicate values will be ignored (I believe you can use...
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".
July 14, 2015 at 3:24 pm
Or maybe this?:
SELECT
t.ProductID
,t.Order_Day
,t.Order_Quantity
FROM (
SELECT
ProductID
,Order_Day
,Order_Quantity
,ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY...
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".
July 14, 2015 at 3:15 pm
Yes, but you could have a start-up proc that recreates the table. Or just test for its existence and create it if it's not there.
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".
July 14, 2015 at 2:36 pm
For a particular table, you can add a trigger to the table to capture the relevant details from any UPDATE statements.
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".
July 14, 2015 at 2:35 pm
One of the tasks will acquire the lock first, and then the other one will have to wait. SQL's locking mechanism is what it allows it to "sequence" these...
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".
July 14, 2015 at 2:34 pm
Worst case, create the table in tempdb. Btw, yes, you can create a permanent (non-temporary) table in tempdb.
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".
July 14, 2015 at 2:28 pm
You need a separate table, clustered on the matching value, if you want best performance, or even good performance in the main table that is being compared has lots of...
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".
July 14, 2015 at 1:03 pm
Lol, kind of as an exercise I did do a recursive function for this task. As you said, I'm not sure about how it will perform, and I have...
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".
July 14, 2015 at 11:03 am
Try sp_helptext, as in:
EXEC master.sys.sp_helptext 'sys.server_principals'
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".
July 13, 2015 at 4:11 pm
The best-practice recommendation is to grant only the specific permissions needed, in this case, I think that would be:
GRANT SELECT ON SCHEMA::dbo TO [<user_name>];
GRANT CONTROL ON SCHEMA::rpt TO [<user_name>];
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".
July 13, 2015 at 12:03 pm
Viewing 15 posts - 4,966 through 4,980 (of 7,613 total)