Viewing 15 posts - 5,236 through 5,250 (of 7,613 total)
You don't have to be a full sysadmin, necessarily, but you do need the proper authority/permissions.
The dbcreator role should handle the restore.
You'd need to be db_owner to issue...
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".
April 2, 2015 at 2:15 pm
Immediately after every restore of that db, run this command:
EXEC sp_change_users_login 'UPDATE_ONE', 'nSpekUser', 'nSpekUser'
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".
April 2, 2015 at 2:02 pm
djj (4/2/2015)
If you have a VARCHAR I would suggest converting to a date format of some sort before compare.
A bad idea. Not necessary and could potentially force...
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".
April 2, 2015 at 12:36 pm
GilaMonster (4/2/2015)
ScottPletcher (4/2/2015)
You can't name the indexes yourself unless you insure they're unique. It's a pain, but using dynamic SQL you can do it.
That's the case for constraints, 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".
April 2, 2015 at 10:37 am
dwilliscp (4/1/2015)
CREATE 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".
April 2, 2015 at 10:28 am
Use 'YYYYMMDD', since that format always works correctly in SQL Server; YYYY-MM-DD doesn't work correctly if the local settings are for dmy instead of mdy.
WHERE table_column = CONVERT(varchar(8), GETDATE(), 112)
Never...
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".
April 2, 2015 at 10:20 am
What's critical is to get the best clustering. If, to do that, you need to change the PK to match the clustering key, then you really need to do...
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".
April 2, 2015 at 8:31 am
Sioban Krzywicki (4/1/2015)
ScottPletcher (4/1/2015)
SET DEADLOCK_PRIORITY...
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".
April 1, 2015 at 4:00 pm
Matthew Darwin (4/1/2015)
ScottPletcher (4/1/2015)
All excellent points.
Keep in mind though:
1) You can leave the PK solely on the SmsID column and still cluster the table by ( date, id...
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".
April 1, 2015 at 3:35 pm
Matthew Darwin (4/1/2015)
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".
April 1, 2015 at 2:30 pm
You can try to get SQL to make the ALTER the task that SQL kills if a deadlock occurs, but I've found it's not 100% effective.
SET DEADLOCK_PRIORITY -8
ALTER INDEX 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".
April 1, 2015 at 1:30 pm
Cluster the original table on ( CreatedDate, SmsId ), in that order, not vice-versa.
Once you get the best clustering key, you're likely not to need partitioning yet. And, 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".
April 1, 2015 at 1:27 pm
I've seen it cause errors, although it's somewhat obscure, a combination of things, including linked servers, IIRC. Perhaps it's been fixed, but I don't intend to risk job failures...
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".
April 1, 2015 at 8:55 am
CELKO (3/31/2015)
use lowercase only for data type names in SQL Server
.
No. The human eye reads certain words as a single unit called a bouma. The keywords in programming...
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 31, 2015 at 4:02 pm
A JOIN is preferred over the older-style WHERE clause, although with an INNER JOIN they should function the same either way:
INSERT INTO myTable
(ID,someData,someData2)
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".
March 31, 2015 at 12:32 pm
Viewing 15 posts - 5,236 through 5,250 (of 7,613 total)