July 31, 2002 at 1:03 pm
I need to write a procedure that would return the results of sp_spaceused to a table or output parameters/variables so I could join that with other info. from the system tables to make a report.
Help???
-K
July 31, 2002 at 6:03 pm
You can use the output of a proc as the data for an insert, like this:
insert into tablea(col1, col2, etc) exec sp_spaceused
Just need to build a table to match the output.
Andy
July 31, 2002 at 7:23 pm
Thanks Andy,
I don't suppose there would be any way to dynamically build that table based on the results ?
Like...
select * into table_x from table_y
-K
July 31, 2002 at 8:18 pm
The return from sp_spaceused should always be the same so you could predefine the layout. Select into will work if you use openrowset as the "from" and put the proc in it I think.
Andy
August 1, 2002 at 6:17 am
Andy,
I can get a select statement to work but not a procedure:
select * into zzz
FROM OPENROWSET('SQLOLEDB','server_name';'sa';'secret_password',
'exec sp_spaceused')
error:
Could not process object 'exec sp_helpdb'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
*** Also, sp_spaceused returns 2 result sets. I was able to do the insert with a procedure that returns one result set but not 2. sp_helpdb worked fine.
-K
August 1, 2002 at 8:58 am
I'll try to find an example on the openrowset. On sp_spaceused you can pass a table name to it, then you only get one result set I think.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply