Interesting problem, Why did this happen with an index?

One of the developers here just came accross an interesting problem, I am
interested to know if there is anyone out there that can explain this to me
as to why the sql server internals are behaving this way:
Table A - > has a trigger for delete that does this: Delete FROM table B.
Table B - > has an FK to table A with On Cascade delete defined.
As is, DELETE FORM TableA works perfect, no problems.
Now, for performance reasons, a unique index is introduced on Table B that
includes the FK.
Now, DELETE FORM TableA causes an error indicating that the nesting of
triggers, stored procedures, etc, is greater than 32.
If anyone can explain why the introduction of an index in this case results
in the nesting limit being exceeded, it clearly created a never ending loop
some how, I am just unclear as to how this is happening.
Thanks,
Rick.
Rick wrote:
> One of the developers here just came accross an interesting problem,
> I am interested to know if there is anyone out there that can explain
> this to me as to why the sql server internals are behaving this way:
> Table A - > has a trigger for delete that does this: Delete FROM
> table B.
> Table B - > has an FK to table A with On Cascade delete defined.
> As is, DELETE FORM TableA works perfect, no problems.
> Now, for performance reasons, a unique index is introduced on Table B
> that includes the FK.
> Now, DELETE FORM TableA causes an error indicating that the nesting of
> triggers, stored procedures, etc, is greater than 32.
> If anyone can explain why the introduction of an index in this case
> results in the nesting limit being exceeded, it clearly created a
> never ending loop some how, I am just unclear as to how this is
> happening.
> Thanks,
> Rick.
Is this a real production issue? If so, why do have a trigger on table A
to delete related rows from table B and a cascade FK on Table B to
delete related rows in Table A. Design-wise, it seems messy and
unmanageable. Or is this just a case of a developer trying to break SQL
Server.
Can you provide more details. What rows are being deleted from the
trigger? What is the actual relationship between the tables?
David Gugick
Imceda Software
www.imceda.com

Interesting problem, Why did this happen with an index?

One of the developers here just came accross an interesting problem, I am
interested to know if there is anyone out there that can explain this to me
as to why the sql server internals are behaving this way:
Table A - > has a trigger for delete that does this: Delete FROM table B.
Table B - > has an FK to table A with On Cascade delete defined.
As is, DELETE FORM TableA works perfect, no problems.
Now, for performance reasons, a unique index is introduced on Table B that
includes the FK.
Now, DELETE FORM TableA causes an error indicating that the nesting of
triggers, stored procedures, etc, is greater than 32.
If anyone can explain why the introduction of an index in this case results
in the nesting limit being exceeded, it clearly created a never ending loop
some how, I am just unclear as to how this is happening.
Thanks,
Rick.Rick wrote:
> One of the developers here just came accross an interesting problem,
> I am interested to know if there is anyone out there that can explain
> this to me as to why the sql server internals are behaving this way:
> Table A - > has a trigger for delete that does this: Delete FROM
> table B.
> Table B - > has an FK to table A with On Cascade delete defined.
> As is, DELETE FORM TableA works perfect, no problems.
> Now, for performance reasons, a unique index is introduced on Table B
> that includes the FK.
> Now, DELETE FORM TableA causes an error indicating that the nesting of
> triggers, stored procedures, etc, is greater than 32.
> If anyone can explain why the introduction of an index in this case
> results in the nesting limit being exceeded, it clearly created a
> never ending loop some how, I am just unclear as to how this is
> happening.
> Thanks,
> Rick.
Is this a real production issue? If so, why do have a trigger on table A
to delete related rows from table B and a cascade FK on Table B to
delete related rows in Table A. Design-wise, it seems messy and
unmanageable. Or is this just a case of a developer trying to break SQL
Server.
Can you provide more details. What rows are being deleted from the
trigger? What is the actual relationship between the tables?
--
David Gugick
Imceda Software
www.imceda.com

Integration Services

Hi all,

Can someone explain to me why I am getting this kind of error though I am able to integrate all the data succeesfully to the next destination.

I am trying to get the Prescription table from Access to SQL SERVER 2005 database

Ronald

SSIS package "Prescription.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC0202009 at Data Flow Task, SQL Server Destination [521]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 58, column 1. The destination column (PatientId) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 27, column 12. The destination column (ServiceId) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 26, column 12. The destination column (ServiceId) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 25, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 24, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 23, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 22, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 21, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 20, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

I have already told you here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=481569&SiteID=1

Please will you do us all a favour and not post the same thread 4 times! Thanks very much!

-Jamie

Integration Services

Hi all,

Can someone explain to me why I am getting this kind of error though I am able to integrate all the data succeesfully to the next destination.

Ronald

SSIS package "Prescription.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC0202009 at Data Flow Task, SQL Server Destination [521]: An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 58, column 1. The destination column (PatientId) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 27, column 12. The destination column (ServiceId) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 26, column 12. The destination column (ServiceId) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 25, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 24, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 23, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 22, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 21, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The bulk load failed. Unexpected NULL value in data file row 20, column 7. The destination column (AllergyCode) is defined as NOT NULL.".

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

You are inserting NULL values into columns that do not allow NULL values.

-Jamie

|||Thanx So much.