Compartilhar via

OPENROWSET (Transact-SQL)

  • Artigo

Inclui todas as informações de conexão exigidas para acessar dados remotos de uma fonte de dados OLE DB. Este método é uma alternativa para acessar tabelas em um servidor vinculado e se trata de um método de uso único e ad hoc para conexão e acesso a dados remotos por meio de OLE DB. Para mais referências frequentes a fontes de dados OLE DB, use servidores vinculados. Para obter mais informações, consulte Servidores vinculados (Mecanismo de Banco de Dados). A função OPENROWSET pode ser referida na cláusula FROM de uma consulta como se fosse um nome de tabela. A função OPENROWSET também pode ser referida como a tabela de destino de uma instrução INSERT, UPDATE ou DELETE, sujeita aos recursos do provedor OLE DB. Embora a consulta possa retornar vários conjuntos de resultados, OPENROWSET retorna somente o primeiro deles.

OPENROWSET também dá suporte a operações em massa por meio de um provedor BULK interno que permite que dados de um arquivo sejam lidos e retornados como um conjunto de linhas.

Aplica-se a: SQL Server (do SQL Server 2008 à versão atual).

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL

Sintaxe

OPENROWSET 
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' 
   | 'provider_string' } 
   , {   [ catalog. ] [ schema. ] object 
       | 'query' 
     } 
   | BULK 'data_file' , 
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} ) 

<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ , ERRORFILE = 'file_name' ]
   [ , FIRSTROW = first_row ] 
   [ , LASTROW = last_row ] 
   [ , MAXERRORS = maximum_errors ] 
   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]

