Prev: SQL server transaction timeout and stored procedures
Next: select statement with no results is producing locks
From: Gerry Hickman on 5 Jul 2008 16:27
Hi,

Russell Fields wrote:
> 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;

--
Gerry Hickman (London UK)
From: Gerry Hickman on 5 Jul 2008 16:30
Tibor Karaszi wrote:

> 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?


--
Gerry Hickman (London UK)
From: Roy Harvey (SQL Server MVP) on 5 Jul 2008 18:36
On Sat, 05 Jul 2008 21:27:33 +0100, Gerry Hickman
<gerry666uk(a)newsgroup.nospam> wrote:

>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
From: Tibor Karaszi on 6 Jul 2008 01:49
> 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...

--
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:%23W$K83t3IHA.4488(a)TK2MSFTNGP03.phx.gbl...
> Tibor Karaszi wrote:
>
>> 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?
>
>
> --
> Gerry Hickman (London UK)

From: Gerry Hickman on 7 Jul 2008 04:24
Hi,

"Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in
message news:391A6DEC-C1F9-465E-AD23-8BE8306C2BC4(a)microsoft.com...
>> 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:
>
> SELECT *
> INTO #tmp
> FROM OPENQUERY('RESTORE ...')
>
> 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.

--
Gerry Hickman
London (UK)

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