Update Cascade - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Cascade

Author

Topic

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-12-04 : 09:57:23

When editing data on my website I get an error. Some tables will work whilst others will result in an error. If I deselect 'update cascade' on the sql2000 backend then I don't get an error.

On my sql2005 development server i have the same set of tables and all set for cascading updates and I don't get any problems.

I don't know why cascade updates is causing the error on the production server and thinking about it what does the cascade update actually do?. The data on the production server is a subset of the data on the development server.

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-04 : 10:00:24

what is the ddl of the the related tables, and the foreign key between them? Is it identical in production?

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 10:01:36

whats the error you're getting?

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-12-04 : 10:24:25

quote:
Originally posted by visakh16

whats the error you're getting?

It looks like a general error which doesn't help me:
Line:604, Error: Sys.WebForms.PageRequestManagerServerErrorException

But thanks because I then realised I could test it using an update command in the query analyser of the production database.

The update command successfully updated the table so there must be something wrong with the code behind the GUI

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-12-04 : 10:37:37

quote:
Originally posted by visakh16

whats the error you're getting?

I don't have any updates that need cascading. If I delete a row the delete cascades will delete all foreign rows which I want but I don't see the need for update cascade. So what if a user updates a row? It doesn't affect child tables.

Anyway, can I just deselect the cascade update or should I look into why it works on the dev machine but not the prod machine?

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-12-04 : 11:19:33

quote:
Originally posted by visakh16

whats the error you're getting?

I'm doing a different type of update and getting the same error message with additional text:
The query processor could not produce a query plan from the optimizer because a query cannot update a text, ntext, or image column and a clustering key at the same time

jholovacs
Posting Yak Master

163 Posts

Posted - 2008-12-04 : 11:45:32

quote:
Originally posted by insanepaul

quote:
Originally posted by visakh16

whats the error you're getting?

I don't have any updates that need cascading. If I delete a row the delete cascades will delete all foreign rows which I want but I don't see the need for update cascade. So what if a user updates a row? It doesn't affect child tables.

Anyway, can I just deselect the cascade update or should I look into why it works on the dev machine but not the prod machine?

I'd say you need to verify that the DDL on your dev and prod systems for your tables and the foreign key are identical. That sort of thing has gotten me a couple of times; they look close, but one is a *little* different, or a setting like ANSI_NULLS, XACT_ABORT, or something else you would think has nothing to do with it is different... but of course it breaks everything with a cryptic error message.

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-12-08 : 08:53:47

quote:
Originally posted by jholovacs

quote:
Originally posted by insanepaul

quote:
Originally posted by visakh16

whats the error you're getting?

I don't have any updates that need cascading. If I delete a row the delete cascades will delete all foreign rows which I want but I don't see the need for update cascade. So what if a user updates a row? It doesn't affect child tables.

Anyway, can I just deselect the cascade update or should I look into why it works on the dev machine but not the prod machine?

I'd say you need to verify that the DDL on your dev and prod systems for your tables and the foreign key are identical. That sort of thing has gotten me a couple of times; they look close, but one is a *little* different, or a setting like ANSI_NULLS, XACT_ABORT, or something else you would think has nothing to do with it is different... but of course it breaks everything with a cryptic error message.

I exported or imported or backed up the database (can't remember which) but after getting an error when editing a grid on the application I noticed that I needed to set the primary and foreign keys and constraints on all the tables. Having done this I'm left with a couple of tables that still get errors. How does one check the ddl?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 09:02:03

just use

sp_help <tablename>

to check tables DDL

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-12-08 : 09:38:49

quote:
Originally posted by visakh16

just use
sp_help <tablename>

to check tables DDL

I'm comparing one database with another but will take a long time. Is there a way of saving the results to file then I can quickly compare the data in each file? I found 1 field that had a different length but after changing it I still get an error. So I gotta keep looking.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 09:48:02

quote:
Originally posted by insanepaul

quote:
Originally posted by visakh16

just use
sp_help <tablename>

to check tables DDL

I'm comparing one database with another but will take a long time. Is there a way of saving the results to file then I can quickly compare the data in each file? I found 1 field that had a different length but after changing it I still get an error. So I gotta keep looking.

you mean comparing each table's ddls and store differences?

insanepaul
Posting Yak Master

178 Posts

Posted - 2008-12-08 : 10:07:26

quote:
Originally posted by visakh16

quote:
Originally posted by insanepaul

quote:
Originally posted by visakh16

just use
sp_help <tablename>

to check tables DDL

I'm comparing one database with another but will take a long time. Is there a way of saving the results to file then I can quickly compare the data in each file? I found 1 field that had a different length but after changing it I still get an error. So I gotta keep looking.

you mean comparing each table's ddls and store differences?

yes, i should have 2 identical databases but i need to check that they are identical.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources