Result Set from RESTORE FILELISTONLY into #table?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gerry Hickman - 04 Jul 2008 18:05 GMT

Hi,

If I have a table called tblEmployee with columns of diffeernt types, I can
do something like this

SELECT * INTO #foo FROM tblEmployee;

and it will create #foo with all the correct column types and insert the
data. I do NOT have to create the table definition first. I now want to do
the same with the result set from RESTORE FILELISTONLY, but this (and
similar attempts) won't work:

SELECT * INTO #foo FROM exec('RESTORE FILELISTONLY ...');

If I create #foo first with perfectly matching column types, then I can do.

INSERT #foo exec('RESTORE FILELISTONLY ...');

Is there some way I can get the result set into a #temp table WITHOUT having
to define it first?

The main reason I want to do this, is that I'm only interested in one
column, plus the definition is different between SQL 2000 and 2005.

Signature

Gerry Hickman
London (UK)

Reply to this Message

Russell Fields - 04 Jul 2008 19:11 GMT

Gerry,

I am afraid that you will just need to have version specific scripts per SQL
Server version. You (if you need to) can encapsulate both choices in a
single stored procedure that will determine the version of the server and
run the proper query. Something like:

CREATE TABLE #MyTable (TheOnlyColumnIWant NVARCHAR(200))

IF SERVERPROPERTY (PRODUCTVERSION) LIKE '9.00%'
BEGIN
-- Place dynamic SQL here to create 2005 format table, do the restore,
the place the one column into #MyTable
-- Execute the SQL
END
ELSE IF SERVERPROPERTY (PRODUCTVERSION) LIKE '8.00%'
-- Place dynamic SQL here to create 2000 format table, do the restore,
the place the one column into #MyTable
-- Execute the SQL
ELSE
RETURN(1) -- Invalid version

The dynamic-SQL, which I did not write out, encapsulates the version
specific code and you get your one column results in #MyTable.

FWIW,
RLF

> Hi,
>
[quoted text clipped - 20 lines]
> The main reason I want to do this, is that I'm only interested in one
> column, plus the definition is different between SQL 2000 and 2005.

Reply to this Message

Gerry Hickman - 04 Jul 2008 20:48 GMT

Hi,

Thanks for the help on how to branch for different versions, but I still
don't know how to actually write the T-SQL to get the columns (single or
otherwise) from the RESTORE FILELISTONLY command (without knowing all
the columns and types in advance).

A different but similar question is how can I get a list of column types
from a command such as RESTORE FILESONLY?

It seems this command returns a "result set", so SQL Management Studio
must have a way to place this result set into a grid without knowing
every column type in advance? I want to do the same... it should be
possible to do something like

SELECT LogicalName FROM RESTORE FILESONLY ...

but I know how.

> Gerry,
>
[quoted text clipped - 48 lines]
>> The main reason I want to do this, is that I'm only interested in one
>> column, plus the definition is different between SQL 2000 and 2005.

Signature

Gerry Hickman (London UK)

Reply to this Message

Tibor Karaszi - 04 Jul 2008 21:10 GMT

See blow for some inspiration and code to "steal".

http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp

> A different but similar question is how can I get a list of column types from a command such as
> RESTORE FILESONLY?

