Viewing 15 posts - 961 through 975 (of 7,619 total)
NOLOCK does basically nothing for a shared lock (which is usually what a select statement will request)
That's not factually correct. NOLOCK prevents having to take, and release, that shared...
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 9, 2022 at 7:54 pm
RETURN works only within a stored proc.
You could switch to a GOTO:
IF EXISTS(SELECT 1 FROM dbo._proc_control_table WHERE Proc_Name = 'sp_Populate_NEW_NEW')
BEGIN
RAISERROR('This proc is already running, please wait for...
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 8, 2022 at 10:33 pm
Is the code inside a stored proc (as stated in original post)?
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 8, 2022 at 10:23 pm
The RETURN statement will exit the proc. The -1 is a return code that can be checked by code calling the proc to see if an error occurred.
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 8, 2022 at 8:10 pm
Easiest way would likely be at the start of the proc, write a row to a control table. At the end of the proc, delete that row.
If the proc tries...
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 8, 2022 at 2:58 pm
Nvm, just saw the follow up comment from OP.
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 8, 2022 at 2:45 pm
You would want a nonclustered index on dbo.OrderProducts ( OrderID, ProductID ) to support this query.
--using variables to make the code easier to use and maintain
--may specify...
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 7, 2022 at 9:03 pm
SELECT ca1.*
FROM TableA a
CROSS APPLY (
SELECT A.Extension, A.Name, A.UsageCount, 'TableB' AS [Used By], B.VDN AS [Usage Data]
FROM TableB B
...
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 7, 2022 at 4:34 pm
Hmm. I'm not sure how we are supposed to debug this not seeing any of the SQL used to create the table.
Can you at least post the DDL that you...
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 7, 2022 at 4:24 pm
...
SELECT SUBSTRING(V.YourString,CI4.CI+1, CI5.CI - CI4.CI - 1)
FROM (VALUES('Data:A:B:C:20:0:0'))V(YourString)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString)))CI1(CI)
CROSS APPLY (VALUES(CHARINDEX(':',V.YourString,CI1.CI+1)))CI2(CI)
...
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 7, 2022 at 3:05 pm
SELECT
COALESCE(ABEE.Operator_Ref, ABG.Operator_Ref, GMBH.Operator_Ref) AS Operator_Ref,
CA1.[DateTime_Started]
FROM (
SELECT Operator_Ref,
...
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 4, 2022 at 3:25 pm
I still don't get your insistence on the guid being so always great as the clustering key for a table.
As a stand-alone table, maybe, IF all the rows...
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 3, 2022 at 11:49 pm
I still don't get your insistence on the guid being so always great as the clustering key for a table.
As a stand-alone table, maybe, IF all the rows inserted were...
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 3, 2022 at 9:22 pm
That's relatively easy to do:
DECLARE @table1 sysname
DECLARE @table2 sysname
SET @table1 = 'dbo.table1' --<<--!!CHANGE TABLES NAMES HERE!!
SET @table2 = 'dbo.table2' --<<--!!CHANGE TABLES NAMES HERE!!
IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
...
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 3, 2022 at 3:35 pm
If no matching PATINDEX string was found in the data, then it would default to the start of the string, which might seem to be weird results if you didn't...
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".
February 28, 2022 at 9:55 pm
Viewing 15 posts - 961 through 975 (of 7,619 total)