Board index » delphi » MSSQL fails to cascade update or delete

MSSQL fails to cascade update or delete

Can anybody explain why %subj%
It is declared that MSSQL would update all referencing fields in all
referencing tables if I update the referenced table.
But it doesn't do so, generating an error. Why?
I tried to figure out some options but couldn't find anything. Maybe the
option is hold in some system table?
 

Re:MSSQL fails to cascade update or delete


May be you have more than 15 related tables. If it so, MSSQL limitation
works - only up to 16 tables may be involved in one query.

Re:MSSQL fails to cascade update or delete


Do you know if this limit has been increased in SQL Server 7.0? I seem to be
running into this limit quite frequently especially in views and in tables
which are lookups for many other tables in the database. I do not run into
such a limit in Oracle, Informix, DB2 or Interbase. Either their limits are
significantly greater than SQL Server or they have no limit in terms of
number of tables involved in queries.
Quote
Sergey Shapovalov wrote in message <71cfg2$ll...@forums.borland.com>...
>May be you have more than 15 related tables. If it so, MSSQL limitation
>works - only up to 16 tables may be involved in one query.

Re:MSSQL fails to cascade update or delete


Elliott wrote <71cg7c$ll...@forums.borland.com> ...
Quote
>Do you know if this limit has been increased in SQL Server 7.0?

No, I don't know it. I don't work with MSSQL now, because it doesn't satisfy
my requirements.
But I think, Microsoft must to fix this problem, because there are no real
limitations, so it's artificial one.
Even compact SQL Anywhere have not such limitation.

Re:MSSQL fails to cascade update or delete


Not that I like defending Microsoft, but there is the possibility of
using
#Temp tables inside the SQL that can do large selects, store it, do
another select, store it, etc..

That way I believe you could do the large join or the like inside the
maximum range referring to the #Temp tables.

Rkr

Quote
Sergey Shapovalov wrote:

> Elliott wrote <71cg7c$ll...@forums.borland.com> ...
> >Do you know if this limit has been increased in SQL Server 7.0?
> No, I don't know it. I don't work with MSSQL now, because it doesn't satisfy
> my requirements.
> But I think, Microsoft must to fix this problem, because there are no real
> limitations, so it's artificial one.
> Even compact SQL Anywhere have not such limitation.

--
                   \|||/
                   /'^'\
                  ( 0 0 )
--------------oOOO--(_)--OOOo--------------
. Reid Roman                              .
. Delphi Programmer / Analyst             .
. TVisualBasic:=class(None);              .
. May the Source be With You              .
-------------------------------------------
. Auto-By-Tel (http://www.autobytel.com)  .
. Irvine, CA U.S.A                        .
. E-Mail : rkroman (at) pacbell (dot) net .
. or reidr (at) autobytel (dot) com       .
-------------------------------------------

Re:MSSQL fails to cascade update or delete


This error often occures when executing

Quote
Reid Roman wrote...
>#Temp tables inside the SQL that can do large selects, store it, do
>another select, store it, etc..

Unfortunately, this error often occurs in internal MS SQL queries, such as
cascade deletion which is a subject of this discussion.
In this case your method will not work.

Re:MSSQL fails to cascade update or delete


Sergey Shapovalov <shapo...@by.rainbow.com> D?? ???
<71cfg2$ll...@forums.borland.com>...

Quote
> May be you have more than 15 related tables. If it so, MSSQL limitation
> works - only up to 16 tables may be involved in one query.

No. I just have two related tables - and it doesn't work.
SQL server reports an error stating that it cannot update a field because
there exists a foreign key on this field
So it looks like it can't update such records at all.

Re:MSSQL fails to cascade update or delete


SQL Server 7.0 has a limit of 256 tables in a single query versus 16 in SQL
Server 6.5...

Stew.
at...@execpc.com

Quote
Elliott wrote in message <71cg7c$ll...@forums.borland.com>...
>Do you know if this limit has been increased in SQL Server 7.0? I seem to
be
>running into this limit quite frequently especially in views and in tables
>which are lookups for many other tables in the database. I do not run into
>such a limit in Oracle, Informix, DB2 or Interbase. Either their limits are
>significantly greater than SQL Server or they have no limit in terms of
>number of tables involved in queries.

>Sergey Shapovalov wrote in message <71cfg2$ll...@forums.borland.com>...
>>May be you have more than 15 related tables. If it so, MSSQL limitation
>>works - only up to 16 tables may be involved in one query.

Other Threads