Partilhar via

OPENROWSET (Transact-SQL)

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores

OPENROWSET é uma alternativa para acessar tabelas em um servidor vinculado e é um método único e ad hoc de conexão e acesso a dados remotos. Um OPENROWSET comando T-SQL inclui todas as informações de conexão necessárias para acessar dados remotos de uma fonte de dados externa.

A função OPENROWSET pode ser referenciada na cláusula FROM de uma consulta como se fosse um nome de tabela. A OPENROWSET função também pode ser referenciada como a tabela de destino de uma INSERT, UPDATEou DELETE instrução, sujeita aos recursos do provedor de dados. Embora a consulta possa retornar vários conjuntos de resultados, OPENROWSET retorna apenas o primeiro.

Sugestão

Para referências mais frequentes a fontes de dados externas, use servidores vinculados. Para obter mais informações, consulte Servidores vinculados (Mecanismo de Banco de Dados).

OPENROWSET sem o BULK operador está disponível apenas no SQL Server. Detalhes e links para exemplos semelhantes em outras plataformas:

Transact-SQL convenções de sintaxe

Sintaxe

OPENROWSET sintaxe é usada para consultar fontes de dados externas:

OPENROWSET
(  'provider_name'
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {  [ catalog. ] [ schema. ] object | 'query' }
)

Argumentos

'provider_name'

Uma cadeia de caracteres que representa o nome amigável (ou PROGID) do provedor de dados, conforme especificado no Registro. provider_name não tem valor padrão. Os exemplos de nomes de provedores são MSOLEDBSQL, Microsoft.Jet.OLEDB.4.0ou MSDASQL.

'fonte de dados'

Uma constante de cadeia de caracteres que corresponde a uma fonte de dados específica. de fonte de dados é a propriedade DBPROP_INIT_DATASOURCE a ser passada para a interface IDBProperties do provedor para inicializar o provedor. Normalmente, essa cadeia de caracteres inclui o nome do arquivo de banco de dados, o nome de um servidor de banco de dados ou um nome que o provedor entende para localizar o banco de dados ou bancos de dados.

A fonte de dados pode ser C:\SAMPLES\Northwind.mdb' de caminho de arquivo para Microsoft.Jet.OLEDB.4.0 provedor ou Server=Seattle1;Trusted_Connection=yes; de cadeia de conexão para MSOLEDBSQL provedor.

'user_id'

Uma constante de cadeia de caracteres que é o nome de usuário passado para o provedor de dados especificado. user_id especifica o contexto de segurança para a conexão e é passado como a propriedade DBPROP_AUTH_USERID para inicializar o provedor. user_id não pode ser um nome de login do Microsoft Windows.

'palavra-passe'

Uma constante de cadeia de caracteres que é a senha de usuário a ser passada para o provedor de dados. de senha é passada como a propriedade DBPROP_AUTH_PASSWORD ao inicializar o provedor. senha não pode ser uma senha do Microsoft Windows. Por exemplo:

SELECT a.* FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\SAMPLES\Northwind.mdb';
    '<user name>';
    '<password>',
    Customers
) AS a;

'provider_string'

Uma cadeia de conexão específica do provedor que é passada como a propriedade DBPROP_INIT_PROVIDERSTRING para inicializar o provedor OLE DB. provider_string normalmente encapsula todas as informações de conexão necessárias para inicializar o provedor.

Para obter uma lista de palavras-chave que o provedor OLE DB do SQL Server Native Client reconhece, consulte Propriedades de inicialização e autorização (provedor OLE DB do Native Client). O SQL Server Native Client (geralmente abreviado SNAC) foi removido do SQL Server 2022 (16.x) e do SQL Server Management Studio 19 (SSMS). O provedor OLE DB do SQL Server Native Client (SQLNCLI ou SQLNCLI11) e o provedor Microsoft OLE DB herdado para SQL Server (SQLOLEDB) não são recomendados para novos desenvolvimentos. Alterne para o novo Microsoft OLE DB Driver (MSOLEDBSQL) para SQL Server no futuro.

SELECT d.* FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=Seattle1;Trusted_Connection=yes;',
    Department
) AS d;

[ catálogo. ] [ esquema. ] objeto

Tabela ou vista remota contendo os dados que OPENROWSET deve ler. Pode ser um objeto de nome de três partes com os seguintes componentes:

  • catálogo (opcional) - o nome do catálogo ou banco de dados no qual o objeto especificado reside.
  • do esquema (opcional) - o nome do proprietário do esquema ou do objeto especificado.
  • objeto - o nome do objeto que identifica exclusivamente o objeto com o qual trabalhar.
SELECT d.* FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=Seattle1;Trusted_Connection=yes;',
    AdventureWorks2022.HumanResources.Department
) AS d;

'consulta'

Uma constante de cadeia de caracteres enviada e executada pelo provedor. A instância local do SQL Server não processa essa consulta, mas processa os resultados da consulta retornados pelo provedor, uma consulta de passagem. As consultas de passagem são úteis quando usadas em provedores que não disponibilizam seus dados tabulares por meio de nomes de tabelas, mas apenas por meio de uma linguagem de comando. As consultas de passagem são suportadas no servidor remoto, desde que o provedor de consulta ofereça suporte ao objeto Command OLE DB e suas interfaces obrigatórias.

