Viewing 15 posts - 5,026 through 5,040 (of 7,619 total)
With this method, the web login/user can use only procs that you're explicitly given the web app authority to execute. I don't know how to lessen the risk. ...
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".
June 18, 2015 at 9:26 am
You could try this:
1) create a separate "power user" that has ddladmin authority in that db
2) create a stored proc that runs under the power user account (EXEC AS 'power_user')...
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".
June 17, 2015 at 2:31 pm
For best performance, get rid of all unnecessary variables in functions.
Edit: Changed COUNT to COUNT_BIG based on return data type.
CREATE FUNCTION dbo.spGet_Rec_Count
(
@source_tbl varchar(100)
)
RETURNS bigint
AS
BEGIN
RETURN (
...
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".
June 17, 2015 at 12:01 pm
I'd put it all in the JOIN clause, since that's what those conditions effectively are:
DELETE x
FROM TableX x
INNER JOIN TableY y ON (x.Id = y.Id) 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".
June 17, 2015 at 11:57 am
Sure. 8 works for 7 days, and 1 should work for 2 days. The weekend adjustment is the same.
SELECT *
FROM Test
WHERE DATEDIFF(DAY, DischargeDate, ApptDate) <= ( 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".
June 17, 2015 at 9:38 am
Original code, for easy reference:
SELECT *
FROM Test
WHERE DATEDIFF(DAY, DischargeDate, ApptDate) <= ( 8 +
CASE DATEDIFF(DAY, 0, DischargeDate) % 7
...
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".
June 17, 2015 at 9:23 am
I think you're adding enormous amounts of overhead to this process. How about something like below? I'm not 100% sure on the logic for EventDate being NULL, so...
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".
June 16, 2015 at 4:55 pm
I'd stick with a straightforward scalar function for that. For any column that can't have NULL values, naturally you can remove the IS NULL conditions.
CREATE FUNCTION dbo.Compare_User_Settings_To_Profile
(
...
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".
June 16, 2015 at 3:55 pm
I don't like to use WEEKDAY as it has dependencies on @@DATEFIRST. Maybe this setting-independent method instead:
SELECT *
FROM Test
WHERE DATEDIFF(DAY, DischargeDate, ApptDate) <= ( 8 +
...
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".
June 16, 2015 at 3:44 pm
No, an explicit COMMIT will lock in the transactions.
But if an explicit BEGIN TRANS was issued and either:
1) a COMMIT has not yet been issued for that trans
or
2) a ROLLBACK...
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".
June 16, 2015 at 9:10 am
Is there a possibility the transactions for table Purchase Order not have been committed, so when the second procedure is running does not select correctly?
It's certainly possible, particularly if the...
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".
June 15, 2015 at 6:26 pm
Get rid of @TransactionActive. And don't use @@TRANCOUNT either. And since you're not using SAVE TRANSACTION, the names on the TRANSACTION statement are superfluous and can be removed.
The...
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".
June 15, 2015 at 6:23 pm
The syntax is actually perfect.
You might need to use A.User_ID rather than A.EffectedUser_ID in the CASE statement, I'm not sure which one you need based on your description, but other...
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".
June 15, 2015 at 6:13 pm
That's a valid point, but, from what I've seen, it will just not be possible to force all clients to the current version. They delay for all sorts 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".
June 15, 2015 at 5:19 pm
Our company has that exact situation. Just accept that you will have different "versions" of the product in effect for different clients at different times. It's just too...
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".
June 15, 2015 at 10:04 am
Viewing 15 posts - 5,026 through 5,040 (of 7,619 total)