Showing posts with label parent. Show all posts
Showing posts with label parent. Show all posts

Tuesday, March 27, 2012

Cant Cascade Delete

I have a Sql Server 2005 table with 3 fields, an ID field (primary key), a parent ID field, and Name. The parent ID references the ID field (foreign to primary - many to one) within the same table so that records can reference their parent. I would like to place a cascade delete on the ID field so that when the primary ID is removed it will automatically remove all those records with a parent ID that match. Sql server does not allow me to establish this cascade delete.

I was considering a trigger instead but only know how tio use the AFTER paramter and not an alternative.

Thanks

Hello my friend,

I see that you have a tree-like table. You have records that have a parent record, that can have a parent record that can have a parent record in the same table, and so on. The function at the bottom will help. You will need to change the table name from tblTree and the field names ParentID and PageID to whatever you have called them.

Anyway, the function will return a list of all child records. For example, if PageID 2 had childs 5 and 8, and 5 had 3 childs 67, 68, and 70, the resultset would look like the following: -

2
5
67
68
70
8

All you need to do is run a delete against this returned set as follows, which deletes number 2 and all of its children: -

DELETE FROM tblTree WHERE PageID IN (SELECT PageID FROM dbo.fnGetPages(2))

The function is as follows: -

CREATE FUNCTION dbo.fnGetPages
(
@.PageID AS INT
)

RETURNS @.ChildPageIDs TABLE(PageID INT)

AS

BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM tblTree WHERE ParentID = @.PageID

DECLARE @.TempChildPageIDs TABLE(PageID INT)
INSERT INTO @.TempChildPageIDs (PageID)
SELECT PageID FROM @.ChildPageIDs ORDER BY PageID

DECLARE @.ChildPageID AS INT
SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)

WHILE (@.ChildPageID IS NOT NULL)
BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM dbo.fnGetPages(@.ChildPageID)
DELETE FROM @.TempChildPageIDs WHERE PageID = @.ChildPageID

SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)
END
RETURN
END

If you have any questions on this, please let me know.

Kind regards

Scotty

|||Excellent stuff. Thank you!

Friday, February 10, 2012

Cannot resolve a package path

I have a parent package - Main.dtsx that calls a number of child packages. I also use configuration packages as I must deploy the application across multiple environments.

Main.dtsx contains a single Ccnfiguration file - Child_Packages.dtsConfig. This contains the connection strings for all the child packages.

Each child package contains a single configuration file - Main.dtsConfig that contains a superset of OLE DB connections. That is no single child package uses all of the connections contained in main.dtsConfig.

Each child package will execute independently without error. However, when called from Main.dtsx, I get the following errors:

Error: 2007-08-15 15:44:22.93
Code: 0xC001000E
Source: UserSecurity
Description: The connection "CRM" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Warning: 2007-08-15 15:44:22.93
Code: 0x8001F02F
Source: UserSecurity
Description: Cannot resolve a package path to an object in the package ".Connections[CRM].Properties[ConnectionString]". Verify that the package path is valid.
End Warning
Warning: 2007-08-15 15:44:22.94
Code: 0x80012017
Source: UserSecurity
Description: The package path referenced an object that cannot be found: "\Package.Connections[CRM].Properties[ConnectionString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning


I have seen discussions that suggest that these errors occur where the connection in the config file is not found in the child package. Is there any way of suppressing this ?

Are there any simpler methods that allow me to change connections in each environment?

I suspect I will end up having to tailor config files for each child package, but I live in hope.

Michael M.

Duh! The main cause of this error is the fact that the child packages do not contain all of the connections that are in Main.dtsConfig.

But it is a pain to have to add connections that you don't use to each package.

And it is pain that you can't use the Deployment utility because it crashes attempting to copy the same config files over.

|||

You can use individual configuration files if you want; that way each package uses only the config files that really requires. BTW, I think, a package does not fail because the configuration file has an entry that is not used by that package; rather you see a warning.

|||

Regarding: BTW, I think, a package does not fail...

What caused this error?

Code Snippet

Error: 2007-08-15 15:44:22.93
Code: 0xC001000E
Source: UserSecurity
Description: The connection "CRM" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error

As to writing individual config files, this is creates unneccessary work, IMHO. Other ETL packages can use a single config file.

The best solution would be to build a tool that updates the .dtsx files in each environment and then we don't need config files at all.

Cannot resolve a package path

I have a parent package - Main.dtsx that calls a number of child packages. I also use configuration packages as I must deploy the application across multiple environments.

Main.dtsx contains a single Ccnfiguration file - Child_Packages.dtsConfig. This contains the connection strings for all the child packages.

Each child package contains a single configuration file - Main.dtsConfig that contains a superset of OLE DB connections. That is no single child package uses all of the connections contained in main.dtsConfig.

Each child package will execute independently without error. However, when called from Main.dtsx, I get the following errors:

Error: 2007-08-15 15:44:22.93
Code: 0xC001000E
Source: UserSecurity
Description: The connection "CRM" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Warning: 2007-08-15 15:44:22.93
Code: 0x8001F02F
Source: UserSecurity
Description: Cannot resolve a package path to an object in the package ".Connections[CRM].Properties[ConnectionString]". Verify that the package path is valid.
End Warning
Warning: 2007-08-15 15:44:22.94
Code: 0x80012017
Source: UserSecurity
Description: The package path referenced an object that cannot be found: "\Package.Connections[CRM].Properties[ConnectionString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning


I have seen discussions that suggest that these errors occur where the connection in the config file is not found in the child package. Is there any way of suppressing this ?

Are there any simpler methods that allow me to change connections in each environment?

I suspect I will end up having to tailor config files for each child package, but I live in hope.

Michael M.

Duh! The main cause of this error is the fact that the child packages do not contain all of the connections that are in Main.dtsConfig.

But it is a pain to have to add connections that you don't use to each package.

And it is pain that you can't use the Deployment utility because it crashes attempting to copy the same config files over.

|||

You can use individual configuration files if you want; that way each package uses only the config files that really requires. BTW, I think, a package does not fail because the configuration file has an entry that is not used by that package; rather you see a warning.

|||

Regarding: BTW, I think, a package does not fail...

What caused this error?

Code Snippet

Error: 2007-08-15 15:44:22.93
Code: 0xC001000E
Source: UserSecurity
Description: The connection "CRM" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error

As to writing individual config files, this is creates unneccessary work, IMHO. Other ETL packages can use a single config file.

The best solution would be to build a tool that updates the .dtsx files in each environment and then we don't need config files at all.