An INSERT EXEC statement cannot be nested

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

An INSERT EXEC statement cannot be nested

Author

Topic

EA
Starting Member

19 Posts

Posted - 2006-07-20 : 09:55:33

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)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-20 : 10:29:57

U may need to change ur preference with regards to the change of stored procedure

Srinika

EA
Starting Member

19 Posts

Posted - 2006-07-20 : 13:57:49

quote:
Originally posted by Srinika

U may need to change ur preference with regards to the change of stored procedure

Srinika

Any help is very welcome!

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 15:07:37

I was able to insert into a table using insert...exec with insert statements inside the sp. Can I get the source for the usp_SpaceMon stored proc?

EA
Starting Member

19 Posts

Posted - 2006-07-21 : 03:31:09

Sure! It would be great if you can help me get this working!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
create PROCEDURE [dbo].[usp_SpaceMon]
AS
BEGIN

DECLARE @buf VARCHAR(512)
DECLARE @db_name VARCHAR(80)
SET NOCOUNT ON

-- Create Two Temporary Tables
CREATE TABLE #T
(
_DBName VARCHAR(80) NOT NULL,

_LogSizeMB FLOAT NULL,
_LogSpaceUsedPct FLOAT NULL,
_LogFileName VARCHAR(255) NULL,
_LogTotalExtents INT NULL,
_LogUsedExtents INT NULL,

_DataSizeMB FLOAT NULL,
_DataFileName VARCHAR(255) NULL,
_DataTotalExtents INT NULL,
_DataUsedExtents INT NULL,
_DataSpaceUsedPct FLOAT NULL,

_Status INT NULL
)

CREATE TABLE #T2
(_Fileid INT, _FileGroup INT, _TotalExtents INT, _UsedExtents INT, _Name VARCHAR(255), _FileName VARCHAR(255))

-- PHASE I -- Run DBCC SQLPERF(Logspace)
INSERT INTO #T(_DBName, _LogSizeMB, _LogSpaceUsedPct, _Status)
EXEC('DBCC SQLPERF(LOGSPACE)')

-- PHASE II --
-- Create cursor for cycling through databases
DECLARE MyCursor CURSOR FOR
SELECT _DBName FROM #T

-- Execute the cursor
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @db_name

-- Do Until All Databases Exhausted...
WHILE (@@fetch_status <> -1)
BEGIN
-- Query To Get Log File and Size Info
SELECT @buf = "UPDATE #T"
+ " SET _LogFileName = X.[filename], _LogTotalExtents
= X.[size]"
+ " FROM #T,"
+ " (SELECT '" + @db_name + "' AS 'DBName',"
+ " fileid, [filename], [size] FROM "
+ @db_name + ".dbo.sysfiles WHERE (status & 0x40 <> 0)) X"
+ " WHERE X.DBName = #T._DBName"
--PRINT @buf
EXEC(@buf)

-- "DBCC showfilestats" Query To Get Data File and Size Info
DELETE FROM #T2
SELECT @buf = 'INSERT INTO #T2'
+ '(_Fileid, _FileGroup, _TotalExtents, _UsedExtents, _Name, _FileName)'
+ " EXEC ('USE " + @db_name + "; DBCC showfilestats')"
-- PRINT @buf
EXEC(@buf)

-- Update the Data Info., and Calculate the Remaining Entities
UPDATE #T
SET _DataFileName = #T2._FileName,
_DataTotalExtents = #T2._TotalExtents,
_DataUsedExtents = #T2._UsedExtents,
_LogUsedExtents = CONVERT(INT, (_LogSpaceUsedPct *
_LogTotalExtents / 100.0)),
_DataSpaceUsedPct = 100.0 * CONVERT(FLOAT, #T2._UsedExtents) / CONVERT(FLOAT, #T2._TotalExtents),
_DataSizeMB = CONVERT(FLOAT, #T2._TotalExtents) / 16.0
FROM #T, #T2
WHERE _DBName = @db_name

-- Go to Next Cursor Row
FETCH NEXT FROM MyCursor INTO @db_name
END

-- Close Cursor
CLOSE MyCursor
DEALLOCATE MyCursor

-- Return Results to User
SELECT
@@servername,
cast(getdate() as smalldatetime),
_DBName ,

_LogFileName ,
_LogSizeMB ,
_LogTotalExtents ,
_LogUsedExtents ,
CONVERT(DECIMAL(6, 2), _LogSpaceUsedPct) AS
'_PercentLogSpaceUsed' ,

_DataFileName ,
_DataSizeMB ,
_DataTotalExtents ,
_DataUsedExtents ,
CONVERT(DECIMAL(6, 2), _DataSpaceUsedPct) AS '_PercentDataSpaceUsed'

FROM #T
ORDER BY _DBName

-- Clean Up
DROP TABLE #T
DROP TABLE #T2
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources