I am rather new to database design and modelling concepts in general
and was hoping for some advice on a problem I am trying to solve. I
have designed a piece of software that creates a tree with pluggable
nodes. Each node class can have 0 to n distinct classes plugged into
it to define the type for that node.
For example, a node plugged with a 'customer' class and an 'engineer'
class would indicate that this node in the tree is an engineer who is
also a customer. We could also have a 'owner', 'engineer' etc.
I now want to persist this tree in an SQL Server 2000 Database. I have
chosen to implement the nested set model, and have thought about the
following table design:
So, given the above example - I would have a NODE_TABLE that links to 2
entries in PROPERTIES_TABLE. One entry would link to an entry in the
CUSTOMER_TABLE, the other to an entry in ENGINEER_TABLE.
Are there any more efficient solutions to this problem? As i said, I
am very new to DB design and would welcome any feedback or suggestions
of how else I might model my pluggable tree in a Database. Thank you,
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP SELECT * INTO Emp FROM ( SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b */
I have a pocket pc app that is using a sql ce (.sdf) database. The inserts, updates and deletes are not persisting when I exit debug. It seems like it's getting put in the database because I bind drop down lists after the inserts, updates, etc and the new/changed data shows correctly after rebinding the data (when I repopulate the drop downs I make a call to the database, I don't just manually add the new item to the drop down so it seems like the changes are getting to the database). However, once I stop debugging and restart debugging all my changes are gone. I'm not using transactions but is there something that I need to do to commit the db changes?
I was able to write successfully a Script Task to set the values of several package variables. But when the execution completes, the variables still contain values which were specified by default.
What I want is a method of persisting the values assigned to a variable through a script. I believe this was possible in DTS
And I always know the root ID from the first record on "table" dummy (generated with a common table expression), in this case it's ID 1, but from here, how to process this for any level of depth ?
I have built a Table Valued SqlFunction which streams out filtered results from a table. The way that I have implemented this is by using a custom class which implements the IEnumerator interface.
The class internally stores a SqlDataReader and a SqlConnection as private member variables. The Class initializer (ie: the New function) creates a SqlCommand which is executed with ExecuteReader into the SqlDataReader and returns.
The IEnumerator.MoveNext method loops through the SqlDataReader until it finds a result which matches the heuristic filter, and the IEnumerator.Current method returns the current result from the SqlDataReader.
Unfortunately as soon as the initializer returns the SqlDataReader is automatically closed, and I can't figure out why. I've debugged through this and at the end of the Initializer the SqlDataReader is definately open, and as soon as first call to MoveNext is run it is closed.
Can anyone offer any suggestions on how I can fix this.
In the interim i've had to load all of the filtered results into a temporary ArrayList in the Initializer, which defeats the purpose of streaming out the results.
The same as following Tree as you can see 1__ | 2__ | 4__ | 6 | __ 3__ 5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that Could any one help me.?
I am going through the data mining web control viewer tutorial and its going great. I have been able to build and setup the viewer. The problem I am having is when I publish the site out to my web server, it gives me the following error:
Code Snippet
Error: Either the user, <domain><computerName>$, does not have access to the prospectDataMining database, or the database does not exist.
When I debug this on my local machine via Visual Studio 2005, it works GREAT! It is just when I publish the site to the web server.
I have a dedicated SSAS server along w/ a dedicated web server. To test, I published the site to the SSAS server to see if it was a connection issue. I received the same error w/ a different <domain><computerName>$.
I looked at trying to put in the optional connection info for the dataMining html tree viewer properties... but apparently dont know how to do that properly. I also checked the IIS directory security and enabled Integrated security.
What am i doing wrong? ANY help is much appreciated.
Hi all! I am trying to organize a hierarchical data structure into a table. I need to have the possibility to set 2 parents for some nodes. Curently I see following two options: Example 1 id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1,2 Level 2 Child Example 2 id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1 Level 2 Child3 2 Level 2 Child Is any of the two examples valid database logic wise? In fact, is it possible to achieve the requirement by using only one table? Thanks in advance,
I've recently started a new position and our production box. Contains a procedure that uses 30 + temp tables. I'm currently not in a position to change this as it's production and I would have to be granted a window to re-design.
However the tempDb is showing some strange activity.
If a table is created #CarrierService (CarrierServiceID,DeliveryZoneID,CollectionZoneID) for example
Once the procedure is called It will appear in the tempDB with the session info appended as expected
However once the session has ended the above table will get dropped and a new one created
#2C78E45A, I now have 7000 of these different Tables in the TempDB
When I Interrogate this using
SELECT o.name, o.create_date,o.modify_date , c.Name,C.Column_Id FROM tempdb.sys.Objects o Inner join tempdb.Sys.Columns c ON o.object_id =c.Object_ID WHERE o.type ='U'
I get the Following results
name create_date modify_date object_id name #2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CarrierServiceId #2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CollectionZoneId #2C78E45A26/04/2015 18:0930/04/2015 14:55746120282DeliveryZoneId
Push data from the Integration Services pipeline or a custom application. The data can flow directly into an Analysis Services partition from the Integration Services package pipeline, without intermediate storage. This scenario can be used to reduce the latency (and storage cost) of analytical data.
What I think this means is that using SSIS I could bypass porting the data from ODS to DW, and instead use SSIS control flow/data flow components to direct the data straight to the Analysis Services Cube database storage partition.
I was hoping by posting this thread on this MSDN forum I could get details or links from those more knowledeable than I on this subject pointing me to how I may go about using SSIS to implement this approach. Perhaps package code examples or instructions about the components I could use to do this?
We've successfully processed a large decision tree model in SQL Server 2005. When I try to view the tree in the mining model viewer, I get the following error:
TITLE: Microsoft Visual Studio ------------------------------
The tree graph cannot be created because of the following error:
'Exception of type 'System.OutOfMemoryException' was thrown.'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.AnalysisServices.Viewers.SR&EvtID=ErrorCreateGraphFailed&LinkId=20476
The link provides no other documentaiton on the error.
We're using 64-bit SQL on a Dell Workstation running XP-64 with 16GB of memory. From my view of things we aren't close to running out of memory. Since the model processed and the error occurs when viewing the model, is this a problem with Visual Studio and nont necessarily Anlaysis Services?
I am seeing a particular problem in the XML Source Editor "Columns" configuration where it is not persisting the "Output name" selection.
Control Flow Tab: 1. I use a "Exec SQL Command" to drop, create, or alter the destination tables in the database that I want to be repository for the inbound XML data. The data types are fairly straightforward.
2. I add a singular "Data Flow"
Data Flow Tab:
1. I add a "XML Source" task, and assign a well-defined XML file. I then use the "Generate XSD" option in the "Connection manager"; and I am fairly satisfied with the generated XSD.
2. I create "OLE DB Destination"
3. I wire the "XML Source" to the "OLE DB Destination". In the "XML Source" in the "Columns".
4. I go to the dropdown list of "Output name" and see the list ordered with the various complex-types that I want to map and transfer to a target table.
For the sake of this report, I select the 5th one down on the list (for which I already have a target table) - let's call this "Mesh"
5. In the "Input Output" dialog, I select the "output" to be the desired 5th item, "Mesh"
6. I check all my mappings so that they map one-to-one ... XML name entries match SQL table destination mapping entries; correct types; correct size
7. Check the metadata and it all looks good.
8. When I hit "Debug" to test the package the failure occurs at the "XML Source". The error report comes back saying that it failed because "field xxx in Contributor was truncated". However, "Contributor" corresponds to the 1st name in the dropdown list presented in "Columns" "Output name:".
If I select return to Step 4, when I open up "Columns" I see that my previous selection of the 5th item on the list named "Mesh" was not persisted, but invariably and no matter how often I select item #5 "Mesh" and save to ensure that selection sticks, it is not persisted.
I hand-edited the .dtsx file and only then was I able to make this stick. However, if I ever re-save the package this non-persistency pops up again.
Am I doing something wrong here or is this a known defect? As I have several dozen XSD mappings that I want to transfer to tables, hand-editing is not something I relish.
I would like to know how to create a single level hierachy structure in SQL. Example, I have a single parent record in table A that may later on spawn one or child record in table B that relates back to the parent. It only needs a single level. that means, one to many.
table1 has 3 fields a , b and c. field "a" is a primary key. table2 has 2 fields x and y. Fields x and y are nothing but the value of "a". Also, y is the child of x. Therefore, x and y can never have same value. It means value of "a" either be child or parent. But there is possibility that parent has no child.
Now, i wanted to write Select/Insert query for parent, b , c and child.
Hi, I'm having a major brain-failure moment here. Using T-SQL I want to be able to get all of the leaf nodes (e.g. nodes at the furthest end of a tree from the root) in a hierarchical relationship where the table structure is such NodeID|ParentID|NodeName. Basically if I had the following tree structure: Root Child1 Grandchild1 Child2 Grandchild2
I want to get all of the Grandchild nodes. Number of levels will vary and I haven't got any kind of HasChilds column. I know this is possible because I remember having done it on a course years ago but I can't for the life of me figure it out on this sunny Friday afternoon. I know it's going to involve either recursion, a while loop or cursors but my mind is currently jelly. Can anyone help?
hi. I am working on Multiline Marketing Project.I have to calculate all the childens and display the whole tree..I have save records in tree format...But not able to calculate the all childrens. SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '16%') This query works properly.Problem is that it is not working in the project because in the like I have to pass a variable. SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '@idl%') Please Suggest me...
HI, I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed for this i need a table and procedure to complete family tree Thanks @mbi
Consider the following SQL query: SELECT ENAME,SAL FROM EMP,ASG,PAY,PROJ WHERE EMP.ENO=ASG.ENO AND PAY.TITLE=EMP.TITLE AND ASG.PNO=PROJ.PNO AND ASG.DUR=48 AND BUDGET>200000 Give the possible operator trees:right-deep,left-deep and bush Tank you very much!
i'm writing a app in c# and have to store Trees in a Database.
I'm working with Datasets for the exchange between the DB and the App.
The trees have the same options like the windows folders. If u delete a node, all subnodes should be deleted too.
But something a Foreign Key from ParentID references (Id) with the delete-Rule on cascade seems not to be possible, because of multiple cascade Paths or cycles. Do i have to add some xtra constarins:
Not Possible:
create Table tree ( Id varchar Not null, ParentId varchar Not null, Constraint pk1 Primary Key (Id), Constraint fk1 Foreign Key (ParentId) references tree(Id) On Update Cascade On delete CAscade )
Do i have to write triggers, which delete The subnodes too and set the Update-/deleterulr on NO Action
Does anyone know any good links for SQL tree structures and example queries and stuff... I cant really find anything part from the standard example of emplyee, boss, salary which explains how to create the tree table...(dun dis bit) I did notice a book but I live in a little village so cant go get it till wekend?
I'm desperate, reli need to work out how too do this.....
SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description, d.Security_Type_Description, a.Security_Value FROM Company_Hierarchy_Security a JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code JOIN Company_Security_Type d on a.Security_Type = d.Security_Type inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description, f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description, i.Team_Description from Company_Hierarchy_Security a. Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID Inner JOIN Company f on a.Security_Value = f.Company_ID Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5)) Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID Inner JOIN Team i on a.Security_Value = i.Team_ID
Ok. I have the Security Value located in Company_Hierarchy_Security table. All those values are divided into 5 other tables that I need to join together. I found all of them separate but I have not been able to figue it out how to put it together with the rest of the querie.
Thank You visakh16 --- The following section of my querie works but doesn't give the exact information that I need. This is what is giving me:
NetworkId Full Name Sec Class Desc Sec Type Desc SecValue tte Test Scenario Accounting Budget Center 142- ?
SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description, d.Security_Type_Description, a.Security_Value FROM Company_Hierarchy_Security a JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code JOIN Company_Security_Type d on a.Security_Type = d.Security_Type
------------------
inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description, f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description, i.Team_Description from Company_Hierarchy_Security a. Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID Inner JOIN Company f on a.Security_Value = f.Company_ID Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5)) Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID Inner JOIN Team i on a.Security_Value = i.Team_ID
Any recomendations on how to store organization trees on a database withlots of paths and branches? Any white papers out there that explain this?Thanks
Hello,I have a "tree" table:Id - primary keyParrentId - (foreign key) related to IdTitle.....when I delete some record I want to delete it with all childs (cascadedeleting). I can't set cascade deleting on the same table :(. Is thereany easy way in the MSSQL 2005 to do this ? There is one idea - usingcursors + recursive functions but I think this solution is not easyand elegant.Thakns for any help and sugestions.Regards.Andy
I wanted to use the Decision Tree to show a result..... after i configure the Mining Structures..... and set all the input.... my decision tree shows only until level 2..... i have 3 input and one PredictOnly column.....where is the other input?
Say.... i have House Owner, Marital Status, Num Cars Owned and Number Of Children(PredictOnly)
my Tree only shows All ---- > Marital Status when i input all 3 together...... the other 2 doesn't seems to show.
wat should i do?? my database in SQL Server and the other keys are all correct and deploying finely.....why is this happening.....?
i'm a newbie in this software.......so any pro here can plz help me if there's actually something that i might have missed out along the way.......
i have tried 3 different solutions all coming to a crash at some point, if ANYONE can help me out i would appreciate it, i know the logic will be recursive, i just dont know how to implement
Hi..I have a table register..in this fields are username,parent id,downline ; I have to determine all the child of a particular parent. suppose table is like this. username parentid downline B A left C A right D B left E B right.... I have to also determine the level in the tree....please help...
