SQL Server 2005 Insert exec

rmk
rmk used Ask the Experts™
on
Why does the following not insert into the table?
CREATE TABLE #t1 (MessageText  VARCHAR(1000))
declare @DbName varchar(255)
set @DbName=DB_Name()
declare @SQL VARCHAR(1000)
SET @SQL = 'dbcc checkdb (''' + @DbName + ''')'
PRINT @SQL
insert   #t1
EXEC (@SQL)
Select *
from #t1
DROP TABLE #t1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
because it returns MESSAGES and not rows.

Good question though.

It ain't work that way

@return_status
Is an optional integer variable that stores the return status of a module. This variable must be declared in the batch, stored procedure, or function before it is used in an EXECUTE statement.

When used to invoke a scalar-valued user-defined function, the @return_status variable can be of any scalar data type.

I have no idea what Mohan Sekar is trying to tell you.  But I think whatever he's tryign to tell you isn't applicable.

Run the following in Management Studio:

dbcc checkdb ('Your_database_name_goes_Here')

You'll notice that it doesn't have a "resultset" tab, but rather just a messages tab.  This is equivilent to: Print 'Statement'
Print 'Statement'
Print 'Statement'

These are just messages being returned to the console, they are not Records for a resultset.

The real question is, can you put that command into a record result set.  That's a different question.  I'll look into it though for you.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Per this thread, it's only applicable in SQL 2000 can you get it into a result set.

They suggest using e-mail

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=32042

My bad....the response was actually for another query.
rmk

Author

Commented:
even though it's not supposed to exist in 2005 I was able to use
DBCC CHECKDB WITH TABLERESULTS
Yeah, I found the same thing.  Odd.  Undocumented feather.  Perhaps it was put back in a service pack.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial