Deletion Of Parent And Child Table
how can we delete parent table as well as child table using a single query applied on parent table, can someone please help me onn this topic? it will be very nice of you guys.
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
View Complete Forum Thread with Replies
Related Forum Messages:
Parent And Child Records From Same Table
Hi i have a table named categorymaster categoryid catname parentid 1 Boxing 0 2 Tennis 0 3 Basketball 0 4 MayWeather 1 5 Tyson 1 6 Clinton woods 1 7 RogerFederer 2 8 Micheal 3 9 Hingis 2 so if i give input say categoryid=1[This falls under main category-boxing] i need to get result as 1 boxing [main category] 4 mayweather [sub category] 5 tyson [sub category] 6 clinton woods [sub category] if i give categoryid=5[Note:Tyson] result should be as 1 boxing [main category] 5 tyson [sub category] hope u can get my question Thanks in advance
View Replies !
Parent/Child Table Replication
Hello all gurus out there. I'm an entre level junior programmer. My question is kind of confusing but I'll try to put it as simple as I can. First we have a main table called "job1". This table consists the order information. The file_id is the unique id and the primary key for this table. This table also pertains other information such as customer data (max limit 5), job data etc. This table is actively (non-stop) used throughout the day. We have a non-interactive process which will take customers information from the main table and insert into the child table table "jobcust". Jobcust would have file_id, cust, cust_type. For example, if Job1 table had fiel_id=100 and cust1="Tom" and Cust2="David", now Jobcust will have two records file_id, cust1 and file_id,cust2. The main problem is the child table needs to be updated right away and our non-interactive process is good at doing that.. but it is causing a major DATA LATENCY. I would like to ask you all, if you know any better way of doing this without any process.. like in the back end with a trigger/procedure or something like that. All you help is appreciated. Thanks much.
View Replies !
Delete Child Table Rows Based On Predicates In A Parent Table
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
View Replies !
Parent/child Two Table Quary Distinct
hi, i have two tables with parent/child relationship - pipeline and pipelineStatus. the select statement like this: SELECT * FROM pipeline INNER JOIN pipelineStatus ON pipeline.id = pipelineStatus.parentID i got multiple records for each pipeline.id because of multiple records of pipelineStatus. Is it possible to get only one record for each pipeline.id with last record of pipelineStatus table? (stored procedure ok) thanks advance for answering my question.
View Replies !
Trigger In Child Table That Required Parent Info
Hi, I have tables parent - PurchaseInvoices and child - PurchaseInvoiceDetails. I have trigger in PurchaseInvoiceDetails that would update stock qty based on Location code stored in parent table - PurchaseInvoices upon INSERT/UPDATE/DELETE. I also set table RI to have cascade delete. I faced problem that, when I delete record in parent table, it would cascade delete child table. Trigger in child table fired and try to update stock qty. However, in this case, trigger unable to locate parent table record anymore. How to overcome this? I can't move my stock update code to other place since I got to update stock if any changes happen to child table. Please advice.
View Replies !
Parent Child Relation Ship Table In SQL Express
I want to build Parent child relation . i have two aproaches .I would like to know which is the best solution ? 1)1st method:- Parent table with parent id . Child table with child id and parent id.Foreign key relationship exists between parent and child tables with cascade delete option enabled. Parent TAble Id name 1 XYZ Child table id name parent id 1 abc 1 2 qwe 1 2)2nd method table with id and parent ID. Top level element will have null value in table. eg id name parent id 1 xyz 2 abc 1 3 qwe 2 4 adf 1 Retrieve data using recursive queries supported in SQL Express. Which is the best solution to store parent child relationship???
View Replies !
How To Postpone Trigger Fire Until After Both Parent And Child Table Values Have Been Inserted
Hi all, I am sure someone must have run into this before. I have a couple of tables with a parent child relationship. I created a trigger on the insert of the parent but don't want it to fire until both the parent and child have been inserted into. However sometimes the child may not get inserted in to at all. In other words it is a 1 to 0 or more relationship. I created the whole insert into the parent and the child and wrapped it all up in a transaction hoping that the trigger would not fire until the transaction actually completed. However such is not the case and it fires when the parent is inserted into but nothing is inserted into the child yet even though that is part of the transaction. Is it possible to postpone trigger fire until after both parent and child table values have been inserted? Thank you, John
View Replies !
Parent/Child Rows In Report, Nested Table, Textbox Value In Filter Condition
Hi All, I am working on SQL server 2005 Reports. I have one report, one dataset is assigned to it, and one table which displays it. Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox. I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters? I want to display parent/child records on report. I am not getting the proper solution. The data is like this: Sequence ItemCode IsParent 1 XYZ 0 'do not have child record 2 PQR 1 'have child records with sequence no 3 3 ASD 0 3 AFDGE 0 3 VDC 0 4 ASR 1 'have child records with sequence no 5 5 ASR 0 If IsParent = 1, that record has child records with sequence = parent sequenece + 1 I think u can understand the data I need to bind, and it is like: XYZ + PQR ASD AFDGE VDC ASR On + click we can do show/hide of child records. I m not getting how to achive this in SQL server report. Can u give some hint? Thanks in advance Pravin
View Replies !
SSMS Express: Creating Parent-Child Table Via LEFT OUTER JOIN - Error Message 156
Hi all, I got an error message 156, when I executed the following code: ////--SQLQueryParent&Child.sql---//////// Use newDB GO ----Creating dbo.Person as a Parent Table---- CREATE TABLE dbo.Person (PersonID int PRIMARY KEY NOT NULL, FirstName varchar(25) NOT NULL, LastName varchar(25) NOT NULL, City varchar(25) NOT NULL, State varchar(25) NOT NULL, Phone varchar(25) NOT NULL) INSERT dbo.Person (PersonID, FirstName, LastName, City, State, Phone) SELECT 1, "George", "Washington", "Washington", "DC", "1-000-1234567" UNION ALL SELECT 2, "Abe", "Lincoln", "Chicago", "IL", "1-111-2223333" UNION ALL SELECT 3, "Thomas", "Jefferson", "Charlottesville", "VA", "1-222-4445555" GO ----Creating dbo.Book as a Child table---- CREATE TABLE dbo.Book (BookID int PRIMARY KEY NOT NULL, BookTitle varchar(25) NOT NULL, AuthorID int FOREIGN KEY NOT NULL) INSERT dbo.Book (BookID, BookTitle, AuthorID) SELECT 1, "How to Chop a Cherry Tree", 1 UNION ALL SELECT 2, "Valley Forge Snow Angels", 1 UNION ALL SELECT 3, "Marsha and ME", 1 UNION ALL SELECT 4, "Summer Job Surveying Viginia", 1 UNION ALL SELECT 5, "Log Chopping in Illinois", 2 UNION ALL SELECT 6, "Registry of Visitors to the White House", 2 UNION ALL SELECT 7, "My Favorite Inventions", 3 UNION ALL SELECT 8, "More Favorite Inventions", 3 UNION ALL SELECT 9, "Inventions for Which the World is Not Ready", 3 UNION ALL SELECT 10, "The Path to the White House", 2 UNION ALL SELECT 11, "Why I Do not Believe in Polls", 2 UNION ALL SELECT 12, "Doing the Right Thing is Hard", 2 GO ---Try to obtain the LEFT OUTER JOIN Results for the Parent-Child Table SELECT * FROM Person AS I LEFT OUTER JOIN Book ON I.ID=P.ID GO ////---Results---////// Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'NOT'. //////////////////////////////////////////////////// (1) Where did I do wrong and cause the Error Message 156? (2) I try to get a Parent-Child table by using the LEFT OUTER JOIN via the following code statement: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'NOT'. Can I get a Parent-Child table after the error 156 is resolved? Please help and advise. Thanks, Scott Chang
View Replies !
MS SQL Server 7: Deletion Of Child Records.
Hello All, I have been involved in a project wherein i have to delete old records from two table and the corresponding child records from the interlinked tables. I try to find a SQL Procedure that deletes the child records, but that procedure uses the sysforeignkeys table for recursively deleting the child records. When i looked at my sysoreignkeys table, it was empty(no rows). Request you all if you can provide me the SQL Procedure that can delete record in the table with the child records. Thanks in advance Regards, Manoj Kumar
View Replies !
Bcp In Child Before Parent
Huh? I've got good RI data...BUT..a developer loaded the tables in alpha table order.... Such that the child loaded BEFORE the parent.... Huh? Got a test being set up now to mess with the child file to add a key that doesn't exist in the parent... But Why is this allowed? In DB2 you can specify LOAD DATA REPLACE NO CHECK.... On the load card...you then need to run a check after to verify the data... Is that what's going on? Is there such a utility in SQL Server to run a check post load? I'm confused.... Any comments appreciated. Thanks Brett 8-)
View Replies !
Insert Into Parent/child
hi, i have two tables i want the identity value of the parent table to be inserted into the chile table here is my code,but i don't know why it isn't working ! protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string pcontent = TextBox1.Text; string data = TextBox2.Text; addtopic(pcontent,connectionString); addfile(data, connectionString); } public void addtopic(string subject,string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) { SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" + "Values(@content)", connection); command.Parameters.Add("@content", SqlDbType.Text).Value = subject; connection.Open(); command.ExecuteNonQuery(); } } public void addchild(string name, string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) {Guid id = Guid.NewGuid(); SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" + "Values(@@IDENTITY,@data,@uid)", connection); commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name; commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id; thanks in advance :) connection.Open(); commandd.ExecuteNonQuery(); } }
View Replies !
Parent Child Transversing
I have a parent/child relationship in a relational database broken out like this: Table Name: categories[category_id] int (primary_key NOT NULL),[category_name] varchar(50),[parent_fk] int The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this: select category_id, category, parent_fk from categories where category_id not in ( select parent_fk from categories) Each bottom-level category has a count attached to it. The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts). My sql is a little weak, could you help me out? I can utilize everying in SQL 2000 (stored proc, UDF, anything). Thanks!
View Replies !
Parent Child Tables
In our database we have a list of devices in a "Device" Table, eachhaving one or more IP's located in the "IP" Table linked through aforein key on the DeviceID Column.I would like to retrieve this information as SuchDeviceID IpAddress1 10.0.0.1, 10.0.0.2, 10.0.0.32 ...345etc.Is it possible to do that without using cursors? Through a query?
View Replies !
Parent / Child Query
Hi there I have come accross this problem. I want to get all the childs which are not parents (last node on the model tree, so that I will end up with the actual models and not model lines) I use this SQL statement but the result in null. select * from CC_MODEL where ccactiveflag = 1 and ccModelId NOT IN ( select distinct ccParentId from CC_MODEL where ccactiveflag = 1 ) Am I missing something or it will just never work. I have tried this query and it worked. select * from CC_MODEL where ccactiveflag = 1 and ccModelId NOT IN (10,11) where numbers in brackets are some of the results from the query select distinct ccParentId from CC_MODEL where ccactiveflag = 1 Any ideas??? Thanks
View Replies !
Parent Child Dimension
I am trying to create a Local Cube with a Parent Child Dimension.I have used the following Create Cube Clause.This works fine.But I do not have a clue as to how to include the Level Naming Template.Any help will be greatly appreciated. CreateCubeStr1 ="CREATECUBE=CREATE CUBE LocalCube(" CreateCubeStr1 =CreateCubeStr1 & "DIMENSION [Employees] DIMENSION_STRUCTURE PARENT_CHILD,LEVEL [All Employees] TYPE ALL " CreateCubeStr1 =CreateCubeStr1 & ",MEASURE [Org Salary] Function Sum Format '#.#' )"
View Replies !
Parent-Child Dimension
Is there any way to combine a star schema dimension with a parent-child dimension? I want to start at the top of our organization and drill down to the level of an individual order. By using the Position dimension, I can only see units by the person responsible for selling them, I cannot drill down into the territories, customer and finally the order. I've tried to put the PositionID in the Division dimension, this works good except it begins at the sales rep level. Any thoughts or ideas are greatly appreciated. Thanks!
View Replies !
Hierarchy – Parent And Child
I am designing a table to represent data in hierarchy structure, I use id and parent id to represent the data in hierarchy form: Id | parent_id ---+----------- 1 | 0 2 | 0 3 | 0 4 | 1 5 | 1 6 | 4 7 | 4 8 | 7 9 | 7 This structure requires complicated queries (recursive call) to find out all the child of a root node, so I have added another field for the root id. Is this a good relational database design ? kindly suggest. Id | parent_id | root_id ---+-----------+--------- 1 | 0 | 2 | 0 | 3 | 0 | 4 | 1 | 1 5 | 1 | 1 6 | 4 | 1 7 | 4 | 1 8 | 7 | 1 9 | 7 | 1 10 | 2 | 2 11 | 2 | 2 12 | 10 | 2 13 | 10 | 2 Rgds Vijay
View Replies !
Parent Child Problem In Sql
I want to find all the child of a node in a tree . A child can have multiple parent i.e 2 can be place under multiple parent . The folling is the data: ParentID ChildID ------------------------- 10 8 8 6 8 7 7 2 7 5 5 1 5 2 6 2 child of parent if input 10 8 7 6 2 2 5 1 (2) can be neglected input 7 2 5 1 (2) can be neglected input 8 7 2 5 (2) can be neglected Plz help we to write the sql statements Thanks
View Replies !
Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table
I have table "Clients" who have associated records in table "Mailings" I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting. I'm trying to return records containing the next upcoming mailing for each client. The closest I can get is below: I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date' SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View Replies !
Insert Trigger For Parent/Child
I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger. Thanks, James
View Replies !
Selecting Parent / Child Relationships
Hello all, I'm having a real hard time trying to figure this one out. I'm trying to create a sql query that selects both the parent name and it's children, but it's got to loop through all the record sets to populate a drop down as an end result. I think I thought this out correctly: I have 2 tables category relationship tbl category cat_id //auto int cat_name // varchar relationship r_id // auto int parent_id // int child_id // int both the parent_id and child_id are associated with the cat_id in my category table I could have 1cars // this is parent 2 audi 3 bmw 4 chevy Table data example r_id parent_id child_id **************************** 1 1 15 2 1 16 3 1 17 4 2 55 5 2 56 etc... I want to select both the parent cat_name from category and also select the child cat_name where the parent_id = # I can do it manaully like this select cat_name, cat_id, parent_id , child_id from category, relationships where child_id = cat_id and parent_id = 1 what is the best way to loop through all the parent ids to find child category? Could this be done in a stored procedure? thanks in advance.
View Replies !
Parent / Child / Grandchild Report
Hi guys, I hope you can help me creating a report. I'm trying to create a Parent / Child / Grandchild report. Looking something like this: Region1 SalesmanA Invoice1 Invoice4 Invoice7 SalesmanB Invoice2 Invoice8 SalesmanD Invoice12 Region1 SalesmanC Invoice3 Invoice5 SalesmanE Invoice6 Invoice9 SalesmanF Invoice10 Invoice11 Invoice13 I have searched for an solution for some time now but I can't find a good solution. Hope you can help me. Thanks.
View Replies !
Tables In Parent-Child Order
Greetings,I just wanna know if anyone can tell me how to get all user definedtables in parent-then-child manner. I mean all the parents should belisted first and then childs.I dont think there is any direct way to do this, but i am not able toform any sort of query to achieve this.Any help will be greatly appreciated.TIA
View Replies !
Flattening Parent Child, An Issue, Please Help
Hello Experts,Here is the code to flatten a PC hierarchy into a level based table. Itworks fine.SELECTt1.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_LV5FROM dbo.Project t1 LEFT OUTER JOINdbo.Project t2 ON t2.PARENT_TASK_ID = t1.TASK_IDAND t2.WBS_LEVEL = 2 LEFT OUTER JOINdbo.Project t3 ON t3.PARENT_TASK_ID = t2.TASK_IDAND t3.WBS_LEVEL = 3 LEFT OUTER JOINdbo.Project t4 ON t4.PARENT_TASK_ID = t3.TASK_IDAND t4.WBS_LEVEL = 4 LEFT OUTER JOINdbo.Project t5 ON t5.PARENT_TASK_ID = t4.TASK_IDAND t5.WBS_LEVEL = 5How do modify the code to work for any level rather than hard codingthe level up to "5"?Please help.Thanks.Soumya
View Replies !
Parent - Child Relation Query
I want to show data in form of parent child relationship.. Tb structure: uniqueIDClientIIDParentIDClientName 110A 221B 331C 442D 550E output... =>A B D C =>E Can anyone help me…for writing query?
View Replies !
Parent-Child View Without Using Cursors
I would like to create a View (we'll call it FamilyView) using two tables, that I can then query against. For example: Parent { ID_PK, Name, PhoneNum, Address } Child { ID_PK, ParentID_FK, Name } The view would return a dataset like this: Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward with an unknown number of children for each parent. Then I would like to be able to query against this view with something like this: SELECT * FROM FamilyView Where Child2 = 'Peter' I have no idea how to write the SQL for this View. Is it possible? Is this possible without using a cursor? Thanks for any advice you all can give me. Brian
View Replies !
Cascade Delete From Child To Parent
Hi All, I have the following db design: a Primary table say CityTable CityId Pk CityName SectorTable SectorID CityID FK to CityTable SectorName MuncipalTable MunicipallD CityID FK to CityTable MuncipalName CantonmentTable CantonmentlD CityID FK to CityTable CantonmentName The CityId can spread across multiple tables. I would like to delete the record from CityTable if all references of CityId have been removed. For e.g CityTable CityId: 1, 2 CityName: Pune, Mumbai SectorTable SectorID: 1a, 2a, 3a CityID: 1, 2, 1 SectorName: Sec1, Sec2, Sec3 MuncipalTable MunicipallD: 1c, 2c, 3c CityID: 2, 1, 1 MuncipalName: abc, def, hij So If I delete SectorID 1a, the CityTable should not delete its record since SectorID 2a and MuncipalID 2c and 3c are still referencing it. But If delete all the records from SectorTable and MuncipalTable referencing CityID 1 then the record from CityTable should get deleted. How can I achieve this using a SP? Please help. Thanks & regards Sunil
View Replies !
Nested Parent-Child Packages
I have a fairly simple SSIS project that has nested parent-child packages. I am trying to find the best way to manage the connections strings so as to make the package portable across machines and environments. Currently there is one "master" package which calls 6 child packages. 1 of these child package calls 3 child packages of its own. For the database connections, I've settled on creating a standardized .dtsConfig file for each server/login. This is a relatively small number (intially 8) that I don't expect to grow much. I've taken a different approach for the file-system connections used by Execute Package components that call the child packages. For each package that has child packages, I store all the connection strings (paths) to the child packages in a single .dtsConfig file. This works well for the top-level "master" package where I can pass in the .dtsConfig file (that has the paths to the child packages) as a run-time option in the Execute Package Utility. However, this approach seems to fall apart for the 2nd generation package that in turn call 3rd generation packages because I don't know how to get the .dtsConfig file (with the 3rd generation .dtsx package paths) path to this downstream dtsx package. Though I'm sure there are others, the only two solutionsI can think of now are (a)don't nest packages beyond 1 parent/child relationship -- not really an option or (b)Store the path of .dtsConfig files for each .dtsx package as an environment variable on each machine. This option is unappealing because it would require adding an environment variable for every .dtsx package that has child packages. I don't think it would take long for this to grow into a large number, that would make managing environment variables cumbersome. So far my experience with SSIS has been that there was a simple solution for each scenario I had. So this hoop jumping I'm going through seems to indicate I am just missing something. Is there a better way I am just not getting?
View Replies !
Parent Child Package With Expressions
Hi there, sorry if the message seems a bit garbled i cannot see the textbox properly with ie7/ff anyways. the situation. childpackage contains a loop-container. in there i use an ole-db-source with a variable based on an expression. ie. "select * from foo where bar=" + len(@[bar]) == 0 ? "initial" : @[bar] + " and etc=1" where bar is the variable that is set by the loop. this works great. however i need to call this package several times, only the expression is a tad different. so i need a scripttask that sets the expression correctly, then i call the childpackage and map the current-expression to the expression in childpackage. how do i do that? or am i doing it wrong? my script-task looks something like: dts.Variable("theVar").Expression = " ""select * from foo where bar="" + len(@[bar]) == 0 ? ""initial"" : @[bar] + "" and etc=1"" " in the childpackage i have a package-conf that maps thevar to thequery with target-object expression.
View Replies !
Debugging Parent / Child Packages
Hi there, I have a Business Intelligence project containing 4 packages. One of the packages is a parent package that just runs the other 3 packages sequentially (passing in variables via Package Configurations). When I set the properties of the Execute Package tasks in the parent package, it seems that I can only select SQL Server or File System locations for the child packages. What I want to do is for the parent to run the packages in the Visual Studio Project so that I can debug the whole process. I don't want to be bothered deploying the child packages so that I can run them from the parent, at least not while I am debugging the whole process. How do I get the children to run within Visual Studio as well as the parent? I can't for the life of me find anything on the MSDN about this. Many thanks in advance. Richard F
View Replies !
Passing Parent Configurations To Child
Hi All, How can I pass configurations from Parent Packages to a child package? I am using Execute package task to execute a child package but am unable to configure a variable to pass configurations to a child package. Thanks
View Replies !
Parent-Child Package Configurations
Hi All, May be I am doing something wrong over here, but I have been trying in vain to test out a simple scenario where I can use my Parent Package configurations in my Child package. I have two packages ready and can someone please walk me through this process. Appreciate all help Thanks
View Replies !
Parent-&&>Child Package Editing Bug?
Before I submit this to connect, I'd like to see if someone can verify: Create a package that executes another package. When you start debuging (build) the parent package it opens up the child package and executes. Once you're done and stop debugging, the child package remains open. When you make edits to the child package and save and close, none of the edits are saved. You must close the package and re-open it before you make your edits...
View Replies !
Insert Parent Child Records...
Hello, We have a complex functionality of migrating data from a single record into multiple parent child tables. To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table. Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails. As of now, we are using the following approach for achieving this functionality. 1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset. 2. Use the recordset as the source to a foreachloop , using foreachADO enumerator 3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :) Thanks, Satya
View Replies !
Parent-Child Variable Issue
Hello, I'm having problem to pass variable from a package to a another one. I having 2 packages: the parent (parent.dtsx) use a Execute Package Task to execute the child package (child.dtsx). In the parent package, I have a variable named var1 (data type String) with value=test In my child package, I having a script task using the variable var1 to make a MsgBox (var1 is in readonly in the script): MsgBox(CStr(Dts.Variables("var1").Value)) In this case, I having an error (cause the variable may no exist) but i can see the value of the variable in the MessageBox and the execute package task fail because of this error. After I tried this solution: (Found in this Forum) In the child package, I create the variable var1 (same type, no value). Then in the control flow, I right click on the background and select "package configurations". I enable package configurations. Then I add a new one. I change the configuration type to "parent package variable." Then, in the specify configuration settings entry, I enter the name of the variable in the parent package. On the following screen, I select the "value" property of the variable created in this package (Child package). At last, I give the configuration a name and hit finish. If I execute my parent package, the result is no value in the MessageBox. I also tried the same thing (with the package configuration) to pass a a variable with the connection string from a package to the connection of an another one (I set the connection string of the connection) and it doesn't work. What did I miss ? Thanks in advance for your help. Regards
View Replies !
Parent / Child Variable Frustration
Can't figure out why either of the two setups I've tried on this aren't working. Any help would be appreciated. Business need: Clients upload six MS Access (mdb) data files to server, data files loaded into SS2005 database. Because of legacy concerns, even though each client's six files contain the same data, the individual file names are different. Setup: Package 1 searches the client's ftp folder for new file. If found, contents are unzipped and moved to client's staging folder while uploaded files are archived. Package 1 then calls package 2, passing to it the end date for this particular file, along with client-specific data (e.g. the variables used in the connection strings for the six mdb files and package 2's log file, which appends the client name to a constant file name). Package 1 uses a different config file for each client, and the package is run with that applicable config file. Problem: For some reason, the parent variables are not being passed properly to Package 2. If I run it with the first client, everything works fine. If I run it with the second, Package 1 works fine (finds the file, does the naming, etc.), but Package 2 is using the default variable values that are coded in the Value column in the Variables pane. The first time I tried it, the first step of Package 2 was a script step whose only function was to set Package 2's variables to the values in Package 1: Dts.Variables("package2variablename").Value = Dts.Variables("package1variablename").Value This didn't work at all. The log file's name still used the default column value instead of what Package 1 had, and the package was trying to find the wrong file name in the staging directory. The second time, I set Package 2 up to use configurations and have one parent package entry for each variable I want to set. This worked slightly better, as while there was still a log file with the Value-column value in it, there was also one for the client-specific name. However, the package still failed as Package 2 is still looking for the wrong file name. Obviously there's something in there I haven't set correctly, but I'm at a loss as to what it is.
View Replies !
Problem Of Parent-Child Package With ASP.Net
I have an ASP.NET application that calls a SSIS package. The SSIS package internally calls some other child packages. I observed that sometimes some child pacakges are not even called by the parent. and the behaviour is very indeterminate. sometimes they work fine. sometimes they don't. There is no clue available for this behaviour when the pacakges are not executed. (One general observation is that the memory consumption is very high. but that is the case always.) I have enabled logging on all child packages. The log is not updated at all when the child packages failed to execute. i.e. the package execution does not start. Could somebody explain why this is happening? any suggestions/ similar experiences? Regards Saurabh
View Replies !
Parent-Child Hierarchy But Reversed(!)
Hi! I have a report that uses a parent-child hierarchy in a table. It is hidden with the toggle set to itself, and that works perfectly. However, instead of having it look like this(simple example): + Profit After the + has been clicked: -Profit +Sales +Cost I would like it to look like this: +Sales +Cost -Profit Is this possible with when using a parent-child hierarchy? http://ssasfreak.spaces.live.com/
View Replies !
Parent Child Subtotals / Reverse
Hi I'm working with parent-child hierarchies. My source is AS2005. What I have now is: + parent 1 child 1.1 child 1.2 child 1.3 + parent 2 child 2.1 child 2.2 What I would like is: child 1.1 child 1.2 child 1.3 + parent 1 child 2.1 child 2.2 + parent 2 This would allow me to simulate subtotals for the hierarchy in a more financially intuitive way. I call it a simulutation because the optimal solution would be to use a normal group for the parent-child hierarchy and then apply group-headers and -footers. However, this does not seem possible and the documentation I've found also describes parent-child hierarchies wrt. the details-group only. I have thought sorting the data - perhaps on levels, but I haven't got anything to work yet. One of the things that annoys me the most when developing in RS2005 is that it's very hard to debug the layout. For instance, when I apply sorting - first ascending then descending - and the result is the same I get no messages telling me what's wrong (which there obviously must be...). Best regards Mikkel
View Replies !
Parent-Child Report Problem
Hi, I have a report which I have created 3 or 4 child reports from. If I make a change to the format of an object within the parent report (e.g. the colour of a text box) then the change appears on the child reports as I would expect. The problem is that if I make a change to the properties of the report (e.g. the margins or the page size) then the change is NOT picked up in the child reports. Does anybody have any solution to this annoying problem. Regards, James
View Replies !
Storedproc For Parent Child Gridview
Hi I have a search page which contains 4 fields.Giving input to anyone of the field should display the result in Parent Gridview.Parent Gridview has button in it .when i click on the button child Gridview should display related refund details of customer in parent Gridview. let us think i have two tables like Customer and refunddetails. Parent Gridview should display Customer details,Child should display corresponding customers refund details. I need two storedprocs for binding to both Gridviews. i have first stored proc for Gridview1 set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[MyProc] (@val1 varchar(255), @val2 varchar(50), @val3 varchar(50), @val4 varchar(50)) --@out smallint OUTPUT AS select * from customer where ((@val1 IS NULL) or (name = @val1)) AND ((@val2 IS NULL) or(ssn = @val2)) AND ((@val3 IS NULL) or(accountnumber = @val3)) AND ((@val4 IS NULL) or(phonenumber = @val4)) now i need to capture the @val1 from storedproc1 and using that value retrieve the remaining values in refund table. name is common in both the tables. i need this because user can search the value using ssn or accountnumber or phonenumber or name.it is not required that user serches using name.Name textbox can be null. so please someone help me.
View Replies !
Assign Single ID To Parent And Child Doc.
Hi, i have 2 Tables. i am saying emails in table1 and also if there is any attachments there i saved them also in the same table but with differet ID, and in another table i am saving the relationship of both documents. Now i have defined another table in which i want to store a single New ID generated by the new Table to email document and it's attachment document.. what i am now doing i just create a SP, and it's take the first record from the main table and check if there is attachments , if no then it's just assign a new ID and save the document id into newly created table, but if there is attachement then it's go to relationship table and returen me the child id on behalf of parent ID and then in the newly created table it save the child docment id with the same id as the parent id has already been saved. i m doing like this SELECT all the emails those has attachments.. Insert Into newlyCreated Table (ID,doc)values(1,1) then SELECT doc from Relationship table where Maintable doc=@doc Insert into newlycreated tAble(ID,doc)values(1,2) and so on .. and if i have more then one attachement then i have to use the loop.. what i m looking , is there any short way to do this all, bcoz it's taks ages to do.. is there any single line query that i can use to get the child doc id rather then going into loops .. Thanks ....
View Replies !
Returning Parent / Child Rows
Hi, I have the following table JobID JobType JobName ParentJobID etc... 12345 Disconnection MyJob1 98475 New Pilot MyJob2 12345 85746 Streetlight MyJob3 98475 etc I need a query that returns all the jobs that relate to a specific job e.g if I pick 12345 I get the following: 12345 Disconnection MyJob1 98475 New Pilot MyJob2 85746 Streetlight MyJob3 As they are all related records. Can anyone help Thanks
View Replies !
Delete Child If Parent Deleted
dear friend,, i have two table.in my first table id is primarykey and in my second table id if foreign key. so my need i have to use one query to delete the primary key table values. so if i am deleting one id in primary key table the child in the second table has to be deleted automatically.if parent get deleted the child should get deleted automatically. so plese help me to do this please give me sample query please
View Replies !
Making Sure Every Parent Has A Child - Constraints
hello there, I think I might know the answer to this, but I wanted to see if any one has come up with a slick idea for enforcing this relationship. If I have two tables and one is dependent on the other (parent-child relationship), how can I enforce that every parent record has a corresponding child record? Here is a code example USE tempdb GO CREATE TABLE dbo.Parent ( ParentId int NOT NULL ) ALTER TABLE dbo.Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentId) CREATE TABLE dbo.Child ( ParentId int NOT NULL ,ChildId int NOT NULL ) ALTER TABLE dbo.Child ADD CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (ParentId, ChildId) ALTER TABLE dbo.Child ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentId) REFERENCES dbo.Parent(ParentId) INSERT INTO dbo.Parent VALUES (1) INSERT INTO dbo.Child VALUES (1,1) INSERT INTO dbo.Parent VALUES (2) INSERT INTO dbo.Child VALUES (2,2) INSERT INTO dbo.Child VALUES (2,1) INSERT INTO dbo.Parent VALUES (3) SELECT p.ParentId, 'I SHOULD HAVE A DEPENDENT RECORD' FROM dbo.Parent p LEFT JOIN dbo.Child c ON p.ParentId = c.ParentId WHERE c.ParentId IS NULL ParentId 3 should have a child record associated with it. I am assuming that these are my choices: 1) code all inserts to the parent table along with a insert to the child table and wrap those in a transaction 2) place a trigger for insert on the parent table that ensures that the child table is populated after data for the parent. Here is the gotcha, we will be using a middle-tier data access layer (nhibernate or dlink) so .NET application developers will be creating the data modifications at the transactional level. Also there might be several ongoing ETLs that populate this schema as well, so multiple points of entry and seperate code blocks. I don't want to hide business logic within triggers. I assume that all our coders are competent and could enforce this properly via code, but I know that mistakes happen. Has any one come across this situation and have a solution for enforcing the integrity of the schema with constraints?
View Replies !
|
TRACKER
