Problem with cascading deletion (circular)

Problem with cascading deletion (circular)

Hi, I am reletively new to DB programming and have come across a fairly poorly designed DB. I am trying to set up cascating deletes and triggers in such a way that if the application deletes an entry in Table A then all the associate data in table B through H will also be deleted. However because of SQL Server 2005 restrictions on Cascading deletes and the cycles in the involved tables I have not been able to do it. Does anyone have some tips or suggestions?

Here is the basic design:

Table A
Table B
Table C
Table D
Table E
Table F
Table G
Table H

Relationships

1) Table B --> Table A
2) Table C --> Table B
3) Table D --> Table B
4) Table E --> Table C
5) Table E --> Table D
6) Table F --> Table E
7) Table F --> Table G
8) Table H --> Table G
9) Table H --> Table B

I can get cascading deletes for relationships 1 to 4 but once I try 5 it
recognizes the B-C-D-E cycle.

Circular cascading deletes are not allowed.

You may be able to use triggers instead.

FROM The Books Online topic:

Cascading Referential Integrity Constraints

Multiple Cascading Actions

Individual DELETE or UPDATE statements can start a series of cascading referential actions. For example, a database contains three tables: TableA, TableB, and TableC. A foreign key in TableB is defined with ON DELETE CASCADE against the primary key in TableA. A foreign key in TableC is defined with ON DELETE CASCADE against the primary key in TableB. If a DELETE statement deletes rows in TableA, the operation also deletes any rows in TableB that have foreign keys matching the deleted primary keys in TableA, and then deletes any rows in TableC that have foreign keys that match the deleted primary keys in TableB.

The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.

I think the Advanced Transact SQL Book by Tom Moreau and Itzik Ben-Gan has some design info around this.

Problem uploading data into SQL Server with SSIS

Hi

I designed a package in SSIS that loads data from an Excel source into a oledb SQL database table.

The problem I have is that two of the columns that are supposed to be nvarchar type columns sometimes contain numerical strings. These values causes an error when 'Allow Null' is not set and loads as NULL if 'Allow Null' is set.

How can I force these values into the table?

I have a script transformation object in the package with this code:

If IsNumeric(Row.OCCode) Then

Row.ItemCode = CStr(Row.OCCode)

Else

Row.ItemCode = Row.OCCode

End If

ItemCode is of type unicode string and the column in the SQL DB Table it's mapped to is of type nvarchar

What is the source data type set to for those columns in the source connection?|||

I don't know how to check that, but the input columns into the transformation from the source are unicode strings.

I Also tried Forcing the Excel columns to Text.

|||In the source, the columns are set to Unicode string [DT_WSTR]|||Yeah, I don't understand what NULLs have to do with numeric data appearing in a string field. Can you shed more light on this?|||

It seems impossible to upload Text and numeric values into one Text column in the SQL database. The excel source picks up the source data as unicode string if its alphanumeric values and Numeric type when it's numeric value. if I make all the source columns in the source Unicode String it stops the running package as soon as it comes accross a numeric value with this Error Message:

There was an error with input column "ItemDescription" (889) on input "OLE DB Destination Input" (248). The column status returned was: "The value violated the integrity constraints for the column

If I make the column accept NULL values in the database, the package completes the data load, but make all the numeric values NULL.

I can enter data manually into this database to take numeric values in the nvarchar field, but I don't know how to set the SSIS package to upload the numeric values as Text.

|||Take a look here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1277733&SiteID=1|||

I managed to upload the data, thanks. The link you added mentioned that numerical strings in the excel file need to be re-entered after the column format is changed to Text. Seeing that I had 54000 rows, I had to perform a text-function operation on all the relative cells. Something like =trim(A2) and then copy these cells back to the original columns as values.

Thanks

|||The important thing in that link was the setting of the IMEX parameter.|||

Yeah I saw the code, but don't know where to add it or how to change the IMEX property. I had to get the data in.

It will help a lot if you can explain where to change this property. I can change the registry entries.