Para obter mais informações, consulte Interfaces do SQL Server Native Client (OLE DB).

SELECT a.*
FROM OPENROWSET(
    'MSOLEDBSQL',
    'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT TOP 10 GroupName, Name FROM AdventureWorks2022.HumanResources.Department'
) AS a;

Comentários

OPENROWSET pode ser usado para acessar dados remotos de fontes de dados OLE DB somente quando a opção de registro DisallowAdhocAccess estiver explicitamente definida como 0 para o provedor especificado e a opção de configuração avançada Consultas Distribuídas Ad Hoc estiver habilitada. Quando essas opções não são definidas, o comportamento padrão não permite acesso ad hoc.

Quando você acessa fontes de dados OLE DB remotas, a identidade de logon de conexões confiáveis não é delegada automaticamente do servidor no qual o cliente está conectado ao servidor que está sendo consultado. A delegação de autenticação deve ser configurada.

Os nomes de catálogo e esquema são necessários se o provedor de dados oferecer suporte a vários catálogos e esquemas na fonte de dados especificada. Os valores para catalog e schema podem ser omitidos quando o provedor de dados não oferece suporte a eles. Se o provedor oferecer suporte apenas a nomes de esquema, um nome de duas partes do formulário schema.object deverá ser especificado. Se o provedor oferecer suporte apenas a nomes de catálogo, um nome de três partes do formulário catalog.schema.object deverá ser especificado. Para obter mais informações, consulte Transact-SQL convenções de sintaxe.

Nomes de três partes são necessários para consultas de passagem que usam o provedor OLE DB do SQL Server Native Client.

OPENROWSET não aceita variáveis para seus argumentos.

Qualquer chamada para OPENDATASOURCE, OPENQUERYou OPENROWSET na cláusula FROM é avaliada separadamente e independentemente de qualquer chamada para essas funções usadas como destino da atualização, mesmo que argumentos idênticos sejam fornecidos para as duas chamadas. Em especial, as condições de filtragem ou de junção aplicadas ao resultado de uma dessas chamadas não têm incidência nos resultados da outra.

Permissões

OPENROWSET As permissões são determinadas pelas permissões do nome de usuário que está sendo passado para o provedor de dados.

Exemplos

Esta seção fornece exemplos gerais para demonstrar como usar OPENROWSET.

Observação

Para obter exemplos que mostram o uso do INSERT...SELECT * FROM OPENROWSET(BULK...), consulte OPENROWSET BULK (Transact-SQL).

O SQL Server Native Client (geralmente abreviado SNAC) foi removido do SQL Server 2022 (16.x) e do SQL Server Management Studio 19 (SSMS). O provedor OLE DB do SQL Server Native Client (SQLNCLI ou SQLNCLI11) e o provedor Microsoft OLE DB herdado para SQL Server (SQLOLEDB) não são recomendados para novos desenvolvimentos. Alterne para o novo Microsoft OLE DB Driver (MSOLEDBSQL) para SQL Server no futuro.

Um. Use OPENROWSET com SELECT e o provedor OLE DB do SQL Server Native Client

O exemplo a seguir usa o provedor OLE DB do SQL Server Native Client para acessar a tabela HumanResources.Department no banco de dados AdventureWorks2022 no servidor remoto Seattle1. (Use MSOLEDBSQL para o provedor de dados OLE DB moderno do Microsoft SQL Server que substituiu SQLNCLI.) Uma SELECT instrução é usada para definir o conjunto de linhas retornado. A cadeia de caracteres do provedor contém as palavras-chave Server e Trusted_Connection. Essas palavras-chave são reconhecidas pelo provedor OLE DB do SQL Server Native Client.

SELECT a.*
FROM OPENROWSET(
    'MSOLEDBSQL', 'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT GroupName, Name, DepartmentID
         FROM AdventureWorks2022.HumanResources.Department
         ORDER BY GroupName, Name'
) AS a;

B. Usar o Microsoft OLE DB Provider for Jet

O exemplo a seguir acessa a tabela Customers no banco de dados do Microsoft Access Northwind por meio do Microsoft OLE DB Provider for Jet.

Observação

Este exemplo pressupõe que o Microsoft Access esteja instalado. Para executar este exemplo, você deve instalar o banco de dados Northwind.

SELECT CustomerID, CompanyName
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
    'admin';'',
    Customers
);

C. Use OPENROWSET e outra tabela em um INNER JOIN

O exemplo a seguir seleciona todos os dados da Customers tabela da instância local do banco de dados do SQL Server Northwind e da Orders tabela do banco de dados do Microsoft Access Northwind armazenado no mesmo computador.

Observação

Este exemplo pressupõe que o Microsoft Access esteja instalado. Para executar este exemplo, você deve instalar o banco de dados Northwind.

USE Northwind;
GO

SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET(
        'Microsoft.Jet.OLEDB.4.0',
        'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'',
        Orders) AS o
    ON c.CustomerID = o.CustomerID;

Conteúdo relacionado