July 20, 2006 at 9:21 am
Hi,
I want to record the (file) sizes of database and logfiles periodically.
I’ve found a nice stored procedure which gives me all the wanted information. Scheduling the execution and storing the result in a table seems to be a good idea to me. But...
When I execute the command:
insert into DBA_SpaceMon exec usp_SpaceMon
to store the information given by the stored procedure I get the error:
An INSERT EXEC statement cannot be nested.
Indeed there is another “INSERT INTO” used in the usp_SpaceMon.
How can I solve this? (I prefer not to change the stored procedure, just because I don't know how.....)
Any help is welcome!
Two statements taken from the usp where the insert into is used:
INSERT INTO #T(_DBName, _LogSizeMB, _LogSpaceUsedPct, _Status)
EXEC('DBCC SQLPERF(LOGSPACE)')
SELECT @buf = 'INSERT INTO #T2'+ '(_Fileid, _FileGroup, _TotalExtents, _UsedExtents, _Name, _FileName)'+
" EXEC ('USE " + @db_name + "; DBCC showfilestats')"
EXEC(@buf)
July 21, 2006 at 10:29 am
I suggest you just snip the useful statements from the other SP and include them in-line in your own. It creates some extra work, but it's the only way to accomplish what you need.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply