Create a UDF for INSERT INTO # - EXEC construction?

Question

Post reply

Create a UDF for INSERT INTO # - EXEC construction?

EA

Mr or Mrs. 500

Points: 521
More actions
July 21, 2006 at 2:17 am

#113703

Hi,
I'm using the construction:
INSERT INTO #T(_DBName, _LogSizeMB, _LogSpaceUsedPct, _Status)
EXEC('DBCC SQLPERF(LOGSPACE)')
in a stored procedure. Becaus I ran into the 'An INSERT EXEC statement cannot be nested' error I need to create another construction.
Any help is much appreciated!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

This worked for me:

--Table

CREATE TABLE #t(dbname sysname, logsize decimal, logspace decimal, status INT)

--Insert

INSERT INTO #t(dbname, logsize, logspace, status)

EXEC ('dbcc sqlperf(logspace)')

--Results

SELECT * FROM #t

John

EA Mr or Mrs. 500 Points: 521 More actions · Answer 2

Hi John,

Thank you for your reaction.

You're still using the insert into ---- exec construction. If I use this in my stored procedure, the procedure can be executed with succes. But when I try to store the result of my stored procedure in a table:

insert into DataBaseSizeReport exec StoredProcedure the error "An INSERT EXEC statement cannot be nested" results.

I can't do the insert into trick twice.... so that's why one of them needs to be in another construction.

Any suggestions on how to do that?

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 3

Can you not have the stored procedure insert directly into the DataBaseSizeReport table? Or you could have it insert into the same temp table and then copy the data from there into DataBaseSizeReport.

John

EA Mr or Mrs. 500 Points: 521 More actions · Answer 4

The idea is to execute it on linked servers to gather space usage information and store the collected info in one centralised table.... so it would be a nice solution to execute the sp only on the 'master' instance.

R2ro Default port Points: 1409 More actions · Answer 5

You may be able to use OPENROWSET() and substitute the DBCC command in for the <query> to directly insert into the 'master' table.

I did a process similar to yours and used an sp + table on each server (in the SQLDBA database) and a master table on the 'master' server & pulled the data in using a DTS package that driven by a list of servers stored in a table on the 'master' server.