FTS Index Size: level

Hello Experts,
Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

How do modify the code to work for any level rather than hard coding
the level up to "5"?
Please help.
Thanks.
SoumyaDip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. It
works fine.
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
>
How do modify the code to work for any level rather than hard coding
the level up to "5"?

If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.

If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

How do modify the code to work for any level rather than hard coding
the level up to "5"?

>
If this means that you would get a dynamic number of columns, then you
would need to construct the query dynamically.
>
If you want set absolute maximum of, say, 20, but don't want to repeat the
above over and over, you could use a recursive Common Table Expression if
you are on SQL 2005.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||>Here is the code to flatten a PC hierarchy into a level based table. <<

I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.

Quote:

Originally Posted by

Quote:

Originally Posted by

>How do modify the code to work for any level rather than hard coding the level up to "5"? <<

One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.

A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.

The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.

You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.|||Hi Celko,
Thanks for your input.
The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5

The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!
Any help is greatly appriciated.
Thanks.
Soumya

--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Here is the code to flatten a PC hierarchy into a level based table. <<

>
I am not sure what a "level based table" is and you did not bother to
post DDL. I am guessing you mean that you have an adjacency list model
for your hierarchy.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

How do modify the code to work for any level rather than hard coding the level up to "5"? <<

>
One kludge is dynamic SQL. A table BY DEFINITION has a fixed number of
columns.
>
A seocnd kludge is a recursive CTE (watch for cycles!!) that builds a
concatenated string.
>
The right answer is that display is done in the front end and never in
the back end in a tiered archtiecture.
>
You might also want to get a copy of TREES & HIERARCHIES IN SQL for
toher ways to model these problems.

|||Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

The code that I have currently working is this:
SELECT
t1.TASK_ID AS TASK_LV1,
t2.TASK_ID AS TASK_LV2,
t3.TASK_ID AS TASK_LV3,
t4.TASK_ID AS TASK_LV4,
t5.TASK_ID AS TASK_LV5
FROM dbo.Project t1 LEFT OUTER JOIN
dbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_ID
AND t2.WBS_LEVEL = 2 LEFT OUTER JOIN
dbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_ID
AND t3.WBS_LEVEL = 3 LEFT OUTER JOIN
dbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_ID
AND t4.WBS_LEVEL = 4 LEFT OUTER JOIN
dbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_ID
AND t5.WBS_LEVEL = 5
>
The table Project has "Task_ID, "Parent_ID", "Task_Name",and
"WBS_Level" under Parent Child Adjacent hierarchy. I need to flat this
model into levels. The code above is working by hard coding "WBS_Level"
as "5" since I have only 5 levels so far but it can go upto 10 or 15
levels. I am using SQL Server 2000 with SP4. Is there anyway converting
this code for any levels, which also means it has to generate columns
dynamically. I am struck and tried many ways but no ciger!

You need to retrieve the current max level, and then construct the
query dynamically according to this. This can be done in client
code or in T-SQL. For information about dyamic SQL from T-SQL see
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?
Regards,
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

>
You need to retrieve the current max level, and then construct the
query dynamically according to this. This can be done in client
code or in T-SQL. For information about dyamic SQL from T-SQL see
http://www.sommarskog.se/dynamic_sql.html.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

WBS_LEVEL would be, in this situation, 5 but it could go for any number
in future when all divisions would start using Project Module. They can
have any depth of tasks allocated for a project.
To me, it's appearing a bit more complex than I initially thought. How
do I construct the self joins for each level dynamically?
Has anyone had done this before? Any sample code is available suitable
to this scenario?

Did you even look at the article I posted the link to?

What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.

It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)

I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.

What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,
I actually printed out your article and went through it. It is actually
very well written and covers all general situations, however, I
didn't have much luck constructing the Dynamic SQL to generate
"possible" columns and add each "LEFT OUTER JOIN" for each
level. Even if I break it down to two SQL Text, I would still need to
tell it to add 10 columns for each level for example and 9 LEFT OUTER
JOINs to break the Parent Child Adjacent model if WBS_LEVEL is 10 for
instance.

I have designed Stored Proc with Dynamic SQL in it but I haven't done
anything like this one before. Either it is silly simple or I just
can't get my head around to it.

I don't think any literature would help me to solve this problem but
some actual code that relates to this issue.
Thanks for all help.
Soumya

Erland Sommarskog wrote:

Quote:

Originally Posted by

