Viewing 15 posts - 1,051 through 1,065 (of 7,619 total)
You could probably use TRIGGER_NESTLEVEL to do that.
I tend to use session context values instead, set via sys.sp_set_session_context and SESSION_CONTEXT(N'<key_name>'). If you want more info on this method, let me...
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".
January 20, 2022 at 5:32 pm
Do you have an index on table_a that covers the UPDATE query? That could help prevent some contention.
You could also try to "help" SQL by explicitly "telling" SQL about 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".
January 20, 2022 at 5:15 pm
I agree with Steve.
Also, wouldn't you want the MIN() time for the LogOn?
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".
January 20, 2022 at 5:09 pm
The customer would have an identifying customer number and account number, and the ATM would have an identifying ATM number.
Therefore, the transaction would broadly only need to capture cust#, acct#,...
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".
January 20, 2022 at 5:07 pm
That join is not needed.
are the data changes made by a trigger included in the transaction, so that they are rolled back if the INSERT or UPDATE on 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".
January 20, 2022 at 5:01 pm
The first big difference is that the second command is not valid, and thus won't run and therefore won't do anything.
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".
January 17, 2022 at 4:29 pm
Instead of using a CASE expression (as you were trying) - or stacked OR's: WHERE @mDepth > iif(@tDepth = 0, -1, @tDepth)
Consider: @mDepth >= 0 is equivalent to @mDepth...
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".
January 14, 2022 at 8:12 pm
The values go into the variables at the top.
You'll note that I declared the variables first, then SET them all together. I personally think it's much more confusing to mix...
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".
January 14, 2022 at 8:10 pm
Verify that variable @CopyFromUser includes a valid value.
Add a PRINT statement in the script. Existing code:
...
From dbo.Operator
Where Operator_Ref = @CopyFromUser
SET @rowcount = @@ROWCOUNT
...
so that you can verify how many INSERTs...
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".
January 14, 2022 at 6:07 pm
CASE is an expression not a statement. That means that the result of WHEN or THEN must be a single value. No operators (>,>=, etc.) and no keywords can be...
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".
January 14, 2022 at 5:03 pm
I executed the script on an instance with 100s of dbs and it worked fine. Of course I got the "No insertions made..." message since I didn't have dbs named...
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".
January 14, 2022 at 4:45 pm
I presume you want to know why, when running under the DP300User1 context, the SP works but the query on it's own does not.
When a SP is created 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".
January 13, 2022 at 9:35 pm
DECLARE @CopyUser varchar(30)
DECLARE @Dbnames varchar(8000)
DECLARE @Sql varchar(8000)
DECLARE @UserOp varchar(30)
SET @Dbnames = 'DB1,DB2,DB3' --<<--!!set these values before running!!--<<--
SET @CopyUser = 'User1' --<<--!!set these values before running!!--<<--
SET @UserOp =...
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".
January 13, 2022 at 9:22 pm
You should never shrink a database, only shrink a file(s).
It seems as if you have one gigantic data file (very bad idea, btw, you should use multiple data files, but...
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".
January 13, 2022 at 5:34 pm
You must use the max memory setting when you put more than one instance of SQL on a given box, for the reasons others have stated above.
And, for that many...
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".
January 12, 2022 at 4:22 pm
Viewing 15 posts - 1,051 through 1,065 (of 7,619 total)