SQL compact edition: pls

Showing posts with label pls. Show all posts

Monday, March 26, 2012

Polish characters problem.

Hi,
First off all I am not SQL expert so pls forgive me if something will be
unclear.
I have old SQL 7.0 with Code page CP1252 and database application on it with
polish characters.
I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
database) and my application do not see polish characters.
Any idea?
Artur
Hi
If you had code page 1250 then I would expect a Polish collation to be used.
Extended data would be lost if you switched from 1250 to 1252. Therefore you
may want to try a Polish collation for the destination database.
How did you convert the database?
John
"arti" <artipp@.op.pl> wrote in message news:cll8au$odf$1@.news.onet.pl...
> Hi,
> First off all I am not SQL expert so pls forgive me if something will be
> unclear.
>
> I have old SQL 7.0 with Code page CP1252 and database application on it
with
> polish characters.
> I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
> database) and my application do not see polish characters.
> Any idea?
>
> Artur
>

Polish characters problem.

Hi,
First off all I am not SQL expert so pls forgive me if something will be
unclear.
I have old SQL 7.0 with Code page CP1252 and database application on it with
polish characters.
I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
database) and my application do not see polish characters.
Any idea?
ArturHi
If you had code page 1250 then I would expect a Polish collation to be used.
Extended data would be lost if you switched from 1250 to 1252. Therefore you
may want to try a Polish collation for the destination database.
How did you convert the database?
John
"arti" <artipp@.op.pl> wrote in message news:cll8au$odf$1@.news.onet.pl...
> Hi,
> First off all I am not SQL expert so pls forgive me if something will be
> unclear.
>
> I have old SQL 7.0 with Code page CP1252 and database application on it
with
> polish characters.
> I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
> database) and my application do not see polish characters.
> Any idea?
>
> Artur
>sql

Polish characters problem.

Hi,
First off all I am not SQL expert so pls forgive me if something will be
unclear.
I have old SQL 7.0 with Code page CP1252 and database application on it with
polish characters.
I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
database) and my application do not see polish characters.
Any idea?
ArturHi
If you had code page 1250 then I would expect a Polish collation to be used.
Extended data would be lost if you switched from 1250 to 1252. Therefore you
may want to try a Polish collation for the destination database.
How did you convert the database?
John
"arti" <artipp@.op.pl> wrote in message news:cll8au$odf$1@.news.onet.pl...
> Hi,
> First off all I am not SQL expert so pls forgive me if something will be
> unclear.
>
> I have old SQL 7.0 with Code page CP1252 and database application on it
with
> polish characters.
> I migrated to SQL 2000 with Server collation Latin1_General_CI_AS (also on
> database) and my application do not see polish characters.
> Any idea?
>
> Artur
>

Tuesday, March 20, 2012

pls/sql help needed urgent.

database:Oracle 7.x
coding:pl/sql
table_size:125 million rows
table_name: ORDER_PROC

columns:
Name Null? Type
---------- --- --
CUST_ID NOT NULL NUMBER(10)
DIV_CODE NOT NULL CHAR(2)
ORDER_ID NOT NULL CHAR(8)
LINE_NBR NOT NULL NUMBER(4)
TOTAL_DEMAND_AMT NUMBER(9,2)
ORDER_DISC_AMT NUMBER(9,2)
DISC_AMT NUMBER(9,2)
ORDER_DEMAND_AMT NUMBER(9,2)
INSERT_DATE DATE

Requirement: Update disc_amt
Formula: disc_amt=(total_demand_amt/order_demand_amt)*order_disc_amt
Condition: Order_disc_amt > 0

Read a summary of order_proc information into a cursor (summarize at cust_id, div_code, order_id level) Summarize total demand amt (line_nbr) and order_disc_amt (order_id). This summary data is required in updating.

I am Unix & SQL guy. I am not a pl/sql coding person. I know very little about cursors. struggled a week with my limited knowledge, now asking for help from a pl/sql expert. Any questions...? Thanking you in advance.Originally posted by hnvhelp
database:Oracle 7.x
coding:pl/sql
table_size:125 million rows

columns:
Name Null? Type
---------- --- --
CUST_ID NOT NULL NUMBER(10)
DIV_CODE NOT NULL CHAR(2)
ORDER_ID NOT NULL CHAR(8)
LINE_NBR NOT NULL NUMBER(4)
TOTAL_DEMAND_AMT NUMBER(9,2)
ORDER_DISC_AMT NUMBER(9,2)
DISC_AMT NUMBER(9,2)
ORDER_DEMAND_AMT NUMBER(9,2)
INSERT_DATE DATE

Requirement: Update disc_amt
Formula: disc_amt=(total_demand_amt/order_demand_amt)*order_disc_amt
Condition: Order_disc_amt > 0

Summarize total_demand_amt (at line_nbr level) and order_disc_amt (at order_id level) grouped by cust_id, div_code & order_id.

I am Unix & SQL guy. I am not a pl/sql coding person. I know very little about cursors. struggled a week with my limited knowledge, now asking for help from a pl/sql expert. Any questions...? Thanking you in advance.
It is not clear to me whether the "Summarize..." sentence relates to the update, or is a separate requirement.

Assuming it is a separate requirement, the update would be simply:

UPDATE tablename
SET disc_amt=(total_demand_amt/order_demand_amt) * order_disc_amt
WHERE Order_disc_amt > 0;

I'm not sure I understand the next bit. Could be:

SELECT cust_id, div_code, order_id, SUM(total_demand_amt), SUM(order_disc_amt)
FROM tablename
GROUP BY cust_id, div_code, order_id;

... but that may not be quite right.|||to andrewst,

Yes, Summarize relates to the update statement. First, read this summary of data into a cursor (inluding cust_id, div_code, order_id) and then update the corresponding disc_amt whose order_disc_amt > 0.

another different question:
And even if it is a direct update, can we update a table simply using a update statement who has 125+ million rows...?|||Originally posted by hnvhelp
to andrewst,

Yes, Summarize relates to the update statement. First, read this summary of data into a cursor (inluding cust_id, div_code, order_id) and then update the corresponding disc_amt whose order_disc_amt > 0.

another different question:
And even if it is a direct update, can we update a table simply using a update statement who has 125+ million rows...?
I can't really follow the requirements for this update. Maybe you could post an example with data before and after?

Yes, you can update a 125+ million row table with an UPDATE statement. Depending on what percentage of rows in the table the UPDATE will modify, you may need a large rollback segment, but using a single UPDATE statement is much more efficient than using PL/SQL cursors and loops.

pls..help me to find out the error in the code

hi,

when i tried to run the following code , the code keeps on running without creating the required table...............

pls can anyone help me to figure out the problem........

waiting for your replies...

int row_head = 3;

int col_head = 3;

TextBox[] columns1=new TextBox[5];

TextBox[] rows = new TextBox[2];

for (int i = 0; i < 3; i++)

columns1[ i ] = new TextBox();

for (int i = 0; i < 2; i++)

rows[ i ] = new TextBox();

columns1[0].Text = "col1";

columns1[1].Text = "col2";

columns1[2].Text = "col3";

rows[0].Text = "rowh1";

rows[1].Text = "rowh2";

String[] description=new string[10];

for (int k = 0; k < 10; k++)

description[k] = k.ToString();

int index_value = 0;

int des_index = 0;

string datatype = "";

string command = "";

SqlConnection sqlCN = new SqlConnection(connectionString);

for (int i = 0; i < col_head + 1; i++)

{

if (i == 0)

datatype += "(col " + i + " varchar(20),";

else if (i + 1 == col_head+1)

datatype += "col" + i + " varchar(20))";

else

datatype += "col" + i + " varchar(20),";

}

MessageBox.Show(datatype);

MessageBox.Show("create table " + textBox1.Text + " " + datatype);

string selectCommandText = "create table " + textBox1.Text +datatype;

MessageBox.Show("executed");

try