Dip (soumyadip.bhattacharya@.gmail.com) writes:

Quote:

Originally Posted by

>
Did you even look at the article I posted the link to?
>
What you need to do is:
1) Get the current MAX value of WBS_LEVEL from Projects.
2) Initiate two SQL Strings to "SELECT t1.TASK_ID AS TASK_LV1" and
"FROM dbo.Project t1".
3) Loop from 2 to the MAX or WBS_LEVEL and add the column and the
join condition to respective strings.
4) Execute the SQL string.
>
It's a plain applicaiton of dynamic SQL, and the newsgroups for SQL Server
are full of samples with dynamic SQL, even if not for this precise problem.
(The most reason there are some many samples, is because people often mess
up when they work with dynamic SQL and ask for help.)
>
I purposely did not include any sample code, because there is not really
any reason to build the string in T-SQL, even if it's possible. It may
be better to do this client-side, as client-side languages are better on
string manipulation.
>
What's important to understand is that a given query, always returns a
fixed set a columns. This is why you have to use dynamic SQL.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

What is involved in setting up a SQL Server with IIS where SQL Server is behind the firewall. I'm interested in high level configuration not codingWhat is involved in setting up a SQL Server with IIS where SQL Server is behind the firewall. I'm interested in high level configuration not coding

It's pretty straightforward (unless I am totally missing the point of your question). Your web server sits in a DMZ (an isolated network segment with internet connectivity). All traffic to/from this DMZ is filtered/blocked by a firewall. You then add a rule to the firewall to permit your web server to talk to the database server (usually this means permitting traffic over port 1433 from the web server to the database server).

Most firewalls are smart enough to handle the port negotiation process (whereby SQL Server accepts the incoming port 1433 and "hands-off" the connection to a mutually negotiated port). However, in some cases you will have to define additional ports to allow for this negotiation.

You would also be advised to put your DMZ behind a separate firewall blocking incoming traffic from the internet (eg. allowing only port 80 connections to your web server).

Finally, you may need to define an additional rule on the firewall between your internal network and the DMZ which would allow management of the IIS server (eg. allow port 3389 - Terminal Services - from your internal network).

Regards,

hmscott|||your reply was clear and helpful. This leads me to another question.

If say the IIS server gets hacked and the attacker gains control of the IIS server, can you comment on possible ways (if any) to get into the SQL Server?

Assuming:
- IIS code will access via application role
- Application role only has access to intended Stored Procedures
- Stored Procedures used by Application to Access SQL Server

Again looking for high level information.

For example I hear the term sql injection used sometimes which granting access via stored procedures should aleviate.|||Sql Injection is most often due to inappropriate use of parameters in urls or javascript/form variables. Sometimes the application just appends and executes data.

ex. select * from table where x = <some valuefrom the client>

If some one replaces the expected value (like id='fred') with ''fred';drop table sysobjects; commit'

You might get issues :>). If you expect a value, make sure it fits.

SP's are a good start, but if don't validate the values coiming back from a client, you can still get in trouble.

Obviously, you shouldn't be connecting as someone who can do real damage from an application. Assuming you validate the SQL, keep you systems patched, you should be ok. Note that the process for securing an application is NOT this simple, and every situation can have a 'gotcha' depending on what exactly it does. The principle Network and Application architects need to know exactly what they are doing. A forum post doesn't cover it.|||Your question and your assumptions lead in different directions. One thread is if the server is taken over, what exposure does your database have to a hacker? and the second thread is how do I prevent SQL injection attacks (which don't require the hacker to gain control over the server)?

For the first, don't store unencrypted passwords on the web server. Consider using COM+ objects as a "middle layer" on a separate server to access the database. Instantiate the COM+ objects from the IIS server. It's a pain, but it works. Mind you, I think they're called something else in ASP.NET, but I have no real experience in ASP.NET.

For the second case, you have to follow the advice someone else posted in another thread:

1. Validate user input
2. Check user input
3. Validate user input
4. Check user input
5. Validate user input

Validate. Rinse. Repeat.

I might suggest picking a copy of Michael Howard's "Designing Secure Web Based Applications". It's geared towards ASP and not ASP.NET (if I recall correctly), but it's got some really good material.

Regards,

hmscott

2012年3月29日星期四

Floating point exception

float vs decimal

2012年3月27日星期二

Flattening Parent Child, an issue, please help

2012年2月24日星期五

Firewalls and SQL Server

FTS Index Size

Blog Archive

About Me