Viewing 15 posts - 6,376 through 6,390 (of 7,613 total)
I think this rewrites the UPDATE; don't have time now to do the INSERT. The code is somewhat confusing in that sometimes ITEMID is matched on and other times...
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".
September 16, 2013 at 1:03 pm
UPDATE TEMPINVENTSUM
SET ESHOPINVENTSUM.ITEMID =@ITEMID
,ESHOPINVENTSUM.INVENTLOCATIONID= @INVENTLOCATIONID
,ESHOPINVENTSUM.AVAILPHYSICAL = @AVAILPHYSICAL
WHERE ESHOPINVENTSUM.ITEMID =@ITEMID AND ESHOPINVENTSUM.INVENTLOCATIONID= @INVENTLOCATIONID
I don't see table "ESHOPINVENTSUM" in the query. Is it supposed to be TEMPINVENTSUM? Or...
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".
September 16, 2013 at 12:22 pm
If you need a (very) large log, rather than a single very large allocation, you might be better off growing in somewhat smaller chunks. For example, if you need...
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".
September 16, 2013 at 11:52 am
An overgrown log file can have too many VLFs, which can adversely affect performance.
On some disks, the autogrowth can result in a less contiguous log file, which can also affect...
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".
September 16, 2013 at 10:54 am
Just as a possibility: Check constraints instead will reduce overhead slightly, since another table doesn't have to accessed. For a very limited number of known and easily recognized values,...
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".
September 16, 2013 at 10:49 am
You don't need a cursor at all, just an UPDATE followed by an INSERT (or MERGE, but some people have run into performance issues with MERGE).
The table names aren't consistent,...
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".
September 16, 2013 at 10:44 am
SELECT
OBJECT_NAME(object_id) AS table_name
FROM sys.columns
WHERE
name IN ( 'col1', 'col2', 'col3' ) AND
OBJECTPROPERTYEX (object_id, 'BaseType') IN ( 'U', 'V' )
GROUP...
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".
September 16, 2013 at 10:03 am
I suggest this method:
DECLARE @date datetime
--@date is used just to make it easier to check other dates:
-- naturally you can hard-code GETDATE() in place of @date 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".
September 13, 2013 at 2:06 pm
Sean Lange (9/13/2013)
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".
September 13, 2013 at 1:56 pm
4 tables of between 1M and 2M rows shouldn't take much time to run, except on a very limited server.
There must be something in the query that is causing some...
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".
September 13, 2013 at 1:54 pm
Some points to consider:
Be sure to make the search value a character string, viz:
SET @MySearchCriteria = '''3496'''
"Max_length" is bytes, which works fine for (n)(var)char, but not so much for int,...
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".
September 13, 2013 at 1:52 pm
A trigger is the best way to handle that.
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".
September 13, 2013 at 1:43 pm
In a D/R situation, it might be a royal pita to have only AD accounts to have access to the instance.
I'd rather be able, in a genuine emergency, to bring...
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".
September 12, 2013 at 10:32 am
WHERE
Docket_Date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @date1), 0) AND
Docket_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, @date1) + 1, 0)
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".
September 11, 2013 at 2:04 pm
I would never allow the sa password to be the same on more than one instance. The sa account is to be used only in an emergency anyway (such...
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".
September 11, 2013 at 2:01 pm
Viewing 15 posts - 6,376 through 6,390 (of 7,613 total)