Argumentos

  • 'provider_name'
    É uma cadeia de caracteres que representa o nome amigável (ou PROGID) do provedor OLE DB conforme especificado no registro. provider_name não tem nenhum valor padrão.

  • 'datasource'
    Uma constante de cadeia de caracteres que corresponder a uma fonte de dados OLE DB específica. datasource é 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 entenda para localizar o banco de dados (ou bancos de dados).

  • 'user_id'
    É uma constante de cadeia de caracteres que é o nome de usuário passado para o provedor OLE DB 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 logon do Microsoft Windows.

  • 'password'
    É uma constante de cadeia de caracteres que é a senha de usuário a ser passada para o provedor OLE DB. password é passada pela propriedade DBPROP_AUTH_PASSWORD ao inicializar o provedor. password não pode ser uma senha do Microsoft Windows.

  • 'provider_string'
    É uma cadeia de conexão específica ao provedor, que é passada na propriedade DBPROP_INIT_PROVIDERSTRING para inicializar o provedor OLE DB. provider_string encapsula normalmente todas as informações de conexão necessárias para inicializar o provedor. Para consultar uma lista de palavras-chave reconhecidas pelo provedor SQL Server Native Client OLE DB, consulte Propriedades de inicialização e autorização.

  • catalog
    É o nome do catálogo ou do banco de dados no qual reside o objeto especificado.

  • schema
    É o nome do esquema ou do proprietário de objeto do objeto especificado.

  • object
    É o nome de objeto que identifica com exclusividade o objeto com o qual trabalhar.

  • 'query'
    É uma constante de cadeia de caracteres enviada ao provedor e executada por ele. A instância local do SQL Server não processa esta consulta, mas processa resultados de consulta retornados pelo provedor, uma consulta de passagem. Consultas de passagem são úteis quando usadas em provedores que não tornam disponíveis seus dados tabulares por meio de nomes de tabelas, mas somente via linguagem de comando. Há suporte para consultas de passagem no servidor remoto, contanto que o provedor de consulta ofereça suporte ao objeto Command do OLE DB e suas interfaces obrigatórias. Para obter mais informações, consulte Referência do SQL Server Native Client (OLE DB).

  • BULK
    Usa o provedor de conjuntos de linhas BULK para que OPENROWSET leia dados de um arquivo. No SQL Server, OPENROWSET pode ler de um arquivo de dados sem carregar os dados em uma tabela de destino. Permite que você use OPENROWSET com uma instrução SELECT simples.

    Os argumentos da opção BULK permitem um controle significativo sobre os pontos de início e término da leitura de dados, o modo de manipulação dos erros e o modo de interpretação dos dados. Por exemplo, você pode especificar que o arquivo de dados seja lido como uma única linha, um conjunto de linhas de coluna única do tipo varbinary, varchar ou nvarchar. O comportamento padrão é descrito nas descrições de argumento que se seguem.

    Para obter informações sobre como usar a opção BULK, consulte "Comentários", mais adiante neste tópico. Para obter informações sobre as permissões exigidas pela opção BULK, consulte "Permissões" mais adiante, neste tópico.

    Dica

    Quando usado para importar dados com o modelo de recuperação completa, OPENROWSET (BULK...) não otimiza o registro.

    Para obter informações sobre como preparar dados para importação em massa, consulte Preparar dados para exportar ou importar em massa (SQL Server).

  • 'data_file'
    É o caminho completo do arquivo de dados cujos dados serão copiados para a tabela de destino.

  • FORMATFILE ='format_file_path'
    Especifica o caminho completo de um arquivo de formato. O SQL Server dá suporte a dois tipos de arquivos de formato: XML e não XML.

    É necessário um arquivo de formato para definir tipos de coluna no conjunto de resultados. A única exceção é quando SINGLE_CLOB, SINGLE_BLOB ou SINGLE_NCLOB é especificado; nesses casos, o arquivo de formato não é obrigatório.

    Para obter informações sobre arquivos de formato, consulte Usar um arquivo de formato para importação em massa de dados (SQL Server).

  • < bulk_options >
    Especifica um ou mais argumentos para a opção BULK.

  • CODEPAGE = { 'ACP'| 'OEM'| 'RAW'| 'code_page' }
    Especifica a página de código dos dados no arquivo de dados. CODEPAGE só será relevante se os dados contiverem colunas char, varchar ou text com valores de caracteres maiores que 127 ou menores que 32.

    Dica

    É recomendável especificar um nome de agrupamento para cada coluna em um arquivo de formato.

    Valor de CODEPAGE

    Descrição

    ACP

    Converte colunas do tipo de dados char, varchar ou text da página de código do ANSI/Microsoft Windows (ISO 1252) para a página de código do SQL Server.

    OEM (padrão)

    Converte colunas do tipo de dados char, varchar ou text da página de código de OEM do sistema para a página de código do SQL Server.

    RAW

    Não ocorre nenhuma conversão de uma página de código para outra. Esta é a opção mais rápida.

    code_page

    Indica a página de código de origem na qual são codificados os dados de caracteres do arquivo de dados; por exemplo, 850.

    Importante

    O SQL Server dá suporte à página de código 65001 (codificação UTF-8).

    • ERRORFILE ='file_name'
      Especifica o arquivo usado para coletar linhas com erros de formatação e que não podem ser convertidas em um conjunto de linhas OLE DB. Essas linhas são copiadas do arquivo de dados para esse arquivo de erro "no estado em que se encontram".

      O arquivo de erro é criado no início da execução do comando. Ocorrerá um erro se o arquivo já existir. Além disso, é criado um arquivo de controle com a extensão .ERROR.txt. Esse arquivo faz referência a cada linha do arquivo de erro e fornece um diagnóstico dos erros. Corrigidos os erros, os dados podem ser carregados.

    • FIRSTROW =first_row
      Especifica o número da primeira linha a carregar. O padrão é 1. Indica a primeira linha no arquivo de dados especificado. Os números de linhas são determinados pela contagem dos terminadores de linha. FIRSTROW tem base 1.

    • LASTROW =last_row
      Especifica o número da última linha a ser carregada. O padrão é 0. Indica a última linha no arquivo de dados especificado.

    • MAXERRORS =maximum_errors
      Especifica o número máximo de erros de sintaxe ou de linhas fora de conformidade, conforme definido no arquivo de formato, que podem ocorrer antes de OPENROWSET lançar uma exceção. Até que MAXERRORS seja atingido, OPENROWSET ignora as linhas inválidas, deixando de carregá-las, e as conta como erros.

      O padrão de maximum_errors é 10.

      Dica

      MAX_ERRORS não se aplica a restrições CHECK ou à conversão dos tipos de dados money e bigint.

    • ROWS_PER_BATCH =rows_per_batch
      Especifica o número aproximado de linhas de dados no arquivo de dados. Este valor deve ser da mesma ordem que o número real de linhas.

      OPENROWSET sempre importa um arquivo de dados como um único lote. Contudo, se você especificar rows_per_batch com um valor > 0, o processador de consultas usará o valor de rows_per_batch como dica para alocar recursos no plano de consulta.

      Por padrão, ROWS_PER_BATCH é desconhecido. Especificar ROWS_PER_BATCH = 0 é o mesmo que omitir ROWS_PER_BATCH.

    • ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] )
      Uma dica opcional que especifica como os dados são classificados no arquivo de dados. Por padrão, a operação em massa presume que o arquivo de dados não está ordenado. O desempenho poderá melhorar se a ordem especificada puder ser explorada pelo otimizador de consulta para gerar um plano de consulta mais eficiente. São exemplos de quando especificar uma classificação pode ser benéfico:

      • Ao inserir linhas em uma tabela que tem um índice clusterizado, na qual os dados dos conjuntos de linhas são classificados na chave do índice clusterizado.

      • Ao unir o conjunto de linhas com outra tabela, cujas colunas de classificação e de união correspondam.

      • Ao agregar os dados dos conjuntos de linhas pelas colunas de classificação.

      • Ao usar o conjunto de linhas como tabela de origem na cláusula FROM de uma consulta, cujas colunas de classificação e de junção correspondam.

      UNIQUE especifica que o arquivo de dados não tem entradas duplicadas.

      Se as linhas reais do arquivo de dados não estiverem classificadas na ordem especificada, ou se a dica UNIQUE tiver sido especificada e houver chaves duplicadas, será retornado um erro.

      Aliases de coluna são necessários quando se usa ORDER. A lista de aliases de coluna deve referenciar a tabela derivada que está sendo acessada pela cláusula BULK. Os nomes de coluna especificados na cláusula ORDER se referem a essa lista de aliases de coluna. Colunas de tipos de valor grande (varchar(max), nvarchar(max), varbinary(max) e xml) e LOB (tipo de objeto grande) (text, ntext e image) não podem ser especificadas.

    • SINGLE_BLOB
      Retorna o conteúdo de data_file como um conjunto de linhas de linha única e coluna única do tipo varbinary(max).

      Importante

      Recomendamos importar apenas os dados XML que usam a opção SINGLE_BLOB, em vez de SINGLE_CLOB e SINGLE_NCLOB, porque só SINGLE_BLOB oferece suporte a todas as conversões de codificação do Windows.

    • SINGLE_CLOB
      A leitura de data_file como ASCII retorna o conteúdo como um conjunto de linhas de uma única linha e coluna do tipo varchar(max), usando o agrupamento do banco de dados atual.

    • SINGLE_NCLOB
      A leitura de data_file como UNICODE retorna o conteúdo na forma de conjunto de linhas de linha única e coluna única do tipo nvarchar(max), usando o agrupamento do banco de dados atual.

    Comentários

    OPENROWSET poderá ser usado para acessar dados remotos de fontes de dados OLE DB apenas quando a opção do Registro DisallowAdhocAccess estiver definida explicitamente como 0 para o provedor especificado, e a opção de configuração avançada Ad Hoc Distributed Queries estiver habilitada. Quando essas opções não estão definidas, o comportamento padrão não permite acesso ad hoc.

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

    Serão necessários os nomes de catálogo e de esquema, se o provedor OLE DB oferecer suporte a vários catálogos e esquemas na fonte de dados especificada. Os valores de catalog e schema poderão ser omitidos quando o provedor OLE DB não oferecer 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. Devem ser especificados nomes de três partes para consultas de passagem que usam o provedor OLE DB do SQL Server Native Client. Para obter mais informações, consulte convenções de sintaxe Transact-SQL (Transact-SQL).

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

    Qualquer chamada para OPENDATASOURCE, OPENQUERY ou OPENROWSET na cláusula FROM é avaliada separada e independentemente de qualquer chamada para essas funções usadas como o destino da atualização, mesmo se argumentos idênticos forem fornecidos às duas chamadas. Em particular, as condições de filtro ou junção aplicadas no resultado de uma dessas chamadas não têm efeito sobre os resultado da outra.

    Usando OPENROWSET com a opção BULK

    Os seguintes aperfeiçoamentos de Transact-SQL oferecem suporte à função OPENROWSET(BULK...):

    • Uma cláusula FROM que é usada com SELECT pode chamar OPENROWSET(BULK...) em vez de um nome de tabela, com funcionalidade completa de SELECT.

      OPENROWSET com a opção BULK requer um nome de correlação, também conhecido como variável ou alias de intervalo, na cláusula FROM. Podem ser especificados aliases de coluna. Se uma lista de aliases de coluna não for especificada, o arquivo de formato deverá ter nomes de coluna. Especificar aliases de coluna faz com que os nomes de coluna sejam substituídos no arquivo de formato; por exemplo:

      FROM OPENROWSET(BULK...) AS table_alias

      FROM OPENROWSET(BULK...) AS table_alias(column_alias,... n)

    • Uma instrução SELECT...FROM OPENROWSET(BULK...) consulta os dados diretamente em um arquivo, sem importar os dados em uma tabela. As instruções SELECT…FROM OPENROWSET(BULK...) também podem listar aliases de coluna em massa usando um arquivo de formato para especificar nomes de coluna, além de tipos de dados.

    • Usar OPENROWSET(BULK...) como tabela de origem em uma instrução INSERT ou MERGE importa dados em massa de um arquivo de dados para uma tabela do SQL Server. Para obter mais informações, consulte Importar dados em massa usando BULK INSERT ou OPENROWSET(BULK...) (SQL Server) .

    • Quando a opção OPENROWSET BULK for usada com uma instrução INSERT, a cláusula BULK oferecerá suporte a dicas de tabela. Além das dicas de tabela normais, como TABLOCK, a cláusula BULK pode aceitar as seguintes dicas de tabela especializadas: IGNORE_CONSTRAINTS (ignora somente as restrições CHECK e FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS e KEEPIDENTITY. Para obter mais informações, consulte Dicas de tabela (Transact-SQL).

    Para obter informações sobre como usar as instruções INSERT...SELECT * FROM OPENROWSET(BULK...), consulte Importação e exportação em massa de dados (SQL Server). Para obter informações sobre quando as operações de inserção de linhas executadas por importações em massa são registradas no log de transações, consulte Pré-requisitos para log mínimo em importação em massa.

    Dica

    Ao usar OPENROWSET, é importante entender como o SQL Server manipula a personificação.Para obter mais informações sobre considerações de segurança, consulte Importar dados em massa usando BULK INSERT ou OPENROWSET(BULK...) (SQL Server).

    Importação em massa de dados SQLCHAR, SQLNCHAR ou SQLBINARY

    OPENROWSET(BULK...) pressupõe que, se não especificado, o comprimento máximo de dados SQLCHAR, SQLNCHAR ou SQLBINARY não excede 8000 bytes. Se os dados que são importados estiverem em um campo de dados de LOB que contenha varchar(max), nvarchar(max)ou objetos varbinary(max) que excedam 8000 bytes, você deve usar um arquivo no formato XML que defina o comprimento máximo para o campo de dados. Para especificar o comprimento máximo, edite o arquivo de formato e declare o atributo MAX_LENGTH.

    Dica

    Um arquivo de formato gerado automaticamente não especifica o comprimento ou o comprimento máximo para um campo de LOB.No entanto, você pode editar um arquivo de formato e especificar o comprimento ou o comprimento máximo manualmente.

    Exportando ou importando documentos SQLXML em massa

    Para exportar ou importar dados SQLXML em massa, use um dos tipos de dados a seguir em seu arquivo de formato.

    Tipo de dados

    Efeito

    SQLCHAR ou SQLVARYCHAR

    Os dados são enviados na página de código de cliente ou na página de código implicada pelo agrupamento).

    SQLNCHAR ou SQLNVARCHAR

    Os dados são enviados como Unicode.

    SQLBINARY ou SQLVARYBIN

    Os dados são enviados sem qualquer conversão.

    Permissões

    As permissões OPENROWSET são determinadas pelas permissões do nome de usuário que está sendo passado ao provedor OLE DB. Usar a opção BULK requer permissão ADMINISTER BULK OPERATIONS.

    Exemplos

    A.Usando OPENROWSET com SELECT e o provedor OLE DB SQL Server Native Client

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

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

    B.Usando o Microsoft OLE DB Provider for Jet

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

    Dica

    Este exemplo pressupõe que o Access esteja instalado.Para executar este exemplo, é necessário 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);
GO

    C.Usando OPENROWSET e outra tabela em um INNER JOIN

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

    Dica

    Este exemplo pressupõe que o Access esteja instalado.Para executar este exemplo, é necessário 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 ;
GO

    D.Usando OPENROWSET para inserir dados de arquivo em massa em uma coluna varbinary(max)

    O exemplo a seguir cria uma pequena tabela a título de demonstração e insere dados de um arquivo denominado Text1.txt, localizado no diretório raiz C:, em uma coluna varbinary(max).

    USE AdventureWorks2012;
GO
CREATE TABLE myTable(FileName nvarchar(60), 
  FileType nvarchar(60), Document varbinary(max));
GO

INSERT INTO myTable(FileName, FileType, Document) 
   SELECT 'Text1.txt' AS FileName, 
      '.txt' AS FileType, 
      * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
GO

    E.Usando o provedor OPENROWSET BULK com um arquivo de formato para recuperar linhas de um arquivo de texto

    O exemplo a seguir usa um arquivo de formato para recuperar linhas de um arquivo de texto delimitado por tabulação, values.txt, que contém os seguintes dados:

    1     Data Item 1
2     Data Item 2
3     Data Item 3

    O arquivo de formato values.fmt descreve as colunas em values.txt:

    9.0
2
1  SQLCHAR  0  10 "\t"        1  ID                SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"      2  Description        SQL_Latin1_General_Cp437_BIN

    Eis a consulta que recupera esses dados:

    SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt', 
   FORMATFILE = 'c:\test\values.fmt') AS a;

    Exemplos adicionais

    Para obter exemplos adicionais que demonstram o uso de INSERT... SELECT * FROM OPENROWSET (BULK...), consulte os seguintes tópicos:

    Consulte também

    Referência

    DELETE (Transact-SQL)

    FROM (Transact-SQL)

    INSERT (Transact-SQL)

    OPENDATASOURCE (Transact-SQL)

    OPENQUERY (Transact-SQL)

    Funções de conjunto de linhas (Transact-SQL)

    SELECT (Transact-SQL)

    sp_addlinkedserver (Transact-SQL)

    sp_serveroption (Transact-SQL)

    UPDATE (Transact-SQL)

    WHERE (Transact-SQL)

    Conceitos

    Importação e exportação em massa de dados (SQL Server)