i get a error message like this: Table RENTAL_ITEM in RENTALB23 does not have a matching parent key.
this is the sql:
CREATE TABLE RENTALB23/RENTAL_ITEM (RENTALITEM# INT PRIMARY KEY,
RENTALTYPE CHAR(3), TITLE CHAR(30), CATEGORY CHAR(15), DUEDATE DATE,
FOREIGN KEY(RENTALTYPE) REFERENCES
RENTALB23/RENTAL_CHARGE(RENTALTYPE), FOREIGN KEY(CATEGORY)
REFERENCES RENTALB23/RENTAL_CHARGE(CATEGORY))
this is the sql for the rental_charge table:
CREATE TABLE RENTALB23/RENTAL_CHARGE (RENTALTYPE CHAR(3),
CATEGORY CHAR(15), CHARGE DECIMAL(5,2), PRIMARY KEY(RENTALTYPE,
CATEGORY))
I am running a parent SSIS package (running sp2, 9.0.3042) that calls several child packages.
On our development server, we now cannot run this because we get 1 or more of these errors:
"Error 0x80004003 while preparing to load the package. Invalid pointer . " "Error 0xC0011008 while preparing to load the package. Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored. . "
It is not occuring on the same packages. It varies every time it is run.
I can run every one of the child packages individually, using the same login ID that the parent is executed under.
The parent package works fine on my local machine and other servers running the same version of SSIS. Just not on this server.
I'm building a page that will display link categories with the links under each one. I have that part working however there is a code issue that I cant quite figure out related to the SQL query. I want to be able to have the category only display if the 'linkcolumn' is set to 1 in the database. (so I can later separate which ones show in each column of the page. However, I get an error if I try to make my 'select' statement have a 'where' in it. I think it does this because it ends up not showing all the records but I'm not sure why. Exception Details: System.ArgumentException: This constraint cannot be enabled as not all values have corresponding parent values. My codebehind file: using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Data.Sql;using System.Data.SqlTypes; public partial class Modules_NCG_NCGLinks : System.Web.UI.UserControl {public void Page_Load(object sender, EventArgs e) { //Create the connection and DataAdapter for the Category table.SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["mystringname"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from NCG_Links_Category Where LinkCategory = 'Test'",cnn); //Create and fill the DataSet.DataSet ds = new DataSet(); cmd1.Fill(ds, "NCG_Links_Category"); //Create a second DataAdapter for the Links table.SqlDataAdapter cmd2 = new SqlDataAdapter("select * from NCG_Links",cnn); cmd2.Fill(ds,"NCG_Links"); //Create the relation bewtween the Category and Links tables.ds.Relations.Add("myrelation", ds.Tables["NCG_Links_Category"].Columns["LinkCategory"],ds.Tables["NCG_Links"].Columns["LinkCategory"]); //Bind the Category table to the parent Repeater control, and call DataBind.parentRepeater.DataSource = ds.Tables["NCG_Links_Category"]; Page.DataBind(); //Close the connection. cnn.Close(); }private void Page_Init(object sender, EventArgs e) { InitializeComponent(); }private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } }
I am very new to SQL Server 2005, and I am currently trying to migrate a 2000 db to 2005.
I have now migrated all the DTS to SSIS, removing any legacy & replacing with new functionality.
I have been through a group of related packages ironing out any problems. They ALL now run fine when called individually.
However, I am now having problems with the parent package that is used to string these all together. It will not even run the 1st package.
For info, the first task of this package is a "Transfer SQL Server Objects Task". I am copying a few tables with data, but WITHOUT indexes, etc.
Following this, there is an "Execute SQL Task" that creates an index on one of those copied tables.
To get past another issue I had to set the "CopySchema" property of the "Transfer..." task to true, but did not select any schemas to transfer.
Now, when running the PARENT package, it is failing at the 1st step of the 1st package as follows:
Error: 0xC002F325 at Copy LT Data Tables, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert duplicate key row in object 'dbo.Hidden Hearing Limited$Telemarketer' with unique index 'IX_Hidden Hearing Limited$Telemarketer1'.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
I have been searching and searching, finding similar problems, and even the same error codes, but nothing quite matching this particular scenario.
I have been trying to set the "DelayValidation" at various levels, but with no luck.
I would greatly appreciate any help in trying to resolve this. Has anyone else had the same, or similar, issue?
I am very new to SQL Server 2005, and I am currently trying to migrate a 2000 db to 2005.
I have now migrated all the DTS to SSIS, removing any legacy & replacing with new functionality.
I have been through a group of related packages ironing out any problems. They ALL now run fine when called individually.
However, I am now having problems with the parent package that is used to string these all together. It will not even run the 1st package.
For info, the first task of this package is a "Transfer SQL Server Objects Task". I am copying a few tables with data, but WITHOUT indexes, etc.
Following this, there is an "Execute SQL Task" that creates an index on one of those copied tables.
To get past another issue I had to set the "CopySchema" property of the "Transfer..." task to true, but did not select any schemas to transfer.
Now, when running the PARENT package, it is failing at the 1st step of the 1st package as follows:
Code BlockError: 0xC002F325 at Copy LT Data Tables, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot insert duplicate key row in object 'dbo.Hidden Hearing Limited$Telemarketer' with unique index 'IX_Hidden Hearing Limited$Telemarketer1'.". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
I have been searching and searching, finding similar problems, and even the same error codes, but nothing quite matching this particular scenario.
I have been trying to set the "DelayValidation" at various levels, but with no luck.
I would greatly appreciate any help in trying to resolve this. Has anyone else had the same, or similar, issue?
Hi, I have a child package running under main parent pakage. I am passing value of integer & string type variables from parent package to child package's variables ( as disscussed in following artical http://msdn.microsoft.com/en-us/library/ms345179(SQL.100).aspx) ,values assigned in child package variables are futher used to build connection string for flat file connection manager in child package.
I have been using this whole SSIS package sucessfully for last three months,but suddenly the following error arises
on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
When I see the result of above mentioned expression through Log file than it appears that my string variable
Code Snippet"@[User::StrTextFileImpDirectory]
in child package has not been populated with any value from parent package variable, I.e it has been populated with empty string(While this is not desired value).
Same problem arises with my integer type variable
Code Snippet@[User::Staging_Date_Key]
,it has ben populated with value zero (i.e default value at design time for integer type),while this is not desired value.
Can some one guide me how to figure out the cause for error.... Is it possible that such kind of error may arise due to any change in enviornment of server....
I am getting this error when trying to open a report created with the cube. We use dundas olap chart to generate the xml for the report from the base cube or default perspective. What we do is change the xml dynamically to a different perspective for security purposes and then load the report. If the report xml accesses a dimension that is hidden the assigned perspective then we get the error shown below. Does anyone know how to handle this error?
Exception Details: System.NullReferenceException: Internal error. Dimension level or it's parent is NULL.
This only happens with some of our dimensions. With the other hidden dimensions we get no data message on the chart which is what we want.
I am comparing our dimensions to the Adventure Works cube dimensions which seem to work (I have not tried all of their dimensions yet). Nothing sticks out to me as being a setting that will make the error go way. Anyone have suggestions?
Other things I tried but didn't help were removing the parent child hierarchies and Role based security that uses one of the dimensions that throws the error. I also did a trace on the cube when the error is thrown. I could not see any errors and I was able to execute all of the MDX queries without any problems (I copied and pasted the mdx into SSMS).
As an aside we tried used the Cube Roles and Dimension security (Hiding measures in the Measures Dimension too) but the error that MS (#Error or #Value) throws causes dundas not to work. We also looked UDFs and Cell Level Security but both are more complex/what we need for now.
I am trying to pass back the number of errors encountered by a child package to the Parent package. I have a script within the child package, which will set the value of the Parent package's variable (ChildErrCount). However, I have no idea how to access the Child package's Errors collection to get a count.
Any ideas? Has someone figured out a way to reference the current package's properties (besides what's available from Dts.* ?
I have the below query which in turn goes in the where clause
Code:
SELECT CASE WHEN EXISTS (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) THEN (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) END
I am getting below error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I tried to solve this problem with If and else but it is not going well with in-clause
Code: select * from InspectionType where id in (select IF EXISTS (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) BEGIN (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) END ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) )
I am using select * from InspectionType where id in() for illustation only. The sub-query results will go another query
Code: SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129
This returns more than one row.I know why is the error message but need to achieve this functionality.
Unhandled exception has occurred in a component in your application
If you click Continue, the application will ignore this error and attempt to
continue.
Cannot create a stable subkey under a volatile parent key.
It then provides a whole pile of (to the absolute beginner) mumbo-jumbo under a details button.
It does provide the option to continue, whereupon everything appears normal; however, I consider this rather unwise at my very first opening of Management Studio Express.
I have created a databse in VB Express (I have completed the Lesson 09 Video) dealing with databinding - I created the application etc with the video to enhance my learning.
Should this affect it? It's the only database I have created.
This looks to me (my Express illiteracy intact, of course) like the kind of message one would get after having an error in something already created or in the process of being created.
What is this? I was going to begin the video tutorials for SQL, but not with this message.
I have only just installed the Express 'suite' a few days ago.
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date) declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date) insert @parent select 1, '1/1/2015', '12/31/2015' insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
Iam running sql sever 2005 express with advanced services but when i try to launch sql sever management studio express, i get the following message message and i have no idea why. unhandled exception has occurred in a component in your application. If you click continue the application will ignore the this error and attempt to continue . Can not create a stable key under a volatile parent key.
whats the best way to delete a parent record? for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?my code below: The T-SQL Stored ProcedureALTER PROCEDURE dbo.DeleteInventory ( @InventoryID int ) AS DELETE FROM Booking WHERE InventoryID = @InventoryID
DELETE FROM Inventory WHERE InventoryID = @InventoryID The code in the Code Behind to execute when a button(delete) click Try Dim temp As Integer = SqlDataSource4.Delete If temp > 0 Then lblDeleteDtatus.ForeColor = Drawing.Color.Blue lblDeleteDtatus.Text = temp & " Records/Rows Deleted." Else lblDeleteDtatus.ForeColor = Drawing.Color.Orange lblDeleteDtatus.Text = "No records Deleted!" End If Catch ex As Exception lblDeleteDtatus.ForeColor = Drawing.Color.Red lblDeleteDtatus.Text = "An Error Occured.<br />" & ex.Message.ToString End Try any better methods anyone wants to share?
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;
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!
i have a dimension named 'sdoc' which has 2 members 1) symptom word( first hierarchical level) and 2) doc id (second hierarchical level)
more than one 'symptom word' can have the same 'doc id'. i want to know which all symptom words have a particular doc id (say 10.) if i give [10].parent then it returns only the first parent whereas it actually has more than one parent. how to retrieve all the parents.
I need to update the price of one row based on another. In this case it takes the price from a sku which has a value in the "ShadowOf" field. Shadow Of is a parent sku. It takes the parent sku price, adds 2% and applies it to the child sku. It's all working well except I'm not sure how to handle it when a parent has more than one child. It updates the first & sets the rest to null.Enclosed is the sample data + query.
I do not want to rollback child procedure when i am rollbacking parent procedure after execution of child procedure. in child procedure i am committing transaction and in parent rollbacking transaction. Is it possible. Is there any solution to avoid rollbacking child procedure when i rollbacking parent procedure.
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:
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.
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?
I am creating a SSRS report connecting to a cube , Which has geography as Dimension as Hirerarchy ( the Hirerachy consists of Region, Subregion) and a measure , Now this geography dimension is taken as Parameter
when i apply filters i am getting the filter based on Parameter selection , But how can i get the all children under the same parent
As i need to create a graph based on report parameter (ie i should get all the children under the Select parameter parent)
to be more specfic consider this
Region SubRegion
Asia India
Asia Pakistan
Asia Japan
Australia Melborne
Suppose my report parameter is India , I should get all the values under Asia(Parent of india)
I'm working on a report in which I need to group data in a drill down table. Specifically, I'm tracking statistics on how many users are running a specific build. So, right now, my group header displays the results from:
SELECT DISTINCT BuildName as [FriendlyName] FROM DataSource WHERE ProductID = 9 //<- 9 being just for example
Beneath each of these entries, I want the content to reflect the number of occurances of a specified build name, something like:
- Alpha
19 - Beta
144
So,
SELECT COUNT(BuildName) as [Count] FROM DataSource WHERE ProductID = 9 AND BuildName = (The name of the build, as specified by the header row that contains us)
Is there a way to access that last bit of data in VS 2005 Reporting, or do I really have to write a separate count query for Alpha, Beta, etc...
This is my first exposure to both SQL and Reporting. I'm overwhelmed. Please help.
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?
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
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?
Below is my sample data of my table named "Groups"
Code: with Groups as ( select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup ) select * from groups;
Expected result:
Code: with ExpectedResult as ( select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all select '' as GroupName,'peoplesoft' as SubGroup union all select '' as GroupName,'mysql' as SubGroup union all select 'Microsoft' as GroupName,'Nokia' as SubGroup union all select '' as GroupName,'EShop' as SubGroup union all select 'IBM' as GroupName,'Meiosys' as SubGroup union all select '' as GroupName,'UrbanCode' as SubGroup ) select * from ExpectedResult;
some sample query to how to achieve this parent-child has the same table.