{

SqlCommand sqlCmd = new SqlCommand(selectCommandText, sqlCN);

sqlCN.Open();

MessageBox.Show("open ");

// the following command is not executed.....

sqlCmd.ExecuteNonQuery();

MessageBox.Show("open and execu");

for (int i = 0; i < row_head + 1; i++)

{

command="";

if (i == 0)

{

command="' ',";

for(int j=0;j<col_head;j++)

{

if(j+1==col_head)

command+="'"+columns1[j].Text+"'";

else

command+="'"+columns1[j].Text+"',";

}

else

{

command = "'"+rows[index_value++].Text+"',";

for (int j = des_index; j < des_index + col_head; j++)

{

if (j +1== des_index+col_head)

command += "'" + description[j] + "'";

else

command += "'" + description[j] + "',";

}

des_index = des_index + col_head;

}

MessageBox.Show("INSERT INTO " + textBox1.Text + " values(" + command + ")");

sqlCmd.CommandText = "INSERT INTO " + textBox1.Text + " values("+command+")";

sqlCmd.ExecuteNonQuery();

}

MessageBox.Show("fini");

selectCommandText = "SELECT * FROM " + textBox1.Text;

sqlCmd = new SqlCommand(selectCommandText, sqlCN);

sqlCmd.ExecuteNonQuery();

SqlDataAdapter sqlDA = new SqlDataAdapter(selectCommandText, sqlCN);

DataSet DS = new DataSet();

sqlDA.Fill(DS);

MessageBox.Show("fini 2");

DataGridView1.DataSource = DS.Tables[0].DefaultView;

}

catch (Exception eio)

{ }}

Sweety

Appearantly you are getting an error during the execution. Having a try / catch block, with no actions or error retrieval functionalty will keep the error under the covers.

Please post the error message in here which ca be retrieved by reading the eio.Message and post the script which is used for the creation of the table.

BTW if you want to create a table you could use SMO rather than doing a script composing and executing this against the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hello,

I tried to print the message , I got " Incorrect syntax near '0'"..can some one tell me what does it mean...

bye

Sweety

|||

This is a syntax error in your query. Post the query (commandtext of the command here)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

