Hi,
Does anyone got a really good/fast example on how to do a BOM with unlimited
levels in SQL2000
- without using cursors :)
2 tables (one for items, one for parent/child relations)
In SQL2005 we of course are blessed with the new CTE, not in SQL2000 :-/
In advance, thanks...
Kr. Sorenhow do you want your data output? just a SP that returns a list of all
materials a particular item requires, right down the tree?
if so then the only way I can think of without cursors is using global
temporary tables and recursively called stored procedures to populate
it (but then you might as well use a cursor for that).
if you're wanting columns of data to represent the tree structure then
it's Dynamic SQL you'll need|||Soren, here goes:
-- DDL & Sample Data for Parts, BOM
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.BOM') IS NOT NULL
DROP TABLE dbo.BOM;
GO
IF OBJECT_ID('dbo.Parts') IS NOT NULL
DROP TABLE dbo.Parts;
GO
CREATE TABLE dbo.Parts
(
partid INT NOT NULL PRIMARY KEY,
partname VARCHAR(25) NOT NULL
);
INSERT INTO dbo.Parts(partid, partname) VALUES( 1, 'Black Tea');
INSERT INTO dbo.Parts(partid, partname) VALUES( 2, 'White Tea');
INSERT INTO dbo.Parts(partid, partname) VALUES( 3, 'Latte');
INSERT INTO dbo.Parts(partid, partname) VALUES( 4, 'Espresso');
INSERT INTO dbo.Parts(partid, partname) VALUES( 5, 'Double Espresso');
INSERT INTO dbo.Parts(partid, partname) VALUES( 6, 'Cup Cover');
INSERT INTO dbo.Parts(partid, partname) VALUES( 7, 'Regular Cup');
INSERT INTO dbo.Parts(partid, partname) VALUES( 8, 'Stirrer');
INSERT INTO dbo.Parts(partid, partname) VALUES( 9, 'Espresso Cup');
INSERT INTO dbo.Parts(partid, partname) VALUES(10, 'Tea Shot');
INSERT INTO dbo.Parts(partid, partname) VALUES(11, 'Milk');
INSERT INTO dbo.Parts(partid, partname) VALUES(12, 'Coffee Shot');
INSERT INTO dbo.Parts(partid, partname) VALUES(13, 'Tea Leaves');
INSERT INTO dbo.Parts(partid, partname) VALUES(14, 'Water');
INSERT INTO dbo.Parts(partid, partname) VALUES(15, 'Sugar Bag');
INSERT INTO dbo.Parts(partid, partname) VALUES(16, 'Ground Coffee');
INSERT INTO dbo.Parts(partid, partname) VALUES(17, 'Coffee Beans');
CREATE TABLE dbo.BOM
(
partid INT NOT NULL REFERENCES dbo.Parts,
assemblyid INT NULL REFERENCES dbo.Parts,
unit VARCHAR(3) NOT NULL,
qty DECIMAL(8, 2) NOT NULL,
UNIQUE(partid, assemblyid),
CHECK (partid <> assemblyid)
);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 1, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 2, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 3, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 4, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 5, NULL, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(10, 1, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 1, 'mL', 230.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(10, 2, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 2, 'mL', 205.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(11, 2, 'mL', 25.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 6, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 7, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(11, 3, 'mL', 225.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 3, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 9, 4, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 4, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES( 9, 5, 'EA', 1.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(12, 5, 'EA', 2.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(13, 10, 'g' , 5.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 10, 'mL', 20.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(14, 12, 'mL', 20.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(16, 12, 'g' , 15.00);
INSERT INTO dbo.BOM(partid, assemblyid, unit, qty)
VALUES(17, 16, 'g' , 15.00);
GO
-- Creation Script for Function fn_partsexplosion
---
-- Function: fn_partsexplosion, Parts Explosion
--
-- Input : @.root INT: Root part id
--
-- Output : @.PartsExplosion Table:
-- id and level of contained parts of input part
-- in all levels
--
-- Process : * Insert into @.PartsExplosion row of input root part
-- * In a loop, while previous insert loaded more than 0 rows
-- insert into @.PartsExplosion next level of parts
---
USE tempdb;
GO
IF OBJECT_ID('dbo.fn_partsexplosion') IS NOT NULL
DROP FUNCTION dbo.fn_partsexplosion;
GO
CREATE FUNCTION dbo.fn_partsexplosion(@.root AS INT)
RETURNS @.PartsExplosion Table
(
partid INT NOT NULL,
qty DECIMAL(8, 2) NOT NULL,
unit VARCHAR(3) NOT NULL,
lvl INT NOT NULL,
n INT NOT NULL IDENTITY, -- surrogate key
UNIQUE CLUSTERED(lvl, n) -- Index will be used to filter lvl
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0; -- Initialize level counter with 0
-- Insert root node to @.PartsExplosion
INSERT INTO @.PartsExplosion(partid, qty, unit, lvl)
SELECT partid, qty, unit, @.lvl
FROM dbo.BOM
WHERE partid = @.root;
WHILE @.@.rowcount > 0 -- while previous level had rows
BEGIN
SET @.lvl = @.lvl + 1; -- Increment level counter
-- Insert next level of subordinates to @.PartsExplosion
INSERT INTO @.PartsExplosion(partid, qty, unit, lvl)
SELECT C.partid, P.qty * C.qty, C.unit, @.lvl
FROM @.PartsExplosion AS P -- P = Parent
JOIN dbo.BOM AS C -- C = Child
ON P.lvl = @.lvl - 1 -- Filter parents from previous level
AND C.assemblyid = P.partid;
END
RETURN;
END
GO
-- Parts Explosion
SELECT P.partid, P.partname, PE.qty, PE.unit, PE.lvl
FROM dbo.fn_partsexplosion(2) AS PE
JOIN dbo.Parts AS P
ON P.partid = PE.partid;
Output:
partid partname qtyn unit lvl
-- -- -- -- --
2 White Tea 1.00n EA 0
6 Cup Cover 1.00n EA 1
7 Regular Cup 1.00n EA 1
10 Tea Shot 1.00n EA 1
14 Water 205.00n mL 1
11 Milk 25.00n mL 1
13 Tea Leaves 5.00n g 2
14 Water 20.00n mL 2
-- Parts Explosion, Aggregating Parts
SELECT P.partid, P.partname, PES.qty, PES.unit
FROM (SELECT partid, unit, SUM(qty) AS qty
FROM dbo.fn_partsexplosion(2) AS PE
GROUP BY partid, unit) AS PES
JOIN dbo.Parts AS P
ON P.partid = PES.partid;
Output:
partid partname qty unit
-- -- -- --
2 White Tea 1.00 EA
6 Cup Cover 1.00 EA
7 Regular Cup 1.00 EA
10 Tea Shot 1.00 EA
13 Tea Leaves 5.00 g
11 Milk 25.00 mL
14 Water 225.00 mL
In SQL Server 2005 it would look like this:
-- CTE Solution for Parts Explosion
DECLARE @.root AS INT;
SET @.root = 2;
WITH PartsExplosionCTE
AS
(
-- Anchor member returns root part
SELECT partid, qty, unit, 0 AS lvl
FROM dbo.BOM
WHERE partid = @.root
UNION ALL
-- Recursive member returns next level of parts
SELECT C.partid, CAST(P.qty * C.qty AS DECIMAL(8, 2)),
C.unit, P.lvl + 1
FROM PartsExplosionCTE AS P
JOIN dbo.BOM AS C
ON C.assemblyid = P.partid
)
SELECT P.partid, P.partname, PE.qty, PE.unit, PE.lvl
FROM PartsExplosionCTE AS PE
JOIN dbo.Parts AS P
ON P.partid = PE.partid;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Soren S. Jorgensen" <nospam@.nodomain.com> wrote in message
news:ObORLLiXGHA.3936@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Does anyone got a really good/fast example on how to do a BOM with
> unlimited levels in SQL2000
> - without using cursors :)
> 2 tables (one for items, one for parent/child relations)
> In SQL2005 we of course are blessed with the new CTE, not in SQL2000 :-/
> In advance, thanks...
> Kr. Soren
>
>|||Get a copy of TREES & HIERARCHIES IN SQL for details then Google Nested
Sets model.
Showing posts with label sql2000. Show all posts
Showing posts with label sql2000. Show all posts
Thursday, March 22, 2012
Wednesday, March 7, 2012
between dates
in sql2000
"select * from tbl where tbl.date between date1 and date2" - results with
nothing
"select * from tbl where tbl.date >= date1 " - gives result
"select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
with nothing
how to select between 2 dates?
thanksHow about providing us with real specs, e.g. are the columns really
datetime/smalldatetime? What data is in them? What values are you using
for date1 and date2? What format are they in?
Please see http://www.aspfaq.com/5006 ... otherwise vague questions will
only get vague answers.
"Sam" <focus10@.zahav.net.il> wrote in message
news:ey3GNgirFHA.248@.TK2MSFTNGP14.phx.gbl...
> in sql2000
> "select * from tbl where tbl.date between date1 and date2" - results with
> nothing
> "select * from tbl where tbl.date >= date1 " - gives result
> "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
> with nothing
> how to select between 2 dates?
> thanks
>
>|||column date is datetime type
the values are simple:
"select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
gives NO RESULT
"select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
"select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives NO
RESULT
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
> How about providing us with real specs, e.g. are the columns really
> datetime/smalldatetime? What data is in them? What values are you using
> for date1 and date2? What format are they in?
> Please see http://www.aspfaq.com/5006 ... otherwise vague questions will
> only get vague answers.
>
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:ey3GNgirFHA.248@.TK2MSFTNGP14.phx.gbl...
>|||See point number 3 in the first link.
Should I use BETWEEN in my database queries?
http://www.aspfaq.com/show.asp?id=2280
The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp
AMB
"Sam" wrote:
> column date is datetime type
> the values are simple:
> "select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
> gives NO RESULT
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
> "select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives
NO
> RESULT
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
>
>|||If u r using date1 and date2 as parameters, in ur case date2 must be null.
Even if one of the dates is null, BETWEEN or simple comparison will fail as
happening in ur cases.
pls chk and let me know in case of any dbts
Rakesh
"Sam" wrote:
> in sql2000
> "select * from tbl where tbl.date between date1 and date2" - results with
> nothing
> "select * from tbl where tbl.date >= date1 " - gives result
> "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
> with nothing
> how to select between 2 dates?
> thanks
>
>|||(a) you need to delimit datetime values with '
(b) never use ambiguous and silly formats like m/d/y or d/m/y. Is
01/05/2005 January 5th or May 1st? Who knows?
Try:
WHERE date >= '20020101' AND date <= '20050105'
"Sam" <focus10@.zahav.net.il> wrote in message
news:ehA02sirFHA.2592@.TK2MSFTNGP09.phx.gbl...
> column date is datetime type
> the values are simple:
> "select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
> gives NO RESULT
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
> "select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives
> NO RESULT
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
>|||
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
What are the date values for these results? Are they all later than
"01/05/2005"?
Perayu|||i found the solution:
@.date1 nvarchar(12), @.date2 nvarchar(12),
'BETWEEN CONVERT(DATETIME, ' + char(39) + @.date1 + char(39) + ', 102) AND
CONVERT(DATETIME, '+ char(39) + @.date2 + char(39) +', 102)'
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:790C5553-6B88-45F8-A1D6-4F32CF001010@.microsoft.com...
> If u r using date1 and date2 as parameters, in ur case date2 must be null.
> Even if one of the dates is null, BETWEEN or simple comparison will fail
> as
> happening in ur cases.
> pls chk and let me know in case of any dbts
> Rakesh
> "Sam" wrote:
>
"select * from tbl where tbl.date between date1 and date2" - results with
nothing
"select * from tbl where tbl.date >= date1 " - gives result
"select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
with nothing
how to select between 2 dates?
thanksHow about providing us with real specs, e.g. are the columns really
datetime/smalldatetime? What data is in them? What values are you using
for date1 and date2? What format are they in?
Please see http://www.aspfaq.com/5006 ... otherwise vague questions will
only get vague answers.
"Sam" <focus10@.zahav.net.il> wrote in message
news:ey3GNgirFHA.248@.TK2MSFTNGP14.phx.gbl...
> in sql2000
> "select * from tbl where tbl.date between date1 and date2" - results with
> nothing
> "select * from tbl where tbl.date >= date1 " - gives result
> "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
> with nothing
> how to select between 2 dates?
> thanks
>
>|||column date is datetime type
the values are simple:
"select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
gives NO RESULT
"select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
"select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives NO
RESULT
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
> How about providing us with real specs, e.g. are the columns really
> datetime/smalldatetime? What data is in them? What values are you using
> for date1 and date2? What format are they in?
> Please see http://www.aspfaq.com/5006 ... otherwise vague questions will
> only get vague answers.
>
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:ey3GNgirFHA.248@.TK2MSFTNGP14.phx.gbl...
>|||See point number 3 in the first link.
Should I use BETWEEN in my database queries?
http://www.aspfaq.com/show.asp?id=2280
The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp
AMB
"Sam" wrote:
> column date is datetime type
> the values are simple:
> "select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
> gives NO RESULT
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
> "select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives
NO
> RESULT
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
>
>|||If u r using date1 and date2 as parameters, in ur case date2 must be null.
Even if one of the dates is null, BETWEEN or simple comparison will fail as
happening in ur cases.
pls chk and let me know in case of any dbts
Rakesh
"Sam" wrote:
> in sql2000
> "select * from tbl where tbl.date between date1 and date2" - results with
> nothing
> "select * from tbl where tbl.date >= date1 " - gives result
> "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
> with nothing
> how to select between 2 dates?
> thanks
>
>|||(a) you need to delimit datetime values with '
(b) never use ambiguous and silly formats like m/d/y or d/m/y. Is
01/05/2005 January 5th or May 1st? Who knows?
Try:
WHERE date >= '20020101' AND date <= '20050105'
"Sam" <focus10@.zahav.net.il> wrote in message
news:ehA02sirFHA.2592@.TK2MSFTNGP09.phx.gbl...
> column date is datetime type
> the values are simple:
> "select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
> gives NO RESULT
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
> "select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives
> NO RESULT
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
>|||
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
What are the date values for these results? Are they all later than
"01/05/2005"?
Perayu|||i found the solution:
@.date1 nvarchar(12), @.date2 nvarchar(12),
'BETWEEN CONVERT(DATETIME, ' + char(39) + @.date1 + char(39) + ', 102) AND
CONVERT(DATETIME, '+ char(39) + @.date2 + char(39) +', 102)'
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:790C5553-6B88-45F8-A1D6-4F32CF001010@.microsoft.com...
> If u r using date1 and date2 as parameters, in ur case date2 must be null.
> Even if one of the dates is null, BETWEEN or simple comparison will fail
> as
> happening in ur cases.
> pls chk and let me know in case of any dbts
> Rakesh
> "Sam" wrote:
>
Friday, February 24, 2012
Best way to upgrade for SQLServer2000 standard to Enterprice
Hi all.
One of our customers need SQL to run on more than 2GB of memory. They are
running on SQL2000 standard edition and therefor need to upgrade to
Enterprice edition. Is it just to install then new CD's over the old system
or do we need to do it as a reinstall of the server and some backup/restore
of the database? Our customers talked about some wizards to do this. Any
"best practice" advice anyone?
Thanx
GeirGeir ,
it's really simple. Just run the setup select the option upgrade and if
necessary add some (new) components. It shouldn't take more tha 2
minutes and if I there's no need to reapply service packs.
But as always, make sure you have a backup of all your databases
including the system databases before you start.
Markus|||Hi
The amount of memory you can use also depends on the version of the OS you
are using, which you haven't specified.
If you haven't already check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.sql-server-performance.com/awe_memory.asp
http://support.microsoft.com/kb/274750
You should be able to install over your existing system, make sure that you
have backups of all your databases and a recovery plan for each stage in the
process, so that you know exactly how to get your system back up and running
in the event of problems being encountered.
John
"Geir Holme" wrote:
> Hi all.
> One of our customers need SQL to run on more than 2GB of memory. They are
> running on SQL2000 standard edition and therefor need to upgrade to
> Enterprice edition. Is it just to install then new CD's over the old system
> or do we need to do it as a reinstall of the server and some backup/restore
> of the database? Our customers talked about some wizards to do this. Any
> "best practice" advice anyone?
> Thanx
> Geir
>
>
One of our customers need SQL to run on more than 2GB of memory. They are
running on SQL2000 standard edition and therefor need to upgrade to
Enterprice edition. Is it just to install then new CD's over the old system
or do we need to do it as a reinstall of the server and some backup/restore
of the database? Our customers talked about some wizards to do this. Any
"best practice" advice anyone?
Thanx
GeirGeir ,
it's really simple. Just run the setup select the option upgrade and if
necessary add some (new) components. It shouldn't take more tha 2
minutes and if I there's no need to reapply service packs.
But as always, make sure you have a backup of all your databases
including the system databases before you start.
Markus|||Hi
The amount of memory you can use also depends on the version of the OS you
are using, which you haven't specified.
If you haven't already check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
http://www.sql-server-performance.com/awe_memory.asp
http://support.microsoft.com/kb/274750
You should be able to install over your existing system, make sure that you
have backups of all your databases and a recovery plan for each stage in the
process, so that you know exactly how to get your system back up and running
in the event of problems being encountered.
John
"Geir Holme" wrote:
> Hi all.
> One of our customers need SQL to run on more than 2GB of memory. They are
> running on SQL2000 standard edition and therefor need to upgrade to
> Enterprice edition. Is it just to install then new CD's over the old system
> or do we need to do it as a reinstall of the server and some backup/restore
> of the database? Our customers talked about some wizards to do this. Any
> "best practice" advice anyone?
> Thanx
> Geir
>
>
Best way to upgrade for SQLServer2000 standard to Enterprice
Hi all.
One of our customers need SQL to run on more than 2GB of memory. They are
running on SQL2000 standard edition and therefor need to upgrade to
Enterprice edition. Is it just to install then new CD's over the old system
or do we need to do it as a reinstall of the server and some backup/restore
of the database? Our customers talked about some wizards to do this. Any
"best practice" advice anyone?
Thanx
GeirGeir ,
it's really simple. Just run the setup select the option upgrade and if
necessary add some (new) components. It shouldn't take more tha 2
minutes and if I there's no need to reapply service packs.
But as always, make sure you have a backup of all your databases
including the system databases before you start.
Markus|||Hi
The amount of memory you can use also depends on the version of the OS you
are using, which you haven't specified.
If you haven't already check out:
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.sql-server-performance.com/awe_memory.asp
http://support.microsoft.com/kb/274750
You should be able to install over your existing system, make sure that you
have backups of all your databases and a recovery plan for each stage in the
process, so that you know exactly how to get your system back up and running
in the event of problems being encountered.
John
"Geir Holme" wrote:
> Hi all.
> One of our customers need SQL to run on more than 2GB of memory. They are
> running on SQL2000 standard edition and therefor need to upgrade to
> Enterprice edition. Is it just to install then new CD's over the old syste
m
> or do we need to do it as a reinstall of the server and some backup/restor
e
> of the database? Our customers talked about some wizards to do this. Any
> "best practice" advice anyone?
> Thanx
> Geir
>
>|||Geir ,
it's really simple. Just run the setup select the option upgrade and if
necessary add some (new) components. It shouldn't take more tha 2
minutes and if I there's no need to reapply service packs.
But as always, make sure you have a backup of all your databases
including the system databases before you start.
Markus|||Hi
The amount of memory you can use also depends on the version of the OS you
are using, which you haven't specified.
If you haven't already check out:
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.sql-server-performance.com/awe_memory.asp
http://support.microsoft.com/kb/274750
You should be able to install over your existing system, make sure that you
have backups of all your databases and a recovery plan for each stage in the
process, so that you know exactly how to get your system back up and running
in the event of problems being encountered.
John
"Geir Holme" wrote:
> Hi all.
> One of our customers need SQL to run on more than 2GB of memory. They are
> running on SQL2000 standard edition and therefor need to upgrade to
> Enterprice edition. Is it just to install then new CD's over the old syste
m
> or do we need to do it as a reinstall of the server and some backup/restor
e
> of the database? Our customers talked about some wizards to do this. Any
> "best practice" advice anyone?
> Thanx
> Geir
>
>
One of our customers need SQL to run on more than 2GB of memory. They are
running on SQL2000 standard edition and therefor need to upgrade to
Enterprice edition. Is it just to install then new CD's over the old system
or do we need to do it as a reinstall of the server and some backup/restore
of the database? Our customers talked about some wizards to do this. Any
"best practice" advice anyone?
Thanx
GeirGeir ,
it's really simple. Just run the setup select the option upgrade and if
necessary add some (new) components. It shouldn't take more tha 2
minutes and if I there's no need to reapply service packs.
But as always, make sure you have a backup of all your databases
including the system databases before you start.
Markus|||Hi
The amount of memory you can use also depends on the version of the OS you
are using, which you haven't specified.
If you haven't already check out:
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.sql-server-performance.com/awe_memory.asp
http://support.microsoft.com/kb/274750
You should be able to install over your existing system, make sure that you
have backups of all your databases and a recovery plan for each stage in the
process, so that you know exactly how to get your system back up and running
in the event of problems being encountered.
John
"Geir Holme" wrote:
> Hi all.
> One of our customers need SQL to run on more than 2GB of memory. They are
> running on SQL2000 standard edition and therefor need to upgrade to
> Enterprice edition. Is it just to install then new CD's over the old syste
m
> or do we need to do it as a reinstall of the server and some backup/restor
e
> of the database? Our customers talked about some wizards to do this. Any
> "best practice" advice anyone?
> Thanx
> Geir
>
>|||Geir ,
it's really simple. Just run the setup select the option upgrade and if
necessary add some (new) components. It shouldn't take more tha 2
minutes and if I there's no need to reapply service packs.
But as always, make sure you have a backup of all your databases
including the system databases before you start.
Markus|||Hi
The amount of memory you can use also depends on the version of the OS you
are using, which you haven't specified.
If you haven't already check out:
http://msdn.microsoft.com/library/d...br />
8dbn.asp
http://www.sql-server-performance.com/awe_memory.asp
http://support.microsoft.com/kb/274750
You should be able to install over your existing system, make sure that you
have backups of all your databases and a recovery plan for each stage in the
process, so that you know exactly how to get your system back up and running
in the event of problems being encountered.
John
"Geir Holme" wrote:
> Hi all.
> One of our customers need SQL to run on more than 2GB of memory. They are
> running on SQL2000 standard edition and therefor need to upgrade to
> Enterprice edition. Is it just to install then new CD's over the old syste
m
> or do we need to do it as a reinstall of the server and some backup/restor
e
> of the database? Our customers talked about some wizards to do this. Any
> "best practice" advice anyone?
> Thanx
> Geir
>
>
Sunday, February 19, 2012
Best way to stop MS SQL in a Failover Cluster?
Hi,
Is it best practice to use SEM or the Services applet to stop SQL2000 on a
failover cluster or is there a way in Cluster Admin?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
Hi,
You should use Cluter Administrator Console to take the SQL Server resource
Offline.
Shutting SQL Service in SEM will initiate a failover.
Danijel
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ei5JlFx$EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it best practice to use SEM or the Services applet to stop SQL2000 on a
> failover cluster or is there a way in Cluster Admin?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>
|||Danijel,
But you could use Services to stop SQL?
Chris
"Danijel Novak" <danijel.novak@.snt.si> wrote in message
news:GDRHd.8418$F6.1490871@.news.siol.net...
> Hi,
> You should use Cluter Administrator Console to take the SQL Server
> resource Offline.
> Shutting SQL Service in SEM will initiate a failover.
> Danijel
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ei5JlFx$EHA.2568@.TK2MSFTNGP10.phx.gbl...
>
|||Nope, it would initiate a failover too...
Best practice is to use Cluster Administrator to shutdown the service.
Danijel
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23JoVHcx$EHA.1084@.tk2msftngp13.phx.gbl...
> Danijel,
> But you could use Services to stop SQL?
> Chris
> "Danijel Novak" <danijel.novak@.snt.si> wrote in message
> news:GDRHd.8418$F6.1490871@.news.siol.net...
>
|||That's what I wanted to know.
Thanks
Chris
"Danijel Novak" <danijel.novak@.snt.si> wrote in message
news:cdSHd.8423$F6.1490201@.news.siol.net...
> Nope, it would initiate a failover too...
> Best practice is to use Cluster Administrator to shutdown the service.
> Danijel
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23JoVHcx$EHA.1084@.tk2msftngp13.phx.gbl...
>
|||As of SQL Server 2000 Failover Clustering, you should be able to use SQL Enterprise Manager, SQL Server Services Manager, Query Analyzer and Cluster Administrator to start/stop SQL Server resources as all
these are cluster aware. This was not true for SQL Server 7.0 Failover Clustering and in that case only Cluster Administrator had to be used to start/stop the SQL Server Services.
Additional Information:
======================
INF: Clustered SQL Server Do's, Don'ts, and Basic Warnings
http://support.microsoft.com/?kbid=254321
Here is the relevant section from the above mentioned Microsoft Knowledge Base article
================================================== ================================================== ========================
Start and stop SQL Server services
SQL Server 6.5 and SQL Server 7.0 virtual servers
To start or stop SQL Server, SQL Server Executive, or SQL Agent services from a SQL Server 6.5 or SQL Server 7.0 virtual server, you must use the Microsoft Cluster Administrator or the Cluster.exe command line
tool.If you attempt to start or stop services in any other way (for instance, from Control Panel, SQL Service Manager, or SQL Enterprise Manager), the registry may be corrupted, and you may need to uncluster or
completely reinstall SQL Server.The most common sign of having started a service incorrectly is that the service accounts appear as a jumble of ASCII characters.If you need to start SQL Server from a command
line, you must use the Cluster Administrator or Cluster.exe tool to first take the SQL Server, SQL Executive, or SQL Agent services offline.When you start SQL Server from a command line, connectivity takes place
using the virtual server name. The only way to make a local connection is if the resources are owned by the node from which you originally installed SQL Server.
SQL Server 2000 virtual servers
SQL Server 2000 virtual servers do not have the above restrictions. We recommend that you use SQL Server Enterprise Manager, SQL Server Services applet, or Cluster Administrator to start and to stop SQL
Server 2000 virtual server services. Although you can use Service Control Manager or Control Panel/Service to start and to stop the services without damaging the registry, these options will not cause the
services to stay in a stopped state. Instead, the services will be detected by the clustered server and you will receive multiple event ID 17052 error messages in you SQL Server that are similar to the following:
[sqsrvres] CheckServiceAlive: Service is dead
[sqsrvres] OnlineThread: service stopped while waiting for QP
[sqsrvres] OnlineThread: Error 1 bringing resource online
After you receive these error messages, SQL Server will be restarted by the cluster service. This behavior is expected for these types of errors.
================================================== ================================================== ========================
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
Is it best practice to use SEM or the Services applet to stop SQL2000 on a
failover cluster or is there a way in Cluster Admin?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
Hi,
You should use Cluter Administrator Console to take the SQL Server resource
Offline.
Shutting SQL Service in SEM will initiate a failover.
Danijel
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ei5JlFx$EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it best practice to use SEM or the Services applet to stop SQL2000 on a
> failover cluster or is there a way in Cluster Admin?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>
|||Danijel,
But you could use Services to stop SQL?
Chris
"Danijel Novak" <danijel.novak@.snt.si> wrote in message
news:GDRHd.8418$F6.1490871@.news.siol.net...
> Hi,
> You should use Cluter Administrator Console to take the SQL Server
> resource Offline.
> Shutting SQL Service in SEM will initiate a failover.
> Danijel
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ei5JlFx$EHA.2568@.TK2MSFTNGP10.phx.gbl...
>
|||Nope, it would initiate a failover too...
Best practice is to use Cluster Administrator to shutdown the service.
Danijel
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:%23JoVHcx$EHA.1084@.tk2msftngp13.phx.gbl...
> Danijel,
> But you could use Services to stop SQL?
> Chris
> "Danijel Novak" <danijel.novak@.snt.si> wrote in message
> news:GDRHd.8418$F6.1490871@.news.siol.net...
>
|||That's what I wanted to know.
Thanks
Chris
"Danijel Novak" <danijel.novak@.snt.si> wrote in message
news:cdSHd.8423$F6.1490201@.news.siol.net...
> Nope, it would initiate a failover too...
> Best practice is to use Cluster Administrator to shutdown the service.
> Danijel
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23JoVHcx$EHA.1084@.tk2msftngp13.phx.gbl...
>
|||As of SQL Server 2000 Failover Clustering, you should be able to use SQL Enterprise Manager, SQL Server Services Manager, Query Analyzer and Cluster Administrator to start/stop SQL Server resources as all
these are cluster aware. This was not true for SQL Server 7.0 Failover Clustering and in that case only Cluster Administrator had to be used to start/stop the SQL Server Services.
Additional Information:
======================
INF: Clustered SQL Server Do's, Don'ts, and Basic Warnings
http://support.microsoft.com/?kbid=254321
Here is the relevant section from the above mentioned Microsoft Knowledge Base article
================================================== ================================================== ========================
Start and stop SQL Server services
SQL Server 6.5 and SQL Server 7.0 virtual servers
To start or stop SQL Server, SQL Server Executive, or SQL Agent services from a SQL Server 6.5 or SQL Server 7.0 virtual server, you must use the Microsoft Cluster Administrator or the Cluster.exe command line
tool.If you attempt to start or stop services in any other way (for instance, from Control Panel, SQL Service Manager, or SQL Enterprise Manager), the registry may be corrupted, and you may need to uncluster or
completely reinstall SQL Server.The most common sign of having started a service incorrectly is that the service accounts appear as a jumble of ASCII characters.If you need to start SQL Server from a command
line, you must use the Cluster Administrator or Cluster.exe tool to first take the SQL Server, SQL Executive, or SQL Agent services offline.When you start SQL Server from a command line, connectivity takes place
using the virtual server name. The only way to make a local connection is if the resources are owned by the node from which you originally installed SQL Server.
SQL Server 2000 virtual servers
SQL Server 2000 virtual servers do not have the above restrictions. We recommend that you use SQL Server Enterprise Manager, SQL Server Services applet, or Cluster Administrator to start and to stop SQL
Server 2000 virtual server services. Although you can use Service Control Manager or Control Panel/Service to start and to stop the services without damaging the registry, these options will not cause the
services to stay in a stopped state. Instead, the services will be detected by the clustered server and you will receive multiple event ID 17052 error messages in you SQL Server that are similar to the following:
[sqsrvres] CheckServiceAlive: Service is dead
[sqsrvres] OnlineThread: service stopped while waiting for QP
[sqsrvres] OnlineThread: Error 1 bringing resource online
After you receive these error messages, SQL Server will be restarted by the cluster service. This behavior is expected for these types of errors.
================================================== ================================================== ========================
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
Subscribe to:
Posts (Atom)