Derived Tables from Non-Select Statements

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

SQL Server Development (2000)

Derived Tables from Non-Select Statements

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-05 : 09:31:45

Chris Petree writes "Periodically I want to write a script or a stored procedure that gets recordsets from built in functions, but I have no idea how to get it to work. I've scoured the books online, the books we have at the office, your help articles, etc, and I can't find anything.

For example, if I run RESTORE FILELISTONLY on a backup file, it returns a recordset with all of the information about the database devices that are contained in the backup. In query analyzer I get the information I need, but I want to be able to work with that recordset from within a script or stored procedure, like you would any other table or derived table. I would like to be able to do something like:

SELECT r.logicalname, r.physicalname
FROM (RESTORE FILELISTONLY ..) as r

Of course, I haven't been able to get that to work. Any suggestions?

Another example, just so you know what I'm talking about, if a process logs an error because it is being blocked by another process, I'd like it to be able to get the blocking procedure with something like:

SELECT @bspid = blocked FROM master..sysprocesses
SELECT @bproc = eventinfo FROM (dbcc inputbuffer(@bspid))

INSERT INTO errortable (blockingspid, blockingproc)
VALUES (@bspid, @bproc)

Actually it looks like dbcc inputbuffer won't take a variable, bah.. anyway, that's the basics of it. I'm stumped. If the function returns a recordset, I should be able to use it just like any other query. At least that's what I think. :D

The lame thing is, if I write a front end (like in ASP or something), I can use the data just fine, because it sees it just like any other recordset. But I can't get Transact-SQL to see it as a recordset.

Thanks in advance for any ideas.."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-05 : 10:01:51

You should be able to pump the rowset into a temp table using the INSERT...EXEC... syntax.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources