Prev: SQL server transaction timeout and stored procedures
Next: select statement with no results is producing locks
From: Gerry Hickman on 4 Jul 2008 13:05
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.

--
Gerry Hickman
London (UK)

From: Russell Fields on 4 Jul 2008 14:11
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

"Gerry Hickman" <gerry666uk(a)newsgroup.nospam> wrote in message
news:e7PUcgf3IHA.3348(a)TK2MSFTNGP03.phx.gbl...
> 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.
>
> --
> Gerry Hickman
> London (UK)


From: Gerry Hickman on 4 Jul 2008 15:48
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.

Russell Fields wrote:
> 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
>
> "Gerry Hickman" <gerry666uk(a)newsgroup.nospam> wrote in message
> news:e7PUcgf3IHA.3348(a)TK2MSFTNGP03.phx.gbl...
>> 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.
>>
>> --
>> Gerry Hickman
>> London (UK)
>
>


--
Gerry Hickman (London UK)
From: Tibor Karaszi on 4 Jul 2008 16:10
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).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Gerry Hickman" <gerry666uk(a)newsgroup.nospam> wrote in message
news:OQUYk7g3IHA.4584(a)TK2MSFTNGP02.phx.gbl...
> 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.
>
> Russell Fields wrote:
>> 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
>>
>> "Gerry Hickman" <gerry666uk(a)newsgroup.nospam> wrote in message
>> news:e7PUcgf3IHA.3348(a)TK2MSFTNGP03.phx.gbl...
>>> 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.
>>>
>>> --
>>> Gerry Hickman
>>> London (UK)
>>
>>
>
>
> --
> Gerry Hickman (London UK)

From: Russell Fields on 4 Jul 2008 16:39
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

"Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in
message news:A1F35A26-95B3-41B9-B007-C6937F023EC9(a)microsoft.com...
> 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).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Gerry Hickman" <gerry666uk(a)newsgroup.nospam> wrote in message
> news:OQUYk7g3IHA.4584(a)TK2MSFTNGP02.phx.gbl...
>> 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.
>>
>> Russell Fields wrote:
>>> 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
>>>
>>> "Gerry Hickman" <gerry666uk(a)newsgroup.nospam> wrote in message
>>> news:e7PUcgf3IHA.3348(a)TK2MSFTNGP03.phx.gbl...
>>>> 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.
>>>>
>>>> --
>>>> Gerry Hickman
>>>> London (UK)
>>>
>>>
>>
>>
>> --
>> Gerry Hickman (London UK)
>


 |  Next  |  Last
Pages: 1 2 3
Prev: SQL server transaction timeout and stored procedures
Next: select statement with no results is producing locks