syntax problem: How to EXEC within EXEC

syntax problem: How to EXEC within EXEC

Post by twoplus.. » Fri, 20 Oct 2000 04:00:00



If someone can write an easier way, please let me know!!!.

What I would like to do is read particular table names from sysobjects
and using sp_spaceused, populate the table TmpTblSpaceUsed.
I'd rather not have to open a cursor and was wondering if I could do
this using only a couple of lines.

PS. Is there also an easy way to convert the varchar(18) eg '18 KB'
results from sp_spaceused to a numeric value ... eg 18

CREATE TABLE #TmpTblSpaceUsed
 (table_name    sysname,
  nrows         int default 0,
  reserved      varchar(18),
  data          varchar(18),
  index_size    varchar(18),
  unused        varchar(18)
 )



    sysobjects.id >  50099219 '


Thanks
TwoPlusTwo

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

syntax problem: How to EXEC within EXEC

Post by Thomas R. Humme » Fri, 20 Oct 2000 04:00:00


If you're using 7.0 then you can use the stored procedure
sp_MSForEachTable to do what you're trying to do. Try something like:

sp_MSForEachTable 'INSERT INTO #TmpTblSpaceUsed EXEC sp_spaceused "?"'

or you can also try:

INSERT INTO #TmpTblSpaceUsed sp_MSForEachTable 'EXEC sp_spaceused "?"'

   HTH,
    -Tom.



> If someone can write an easier way, please let me know!!!.

> What I would like to do is read particular table names from sysobjects
> and using sp_spaceused, populate the table TmpTblSpaceUsed.
> I'd rather not have to open a cursor and was wondering if I could do
> this using only a couple of lines.

> PS. Is there also an easy way to convert the varchar(18) eg '18 KB'
> results from sp_spaceused to a numeric value ... eg 18

> CREATE TABLE #TmpTblSpaceUsed
>  (table_name    sysname,
>   nrows         int default 0,
>   reserved      varchar(18),
>   data          varchar(18),
>   index_size    varchar(18),
>   unused        varchar(18)
>  )



>     sysobjects.id >  50099219 '


> Thanks
> TwoPlusTwo

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

syntax problem: How to EXEC within EXEC

Post by twoplus.. » Fri, 20 Oct 2000 04:00:00


Thanks for the input Tom .....

But I'm not sure if it will work.. You see I already have this code and
other code wrapped in a
exec sp_MSforeachdb
  "USE ?

I beleive SQL 7.0 gets confused when running a foreach of DB and a
foreach for table..

Any other suggestions?



> If you're using 7.0 then you can use the stored procedure
> sp_MSForEachTable to do what you're trying to do. Try something like:

> sp_MSForEachTable 'INSERT INTO #TmpTblSpaceUsed EXEC sp_spaceused "?"'

> or you can also try:

> INSERT INTO #TmpTblSpaceUsed sp_MSForEachTable 'EXEC sp_spaceused "?"'

>    HTH,
>     -Tom.



> > If someone can write an easier way, please let me know!!!.

> > What I would like to do is read particular table names from
sysobjects
> > and using sp_spaceused, populate the table TmpTblSpaceUsed.
> > I'd rather not have to open a cursor and was wondering if I could do
> > this using only a couple of lines.

> > PS. Is there also an easy way to convert the varchar(18) eg '18 KB'
> > results from sp_spaceused to a numeric value ... eg 18

> > CREATE TABLE #TmpTblSpaceUsed
> >  (table_name    sysname,
> >   nrows         int default 0,
> >   reserved      varchar(18),
> >   data          varchar(18),
> >   index_size    varchar(18),
> >   unused        varchar(18)
> >  )



> >     sysobjects.id >  50099219 '


> > Thanks
> > TwoPlusTwo

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

1. EXEC syntax within Conditions?

I think you need to put the whole code inside a EXEC statement.
You can assign the whole query (code) to many character variables, and then
concatenate the variables inside the EXEC.
i.e.




Hope that helps....
--
-------------------------------------------
Carlos Eduardo Rojas
MCSE + I, MCDBA, MCSS

2. Macintosh ODBC ??

3. Syntax problem in exec statement

4. currency format

5. SQL Exec /Cmd Exec Fails

6. ISQL vs includes

7. EXEC wth SP within a select statement

8. Changing field name

9. Exec a SP within a insert statement using #TmpTable

10. Exec store proc within a store proc

11. exec within sp

12. Convert() within an Exec Command (new)

13. Convert() within an Exec Command