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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-09 : 13:34:36
|
Hans writes "Description:
===========
When a query is submitted via the query analyser, the result is returned in a default result set, in the result window.
e.g. SELECT ProductId FROM Products
The resultset of queries like the one above, as well as all SELECT queries, can:
a. be easily be contained in a dataset object of a developing tool (Delphi/Java/etc), and the result set can then be parsed
b. be redirected to a temporary table by using the INTO keyword, e.g. SELECT * FROM Products INTO #ProdTemp
However not all resultset-returning sql queries are produced from SELECT statements, e.g.
a. sp_tables (returns a dataset of multiple columns and rows)
b. RESTORE HEADERONLY FROM DISK = 'C\SomeBackupFile' (returns multiple columns)
Both queries are EXEC type queries as opposed to SELECT queries. Although they can be run from external programs (Delphi/Java/etc) through ODBC, the result sets returned by non-SELECT queries seem to be out of reach for the programs.
My question is:
==============
How can we manipulate the result set returned by Non-SELECT statements. For example, if I want to capture only the fields DatabaseName, BackupStartDate and ServerName from the query:
RESTORE HEADERONLY FROM DISK = 'C\SomeBackupFile'
how would I proceed?
Can we direct this type of result set into a temporary table? If so how?
Can we encapsulate this type of query in a SELECT statement?
Can we access the default result set to which SQL Analyser sends the result by its name? If so what is the name of this result set?" |
|
|
Glockenspiel
Yak Posting Veteran
50 Posts |
Posted - 2004-06-29 : 11:55:14
|
Hmmm... just wondering if anyone has found a solution other piping out to a file. Specifically, I want to be able to create a script or stored proc that can programatically determine the "Position" within a backup disk file for a given database using RESTORE HEADERONLY and then do a RESTORE DATABASE for the database relating to that particular file #.Something like this:SELECT PositionFROM OPENROWSET ('SQLOLEDB', 'Server=.;Trusted_Connection=YES;', 'SET FMTONLY OFF RESTORE HEADERONLY FROM DISK = ''E:\SQL2000\BACKUP\RECOVERY.DAT''')Except without the error:Server: Msg 7357, Level 16, State 2, Line 1Could not process object 'SET FMTONLY OFF RESTORE HEADERONLY FROM DISK = 'E:\SQL2000\BACKUP\RECOVERY.DAT''. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=SET FMTONLY OFF RESTORE HEADERONLY FROM DISK = 'E:\SQL2000\BACKUP\RECOVERY.DAT'']. |
 |
|
|
|
|
|
|
|
How To Manipulate Data From the Default Result Set