hooking into sql server file I/O -- file level encryption

There are a number of third party packages that do "whole file"
encryption of SQL databases. For example:
Encryptionizer (http://www.netlib.com/)
XP_CRYPT (http://www.xpcrypt.com/)
We are considering using one of these products, but I am wondering if
it is possible to "roll our own". We have some experience doing this
with sqlite; because sqlite is open source, it is relatively easy to
hook into the "pager" and perform custom encryption/decryption.
What I'd like to know is whether something similar is possible with
SQL Server (or more specifically MSDE). Or rather, clearly it is
_possible_, since the above products do it. But how? How do these
products insert themselves between the database engine and the
physical file I/O?
I speculate that they are re-implementing some file IO interfaces used
by SQL, but I don't know where to start looking for what those
interfaces might be or where they live in the registry. Or maybe I am
off base and there are some hooks provided by SQL I don't know about.
I have scoured MSDN and Google but it appears to be a difficult topic
to query.
Does anyone have any idea how this works?
TIA,
Rol(rolandp66@.yahoo.com) writes:
> There are a number of third party packages that do "whole file"
> encryption of SQL databases. For example:
> Encryptionizer (http://www.netlib.com/)
> XP_CRYPT (http://www.xpcrypt.com/)
> We are considering using one of these products, but I am wondering if
> it is possible to "roll our own". We have some experience doing this
> with sqlite; because sqlite is open source, it is relatively easy to
> hook into the "pager" and perform custom encryption/decryption.
> What I'd like to know is whether something similar is possible with
> SQL Server (or more specifically MSDE). Or rather, clearly it is
> _possible_, since the above products do it. But how? How do these
> products insert themselves between the database engine and the
> physical file I/O?
> I speculate that they are re-implementing some file IO interfaces used
> by SQL, but I don't know where to start looking for what those
> interfaces might be or where they live in the registry. Or maybe I am
> off base and there are some hooks provided by SQL I don't know about.
> I have scoured MSDN and Google but it appears to be a difficult topic
> to query.
The way to do it would indeed be to intercept the file I/O system calls.
Since SQL Server uses the interfaces in NTFS, the tool would have to
be configured to only encrypt selected files, since else you would be
encrypting - and deenscrypting - the entire disk.
Whether this actually is doable I don't know. You would have to ask in
a Windows newsgroup to get some tips on that. But I would assume that
whatever you do, it would be entirely unsupported.
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|||I may be missing something basic here but wouldn't it be a lot easier to
just use NTFS encryption to encrypt the files? One checkbox in the file
attributes seems a lot easier than writing your own disk driver.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<rolandp66@.yahoo.com> wrote in message
news:1175719135.450235.272090@.b75g2000hsg.googlegroups.com...
> There are a number of third party packages that do "whole file"
> encryption of SQL databases. For example:
> Encryptionizer (http://www.netlib.com/)
> XP_CRYPT (http://www.xpcrypt.com/)
> We are considering using one of these products, but I am wondering if
> it is possible to "roll our own". We have some experience doing this
> with sqlite; because sqlite is open source, it is relatively easy to
> hook into the "pager" and perform custom encryption/decryption.
> What I'd like to know is whether something similar is possible with
> SQL Server (or more specifically MSDE). Or rather, clearly it is
> _possible_, since the above products do it. But how? How do these
> products insert themselves between the database engine and the
> physical file I/O?
> I speculate that they are re-implementing some file IO interfaces used
> by SQL, but I don't know where to start looking for what those
> interfaces might be or where they live in the registry. Or maybe I am
> off base and there are some hooks provided by SQL I don't know about.
> I have scoured MSDN and Google but it appears to be a difficult topic
> to query.
> Does anyone have any idea how this works?
> TIA,
> Rol
>|||Roger Wolter[MSFT] (rwolter@.online.microsoft.com) writes:
> I may be missing something basic here but wouldn't it be a lot easier to
> just use NTFS encryption to encrypt the files? One checkbox in the file
> attributes seems a lot easier than writing your own disk driver.
Just for fun, I stopped my SQL 2000 instance and then encrypted the
Northwind database from Explorer. When I started SQL Server, I found that
I was not able to access Northwind. This was what the SQL Server had to
tell me:
2007-04-07 11:10:32.17 spid9 Device activation error. The physical file
name 'E:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf' may be
incorrect.
2007-04-07 11:10:32.31 spid9 udopen: Operating system error 5(error not
found) during the creation/opening of physical device E:\Program Files\Micro
soft SQL Server\MSSQL\data\northwnd.ldf.
2007-04-07 11:10:32.31 spid9 FCB::Open failed: Could not open device E:\
Program Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf for virtual devic
e number (VDN) 2.
Now, I know zero about NTFS encryption, so there may be a simple solution,
but I figured that I should make this note, in case someone would get the
idea to try this for real.
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|||The key is associated with the user who does the encrypting so if you're not
encrypting while logged in as the domain user that sqlservr.exe executes as,
SQL won't be able to read the file.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns990B72C517060Yazorman@.127.0.0.1...
> Roger Wolter[MSFT] (rwolter@.online.microsoft.com) writes:
> Just for fun, I stopped my SQL 2000 instance and then encrypted the
> Northwind database from Explorer. When I started SQL Server, I found that
> I was not able to access Northwind. This was what the SQL Server had to
> tell me:
> 2007-04-07 11:10:32.17 spid9 Device activation error. The physical
> file name 'E:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.mdf'
> may be incorrect.
> 2007-04-07 11:10:32.31 spid9 udopen: Operating system error 5(error
> not found) during the creation/opening of physical device E:\Program
> Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf.
> 2007-04-07 11:10:32.31 spid9 FCB::Open failed: Could not open device
> E:\Program Files\Microsoft SQL Server\MSSQL\data\northwnd.ldf for virtual
> device number (VDN) 2.
>
> Now, I know zero about NTFS encryption, so there may be a simple solution,
> but I figured that I should make this note, in case someone would get the
> idea to try this for real.
>
> --
> 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|||Roger Wolter[MSFT] (rwolter@.online.microsoft.com) writes:
> The key is associated with the user who does the encrypting so if you're
> not encrypting while logged in as the domain user that sqlservr.exe
> executes as, SQL won't be able to read the file.
I suspected it was something like that, but I didn't feel like testing it.
Thanks for straighten it out, Roger.
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.mspxsql

History Logging

How does the historyverboselevel and transactionsperhistory correlate ? If i
change the historyverbose level to 3 for the distribution agent profile, do
i also need to increase the transactionsperhistory ? I dont follow what the
transactionperhistory is.
Also the agents can log to output file based upon
http://support.microsoft.com/default...;en-us;312292. Is this
similar to changing the historyverboselevel to 3 or are the outputs totally
different between what is captured in the output file and setting it to 3
I am trying to gather some info about certain latency issues that we are
seeing between the time the record is inserted on the publisher and the time
it shows up on the subscriber and wanted to capture some logs.
Is this the right way ?
HistoryVerboseLevel refers to how much history is logged, 0 means none,
1 means only the current history or status is logged, overwriting any
past history, 2 is verbose, 3 is even more verbose and is used for
debugging.
TransactionsPerHistory controls how often the history is logged. So if
you have a value of 100 for TransactionsPerHistory you will not get any
history logged until your distribution agent stops or your distribution
agent distributes 100 transactions.
Hassan wrote:
> How does the historyverboselevel and transactionsperhistory correlate ? If i
> change the historyverbose level to 3 for the distribution agent profile, do
> i also need to increase the transactionsperhistory ? I dont follow what the
> transactionperhistory is.
> Also the agents can log to output file based upon
> http://support.microsoft.com/default...;en-us;312292. Is this
> similar to changing the historyverboselevel to 3 or are the outputs totally
> different between what is captured in the output file and setting it to 3
> I am trying to gather some info about certain latency issues that we are
> seeing between the time the record is inserted on the publisher and the time
> it shows up on the subscriber and wanted to capture some logs.
> Is this the right way ?
>
>
|||What about the output file ? Is that a different kinda logging of history ?
Do i need to set the verbose to 2 or 3 and also specify the output file ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u%23hHynKPFHA.3388@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> HistoryVerboseLevel refers to how much history is logged, 0 means none,
> 1 means only the current history or status is logged, overwriting any
> past history, 2 is verbose, 3 is even more verbose and is used for
> debugging.
> TransactionsPerHistory controls how often the history is logged. So if
> you have a value of 100 for TransactionsPerHistory you will not get any
> history logged until your distribution agent stops or your distribution
> agent distributes 100 transactions.
> Hassan wrote:
If i[vbcol=seagreen]
do[vbcol=seagreen]
the[vbcol=seagreen]
totally[vbcol=seagreen]
3[vbcol=seagreen]
time[vbcol=seagreen]
|||the history is the history of your run, ie 10 transactions, and 20
commands. The output is far more detail. You can see the history in your
agent details. Output can only be read when it is piped to an output
file. You need to set OutputVerboseLevel to read these output statements.
Hassan wrote:
> What about the output file ? Is that a different kinda logging of history ?
> Do i need to set the verbose to 2 or 3 and also specify the output file ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u%23hHynKPFHA.3388@.TK2MSFTNGP10.phx.gbl...
>
> If i
>
> do
>
> the
>
> totally
>
> 3
>
> time
>
>

Higher level of fragmentation and data corruption.

CAN maintaining higher level of fragmentation result in data corruption over
time? OR they are totally unrelated.
I would like to know because rebuilding indexes take resources and that is
an issue for a 24 x 7 environment. In other words, should we keep data device
safely fragmented for sometime if we have some hard disk space available that
allows us to maintain some abnormal growth, allowing us not to rebuild
indexes?
Can someone with SQL Server Internal Data Storage Knowledge please reply?
Also, please let me know if my question is not very clear enough and you
need further explanation.
Regards,
MZeeshan
Fragmentation in and of itself should not cause data corruption. Most
corruption these days is due to hardware issues and not the database itself.
I don't get what you are stating about the extra space and abnormal growth.
Are you staying you have lots of page splits and that is causing your data
size to increase? If so you do not have the clustered index on the right
column or you need to adjust your fill factor.
Andrew J. Kelly SQL MVP
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
> over
> time? OR they are totally unrelated.
> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
> device
> safely fragmented for sometime if we have some hard disk space available
> that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan
|||Inline...
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
over
> time? OR they are totally unrelated.
>
Absolutely not - they are unrelated. Corruption is caused by hardware
problems (and highly-infrequent bugs in various layers of software and
firmware, obviously)

> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
device
> safely fragmented for sometime if we have some hard disk space available
that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
I'm your man. What are you trying to achieve? Minimal space usage, minimal
perf drop from disk/index fragmentation? Are you correlating a perf drop
with increasing fragmentation, or are you just worried about it?

> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan

Higher level of fragmentation and data corruption.

CAN maintaining higher level of fragmentation result in data corruption over
time? OR they are totally unrelated.
I would like to know because rebuilding indexes take resources and that is
an issue for a 24 x 7 environment. In other words, should we keep data device
safely fragmented for sometime if we have some hard disk space available that
allows us to maintain some abnormal growth, allowing us not to rebuild
indexes?
Can someone with SQL Server Internal Data Storage Knowledge please reply?
Also, please let me know if my question is not very clear enough and you
need further explanation.
--
Regards,
MZeeshanFragmentation in and of itself should not cause data corruption. Most
corruption these days is due to hardware issues and not the database itself.
I don't get what you are stating about the extra space and abnormal growth.
Are you staying you have lots of page splits and that is causing your data
size to increase? If so you do not have the clustered index on the right
column or you need to adjust your fill factor.
--
Andrew J. Kelly SQL MVP
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
> over
> time? OR they are totally unrelated.
> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
> device
> safely fragmented for sometime if we have some hard disk space available
> that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan|||Inline...
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
over
> time? OR they are totally unrelated.
>
Absolutely not - they are unrelated. Corruption is caused by hardware
problems (and highly-infrequent bugs in various layers of software and
firmware, obviously)
> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
device
> safely fragmented for sometime if we have some hard disk space available
that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
I'm your man. What are you trying to achieve? Minimal space usage, minimal
perf drop from disk/index fragmentation? Are you correlating a perf drop
with increasing fragmentation, or are you just worried about it?
> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan

Higher level of fragmentation and data corruption.

CAN maintaining higher level of fragmentation result in data corruption over
time? OR they are totally unrelated.
I would like to know because rebuilding indexes take resources and that is
an issue for a 24 x 7 environment. In other words, should we keep data devic
e
safely fragmented for sometime if we have some hard disk space available tha
t
allows us to maintain some abnormal growth, allowing us not to rebuild
indexes?
Can someone with SQL Server Internal Data Storage Knowledge please reply?
Also, please let me know if my question is not very clear enough and you
need further explanation.
Regards,
MZeeshanFragmentation in and of itself should not cause data corruption. Most
corruption these days is due to hardware issues and not the database itself.
I don't get what you are stating about the extra space and abnormal growth.
Are you staying you have lots of page splits and that is causing your data
size to increase? If so you do not have the clustered index on the right
column or you need to adjust your fill factor.
Andrew J. Kelly SQL MVP
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
> over
> time? OR they are totally unrelated.
> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
> device
> safely fragmented for sometime if we have some hard disk space available
> that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan|||Inline...
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:DEC26292-C91A-4DD6-B001-E362FC320E63@.microsoft.com...
> CAN maintaining higher level of fragmentation result in data corruption
over
> time? OR they are totally unrelated.
>
Absolutely not - they are unrelated. Corruption is caused by hardware
problems (and highly-infrequent bugs in various layers of software and
firmware, obviously)

> I would like to know because rebuilding indexes take resources and that is
> an issue for a 24 x 7 environment. In other words, should we keep data
device
> safely fragmented for sometime if we have some hard disk space available
that
> allows us to maintain some abnormal growth, allowing us not to rebuild
> indexes?
> Can someone with SQL Server Internal Data Storage Knowledge please reply?
I'm your man. What are you trying to achieve? Minimal space usage, minimal
perf drop from disk/index fragmentation? Are you correlating a perf drop
with increasing fragmentation, or are you just worried about it?

> Also, please let me know if my question is not very clear enough and you
> need further explanation.
> --
> Regards,
> MZeeshan

Higher Level Edition Error Message

Hello all,

I just installed the June CTP Client Tools on a fresh hard drive (no previous CTPs installed). I am trying to run a package that was written in the April CTP and runs successfully on other computers with the June CTP installed.

When I run the package, it fails on an 'Execute Package Task', and I get the error message:
"The task cannot run on this edition of Integration Services. It requires a higher level edition."

I know the package is okay because it runs on other June CTP computers. Did I miss something in my install? Any suggestions?

Thanks for your time,
Jessica
Did you install SSIS on the PC in question or just tools. If just the tools then that is your problem as you must install SSIS in order to run packages outside of the designer. As a workaround you can set the execute package task to run in process instead of out of process and that will also allow the package to run. We have changed the default from out of process to in process for the next CTP to try and mitigate this issue somewhat, although running outside of the designer still won't work but that is by design.

HTH,|||That was the problem!

Thanks!
Jessica

higher compatibility level already exists

I am attempting to create a merge publication from SQL 2005 to SQL
2000. I am checking the 2005 & 2000 boxes for compatability, but
receive this message.
Publication 'edi' cannot be added to database 'EDI', because a
publication with a higher compatibility level already exists. All
merge publications in a database must have the same compatibiliy
level.
Publication 'edi' does not exist.
Changed database context to 'EDI'. (Microsoft SQL Server, Error:
21528)
I am creating the publications on the colo server (2005). The
publications will be filtered (one for each location). The main
office & all locations will be subscribers. All stores are running
SQL 2000 & the colo & main office SQL 2005.
AHIA,
Larry...
Hi Larry
I think what has happened here is that you have already created a
publication with a higher compatibility level in this database (i.e. SQL
2005). You can no longer create publications for a lower compatibility level
now.
IIRC this depends on specific settings, and it could be a single article, or
setting - which is preventing down-level compatibility publications.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1185461541.304316.20350@.l70g2000hse.googlegro ups.com...
>I am attempting to create a merge publication from SQL 2005 to SQL
> 2000. I am checking the 2005 & 2000 boxes for compatability, but
> receive this message.
> Publication 'edi' cannot be added to database 'EDI', because a
> publication with a higher compatibility level already exists. All
> merge publications in a database must have the same compatibiliy
> level.
> Publication 'edi' does not exist.
> Changed database context to 'EDI'. (Microsoft SQL Server, Error:
> 21528)
> I am creating the publications on the colo server (2005). The
> publications will be filtered (one for each location). The main
> office & all locations will be subscribers. All stores are running
> SQL 2000 & the colo & main office SQL 2005.
> AHIA,
> Larry...
>
|||Thanks Hilary...
sql

High buffer cache hit ration but low page life expectancy

I've noticed that I have a buffer cache hit ratio consistently around
99% but a page life expectancy chronically below the 300 second level.
I'm wondering how this is possible? Presumably the page life
expectancy is low because the buffer pool needs to clear out pages to
make room for new pages but why would it need to do this if the cache
hit ratio is around 100%?
ThanksHow about the read-ahead manager putting asked for data into ram (thus
forcing out 'old' data) before it is actually needed by various SELECT
statements? Also buffer cache hit ratio and page life expectancy could be
calculated on different time schedules internally which could account for
their apparent disparity.
--
TheSQLGuru
President
Indicium Resources, Inc.
<pshroads@.gmail.com> wrote in message
news:1177626752.644290.325550@.s33g2000prh.googlegroups.com...
> I've noticed that I have a buffer cache hit ratio consistently around
> 99% but a page life expectancy chronically below the 300 second level.
> I'm wondering how this is possible? Presumably the page life
> expectancy is low because the buffer pool needs to clear out pages to
> make room for new pages but why would it need to do this if the cache
> hit ratio is around 100%?
> Thanks
>|||Hi
"pshroads@.gmail.com" wrote:
> I've noticed that I have a buffer cache hit ratio consistently around
> 99% but a page life expectancy chronically below the 300 second level.
> I'm wondering how this is possible? Presumably the page life
> expectancy is low because the buffer pool needs to clear out pages to
> make room for new pages but why would it need to do this if the cache
> hit ratio is around 100%?
> Thanks
>
Check out
http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
A low page life expectancy with a high checkpoint pages/sec and lazy
write/sec values would indicate memory pressure. Also check for missing
indexes.
John

Hierarchichal query in SQL Server 2000

Hi All,

How to implement a hierarchical query in SQL Server 2000.

Example ; I have an Oracle Query as below

SELECT LEVEL,employee_id, manager_id, first_name, last_name
FROM employee
START WITH employee_id = 1
CONNECT BY prior employee_id = manager_id;

I need to get the equivalent of this query in SQL Server 2000 .

In SQL Server 2005 i can achieve this using COMMON TABLE EXPRESSION .

Is there any way to implement this in SQL Server 2000Is there any way to implement this in SQL Server 2000not easily, no

if there is some maximum number of levels to the hierarchy (e.g. never more than eleven levels deep, from top executive to lowliest peon), then you can write a query with that number of LEFT OUTER JOINs|||You can do this efficiently using a loop. Check out this link, and then I will answer any other questions you have:
http://sqlblindman.googlepages.com/returningchildrecords|||Hi Blindman,

I am unable to open the link u provided .|||http://sqlblindman.googlepages.com/returningchildrecords
Are you getting an error?|||no error, url works just fine|||Here is a little tutorial i wrote some time ago that gives you what you want.

http://vbforums.com/showthread.php?t=366078

Hierarchical Top Level BOM Query

The following query gives me some top level and some subassembly levels
returned because they are at the same level in the hierarchy. How can I get
it to keep selecting the subassembly levels until all returns are top level?
SELECT DISTINCT TOP 100 PERCENT Parent_Pfl.PflNumber AS ParentPfl,
Child_Pfl.PflNumber AS ChildProfile
FROM (SELECT ParentPflHierarchy.ParentPflID,
ChildPflHierarchy.ChildPflID
FROM dbo.PflHierarchy ChildPflHierarchy INNER JOIN
dbo.PflHierarchy ParentPflHierarchy ON
ChildPflHierarchy.ParentPflID = ParentPflHierarchy.ChildPflID) DERIVEDTBL
INNER JOIN
dbo.Pfl Parent_Pfl ON DERIVEDTBL.ParentPflID =
Parent_Pfl.PflID INNER JOIN
dbo.Pfl Child_Pfl ON DERIVEDTBL.ChildPflID =
Child_Pfl.PflID
WHERE (Child_Pfl.PflNumber = N'28699')
ORDER BY Parent_Pfl.PflNumber
PflHierarchy Table
PflHierarchyID ParentPflID ChildPflID
1 1 2
2 1 3
3 1 4
4 1 5
5 2 6
6 2 7
7 6 8
8 6 9
9 6 10
Pfl Table
PflID Description
1 This is a top level assy
2 This is a Sub assy
3 This is a child level for a part
4 This is a child level for a part
5 This is a child level for a part
6 This is a Sub assy
7 This is a child level for a part
8 This is a child level for a part
9 This is a child level for a part
10 This is a child level for a partGet a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
nested sets model. It will run at least an order of magnitude faster
and let you do hierarchical summaries in a single query.|||I ordered TREES & HIERARCHIES IN SQL, along with Joe Celko's SQL for
Smarties: Advanced SQL Programming, but they won't be here until Tuesday fro
m
Amazon.com. There's none available in any local stores.
One problem is I'm working with data imported from an SQL Anywhere database,
and I can't make any chages to the tables because they will be running both
databases concurrently.
I'm an application programmer, but I've had to deal with SQL due to lack of
support from our DB administrator.
I'll hopefully have this worked out by then, but I'll let you know if I get
any further information from it.
Thank you
"--CELKO--" wrote:

> Get a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
> nested sets model. It will run at least an order of magnitude faster
> and let you do hierarchical summaries in a single query.
>|||I ordered TREES & HIERARCHIES IN SQL along with SQL FOR SMARTIES from
Amazon.com and they'll be here Tuesday, there are none available from any
local bookstores.
One problem I'm dealing with is the database is imported from SQL Anywhere
and no changes can be made to the structure so they can be moved back and
forth.
I'm primarily an application programmer, but I've had to deal with SQL due
to lack of support from our DB Administrator.
I'll hopefully have this worked out before the books come Tuesday, but I'll
let you know if I get any usefull information from them.
Thanks
"--CELKO--" wrote:

> Get a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
> nested sets model. It will run at least an order of magnitude faster
> and let you do hierarchical summaries in a single query.
>|||Check out solutions to BOM in the following whitepaper:
(URL may wrap)
http://msdn.microsoft.com/library/d...TSQLEnhance.asp
Even though the solutions in the paper use Recursive CTEs in SQL Server
2005, they can all be easily converted to UDFs in SQL Server 2000 applying
the same algorithms. If you have any trouble converting to UDFs, let me
know, and I can give you examples.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
news:0C850AAC-50A8-4BBF-A60A-366D510B7FA9@.microsoft.com...
> The following query gives me some top level and some subassembly levels
> returned because they are at the same level in the hierarchy. How can I
> get
> it to keep selecting the subassembly levels until all returns are top
> level?
> SELECT DISTINCT TOP 100 PERCENT Parent_Pfl.PflNumber AS ParentPfl,
> Child_Pfl.PflNumber AS ChildProfile
> FROM (SELECT ParentPflHierarchy.ParentPflID,
> ChildPflHierarchy.ChildPflID
> FROM dbo.PflHierarchy ChildPflHierarchy INNER JOIN
> dbo.PflHierarchy ParentPflHierarchy ON
> ChildPflHierarchy.ParentPflID = ParentPflHierarchy.ChildPflID) DERIVEDTBL
> INNER JOIN
> dbo.Pfl Parent_Pfl ON DERIVEDTBL.ParentPflID =
> Parent_Pfl.PflID INNER JOIN
> dbo.Pfl Child_Pfl ON DERIVEDTBL.ChildPflID =
> Child_Pfl.PflID
> WHERE (Child_Pfl.PflNumber = N'28699')
> ORDER BY Parent_Pfl.PflNumber
> PflHierarchy Table
> PflHierarchyID ParentPflID ChildPflID
> 1 1 2
> 2 1 3
> 3 1 4
> 4 1 5
> 5 2 6
> 6 2 7
> 7 6 8
> 8 6 9
> 9 6 10
> Pfl Table
> PflID Description
> 1 This is a top level assy
> 2 This is a Sub assy
> 3 This is a child level for a part
> 4 This is a child level for a part
> 5 This is a child level for a part
> 6 This is a Sub assy
> 7 This is a child level for a part
> 8 This is a child level for a part
> 9 This is a child level for a part
> 10 This is a child level for a part
>|||I would appreciate seeing some examples, I think that may help.
I've taken 15 different approaches now and the results are all the same. If
an item is 3 levels deep in one BOM and 7 levels deep in another: I get the
top level from the 3 level deep, and the 4th level from the 7 level deep.
Thank you
"Itzik Ben-Gan" wrote:

> Check out solutions to BOM in the following whitepaper:
> (URL may wrap)
> http://msdn.microsoft.com/library/d...TSQLEnhance.asp
> Even though the solutions in the paper use Recursive CTEs in SQL Server
> 2005, they can all be easily converted to UDFs in SQL Server 2000 applying
> the same algorithms. If you have any trouble converting to UDFs, let me
> know, and I can give you examples.
> Cheers,
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
> news:0C850AAC-50A8-4BBF-A60A-366D510B7FA9@.microsoft.com...
>
>|||Sure,
Here are solutions to "Assemblies containing a certain sub-item" using both
Recursive CTEs in SQL Server 2005 and using UDFs in SQL Server 2000:
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('BOM') IS NOT NULL
DROP TABLE BOM;
GO
IF OBJECT_ID('Items') IS NOT NULL
DROP TABLE Items;
GO
CREATE TABLE Items
(
itemid VARCHAR(5) NOT NULL PRIMARY KEY,
itemname VARCHAR(25) NOT NULL,
/* other columns, e.g., unit_price, measurement_unit */
);
CREATE TABLE BOM
(
itemid VARCHAR(5) NOT NULL REFERENCES Items,
assemblyid VARCHAR(5) NULL REFERENCES Items,
qty INT NOT NULL
/* other columns, e.g., quantity */
UNIQUE CLUSTERED(itemid, assemblyid),
CHECK (itemid <> assemblyid)
);
INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A');
INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B');
INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C');
INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D');
INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E');
INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F');
INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G');
INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H');
INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I');
INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J');
INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K');
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'A', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'B', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'C', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'D', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'E', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'F', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'G', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'H', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'I', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'J', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'K', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('E', 'J', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'E', 3);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'C', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'C', 4);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'B', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'F', 1);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'G', 3);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'B', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'D', 2);
INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'I', 1);
GO
-- SQL Server 2005 Solution
DECLARE @.itemid AS VARCHAR(5);
SET @.itemid = 'F';
WITH BOMCTE
AS
(
SELECT itemid, assemblyid, qty,
0 AS lvl, CAST('.' + itemid + '.' AS VARCHAR(900)) AS path
FROM BOM
WHERE itemid = @.itemid
UNION ALL
SELECT BOM.itemid, BOM.assemblyid, BOMCTE.qty * BOM.qty,
BOMCTE.lvl + 1, CAST('.' + BOM.itemid + BOMCTE.path AS VARCHAR(900))
FROM BOMCTE
JOIN BOM
ON BOM.itemid = BOMCTE.assemblyid
)
SELECT itemid, qty, lvl, path
FROM BOMCTE
WHERE assemblyid IS NULL
AND lvl > 0;
Output:
itemid qty lvl path
-- -- -- --
B 1 1 .B.F.
C 2 2 .C.B.F.
H 8 3 .H.C.B.F.
A 4 3 .A.C.B.F.
A 2 2 .A.B.F.
-- SQL Server 2000 Solution
IF OBJECT_ID('fn_AssembliesContainingItem')
IS NOT NULL
DROP FUNCTION fn_AssembliesContainingItem;
GO
CREATE FUNCTION fn_AssembliesContainingItem(@.itemid AS VARCHAR(5))
RETURNS @.T TABLE
(
itemid VARCHAR(5) NOT NULL,
assemblyid VARCHAR(5) NULL,
qty INT NOT NULL,
lvl INT NOT NULL,
path VARCHAR(900) NOT NULL,
UNIQUE CLUSTERED(lvl, assemblyid, itemid)
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.T
SELECT itemid, assemblyid, qty, @.lvl, '.' + itemid + '.'
FROM BOM
WHERE itemid = @.itemid;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.T
SELECT BOM.itemid, BOM.assemblyid, T.qty * BOM.qty,
@.lvl, '.' + BOM.itemid + T.path
FROM @.T AS T JOIN BOM
ON T.lvl = @.lvl - 1
AND BOM.itemid = T.assemblyid;
END
DELETE FROM @.T WHERE lvl = 0 OR assemblyid IS NOT NULL;
RETURN
END
GO
SELECT itemid, qty, lvl, path FROM fn_AssembliesContainingItem('F');
Output:
itemid qty lvl path
-- -- -- --
B 1 1 .B.F.
A 2 2 .A.B.F.
C 2 2 .C.B.F.
A 4 3 .A.C.B.F.
H 8 3 .H.C.B.F.
BG, SQL Server MVP
www.SolidQualityLearning.com
"CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
news:A9C665B8-2CE1-4A88-8008-F29AB2D0B7B0@.microsoft.com...
>I would appreciate seeing some examples, I think that may help.
> I've taken 15 different approaches now and the results are all the same.
> If
> an item is 3 levels deep in one BOM and 7 levels deep in another: I get
> the
> top level from the 3 level deep, and the 4th level from the 7 level deep.
> Thank you
> "Itzik Ben-Gan" wrote:
>|||I'm not sure yet what I might have transposed wrong, but when I tried the
following:
CREATE FUNCTION [dbo].[fn_AssembliesContainingItem]
(@.itemid AS VARCHAR(14))
RETURNS @.T TABLE
(
ChildPflID VARCHAR(14) NOT NULL,
ParentPflID VARCHAR(14) NULL,
lvl INT NOT NULL,
UNIQUE CLUSTERED(lvl, ParentPflID, ChildPflID)
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.T
SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
FROM PflHierarchy
WHERE PflHierarchy.ChildPflID = @.itemid;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.T
SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
FROM @.T AS T JOIN PflHierarchy
ON T.lvl = @.lvl - 1
AND PflHierarchy.ChildPflID = T.ParentPflID;
END
DELETE FROM @.T WHERE lvl = 0 OR ParentPflID IS NOT NULL;
RETURN
END
I get the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY
constraint 'UQ__@.T__20A07422'. Cannot insert duplicate key in object
'#1FAC4FE9'.
My books by Joe Celko just came in, so I'll be reading them for more ideas
to try.
"Itzik Ben-Gan" wrote:

> Sure,
> Here are solutions to "Assemblies containing a certain sub-item" using bot
h
> Recursive CTEs in SQL Server 2005 and using UDFs in SQL Server 2000:
> SET NOCOUNT ON;
> USE tempdb;
> GO
> IF OBJECT_ID('BOM') IS NOT NULL
> DROP TABLE BOM;
> GO
> IF OBJECT_ID('Items') IS NOT NULL
> DROP TABLE Items;
> GO
> CREATE TABLE Items
> (
> itemid VARCHAR(5) NOT NULL PRIMARY KEY,
> itemname VARCHAR(25) NOT NULL,
> /* other columns, e.g., unit_price, measurement_unit */
> );
> CREATE TABLE BOM
> (
> itemid VARCHAR(5) NOT NULL REFERENCES Items,
> assemblyid VARCHAR(5) NULL REFERENCES Items,
> qty INT NOT NULL
> /* other columns, e.g., quantity */
> UNIQUE CLUSTERED(itemid, assemblyid),
> CHECK (itemid <> assemblyid)
> );
> INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A');
> INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B');
> INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C');
> INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D');
> INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E');
> INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F');
> INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G');
> INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H');
> INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I');
> INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J');
> INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K');
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'A', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'B', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'C', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'D', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'E', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'F', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'G', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'H', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'I', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'J', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES(NULL, 'K', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('E', 'J', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'E', 3);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'C', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'C', 4);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('C', 'B', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'F', 1);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('B', 'G', 3);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'B', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('A', 'D', 2);
> INSERT INTO BOM(assemblyid, itemid, qty) VALUES('H', 'I', 1);
> GO
> -- SQL Server 2005 Solution
> DECLARE @.itemid AS VARCHAR(5);
> SET @.itemid = 'F';
> WITH BOMCTE
> AS
> (
> SELECT itemid, assemblyid, qty,
> 0 AS lvl, CAST('.' + itemid + '.' AS VARCHAR(900)) AS path
> FROM BOM
> WHERE itemid = @.itemid
> UNION ALL
> SELECT BOM.itemid, BOM.assemblyid, BOMCTE.qty * BOM.qty,
> BOMCTE.lvl + 1, CAST('.' + BOM.itemid + BOMCTE.path AS VARCHAR(900))
> FROM BOMCTE
> JOIN BOM
> ON BOM.itemid = BOMCTE.assemblyid
> )
> SELECT itemid, qty, lvl, path
> FROM BOMCTE
> WHERE assemblyid IS NULL
> AND lvl > 0;
> Output:
> itemid qty lvl path
> -- -- -- --
> B 1 1 .B.F.
> C 2 2 .C.B.F.
> H 8 3 .H.C.B.F.
> A 4 3 .A.C.B.F.
> A 2 2 .A.B.F.
> -- SQL Server 2000 Solution
> IF OBJECT_ID('fn_AssembliesContainingItem')
IS NOT NULL
> DROP FUNCTION fn_AssembliesContainingItem;
> GO
> CREATE FUNCTION fn_AssembliesContainingItem(@.itemid AS VARCHAR(5))
> RETURNS @.T TABLE
> (
> itemid VARCHAR(5) NOT NULL,
> assemblyid VARCHAR(5) NULL,
> qty INT NOT NULL,
> lvl INT NOT NULL,
> path VARCHAR(900) NOT NULL,
> UNIQUE CLUSTERED(lvl, assemblyid, itemid)
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.T
> SELECT itemid, assemblyid, qty, @.lvl, '.' + itemid + '.'
> FROM BOM
> WHERE itemid = @.itemid;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.T
> SELECT BOM.itemid, BOM.assemblyid, T.qty * BOM.qty,
> @.lvl, '.' + BOM.itemid + T.path
> FROM @.T AS T JOIN BOM
> ON T.lvl = @.lvl - 1
> AND BOM.itemid = T.assemblyid;
> END
> DELETE FROM @.T WHERE lvl = 0 OR assemblyid IS NOT NULL;
> RETURN
> END
> GO
> SELECT itemid, qty, lvl, path FROM fn_AssembliesContainingItem('F');
> Output:
> itemid qty lvl path
> -- -- -- --
> B 1 1 .B.F.
> A 2 2 .A.B.F.
> C 2 2 .C.B.F.
> A 4 3 .A.C.B.F.
> H 8 3 .H.C.B.F.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
> news:A9C665B8-2CE1-4A88-8008-F29AB2D0B7B0@.microsoft.com...
>
>|||Joe,
I've read your books, and perhaps I was not clear on what my initial problem
is:
1. I am working with existing tables and existing data
2. I am working with multiple parent Bills Of Materials
3. I am searching for All top level items when given a part item
The following demonstrates the problem:
CREATE TABLE [Hierarchy] (
[PflHierarchyID] [int] NOT NULL ,
[ParentPflID] [int] NOT NULL ,
[ChildPflID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Part] (
[PflID] [int] NOT NULL ,
[PflNumber] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO [Hierarchy] VALUES(1,1,2)
INSERT INTO [Hierarchy] VALUES(2,1,3)
INSERT INTO [Hierarchy] VALUES(3,3,2)
INSERT INTO [Hierarchy] VALUES(4,3,4)
INSERT INTO [Hierarchy] VALUES(5,5,2)
INSERT INTO [Hierarchy] VALUES(6,5,4)
INSERT INTO [Part] VALUES(1,'1234','PIII Motherboard')
INSERT INTO [Part] VALUES(2,'341','Res 1K 0805')
INSERT INTO [Part] VALUES(3,'2345','Video Card')
INSERT INTO [Part] VALUES(4,'421','Cap 1 pF 0805')
INSERT INTO [Part] VALUES(5,'12345','P4 Motherboard')
This view should return '1234','2345', and '12345': Returns '1234'
SELECT DISTINCT
TOP 100 PERCENT Parent_Pfl.PflNumber AS ParentPfl,
Parent_Pfl.Description AS ParentDescription, Child_Pfl.PflNumber AS
ChildProfile,
Child_Pfl.Description AS ChildDescription
FROM (SELECT ParentPflHierarchy.ParentPflID,
ChildPflHierarchy.ChildPflID
FROM Hierarchy ChildPflHierarchy INNER JOIN
Hierarchy ParentPflHierarchy
ON ParentPflHierarchy.ChildPflID = ChildPflHierarchy.ParentPflID)
HierarchyTBL INNER JOIN
dbo.Part Child_Pfl ON HierarchyTBL.ChildPflID =
Child_Pfl.PflID INNER JOIN
dbo.Part Parent_Pfl ON HierarchyTBL.ParentPflID =
Parent_Pfl.PflID
WHERE (Child_Pfl.PflNumber = N'421')
Any help would be appreciated
"--CELKO--" wrote:

> Get a copy of TREES & HIERARCHIES IN SQL. I worked out a BOM using the
> nested sets model. It will run at least an order of magnitude faster
> and let you do hierarchical summaries in a single query.
>|||I created a UNIQUE constraint to allow filtering using an index, but
multiple paths leading to the same node might end up with dups (which are
valid).
So either remove the constraint, or add an identity column at the end if you
want to allow using an index to filter the previous level:
CREATE FUNCTION fn_AssembliesContainingItem(@.itemid AS VARCHAR(5))
RETURNS @.T TABLE
(
itemid VARCHAR(5) NOT NULL,
assemblyid VARCHAR(5) NULL,
qty INT NOT NULL,
lvl INT NOT NULL,
path VARCHAR(900) NOT NULL,
surkey INT NOT NULL IDENTITY,
UNIQUE CLUSTERED(lvl, assemblyid, itemid, surkey)
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.T
SELECT itemid, assemblyid, qty, @.lvl, '.' + itemid + '.'
FROM BOM
WHERE itemid = @.itemid;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.T
SELECT BOM.itemid, BOM.assemblyid, T.qty * BOM.qty,
@.lvl, '.' + BOM.itemid + T.path
FROM @.T AS T JOIN BOM
ON T.lvl = @.lvl - 1
AND BOM.itemid = T.assemblyid;
END
DELETE FROM @.T WHERE lvl = 0 OR assemblyid IS NOT NULL;
RETURN
END
GO
BG, SQL Server MVP
www.SolidQualityLearning.com
"CapeCAD" <CapeCAD@.discussions.microsoft.com> wrote in message
news:8E1A0DAE-D71A-4849-8FA3-75D3043AD644@.microsoft.com...
> I'm not sure yet what I might have transposed wrong, but when I tried the
> following:
> CREATE FUNCTION [dbo].[fn_AssembliesContainingItem]
> (@.itemid AS VARCHAR(14))
> RETURNS @.T TABLE
> (
> ChildPflID VARCHAR(14) NOT NULL,
> ParentPflID VARCHAR(14) NULL,
> lvl INT NOT NULL,
> UNIQUE CLUSTERED(lvl, ParentPflID, ChildPflID)
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.T
> SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
> FROM PflHierarchy
> WHERE PflHierarchy.ChildPflID = @.itemid;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.T
> SELECT PflHierarchy.ChildPflID, PflHierarchy.ParentPflID, @.lvl
> FROM @.T AS T JOIN PflHierarchy
> ON T.lvl = @.lvl - 1
> AND PflHierarchy.ChildPflID = T.ParentPflID;
> END
> DELETE FROM @.T WHERE lvl = 0 OR ParentPflID IS NOT NULL;
> RETURN
> END
> I get the following error message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of UNIQUE KEY
> constraint 'UQ__@.T__20A07422'. Cannot insert duplicate key in object
> '#1FAC4FE9'.
> My books by Joe Celko just came in, so I'll be reading them for more ideas
> to try.
> "Itzik Ben-Gan" wrote:
>

Hierarchical Resultset Sorting

I have a query like this

with TempCTE(id, Name, level, sortcol)
As
(
Select id, Name, 0 as level,
cast(cast( id AS BINARY(4)) as varbinary(100)) sortcol
from Table1
where id = 1

union all

Select id, Name, 0 as level,
cast(sortcol + cast( id AS BINARY(4)) as varbinary(100)) sortcol

from Table1 inner join TempCTE on TempCTE.id = Table1.parentid
)

select * from TempCTE order by sortcol

My problem is I want to sort this hierarchical resultset further on name like

aaa
--aaaa
--bbbb
--cccc
aaaaa
bbbbb
--dddd
aaaaa
bbbbb
bbb
--aaaa
--bbbb

Thanks

You need to basically add the appropriate columns in the ORDER BY clause. Based on your query above, you need to probably do ORDER BY sortcol, name. See below example based on a query using AdventureWorks sample tables:

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT replicate('.', EmployeeLevel* 10) + right(replicate('0', 10) + cast(coalesce(ManagerID, 0) as varchar), 10) as Mgr
FROM DirectReports
ORDER BY EmployeeLevel, ManagerID, EmployeeID;
GO

hiearchical list of tree

Hi,

table(id,parent,level, name)
parent ...fk referenced table(id))
level ... depth of tree (root has 0)

How SQL for hiearchical list:

name 1
name 1.1
name 1.2
name 2.
name 2.1.
name 2.1.1
name 2.1.2
etc.

?
thanks for your helpPlease post the complete DDL for your table, some sample data, and the desired result. That, I might be able to help with.|||create table A(
id int primary key not null,
parent int null, --parent of the row
level int null, --position in DB tree from root
name varchar (20)
)

alter table A add
constraint fk foreign key(parent) references A(id)

insert into A(id,parent,level,name) values (1,null,0,'name 1.');
insert into A(id,parent,level,name) values (2,1,1,'name 1.1.');
insert into A(id,parent,level,name) values (3,1,1,'name 1.3.');
insert into A(id,parent,level,name) values (4,2,2,'name 1.1.1.');
insert into A(id,parent,level,name) values (5,1,1,'name 1.2.');
insert into A(id,parent,level,name) values (6,3,2,'name 1.3.1.');
...

I'd like to display :
name 1.
name 1.1.1.
name 1.2.
name 1.3.
name 1.3.1.
...

name 1.
name 1.|||this will do it...select name from A order by name
i know it's not the general solution, but it is a prefectly good solution to the particular data that you have, with the enumerated path included as part of the name|||thanks, but else, not order name
Column a.name contains 'sdfdasasd' etc too.|||okay, see Recursive Queries in SQL Server 2005 (http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp)|||I'd like to avoid a recursion solution. How ways are possible?
DDL can be altered.|||how deep is the tree? how many levels?|||more than 15 levels|||my condolences, as that would be phenomenally difficult for most users to navigate|||I'd like to avoid a recursion solution.
Why? Recursion would be the easiest method, especially if the number of levels isn't fixed.|||because a recursion should be time difficult|||you may not want to hear this, but any solution for the adjacency model going down 15+ levels will be "time difficult"

have you thought about a different data design?

do a search for joe celko's nested set model

Hiding table columns :: urgent

Hi All .. I want to hide columns in a Table based on the level of drilldowns.
Say I have a report with Country,State,City,Count first level I want to show
only Country,Count and second level Country,State,Count and so on. I tried
giving the toggle item property of the Column to a textbox in the first group
but gives an error saying
Toggle items must be text boxes that share the same scope as the hidden item
or are in a scope that contains the hidden item, and cannot be contained
within the current report item unless current grouping scope has a Parent.
Also no items are available in the drop down for the toggle item. Am I doign
something wrong or is this a Reporting svc limitation. I am stuck with this
pls help ..
Thnx in advance
--
Happy Hacking
ThajeerYou need to change "Group" property instead of text item property.
Henry|||Thanx henry .. but there is group involved here . What I am trying to do is
hide a column just like hiding a row or a group and toggle the visibility
using another item. .. but the drop down itslef wont populate with any item
and if I enter the textbox name it gives me a compile error .. hope I am
clear ..
"fanh@.tycoelectronics.com" wrote:
> You need to change "Group" property instead of text item property.
> Henry
>

Hiding measures above Country level

Hello,
I have the following problem I would appreciate your help with:
I am designing a cube “Cost Benchmarking” using Analysis Server 2000 with the following structure (simplified for the sake of this post):
Dimensions: Countries (levels: (All), Regions, Countries)
Clients (levels: Client)
Media (levels: TV Station)
Months (levels: Years, Quarters, Months)
Measures:
Cost (amount of money the clients spent on advertising, in local currency),
Cost in USD (Cost converted to USD at the fact record level),
Impressions (raw number of impressions the ad campaign achieved, in thousands),
CPM (Cost Per Thousand impressions, calculated = Cost/Impressions)

The problem is that several measures do not make business sense to aggregate above the country level, namely Cost, Impressions and CPM. Users don’t even want to see those measures until a country is specified (we are planning on using Excel Pivot Table as a client).‘Cost in USD’ however can be aggregated at the Region and (All) levels.

Is there a way to hide those measures above the Country level?

Thanks
Kliment

If you're using AS 2000 Enterprise Edition, you could use the calculated cell feature, to clear cells with the relevant measures above the country level:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmeasures_2ttf.asp

>>

Calculated Cells

Calculated cells are cells whose value is calculated at run time using a Multidimensional Expressions (MDX) expression that you specify when you define the calculated cells. Additionally, the expression can be conditionally applied to specific cells, based on an MDX logical expression also specified when you define the calculated cells.

...

>>

Hiding "ALL" level in a dimension

Hi,

When I browse the data of any dimension then I have the "ALL" level and then the members of the dimension.

How can I remove / hide the "ALL" level in Analysis Services 2005.

Thanks in Advance

You can set the IsAggregatable property of the attributes to false. But this has other implications and might not be exactly what you want as the All member is the default member. The easiest way to explain this is with an example.

If you were to display Sales Amount by customer, without specifying a member of the product dimension then the "All Products" member would be used. If you were to change the Product dimension to remove the all member then you would either have to specify a default member or specify a particular member as default or SSAS will pick a member (usually the first one in the dimension) to use as the default. Thus when you look at Sales Amount by Customer it would now only be for one particular product.

Personally I only remove the all member in very specific circumstances. When doing budgeting cubes, where you have a dimension with budget/actual/forecast members, then it does not make sense to aggregate these together so I usually remove the all member. And for some cubes I take it out of the time dimension as the concept of aggregating measures across all time members does not always make sense.

HideMemberIf OnlyChildWithParentName and member formula

Levels are defined using a SourceAttribute that points to an attribute used to create the level. In the definition of that attribute, the CustomRollupColumn points to a formula to be used in the custom rollup.

But if the level is hidden using HideMemberIf, does the level still participate - does that formula still get executed at query time?

In other words, are custom rollups still calculated, even if they are hidden using something like HideMemberIf?

HideMemberIf ADOMD.NEt

Hello everyone!
I have a ragged hierarchy, and I'm using HideMemberIf = ParentName in the last level of the hierarchy (AS 2005)
I have a client application written using ADOMD.NET where I put the data into a cellset object.
The problem is that I can't differentiate when a level has this property set with adomd.net.
Could anyone help me here?
thanksThere is no way in ADOMD.NET to find out whether a level has HideMemberIf turned on.|||You can however check to see if a member is a placeholder member (that is should be hidden) by checking the IS_PLACEHOLDERMEMBER property in the properties collection. Note that this property isn't retrieved by default for members so you'll probably need to call FetchAllProperties() or request it when you fetch your members. Note that you also need to set the connection string property MDX Compatibility=1 for this IS_PLACEHOLDERMEMBER to be set.