Trial and error. Unless Books Online documents each datatype ( I didn't look).

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi,
>
[quoted text clipped - 61 lines]
>>> The main reason I want to do this, is that I'm only interested in one column, plus the
>>> definition is different between SQL 2000 and 2005.

Reply to this Message

Russell Fields - 04 Jul 2008 21:39 GMT

SQL Server 2005 definition:
http://msdn.microsoft.com/en-us/library/ms173778.aspx
SQL Server 2000 definition:
http://msdn.microsoft.com/en-us/library/aa238420.aspx

RLF

> See blow for some inspiration and code to "steal".
>
[quoted text clipped - 76 lines]
>>>> The main reason I want to do this, is that I'm only interested in one
>>>> column, plus the definition is different between SQL 2000 and 2005.

Reply to this Message

Gerry Hickman - 05 Jul 2008 21:27 GMT

Hi,

> SQL Server 2005 definition:
> http://msdn.microsoft.com/en-us/library/ms173778.aspx
> SQL Server 2000 definition:
> http://msdn.microsoft.com/en-us/library/aa238420.aspx

Ah, I'd read those pages and even noticed the column names but hadn't
realized it also gives the data types!

I still don't understand why I can create a #temp table from a SELECT
statement, but can't do the same from a RESTORE statement, since they
both appear to return a result set. I also don't understand how SQL
Management Console can display the results in a grid without knowing the
column types in advance?

-- This works without knowing the column types in tblEmployee
SELECT * INTO #foo FROM tblEmployee;

Signature

Gerry Hickman (London UK)

Reply to this Message

Roy Harvey (SQL Server MVP) - 05 Jul 2008 23:36 GMT

>I still don't understand why I can create a #temp table from a SELECT
>statement, but can't do the same from a RESTORE statement, since they
>both appear to return a result set.

I'm afraid it is just another case of that is the way it is.

Roy Harvey
Beacon Falls, CT

Reply to this Message

Gerry Hickman - 05 Jul 2008 21:30 GMT

> See blow for some inspiration and code to "steal".
>
> http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp

Thanks, I found your page a few days ago, then noticed it didn't work
with SQL 2005, then manged to add the missing columns, then it worked.

>> A different but similar question is how can I get a list of column
>> types from a command such as RESTORE FILESONLY?
>
> Trial and error. Unless Books Online documents each datatype ( I didn't
> look).

OK, I found the data types, but don't understand how SQL management
console can display the results in a grid without knowing the column
names in advance?

Signature

Gerry Hickman (London UK)

Reply to this Message

Tibor Karaszi - 06 Jul 2008 06:49 GMT

> OK, I found the data types, but don't understand how SQL management console can display the
> results in a grid without knowing the column names in advance?

SQL Server returns meta-data with the resultset, which is retrievable using the API (ODBC, ADO.NET
etc). This is what such tools are using. This isn't directly exposed at the TSQL level, which is why
we need to do things like this. In fact, you might get away with:

SELECT *
INTO #tmp
FROM OPENQUERY('RESTORE ...')

Or OPENROWSET or similar...

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

>> See blow for some inspiration and code to "steal".
>>
[quoted text clipped - 10 lines]
> OK, I found the data types, but don't understand how SQL management console can display the
> results in a grid without knowing the column names in advance?

Reply to this Message

Gerry Hickman - 07 Jul 2008 09:24 GMT

Hi,

>> OK, I found the data types, but don't understand how SQL management
>> console can display the results in a grid without knowing the column
>> names in advance?
>
> SQL Server returns meta-data with the resultset, which is retrievable
> using the API (ODBC, ADO.NET > etc).

OK.

> In fact, you might get away with:
>
[quoted text clipped - 3 lines]
>
> Or OPENROWSET or similar...

I looked into this, but these statements are designed for access to remote
data, as opposed to local, but even if I try to point them at a local data
source, I end up with a security warning about "Ad hoc distributed" queries
being disabled and I don't really want to start changing the registry.

Signature

Gerry Hickman
London (UK)

Reply to this Message

Tibor Karaszi - 07 Jul 2008 12:12 GMT

> I looked into this, but these statements are designed for access to remote data, as opposed to
> local, but even if I try to point them at a local data source, I end up with a security warning
> about "Ad hoc distributed" queries being disabled and I don't really want to start changing the
> registry.

You don't need to change the registry, it is only an sp_Configure parameter. You could set it, run
your queries and then immediately turn it off. But I agree it is nicer to not having to do it in the
first place, so if you can live with INSERT EXEC , I'd stick with that.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi,
>
[quoted text clipped - 18 lines]
> about "Ad hoc distributed" queries being disabled and I don't really want to start changing the
> registry.

Reply to this Message

Gerry Hickman - 09 Jul 2008 19:37 GMT

Hi,

I originally needed this so I could automate restores onto different
servers where the logical drive layout is completely different.

In the end, I decided the better way to do this was using programming,
especially as there are a lot of FileSystem operations involved.

Using 'sqloledb' or 'sqlncli' you can obtain the column values direct
from statements such as RESTORE FILELISTONLY without having to define
the table first or place the results in a #tmp table. You can also use
the backup and restore objects from SQL-DMO or SMO.

>> I looked into this, but these statements are designed for access to
>> remote data, as opposed to local, but even if I try to point them at a
[quoted text clipped - 6 lines]
> it off. But I agree it is nicer to not having to do it in the first
> place, so if you can live with INSERT EXEC , I'd stick with that.

Signature

Gerry Hickman (London UK)

Reply to this Message