Asger Blond
ab-mi at post3.tele.dk
Wed Jan 14 10:01:51 CST 2009
Sorry, please correct first sentence to "I tried this on both AdventureWorks using SQL Server 2005 and AdventureWorks2008 using SQL Server 2008". Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Asger Blond Sendt: 14. januar 2009 16:53 Til: 'Discussion concerning MS SQL Server' Emne: Re: [dba-SQLServer] ?s on sp_MSForEachTable I tried this on both AdventureWorks using SQL Server 2000 and AdventureWorks2008 using SQL Server 2005, and I *did* get error messages for failing deletes due to constraint violations, as expected. Don't know why the error messages are missing in your setup. Asger -----Oprindelig meddelelse----- Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Susan Harkins Sendt: 14. januar 2009 14:56 Til: Discussion concerning MS SQL Server Emne: Re: [dba-SQLServer] ?s on sp_MSForEachTable Maybe you can help. When I run the following statement EXEC sys.sp_MSForEachTable 'DELETE FROM ?' I should get an error on tables with constraints to other tables. Interesting, when I run TRUNCATE TABLE, I see those errors. With DELETE FROM, I see a message that the statement's been completed successfully instead of seeing the expected error messages for related tables. Should I see an error message for these tables or does it just happily delete what it can and ignore the tables it can't? Susan H. >I am, and so is Tim Chapman, who has written a couple of extensions to it, > available from www.techrepubic.com > >> I ran the following statement against a copy of AdventureWorks, expecting >> to >> see some constraint errors: >> >> EXEC sp_MSForEachTable 'DELETE FROM ?' >> >> Instead, the command was completely successfully -- by message, but of >> course, no data was deleted, which is what the statement should do -- >> delete >> all the records from every table in the database. Of course, I didn't >> expect >> it to work because of the constraints, but I did expect to see some kind >> of >> error message. >> >> It's an undocumented sp and despite my efforts to figure this out, I'm >> not >> finding any help. I've found a lot of information on the different >> arguments >> and how it's supposed to run, but answers to the above question -- why >> not >> error message -- can't find that sort of information. >> >> Is anyone familiar with this sp? >> >> Susan H. >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com