This is your mistake: You have put a space after col so your create table command looks like create table (col 0 varchar(20), ... which is not correct, remove the space and it will start working.

for (int i = 0; i < col_head + 1; i++)

{

if (i == 0)

datatype += "(col " + i + " varchar(20),";

else if (i + 1 == col_head+1)

datatype += "col" + i + " varchar(20))";

else

datatype += "col" + i + " varchar(20),";

}

Thanks

Waseem

PLS. HELP. SQL NEWBIE

hello all. please tell me why the following update staement doesn't
work.

what i want to do is update tblmaster.mcol2 based on the value of
tblheader.hcol2

hcol2 values:
1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2

i tried it using query analyzer but it still adds even though the
value of hcol2 is 2.

create table tblmaster ( mcol1 nvarchar(3),
mcol2 float )

insert into tblmaster values ('001', 1)
insert into tblmaster values ('002', 1)
insert into tblmaster values ('003', 1)
insert into tblmaster values ('004', 1)
insert into tblmaster values ('005', 1)

create table tblheader ( hcol1 int,
hcol2 smallint )

create table tbldetails ( dcol1 int,
dcol2 nvarchar(3),
dcol3 float,
dcol4 float )

insert into tblheader values (1, 1)
insert into tblheader values (2, 1)
insert into tblheader values (3, 2)

insert into tbldetails values ( 1, '001', 1, 10 )
insert into tbldetails values ( 1, '002', 1, 10 )

insert into tbldetails values ( 2, '001', 1, 10 )
insert into tbldetails values ( 2, '003', 2, 10 )

insert into tbldetails values ( 3, '004', 1, 10 )
insert into tbldetails values ( 3, '005', 2, 10 )

declare @.lo as int
declare @.hi as int

set @.lo = 1
set @.hi = 3

UPDATE tblmaster
SETmcol2 =
CASE h.hcol2
WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
END
FROM tblmaster t, tblheader h, tbldetails d
WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi

select * from tblmaster

TIA,

diegoHi Rey

It is not clear how your tables are actually related but you may want to try
something like:

"Rey Guerrero" <rey_guerrero@.hotmail.com> wrote in message
news:d401c59b.0412220652.54dfa7cb@.posting.google.c om...
> hello all. please tell me why the following update staement doesn't
> work.
> what i want to do is update tblmaster.mcol2 based on the value of
> tblheader.hcol2
> hcol2 values:
> 1 = add ( tbldetails.dcol3 * tbldetails.dcol4 ) to mcol2
> 2 = subtract ( tbldetails.dcol3 * tbldetails.dcol4 ) from mcol2
> i tried it using query analyzer but it still adds even though the
> value of hcol2 is 2.
>
> create table tblmaster ( mcol1 nvarchar(3),
> mcol2 float )
> insert into tblmaster values ('001', 1)
> insert into tblmaster values ('002', 1)
> insert into tblmaster values ('003', 1)
> insert into tblmaster values ('004', 1)
> insert into tblmaster values ('005', 1)
> create table tblheader ( hcol1 int,
> hcol2 smallint )
> create table tbldetails ( dcol1 int,
> dcol2 nvarchar(3),
> dcol3 float,
> dcol4 float )
> insert into tblheader values (1, 1)
> insert into tblheader values (2, 1)
> insert into tblheader values (3, 2)
> insert into tbldetails values ( 1, '001', 1, 10 )
> insert into tbldetails values ( 1, '002', 1, 10 )
> insert into tbldetails values ( 2, '001', 1, 10 )
> insert into tbldetails values ( 2, '003', 2, 10 )
> insert into tbldetails values ( 3, '004', 1, 10 )
> insert into tbldetails values ( 3, '005', 2, 10 )
> declare @.lo as int
> declare @.hi as int
> set @.lo = 1
> set @.hi = 3
> UPDATE tblmaster
> SET mcol2 =
> CASE h.hcol2
> WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
> END
> FROM tblmaster t, tblheader h, tbldetails d
> WHERE t.mcol1 = d.dcol2 AND h.hcol1 BETWEEN @.lo and @.hi
> select * from tblmaster
> TIA,
> diego|||Hi Rey

It is not clear how your tables are actually related but you may want to try
something like:

UPDATE t
SET mcol2 =
CASE h.hcol2
WHEN 1 THEN mcol2 + ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
WHEN 2 THEN mcol2 - ( ABS( d.dcol3 ) * ABS( d.dcol4 ) )
END
FROM tblmaster t
JOIN tbldetails d ON t.mcol1 = d.dcol2
JOIN tblheader h ON h.hcol1 = d.dcol1
WHERE h.hcol1 BETWEEN @.lo and @.hi

As tblmaster has is a one to many relationship with tbldetails you will have
to either restrict which rows match or use an aggregate

UPDATE t
SET mcol2 = mcol2 + val
FROM tblmaster t
JOIN ( SELECT d.dcol2, SUM( CASE WHEN h.col2 = 1 THEN d.dcol3 * dcol4
ELSE -1 * dcol3 * dcol4 END ) as val
FROM tbldetails d
JOIN tblheader h ON h.hcol1 = d.dcol1
WHERE h.hcol1 BETWEEN @.lo and @.hi GROUP BY d.dcol2 ) d ON t.mcol1 =
d.dcol2

John

Pls. help me optimize this stored procedure

Hi,

I'm kind of rusty when it comes stored procedures and I was wondering if someone could help me with the following SP. What I need to do is to query the database with a certain parameter (ex. Culture='fr-FR' and MsgKey='NoUpdateFound') and when that returns no rows I want it to default back to Culture 'en-US' with the same MsgKey='NoUpdateFound'). This way our users will always get an error message back in English if their own language is not defined. Currently I'm doing the following,

- Check row count on the parameter passed to sql and if found again do a select returning the values
- else do a select on the SQL with the default parameters.

I'm guessing there is a way to run a SQL and return it if it contains rows otherwise issue another query to return the default.

How can I improve this query. Your help is greatly appreciated. Also, if I do any assignment since 'Message' is defined as text I get the following error message

Server: Msg 279, Level 16, State 3, Line 1
The text, ntext, and image data types are invalid in this subquery or
aggregate expression.

CREATE PROCEDURE GetMessageByCulture
(
@.Culture varchar(25),
@.MsgKey varchar(50)

)
AS
SET NOCOUNT ON;

IF ( (SELECT COUNT(1) FROM ProductUpdateMsg WHERECulture=@.Culture AND MsgKey='NoUpdateFound') > 0)
SELECT Message FROM ProductUpdateMsg WHERECulture=@.Culture ANDMsgKey=@.MsgKey
ELSE
SELECT Message FROM ProductUpdateMsg WHERE Culture='en-US' ANDMsgKey=@.MsgKey
GO

Thank you
M.

Why don't you use resource files with globalization? This functionality is built in; all you have to do is edit an XML file to update the messages, and the message will always be displayed in the default language if the user's culture is not supported.

|||

IF EXISTS (SELECT * FROM ProductUpdateMsg WHERECulture=@.Culture AND MsgKey='NoUpdateFound')

SELECT Message FROM ProductUpdateMsg WHERECulture=@.Culture ANDMsgKey=@.MsgKey
ELSE
SELECT Message FROM ProductUpdateMsg WHERE Culture='en-US' ANDMsgKey=@.MsgKey

I am using * for the first statement because I don't know your columns. Ideally, you just only select one column instead of *.

Pls, help: Bill of Materials and SQL 2005 recursion problem

/*
Hello everyone,
Thank you for taking your time to look at my problem. In our busy lives it's
hard to find time,
therefore, I really appreciate your effort.
The problem I am tryiing to resolve is this.
In the Bill of Materials structure, any idem can own components, therefore,
becoming an assembly.
Assemblies can own other assemblies. Any item can be used several times in t
he overall assembly.
However when manufacturing these assemblies, it's essential to know not only
that a part(assy) is owned by another part(assy),
but that any part(assy) is owned by a specific instance of the the parent.
In the example below, ScrewPaint is in the BOM two times and the screw is tw
o times, but the ScrewPaint on the row 11 belongs to screw instance 2.
and ScrewPaint on the row 7 of the result set, belongs to screw instance # o
ne.
The new SQL 2005 recursion is an excellent tool to process BOMs. What I try
to do is to calculate item instance and the instance of the parent within
the recursion algorithm. I am just not seeing how I can do it. I resorted to
path calculation and establishing it this way,
but it looks ugly and I don't like it.
I have make lots of futile efforts to somehow do it while recursing but my m
ind just does not see it. I am not a recursion expert yet.
Is there any way, using defined structure, to define the recursion in such a
way that I can get the final result,
i.e. calculate instance of an item and derive parent and instance on the fly
within recursion, without temp table and post-processing?
Thank you very much. Your time and knowledge is very appreciated.
Farmer.
*/
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDInstance
-- non- relevent columns
, Tree, ItemPath, ItemPathParent, [Level]
FROM @.t t
GO>"Farmer" <someone@.somewhere.com> wrote in message
>news:ugDbygkOGHA.1032@.TK2MSFTNGP11.phx.gbl...
>/*
>Hello everyone,
>Thank you for taking your time to look at my problem. In our busy lives
>it's hard to find time,
>therefore, I really appreciate your effort.
>The problem I am tryiing to resolve is this.
>In the Bill of Materials structure, any idem can own components, therefore,
>becoming an assembly.
>Assemblies can own other assemblies. Any item can be used several times in
>the overall assembly.
>However when manufacturing these assemblies, it's essential to know not
>only that a part(assy) is owned by another >part(assy),
>but that any part(assy) is owned by a specific instance of the the parent.
>In the example below, ScrewPaint is in the BOM two times and the screw is
>two times, but the ScrewPaint on the row >11 belongs to screw instance 2.
>and ScrewPaint on the row 7 of the result set, belongs to screw instance #
>one.
> . . .
How about
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
, Instance
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
, cast('0' as varchar(50))
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY
r.itmID ) as smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
, cast(r.Instance + '.' + cast(Row_Number() over (order by i.itmID)
as varchar) as varchar(50))
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
returns
ItmID itmIDInstance itmIDParent Instance CompTree
-- -- -- -- --
Cabinet 1 NULL 0 Cabinet
Case 1 Cabinet 0.1 Case
Door 1 Case 0.1.1 Door
SPiece 1 Door 0.1.1.1 SPiece
XPiece 1 Door 0.1.1.2 XPiece
Screw 1 Case 0.1.2 Screw
ScrewPaint 2 Screw 0.1.2.1 ScrewPaint
SideL 1 Case 0.1.3 SideL
SideR 1 Case 0.1.4 SideR
Screw 2 Cabinet 0.2 Screw
ScrewPaint 1 Screw 0.2.1 ScrewPaint
Which not only uniquely identifies each part, but also encodes where it's
located.
David|||Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Farmer" <someone@.somewhere.com> wrote in message news:ugDbygkOGHA.1032@.TK2M
SFTNGP11.phx.gbl...
/*
Hello everyone,
Thank you for taking your time to look at my problem. In our busy lives it's
hard to find time,
therefore, I really appreciate your effort.
The problem I am tryiing to resolve is this.
In the Bill of Materials structure, any idem can own components, therefore,
becoming an assembly.
Assemblies can own other assemblies. Any item can be used several times in t
he overall assembly.
However when manufacturing these assemblies, it's essential to know not only
that a part(assy) is owned by another part(assy),
but that any part(assy) is owned by a specific instance of the the parent.
In the example below, ScrewPaint is in the BOM two times and the screw is tw
o times, but the ScrewPaint on the row 11 belongs to screw instance 2.
and ScrewPaint on the row 7 of the result set, belongs to screw instance # o
ne.
The new SQL 2005 recursion is an excellent tool to process BOMs. What I try
to do is to calculate item instance and the instance of the parent within
the recursion algorithm. I am just not seeing how I can do it. I resorted to
path calculation and establishing it this way,
but it looks ugly and I don't like it.
I have make lots of futile efforts to somehow do it while recursing but my m
ind just does not see it. I am not a recursion expert yet.
Is there any way, using defined structure, to define the recursion in such a
way that I can get the final result,
i.e. calculate instance of an item and derive parent and instance on the fly
within recursion, without temp table and post-processing?
Thank you very much. Your time and knowledge is very appreciated.
Farmer.
*/
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDInstance
-- non- relevent columns
, Tree, ItemPath, ItemPathParent, [Level]
FROM @.t t
GO|||Slightly more compact version:
WITH T AS
(
SELECT
CONVERT(varchar(10), NULL) AS itmID,
@.itmID AS compitmID,
0 as level
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:ed8TVplOGHA.2696@.TK2MSFTNGP14.phx.gbl...
Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thank you very much Adam,
it does appeared to work as I sought it to work, but only at the begining...
Here is an updated code for my case.
Things i have learned from your code.
1. In the ancor part, the left join to components is useless (I don't know w
hy I didn't see it myself). I guess I am just too focused on my issue..., d
idn't see obvious.
2. that one can declare the second CTE and use in it the first one. I think
I read about it in the past, I just didn't think of using it. It replaces my
insert @.t temp table, i.e. making it virtual. Beautiful idea. I can also use
it in the subquery as well, i think.
3. I used ROW_RUMBER a number of times and used it in my original attempts b
ut I never considered RANK() function. It seems to be an original solution t
o use it, however it's faltering in the logic somehow. I adjusted my code wi
th your ideas. i decided to complicate it yet and added screws to more parts
making an assymbly "Screw" have more parents. That is where error came. For
example ScrewPaint instance should have a parent screw instance 1 but your
code makes it belong to instance 2 where as it belongs to instance 1 accordi
ng to the calculated path.
I am working on combination of 2 and my original code using calculated path.
I am not sure if your idea can still be taken advantage of. Do you see a wa
y?
In the nutshell here is a problem of converting BOM definition into a true t
ree, as very appropriately David Browny pointed out (prior post), encoding t
he tree. If each node where to be numbered, the component's parent IDs have
to point the right components.
Thank you for good ideas.
Vladimir Moldovanenko,
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
-- LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
,T as (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS itmIDInstan
ce,
RANK() OVER (PARTITION BY itmID ORDER BY Level) AS itmIDParentInstance
FROM itm_tree
)
-- Capture the intermedeate result set
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
, case when itmID is null then null else itmIDParentInstance end as itmIDPar
entInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P as ItemPath
, Level
FROM t
ORDER BY p
Original code below
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDParentInstance
-- non- relevent columns
, Tree as CompTree, ItemPath, ItemPathParent, [Level]
FROM @.t t
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:%23qwjQ0lOGHA.1040@.TK2MSFTNGP12.phx.gbl...
Slightly more compact version:
WITH T AS
(
SELECT
CONVERT(varchar(10), NULL) AS itmID,
@.itmID AS compitmID,
0 as level
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:ed8TVplOGHA.2696@.TK2MSFTNGP14.phx.gbl...
Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||For your better understanding this is a piece of acrual structure where data
will end up to be. there are more fields in it, but relevand are here.
itmID is not varchar, just for test clarity it was made.
CREATE TABLE [dbo].[OrderItems](
[itmID] [int] NOT NULL,
[itmIDInstance] [smallint] NOT NULL,
[itmIDParent] [int] NULL,
[itmIDParentInstance] [smallint] NULL,
..
[olnID] [int] NOT NULL,
[olnIDInstance] [smallint] NOT NULL,
..
CONSTRAINT [PK_OrderItems_itmID_itmIDInstance_olnID
_olnIDInstance] PRIMARY K
EY NONCLUSTERED
(
[itmID] ASC,
[itmIDInstance] ASC,
[olnID] ASC,
[olnIDInstance] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderItems] WITH CHECK ADD CONSTRAINT [FK_OrderItems_OrderItems]
FOREIGN KEY([itmIDParent], [itmIDParentInstance], [olnID], [olnIDInstance])
REFERENCES [dbo].[OrderItems] ([itmID], [itmIDInstance], [olnID], [olnIDInstance])
GO
"Farmer" <someone@.somewhere.com> wrote in message news:OSkZwxmOGHA.3896@.TK2M
SFTNGP15.phx.gbl...
Thank you very much Adam,
it does appeared to work as I sought it to work, but only at the begining...
Here is an updated code for my case.
Things i have learned from your code.
1. In the ancor part, the left join to components is useless (I don't know w
hy I didn't see it myself). I guess I am just too focused on my issue..., d
idn't see obvious.
2. that one can declare the second CTE and use in it the first one. I think
I read about it in the past, I just didn't think of using it. It replaces my
insert @.t temp table, i.e. making it virtual. Beautiful idea. I can also use
it in the subquery as well, i think.
3. I used ROW_RUMBER a number of times and used it in my original attempts b
ut I never considered RANK() function. It seems to be an original solution t
o use it, however it's faltering in the logic somehow. I adjusted my code wi
th your ideas. i decided to complicate it yet and added screws to more parts
making an assymbly "Screw" have more parents. That is where error came. For
example ScrewPaint instance should have a parent screw instance 1 but your
code makes it belong to instance 2 where as it belongs to instance 1 accordi
ng to the calculated path.
I am working on combination of 2 and my original code using calculated path.
I am not sure if your idea can still be taken advantage of. Do you see a wa
y?
In the nutshell here is a problem of converting BOM definition into a true t
ree, as very appropriately David Browny pointed out (prior post), encoding t
he tree. If each node where to be numbered, the component's parent IDs have
to point the right components.
Thank you for good ideas.
Vladimir Moldovanenko,
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
-- LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
,T as (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS itmIDInstan
ce,
RANK() OVER (PARTITION BY itmID ORDER BY Level) AS itmIDParentInstance
FROM itm_tree
)
-- Capture the intermedeate result set
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
, case when itmID is null then null else itmIDParentInstance end as itmIDPar
entInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P as ItemPath
, Level
FROM t
ORDER BY p
Original code below
-- Define recursion
WITH itm_tree (
CompitmID
--, itmcQty
, itmID
-- , itmIDInstance
, P
, Level
) as
(
SELECT
i.itmID
, ic.CompitmID
-- , cast(null as smallint) as itmIDInstance
, CONVERT(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
FROM @.Items i
LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
--, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY r.itmID ) as
smallint) as itmIDInstance
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
)
-- Capture the intermedeate result set
insert @.t
SELECT
CompitmID as ItmID
, cast(ROW_NUMBER() OVER ( PARTITION BY t.CompItmID ORDER BY t.CompItmID ) a
s smallint) as itmIDInstance
, itmID as itmIDParent
, SPACE(4*Level)+ CompitmID as CompTree
, P as Item
, NULLIF(left(P, len(p) -10),'') as ItemParent
, Level
-- ,(
-- SELECT itmIDInstance
-- FROM itm_tree t2
-- WHERE left(t2.P, len(t2.P) -10) = t.P
-- ) as itmIDInstance
FROM itm_tree t
ORDER BY p
-- result I am after
SELECT itmID, itmIDInstance, itmIDParent
,(
SELECT itmIDInstance
FROM @.t t2
WHERE t2.ItemPath = t.ItemPathParent
) as itmIDParentInstance
-- non- relevent columns
, Tree as CompTree, ItemPath, ItemPathParent, [Level]
FROM @.t t
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:%23qwjQ0lOGHA.1040@.TK2MSFTNGP12.phx.gbl...
Slightly more compact version:
WITH T AS
(
SELECT
CONVERT(varchar(10), NULL) AS itmID,
@.itmID AS compitmID,
0 as level
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message new
s:ed8TVplOGHA.2696@.TK2MSFTNGP14.phx.gbl...
Hi Farmer,
Interesting problem! I think the following does what you're looking for:
with T AS
(
SELECT
itmID,
compitmID,
1 as level
FROM @.itemcomponents
WHERE itmID = @.itmID
UNION ALL
SELECT
IC.itmID,
IC.compitmID,
T.level + 1 as level
FROM T
JOIN @.itemcomponents IC ON T.compitmID = IC.itmID
),
U AS
(
SELECT
NULL AS itmID,
@.itmID AS compitmID,
0 AS level,
1 AS compitmNum,
1 AS itmNum
UNION ALL
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY compitmID ORDER BY compitmID) AS compitmNum,
RANK() OVER (PARTITION BY itmID ORDER BY level) AS itmNum
FROM T
)
SELECT
compitmID AS itmID,
compitmNum AS itmIDInstance,
itmId AS itmIDParent,
itmNum AS parentIDInstance
FROM U
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||thank you so much David,
please see my other replies to Adam's posts. Your idea is along the idea of
calculated path I have used, it's just more compressed encoded path
calculation. I like it, it's neater. This data will end up in this table. I
will try to work on incorporating your idea and dual CTE declaration
suggested by Adam and I will post the results.
Thanks again for your idea.
For your better understanding this is a piece of actual structure where data
will end up to be. there are more fields in it, but relevand are here.
itmID is not varchar, just for test clarity it was made.
CREATE TABLE [dbo].[OrderItems](
[itmID] [int] NOT NULL,
[itmIDInstance] [smallint] NOT NULL,
[itmIDParent] [int] NULL,
[itmIDParentInstance] [smallint] NULL,
..
[olnID] [int] NOT NULL,
[olnIDInstance] [smallint] NOT NULL,
..
CONSTRAINT [PK_OrderItems_itmID_itmIDInstance_olnID
_olnIDInstance] PRIMARY
KEY NONCLUSTERED
(
[itmID] ASC,
[itmIDInstance] ASC,
[olnID] ASC,
[olnIDInstance] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderItems] WITH CHECK ADD CONSTRAINT
[FK_OrderItems_OrderItems]
FOREIGN KEY([itmIDParent], [itmIDParentInstance], [olnID], [olnIDInstance])
REFERENCES [dbo].[OrderItems] ([itmID], [itmIDInstance], [olnID],
[olnIDInstance])
GO
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OU%23dUolOGHA.3856@.TK2MSFTNGP12.phx.gbl...
>
> How about
>
> -- Define recursion
> WITH itm_tree (
> CompitmID
> --, itmcQty
> , itmID
> -- , itmIDInstance
> , P
> , Level
> , Instance
> ) as
> (
> SELECT
> i.itmID
> , ic.CompitmID
> -- , cast(null as smallint) as itmIDInstance
> , CONVERT(varchar(max), cast(i.itmID as char(10))) as P
> , 1 as Level
> , cast('0' as varchar(50))
> FROM @.Items i
> LEFT JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
> WHERE i.itmID = @.itmID
> UNION ALL
> SELECT
> ic.CompitmID
> , ic.itmID
> --, cast(ROW_NUMBER() OVER ( PARTITION BY ic.compitmID ORDER BY
> r.itmID ) as smallint) as itmIDInstance
> , CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
> , r.Level + 1 as Level
> , cast(r.Instance + '.' + cast(Row_Number() over (order by
> i.itmID) as varchar) as varchar(50))
> FROM @.Items i
> JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
> JOIN itm_tree r ON r.CompitmID = ic.itmID
> )
>
> returns
> ItmID itmIDInstance itmIDParent Instance CompTree
> -- -- -- -- --
-
> Cabinet 1 NULL 0 Cabinet
> Case 1 Cabinet 0.1 Case
> Door 1 Case 0.1.1 Door
> SPiece 1 Door 0.1.1.1 SPiece
> XPiece 1 Door 0.1.1.2 XPiece
> Screw 1 Case 0.1.2 Screw
> ScrewPaint 2 Screw 0.1.2.1
> ScrewPaint
> SideL 1 Case 0.1.3 SideL
> SideR 1 Case 0.1.4 SideR
> Screw 2 Cabinet 0.2 Screw
> ScrewPaint 1 Screw 0.2.1 ScrewPaint
>
> Which not only uniquely identifies each part, but also encodes where it's
> located.
> David
>
>|||Adam and David,
thank you for your help. I think I got it.
This is what I got so far.
any better ideas? David, i used my path calculation as in my db, I have itmI
D as integer. Using str(itmID, 10) to encode is making it easier to chop the
last (delimited by position) path element.
With your calculation, I need to search for a position of the past "." in th
e string. This may require an iterative function. ? Therefore I thought th
at WHERE s2.P = left(s1.P, len(s1.P) - 10) is simpler.
You may ask me why did I go with the structure I poasted. Well, my thinking
was that int+smallint is much smaller than varchar(max) ? for FK reference a
nd usage even if it is multi-key. For me it just made sense.
Thanks again. You are both very sharp.
any optimizations you see?
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
, P2
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, convert(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
, cast('0' as varchar(max)) P2
FROM @.Items i
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
, cast(r.P2 + '.' + cast(ROW_NUMBER() OVER (ORDER BY i.itmID) as varchar) a
s varchar(max))
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
),tmp AS
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY itm_tree.CompitmID ORDER BY itm_tree.Compit
mID) AS itmIDInstance
FROM itm_tree
)
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
,(
SELECT itmIDInstance
FROM tmp s2
WHERE s2.P = left(s1.P, len(s1.P) - 10)
) as itmIDParentInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P2
, P as ItemPath
, Level
FROM tmp s1
ORDER BY p2|||But then again. Know thy functions. David, your idea can also work.
It just appers that left(s1.P, len(s1.P) - 10) is simpler than
REVERSE(P2) once, then REVERSE(case when CHARINDEX( '.', RT) > 0 then substr
ing(RT, CHARINDEX( '.', RT) + 1, LEN(RT) - CHARINDEX( '.', RT)) end) to acco
mplish the same thing.
thanks
Vladimir
SET NOCOUNT ON;
DECLARE @.items table (itmID varchar(10) PRIMARY KEY CLUSTERED) -- this is ma
in Items table
-- now each item can have one or more components, defining an assembly
DECLARE @.itemComponents table (itmID varchar(10), compitmID varchar(10), itm
cQty dec (18,4), PRIMARY KEY CLUSTERED (itmID, compItmID));
-- this is just to limit which assembly to query
declare @.itmID varchar(10)
-- Items population
insert @.items values ('Cabinet')
insert @.items values ('Case')
insert @.items values ('SideL')
insert @.items values ('SideR')
insert @.items values ('Door')
insert @.items values ('XPiece')
insert @.items values ('SPiece')
insert @.items values ('Screw')
insert @.items values ('ScrewPaint')
--insert @.items values ('Dowel')
-- Assembly definition
insert @.itemcomponents values ('Cabinet','Case', 1)
insert @.itemcomponents values ('Case','SideL', 1)
insert @.itemcomponents values ('Case','SideR', 1)
insert @.itemcomponents values ('Case','Door', 2)
insert @.itemcomponents values ('Cabinet','Screw', 4)
insert @.itemcomponents values ('Case','Screw', 10)
insert @.itemcomponents values ('Door','Screw', 1)
insert @.itemcomponents values ('XPiece','Screw', 1)
insert @.itemcomponents values ('Door','XPiece', 2)
insert @.itemcomponents values ('Door','SPiece', 2)
insert @.itemcomponents values ('Screw','ScrewPaint', .0025)
-- Let us get it from the top
set @.itmID = 'Cabinet';
-- temp table structure to hold pre-processed results
declare @.t table (itmID varchar(10), itmIDInstance smallint, itmIDParent var
char(10), Tree varchar(1000)
, ItemPath varchar(1000), ItemPathParent varchar(1000), [Level] tinyint);
-- Define recursion
WITH itm_tree (
CompitmID
, itmID
, itmcQty
, P
, Level
, P2
) as
(
SELECT
i.itmID
, cast(NULL as varchar(10)) as CompitmID
, cast(NULL as dec(18,4)) as itmcQty
, convert(varchar(max), cast(i.itmID as char(10))) as P
, 1 as Level
, cast('0' as varchar(max)) P2
FROM @.Items i
WHERE i.itmID = @.itmID
UNION ALL
SELECT
ic.CompitmID
, ic.itmID
, ic.itmcQty
, CONVERT(varchar(max), r.P + cast(i.itmID as char(10)) ) as P
, r.Level + 1 as Level
, cast(r.P2 + '.' + cast(ROW_NUMBER() OVER (ORDER BY i.itmID) as varchar) a
s varchar(max))
FROM @.Items i
JOIN @.ItemComponents ic ON ic.CompitmID = i.itmID
JOIN itm_tree r ON r.CompitmID = ic.itmID
),tmp AS
(
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY itm_tree.CompitmID ORDER BY itm_tree.Compit
mID) AS itmIDInstance
,REVERSE(P2) as RT
FROM itm_tree
)
SELECT
CompitmID as ItmID
, itmIDInstance
, itmID as itmIDParent
,(
SELECT itmIDInstance
FROM tmp s2
WHERE s2.P = left(s1.P, len(s1.P) - 10)
) as itmIDParentInstance
--, itmcQty
, SPACE(4*Level)+ CompitmID as CompTree
, P2 Node
, REVERSE(case when CHARINDEX( '.', RT) > 0 then substring(RT, CHARINDEX( '.
', RT) + 1, LEN(RT) - CHARINDEX( '.', RT)) end) ParentNode
, P as ItemPath
, Level
FROM tmp s1
ORDER BY p2|||Get a copy of TREES & HIERATCHIES IN SQL and use the chapter on BOM.
You do not need recursion at all and your queries will run 1-2 orders
of magnitude faster.

Pls, help. TSQL way, Limit the select of a result set by type and quantity

/*
Thanks in advance for your time and knowledge you would share.
Definition:
I have actions, that are of certain type, and workload of these actions to
process
The request comes as a recordset in request table.
How do I produce a record set that has in it quantity of records less or
equal to request quantities by type
I know that I can open a cursor, use (set rowcount @.memvar) and loop to
accumulate final resultset.
I know that if I define an identity field (or select into temp table with
identity) and count these records by selfjoining
But is there a better non-temp table solution?
Thanks again for your ideas
*/
set nocount on
declare @.work table (orderID int, status int, actionID int, primary key
(orderid, status,actionID))
declare @.actions table (actionID int, actionType int, primary key
(actionID))
declare @.request table (actionType int, RecQty int)
declare @.temp table (rec int identity, actionType int , orderID int, status
int, actionID int)
insert @.request values (1,3) -- deliver at least 3 records of this type
insert @.request values (2,8) -- deliver at least 8 records of this type
insert @.request values (3,3) -- deliver at least 3 records of this type
insert @.actions values (1, 1)
insert @.actions values (2, 1)
insert @.actions values (3, 2)
insert @.actions values (4, 2)
insert @.actions values (5, 2)
insert @.actions values (6, 3)
insert @.work values (1, 1, 1)
insert @.work values (1, 1, 2)
insert @.work values (1, 1, 3)
insert @.work values (2, 1, 1)
insert @.work values (2, 1, 2)
insert @.work values (2, 2, 3)
insert @.work values (2, 2, 4)
insert @.work values (2, 1, 6)
insert @.work values (3, 1, 1)
insert @.work values (3, 3, 2)
insert @.work values (3, 1, 5)
insert @.work values (3, 1, 6)
insert @.work values (4, 1, 1)
insert @.work values (4, 1, 2)
insert @.work values (4, 1, 5)
insert @.work values (4, 1, 6)
insert @.work values (4, 2, 6)
insert @.work values (4, 3, 6)
-- Possible solution
-- Total records
select actionType, count(*) recordcount
from @.actions a
join @.work w on w.actionID= a.actionID
group by actionType
insert @.temp (actionType, orderID, status, actionID)
select a.actionType, orderID, status, w.actionID
from @.actions a
join @.work w on w.actionID= a.actionID
-- This is what I am after, but any way not to use temp table as this would
cause procedure recompiles and this is often called one.
select r.RecQty,s1.actionType, s1.orderID, s1.status, s1.actionID, count(*)
from @.temp s1
join @.temp s2 on s2.actionType = s1.actiontype and s1.rec >= s2.rec
join @.request r on r.actionType = s1.actionType
group by s1.actionType, s1.orderID, s1.status, s1.actionID, r.RecQty
having count(*) <= r.RecQtyTry that based on the example of the oubs database:
DECLARE @.RANK INT
SET @.rank = 5
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
HAVING count(*) < @.Rank
To be found on:
http://support.microsoft.com/defaul...b;en-us;Q186133
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
--
"Farmer" <someone@.somewhere.com> schrieb im Newsbeitrag
news:Of0pCjyUFHA.544@.TK2MSFTNGP15.phx.gbl...
> /*
> Thanks in advance for your time and knowledge you would share.
> Definition:
> I have actions, that are of certain type, and workload of these actions to
> process
> The request comes as a recordset in request table.
> How do I produce a record set that has in it quantity of records less or
> equal to request quantities by type
> I know that I can open a cursor, use (set rowcount @.memvar) and loop to
> accumulate final resultset.
> I know that if I define an identity field (or select into temp table with
> identity) and count these records by selfjoining
> But is there a better non-temp table solution?
> Thanks again for your ideas
> */
> set nocount on
> declare @.work table (orderID int, status int, actionID int, primary key
> (orderid, status,actionID))
> declare @.actions table (actionID int, actionType int, primary key
> (actionID))
> declare @.request table (actionType int, RecQty int)
> declare @.temp table (rec int identity, actionType int , orderID int,
> status int, actionID int)
> insert @.request values (1,3) -- deliver at least 3 records of this type
> insert @.request values (2,8) -- deliver at least 8 records of this type
> insert @.request values (3,3) -- deliver at least 3 records of this type
>
> insert @.actions values (1, 1)
> insert @.actions values (2, 1)
> insert @.actions values (3, 2)
> insert @.actions values (4, 2)
> insert @.actions values (5, 2)
> insert @.actions values (6, 3)
> insert @.work values (1, 1, 1)
> insert @.work values (1, 1, 2)
> insert @.work values (1, 1, 3)
> insert @.work values (2, 1, 1)
> insert @.work values (2, 1, 2)
> insert @.work values (2, 2, 3)
> insert @.work values (2, 2, 4)
> insert @.work values (2, 1, 6)
> insert @.work values (3, 1, 1)
> insert @.work values (3, 3, 2)
> insert @.work values (3, 1, 5)
> insert @.work values (3, 1, 6)
> insert @.work values (4, 1, 1)
> insert @.work values (4, 1, 2)
> insert @.work values (4, 1, 5)
> insert @.work values (4, 1, 6)
> insert @.work values (4, 2, 6)
> insert @.work values (4, 3, 6)
> -- Possible solution
> -- Total records
> select actionType, count(*) recordcount
> from @.actions a
> join @.work w on w.actionID= a.actionID
> group by actionType
> insert @.temp (actionType, orderID, status, actionID)
> select a.actionType, orderID, status, w.actionID
> from @.actions a
> join @.work w on w.actionID= a.actionID
> -- This is what I am after, but any way not to use temp table as this
> would cause procedure recompiles and this is often called one.
> select r.RecQty,s1.actionType, s1.orderID, s1.status, s1.actionID,
> count(*)
> from @.temp s1
> join @.temp s2 on s2.actionType = s1.actiontype and s1.rec >= s2.rec
> join @.request r on r.actionType = s1.actionType
> group by s1.actionType, s1.orderID, s1.status, s1.actionID, r.RecQty
> having count(*) <= r.RecQty
>

PLS! Help

Trying to take the result from a report on RS exported as XML via URLSyntax
Ex.
http://servername/ReportServer?/folder/reportName&customerName=customerName&rs:Command=Render&rs:Format=XML
This example renders a XML-document, trying to use Microsoft.XMLHTTP, ASP to
get the data from server.
Sample:
-set xml=server.createObject("Microsoft.XMLHTTP")
-xml.open "GET", XMLURL, false
-xml.send
-xml.responseText
No problems so far, the document is there.
Is there anyone out there who can tell me how to use XSLT serverside
together with above XML.
best regards
Kjell BrandesKjell,
You can assign XSLT using the Data Output tab (Data Transform field) on the
report properties.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Kjell Brandes" <kjell@.brandes.tv> wrote in message news:e2gMQDtqEHA.556@.
tk2msftngp13.phx.gbl...
> Trying to take the result from a report on RS exported as XML via
URLSyntax
> Ex.
>
http://servername/ReportServer?/folder/reportName&customerName=customerName&rs:Command=Render&rs:Format=XML
>
> This example renders a XML-document, trying to use Microsoft.XMLHTTP, ASP
to
> get the data from server.
> Sample:
> -set xml=server.createObject("Microsoft.XMLHTTP")
> -xml.open "GET", XMLURL, false
> -xml.send
> -xml.responseText
> No problems so far, the document is there.
> Is there anyone out there who can tell me how to use XSLT serverside
> together with above XML.
> best regards
> Kjell Brandes
>

Pls recommend Raid 10 stripe size for SQL Server

Is there a rough rule of thumb for the stripe size to use for SQL Server
2000?
I am going to reorganise our disks. Assume 4 disk raid 10 for log and two 6
disk raid 10 sets for data.
Environment fairly mixed. Probably more reads than writes but still plenty
of updates etc.
Controllers have 128Mb battery backed up cache.
Thanks
PaulUnless the controller manufacturer recommends otherwise specifically for SQL
server, go with their default stripe size. That is typically what the
hardware and firmware has been tuned to run. Any minor gains at the SQL
layer would likely be offset by a less than optimal setting at the
controller layer.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Is there a rough rule of thumb for the stripe size to use for SQL Server
> 2000?
> I am going to reorganise our disks. Assume 4 disk raid 10 for log and two
> 6 disk raid 10 sets for data.
> Environment fairly mixed. Probably more reads than writes but still plenty
> of updates etc.
> Controllers have 128Mb battery backed up cache.
> Thanks
> Paul
>|||Thanks Geoff.
I guess I was just wondering give the more random nature of database calls
and sequential nature of log I/O.
Does Sql read/write in a particular batch/block size eg 8K.
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Unless the controller manufacturer recommends otherwise specifically for
> SQL server, go with their default stripe size. That is typically what the
> hardware and firmware has been tuned to run. Any minor gains at the SQL
> layer would likely be offset by a less than optimal setting at the
> controller layer.
> Geoff N. Hiten
> Microsoft SQL Server MVP
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> Is there a rough rule of thumb for the stripe size to use for SQL Server
>> 2000?
>> I am going to reorganise our disks. Assume 4 disk raid 10 for log and two
>> 6 disk raid 10 sets for data.
>> Environment fairly mixed. Probably more reads than writes but still
>> plenty of updates etc.
>> Controllers have 128Mb battery backed up cache.
>> Thanks
>> Paul
>>
>|||Data pages are 8K. Extents (allocation units and read-ahead increments) are
64K. SQL servers tend to do a log more reading than writing. Sequential
data reads will probably be in 64K increments while random reads will be in
8K blocks. Log writes tend to be small and sequential, hence the
recommendation to separate logs and data onto separate spindle sets as you
have done. As I recommended before, let the controller work at its best
which is almost always the default settings.
Geoff N. Hiten
Microsoft SQL Server MVP
"Paul Cahill" <nospam@.hotmail.com> wrote in message
news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
> Thanks Geoff.
> I guess I was just wondering give the more random nature of database calls
> and sequential nature of log I/O.
> Does Sql read/write in a particular batch/block size eg 8K.
> Paul
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> Unless the controller manufacturer recommends otherwise specifically for
>> SQL server, go with their default stripe size. That is typically what
>> the hardware and firmware has been tuned to run. Any minor gains at the
>> SQL layer would likely be offset by a less than optimal setting at the
>> controller layer.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Paul Cahill" <nospam@.hotmail.com> wrote in message
>> news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> Is there a rough rule of thumb for the stripe size to use for SQL Server
>> 2000?
>> I am going to reorganise our disks. Assume 4 disk raid 10 for log and
>> two 6 disk raid 10 sets for data.
>> Environment fairly mixed. Probably more reads than writes but still
>> plenty of updates etc.
>> Controllers have 128Mb battery backed up cache.
>> Thanks
>> Paul
>>
>>
>|||It's a very rare opportunity to work on the system (24/7). Just wanted to do
all I could.
Thanks again
Paul
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:umcRsSbfFHA.2644@.TK2MSFTNGP09.phx.gbl...
> Data pages are 8K. Extents (allocation units and read-ahead increments)
> are 64K. SQL servers tend to do a log more reading than writing.
> Sequential data reads will probably be in 64K increments while random
> reads will be in 8K blocks. Log writes tend to be small and sequential,
> hence the recommendation to separate logs and data onto separate spindle
> sets as you have done. As I recommended before, let the controller work
> at its best which is almost always the default settings.
> Geoff N. Hiten
> Microsoft SQL Server MVP
>
> "Paul Cahill" <nospam@.hotmail.com> wrote in message
> news:uGblSNbfFHA.3616@.TK2MSFTNGP12.phx.gbl...
>> Thanks Geoff.
>> I guess I was just wondering give the more random nature of database
>> calls and sequential nature of log I/O.
>> Does Sql read/write in a particular batch/block size eg 8K.
>> Paul
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:OtSmaKbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> Unless the controller manufacturer recommends otherwise specifically for
>> SQL server, go with their default stripe size. That is typically what
>> the hardware and firmware has been tuned to run. Any minor gains at the
>> SQL layer would likely be offset by a less than optimal setting at the
>> controller layer.
>> Geoff N. Hiten
>> Microsoft SQL Server MVP
>> "Paul Cahill" <nospam@.hotmail.com> wrote in message
>> news:egvaRGbfFHA.3124@.TK2MSFTNGP12.phx.gbl...
>> Is there a rough rule of thumb for the stripe size to use for SQL
>> Server 2000?
>> I am going to reorganise our disks. Assume 4 disk raid 10 for log and
>> two 6 disk raid 10 sets for data.
>> Environment fairly mixed. Probably more reads than writes but still
>> plenty of updates etc.
>> Controllers have 128Mb battery backed up cache.
>> Thanks
>> Paul
>>
>>
>>
>

pls pls pls need help(Urgent)

hi ,
pls tel m how can i get the selected parameter multivalue to my designer textbox. i have tried like following

1) Parameters!BU.Label - error occured
2) Parameters!BU.value(0) - only first value
3) join(Parameters!BU.value,",") - return the uinquename like [Account dsc].[Business_Unit].&[1]

but i need to get the only selected values. not the uniquename, not the first one. if i select three values in the param it should display. pls help me on this.

You were almost there!!

="Business Units " & JOIN(Parameters!test.Label," ,")

Pls Help-2000 Replication

How do I know whether the record in a row was insert,update and delete that replicated to a subscriber?
Is there a flag or an indicator in msmerge_contents,msmerge_tombstone ,msmerge_genhistory tables or any other msdb/replication system tables?You would have to catch the commands before they got deleted out of Distribution database with the system stored procedure called sp_browsereplcmds. Or you can stop the distribution clean up job temporarily to track it down. The system stored procedure can only be executed in the Distribution database. You have to look for the Commands column and see what execute the command, sp_MSDel_, sp_MSIns_, or sp_MSUpd_.|||Hi Joej,

Is there a table that specifically store the flag a changes occured in Subscriber or Publisher database? If I stop the distribution clean up job, which tables can I refer to?
Thanks

Originally posted by joejcheng
You would have to catch the commands before they got deleted out of Distribution database with the system stored procedure called sp_browsereplcmds. Or you can stop the distribution clean up job temporarily to track it down. The system stored procedure can only be executed in the Distribution database. You have to look for the Commands column and see what execute the command, sp_MSDel_, sp_MSIns_, or sp_MSUpd_.|||Sory I left out a sentence. Let say, just to know whether the records in MSMERGE_CONTENTS and MSMERGE_TOMBSTONE has been send over to replicate on Publisher or Subscriber.

Thanks again!

Originally posted by eshl
Hi Joej,

Is there a table that specifically store the flag a changes occured in Subscriber or Publisher database? If I stop the distribution clean up job, which tables can I refer to?
Thanks|||msrepl_commnads and msrepl_transactions.|||I just tried to insert,update and delete, and I stop the Agent that clean the distribution database. I couldnt find any record in msrepl_commands and msrepl_transactions.

Originally posted by joejcheng
msrepl_commnads and msrepl_transactions.|||back to this issue, I could find the history record that stored the latest record that was merged in th publisher and subscriber. By using this, I will be able to know which record was generated and which wasnt. This is helpful in determining the records that was replicated or not yet.
I m looking into modifying the replication engine,MSSQL replication engine is flexible.

Originally posted by eshl
I just tried to insert,update and delete, and I stop the Agent that clean the distribution database. I couldnt find any record in msrepl_commands and msrepl_transactions.

Monday, March 12, 2012

PLS HELP:Merge cells - use cursor?

Hi,
I just wonder if someone could help me here:
Say, i have a table with one column: 'Name'
Name1
Name2
Name3
...
I need to get the string of "Name1, Name2, Name3, ..."
The only way i see is to use cursor - but i know cursors aren't the best way
if an alternative exists...
So is there a way to just use a select to perform that?
Thank you,
AndreyYes, I think you'll need to visit each row.
However instead of using a cursor, try using a table variable (excuse any
typos)
declare @.OutputList varchar(8000)
set @.OutputList=''
declare @.Rows integer
declare @.Val varchar(255)
declare @.I integer
declare @.T1 table (RowNum integer identity(1,1) primary key, RowData
varchar(255))
insert @.T1 select name from YourTable
set @.Rows = @.@.ROWCOUNT
if @.Rows<>0
begin
set @.I=0
while @.I<@.Rows
begin
set @.I=@.I+1
select @.Val=@.T1.RowData where @.T1.RowNum = @.I
set @.OutputList = @.OutputList + @.Val + ','
end
set @.OutputList = left(@.OutputList,Len(@.OutputList)-1) -- get rid of final
comma
end
....gtr
"MuZZy" wrote:

> Hi,
> I just wonder if someone could help me here:
> Say, i have a table with one column: 'Name'
> Name1
> Name2
> Name3
> ...
> I need to get the string of "Name1, Name2, Name3, ..."
> The only way i see is to use cursor - but i know cursors aren't the best w
ay if an alternative exists...
> So is there a way to just use a select to perform that?
> Thank you,
> Andrey
>|||Isn't that just a matter of formatting the output for display? So do it
in the client app, which is where presentation belongs and where it's
generally easier to do this kind of thing.
Otherwise, maybe something like this will help:
CREATE TABLE Foo (name VARCHAR(10) PRIMARY KEY)
SELECT
MAX(CASE ord WHEN 1 THEN name END)+
MAX(CASE ord WHEN 2 THEN ','+name ELSE '' END)+
MAX(CASE ord WHEN 3 THEN ','+name ELSE '' END)+
MAX(CASE ord WHEN 4 THEN ','+name ELSE '' END)+
MAX(CASE ord WHEN 5 THEN ','+name ELSE '' END)
/* repeat as required... */
FROM
(SELECT P.name, COUNT(*)
FROM Foo AS P,Foo AS Q
WHERE P.name >= Q.name
GROUP BY P.name) AS T(name,ord)
David Portas
SQL Server MVP
--

PLS help: SqlserverAgent service could not be started

Anyone can help me:
I get windows events like this:
"Could not start serveragent on local computer. The
service did not return an error. This could be an internal
windows error. If problem persists, contact your system
administrator"
SqlserverAgent error log:
[000]SQLServerAgent could not be started (reason:
SQLServerAgent must be able to connect to SQLServer as
SysAdmin, but '(Unknown)' is not a member of the SysAdmin
role)
[298]SQLServer error: 3621，statement stopped。 [SQLSTATE
01000] (ConnIsLoginSysAdmin)
[298] SQLServer error: 535，2 datetime columns'
difference made runtime overflow。 [SQLSTATE 22003]
(ConnIsLoginSysAdmin)
SqlserverAgent service start account set to administrator
and in SqlserverAgent connection properties, i have
tried "sql server connection" use sql server and NT
authentication, but both failed to start the service.
thanks a lot.> SqlserverAgent service start account set to administrator
Is this start account a member of he local administrators group?
What security authentication is the SQL instance configured to use? Is it
Windows or Mixed? I'd set the service startup account for SQLServerAgent to
the local system account, and try to start the SQL Agent service.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"brooks" <anonymous@.discussions.microsoft.com> wrote in message
news:0b0701c3a8c3$ed09aff0$a101280a@.phx.gbl...
> Anyone can help me:
> I get windows events like this:
> "Could not start serveragent on local computer. The
> service did not return an error. This could be an internal
> windows error. If problem persists, contact your system
> administrator"
> SqlserverAgent error log:
> [000]SQLServerAgent could not be started (reason:
> SQLServerAgent must be able to connect to SQLServer as
> SysAdmin, but '(Unknown)' is not a member of the SysAdmin
> role)
> [298]SQLServer error: 3621，statement stopped。 [SQLSTATE
> 01000] (ConnIsLoginSysAdmin)
> [298] SQLServer error: 535，2 datetime columns'
> difference made runtime overflow。 [SQLSTATE 22003]
> (ConnIsLoginSysAdmin)
> SqlserverAgent service start account set to administrator
> and in SqlserverAgent connection properties, i have
> tried "sql server connection" use sql server and NT
> authentication, but both failed to start the service.
> thanks a lot.|||> > SqlserverAgent service start account set to administrator
> Is this start account a member of he local administrators group?
Also, is the BUILTIN\Administraors windows account's Login in SQL Server
member of the sysadmin server role?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:eB44wpOqDHA.1496@.TK2MSFTNGP11.phx.gbl...
> > SqlserverAgent service start account set to administrator
> Is this start account a member of he local administrators group?
> What security authentication is the SQL instance configured to use? Is it
> Windows or Mixed? I'd set the service startup account for SQLServerAgent
to
> the local system account, and try to start the SQL Agent service.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "brooks" <anonymous@.discussions.microsoft.com> wrote in message
> news:0b0701c3a8c3$ed09aff0$a101280a@.phx.gbl...
> > Anyone can help me:
> >
> > I get windows events like this:
> > "Could not start serveragent on local computer. The
> > service did not return an error. This could be an internal
> > windows error. If problem persists, contact your system
> > administrator"
> >
> > SqlserverAgent error log:
> > [000]SQLServerAgent could not be started (reason:
> > SQLServerAgent must be able to connect to SQLServer as
> > SysAdmin, but '(Unknown)' is not a member of the SysAdmin
> > role)
> > [298]SQLServer error: 3621，statement stopped。 [SQLSTATE
> > 01000] (ConnIsLoginSysAdmin)
> > [298] SQLServer error: 535，2 datetime columns'
> > difference made runtime overflow。 [SQLSTATE 22003]
> > (ConnIsLoginSysAdmin)
> >
> > SqlserverAgent service start account set to administrator
> > and in SqlserverAgent connection properties, i have
> > tried "sql server connection" use sql server and NT
> > authentication, but both failed to start the service.
> >
> > thanks a lot.
>|||Thanks a lot for your warmhearted reply, i tried
administrator account, sa(in connection properties of
sqlserveragent service), and bulltin/administrators, the
started account both in the sysadmin group, but it can't
start the service.
sqlserver authentication set to mixed
>--Original Message--
>> SqlserverAgent service start account set to
administrator
>Is this start account a member of he local administrators
group?
>What security authentication is the SQL instance
configured to use? Is it
>Windows or Mixed? I'd set the service startup account for
SQLServerAgent to
>the local system account, and try to start the SQL Agent
service.
>--
>Linchi Shea
>linchi_shea@.NOSPAMml.com
>
>"brooks" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b0701c3a8c3$ed09aff0$a101280a@.phx.gbl...
>> Anyone can help me:
>> I get windows events like this:
>> "Could not start serveragent on local computer. The
>> service did not return an error. This could be an
internal
>> windows error. If problem persists, contact your system
>> administrator"
>> SqlserverAgent error log:
>> [000]SQLServerAgent could not be started (reason:
>> SQLServerAgent must be able to connect to SQLServer as
>> SysAdmin, but '(Unknown)' is not a member of the
SysAdmin
>> role)
>> [298]SQLServer error: 3621，statement stopped。
[SQLSTATE
>> 01000] (ConnIsLoginSysAdmin)
>> [298] SQLServer error: 535，2 datetime columns'
>> difference made runtime overflow。 [SQLSTATE 22003]
>> (ConnIsLoginSysAdmin)
>> SqlserverAgent service start account set to
administrator
>> and in SqlserverAgent connection properties, i have
>> tried "sql server connection" use sql server and NT
>> authentication, but both failed to start the service.
>> thanks a lot.
>
>.
>|||As I mentioned in my other post, you need to verify that the startup
account's login in SQL Server is member of the sysadmin server role.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"brooks" <brooks_xiao@.hotmail.com> wrote in message
news:1f4801c3a8fa$22560920$a601280a@.phx.gbl...
> Thanks a lot for your warmhearted reply, i tried
> administrator account, sa(in connection properties of
> sqlserveragent service), and bulltin/administrators, the
> started account both in the sysadmin group, but it can't
> start the service.
> sqlserver authentication set to mixed
> >--Original Message--
> >> SqlserverAgent service start account set to
> administrator
> >
> >Is this start account a member of he local administrators
> group?
> >
> >What security authentication is the SQL instance
> configured to use? Is it
> >Windows or Mixed? I'd set the service startup account for
> SQLServerAgent to
> >the local system account, and try to start the SQL Agent
> service.
> >
> >--
> >Linchi Shea
> >linchi_shea@.NOSPAMml.com
> >
> >
> >"brooks" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b0701c3a8c3$ed09aff0$a101280a@.phx.gbl...
> >> Anyone can help me:
> >>
> >> I get windows events like this:
> >> "Could not start serveragent on local computer. The
> >> service did not return an error. This could be an
> internal
> >> windows error. If problem persists, contact your system
> >> administrator"
> >>
> >> SqlserverAgent error log:
> >> [000]SQLServerAgent could not be started (reason:
> >> SQLServerAgent must be able to connect to SQLServer as
> >> SysAdmin, but '(Unknown)' is not a member of the
> SysAdmin
> >> role)
> >> [298]SQLServer error: 3621，statement stopped。
> [SQLSTATE
> >> 01000] (ConnIsLoginSysAdmin)
> >> [298] SQLServer error: 535，2 datetime columns'
> >> difference made runtime overflow。 [SQLSTATE 22003]
> >> (ConnIsLoginSysAdmin)
> >>
> >> SqlserverAgent service start account set to
> administrator
> >> and in SqlserverAgent connection properties, i have
> >> tried "sql server connection" use sql server and NT
> >> authentication, but both failed to start the service.
> >>
> >> thanks a lot.
> >
> >
> >.
> >|||Hi, Tibor Karaszi,thank you very much
I searched your articles in the group.sql...., but it
does not do any help, my problems is, i add a test account
(in NT administrators group), then add this to sql server
sysadmin group, but it could not make the agent service
start.
i think the following error messages is difference from
the others refered:
>> >> [298]SQLServer error: 3621，statement stopped。
>> [SQLSTATE
>> >> 01000] (ConnIsLoginSysAdmin)
>> >> [298] SQLServer error: 535，2 datetime columns'
>> >> difference made runtime overflow。 [SQLSTATE 22003]
>> >> (ConnIsLoginSysAdmin)
>> >>
thanks a lot for your help.|||You need to find out which Service Account Agent is using (EM, Management,
Properties).
Then you need to go to the security tab in SQL Server, logins, and for that
account (or a group that the account is a member of), edit it and on the
server roles tab, make it member of syadmins.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"brooks" <brooks_xiao@.hotmail.com> wrote in message
news:0e6501c3a8fd$49b3ac90$a101280a@.phx.gbl...
>
> Hi, Tibor Karaszi,thank you very much
> I searched your articles in the group.sql...., but it
> does not do any help, my problems is, i add a test account
> (in NT administrators group), then add this to sql server
> sysadmin group, but it could not make the agent service
> start.
> i think the following error messages is difference from
> the others refered:
> >> >> [298]SQLServer error: 3621，statement stopped。
> >> [SQLSTATE
> >> >> 01000] (ConnIsLoginSysAdmin)
> >> >> [298] SQLServer error: 535，2 datetime columns'
> >> >> difference made runtime overflow。 [SQLSTATE 22003]
> >> >> (ConnIsLoginSysAdmin)
> >> >>
> thanks a lot for your help.

Monday, March 26, 2012

Polish characters problem.

Polish characters problem.

Polish characters problem.

Tuesday, March 20, 2012

pls/sql help needed urgent.

pls..help me to find out the error in the code

PLS. HELP. SQL NEWBIE

Pls. help me optimize this stored procedure

Pls, help: Bill of Materials and SQL 2005 recursion problem

Pls, help. TSQL way, Limit the select of a result set by type and quantity

PLS! Help

Pls recommend Raid 10 stripe size for SQL Server

Pls recommend Raid 10 stripe size for SQL Server

Pls recommend Raid 10 stripe size for SQL Server

pls pls pls need help(Urgent)

Pls Help-2000 Replication

Monday, March 12, 2012

PLS HELP:Merge cells - use cursor?

PLS help: SqlserverAgent service could not be started

SQL compact edition

Blog Archive

About Me