[dba-SQLServer] NEVERMIND (Fw: executing systemstored procedure)

Susan Harkins ssharkins at gmail.com
Tue Jan 13 15:03:23 CST 2009


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
> 




More information about the dba-SQLServer mailing list