Susan Harkins
ssharkins at gmail.com
Tue Jan 13 15:11:39 CST 2009
Martin, my guess is this is just about AdventureWorks -- since it's downloadable and doesn't actually come with SQL Server, my guess is you have to explicitly specify the path to the system stored procedure. However, that doesn't explain why the following doesn't work, regardless of the syntax: EXEC sys.sp_MSForEachTable 'DELETE FROM ?' That would delete all the records in any table without constraints to another table, but it doesn't. The message says it does, but it doesn't. Since other examples work, I can only guess that I'm expecting the wrong result. Susan H. > Okay, a little more experimenting and here's what's interesting. > > Mystery #1: When running against AdventureWorks, I have to use master..sp_ > syntax, but others recognize sys.sp_ syntax. > Mystery #2: Even when using master..sp_ against AdventureWorks, it > recognizes the sp, but doesn't actually run it -- I get a message that > it's been successfully completed, but it hasn't actually done a thing. > > Susan H. > > > > ----- Original Message ----- > From: "Martin Reid" <mwp.reid at qub.ac.uk> > To: "Discussion concerning MS SQL Server" > <dba-sqlserver at databaseadvisors.com> > Sent: Tuesday, January 13, 2009 3:50 PM > Subject: Re: [dba-SQLServer] NEVERMIND (Fw: executing systemstored > procedure) > > >> Susan >> >> I ran it just there and it worked fine here I changed the database to a >> copy I have here and it worked fine. >> >> [dbo].[Customers] 29 >> [dbo].[Employee Privileges] 1 >> [dbo].[Employees] 9 >> [dbo].[Inventory Transaction Types] 4 >> [dbo].[Inventory Transactions] 102 >> >> use NWind >> go >> create table #rowcount (tablename varchar(128), rowcnt int) >> exec sp_MSforeachtable >> 'insert into #rowcount select ''?'', count(*) from ?' >> select top 5 * from #rowcount >> order by tablename >> drop table #rowcount >> >> >> Martin >> >> >> Martin WP Reid >> Information Services >> Queen's University >> Riddel Hall >> 185 Stranmillis Road >> Belfast >> BT9 5EE >> Tel : 02890974465 >> Email : mwp.reid at qub.ac.uk >> ________________________________________ >> From: dba-sqlserver-bounces at databaseadvisors.com >> [dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan Harkins >> [ssharkins at gmail.com] >> Sent: 13 January 2009 20:38 >> To: Discussion concerning MS SQL Server >> Subject: Re: [dba-SQLServer] NEVERMIND (Fw: executing system stored >> procedure) >> >> I've tried several examples now -- they run, but they don't actually >> return >> anything -- this one creates the table, but doesn't fill it. >> >> Susan H. >> >> >>> Try this >>> >>> Just lifted it of the web >>> >>> use pubs >>> go >>> create table #rowcount (tablename varchar(128), rowcnt int) >>> exec sp_MSforeachtable >>> 'insert into #rowcount select ''?'', count(*) from ?' >>> select top 5 * from #rowcount >>> order by tablename >>> drop table #rowcount >> >> _______________________________________________ >> 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 >> >