Prev: SQL server transaction timeout and stored procedures
Next: select statement with no results is producing locks
From: Tibor Karaszi on 7 Jul 2008 07:12
> 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.

--
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:%23x3oxrA4IHA.2064(a)TK2MSFTNGP02.phx.gbl...
> 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)

From: Gerry Hickman on 9 Jul 2008 14:37
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.

Tibor Karaszi wrote:
>> 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.
>


--
Gerry Hickman (London UK)
First  |  Prev  | 
Pages: 1 2 3
Prev: SQL server transaction timeout and stored procedures
Next: select statement with no results is producing locks