OPENROWSET (Transact-SQL)

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 freqüentes a fontes de dados OLE DB, use servidores vinculados. Para obter mais informações, consulte Vinculando servidores. 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.

Convenções de 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. O provider_name não tem nenhum valor padrão.

• 'datasource'
  É uma constante de cadeia de caracteres que corresponde a uma fonte de dados OLE DB específica. datasource é a propriedade DBPROP_INIT_DATASOURCE a ser transmitida à interface IDBProperties do provedor para inicializá-lo. Normalmente, esta 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).

• 'user_id'
  É uma constante de cadeia de caracteres que é o nome de usuário transmitido ao provedor OLE DB especificado. user_id especifica o contexto de segurança para a conexão e é transmitido como 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 transmitida ao provedor OLE DB. password é transmitida como propriedade DBPROP_AUTH_PASSWORD durante a inicialização do provedor. password não pode ser uma senha do Microsoft Windows.

• 'provider_string'
  É uma cadeia de caracteres de conexão específica ao provedor que é transmitida como 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 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 exclusivo 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 essa 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. Isso lhe permite usar OPENROWSET com uma instrução SELECT simples.

  Os argumentos da opção BULK permitem um controle significativo sobre o ponto 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.

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

  Para obter informações sobre como preparar dados para importação em massa, consulte Preparando dados para exportar ou importar em massa.

• '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 oferece 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 Usando um arquivo de formato para importar dados em massa.

• < 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.

  Observação
  É 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 não suporta a 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 este arquivo de erro "no estado em que se encontram".

  O arquivo de erro é criado ao 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 é baseado em 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, como definido no arquivo de formato, permitido sem que OPENROWSET lance 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.

  Observação
  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 de > 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 omiti-lo.

• 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 assume que o arquivo de dados não se encontra 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 para utilizar ORDER. A lista de aliases de coluna deve fazer referência à 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 de tipos de objeto grande (LOB) (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 na forma de conjunto de linhas de linha única e coluna única 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 pode ser usado para acessar dados remotos de fontes de dados OLE DB somente quando a opção do Registro DisallowAdhocAccess está definida explicitamente como 0 para o provedor especificado, e quando a opção de configuração avançada Consultas Distribuídas Ad Hoc está 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. Para obter mais informações, consulte Configurando servidores vinculados para delegação.

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 SQL Server Native Client OLE DB. Para obter mais informações, consulte convenções de sintaxe Transact-SQL (Transact-SQL).

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

Usando OPENROWSET com a opção BULK

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

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

  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 deve 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 diretamente os dados de um arquivo de dados, sem importá-los para uma tabela. Instruções SELECT…FROM OPENROWSET(BULK...) também podem listar aliases de colunas em massa, usando um formato de arquivo para especificar nomes de coluna, além de tipos de dados.

• Usar o 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 Importando dados em massa usando BULK INSERT ou OPENROWSET(BULK...) .

• 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 instruções INSERT...SELECT * FROM OPENROWSET(BULK...), consulte Importando e exportando dados em massa. 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.

Observação
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 Importando dados em massa usando BULK INSERT ou OPENROWSET(BULK...).

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

OPENROWSET(BULK...) assume 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. Para obter mais informações, consulte Sintaxe de esquema para arquivos de formato XML.

Observação
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

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

Exemplos

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

O exemplo a seguir usa o provedor SQL Server Native Client OLE DB para acessar a tabela HumanResources.Department no banco de dados AdventureWorks no servidor remoto Seattle1. (Use SQLNCLI e o SQL Server redirecionará para a última versão do provedor SQL Server Native Client OLE DB.) 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 AdventureWorks.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.

Observação
Este exemplo pressupõe que o Access esteja instalado. Para executar este exemplo, é necessário instalar o banco de dados Northwind. Para obter informações sobre como instalar o banco de dados Northwind, consulte Baixando os bancos de dados de exemplo Northwind e pubs.

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.

Observação
Este exemplo pressupõe que o Access esteja instalado. Para executar este exemplo, é necessário instalar o banco de dados Northwind. Para obter informações sobre como instalar o banco de dados Northwind, consulte Baixando os bancos de dados de exemplo Northwind e pubs.

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 AdventureWorks
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 de 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:

• Exemplos de importação e exportação em massa de documentos XML

• Mantendo valores de identidade ao importar em massa dados

• Mantendo valores nulos ou usando valores padrão durante a importação em massa

• Usando um arquivo de formato para importar dados em massa

• Usando formato de caractere para importar ou exportar dados

• Usando um arquivo de formato para ignorar uma coluna de tabela

• Usando um arquivo de formato para ignorar um campo de dados

• Usando um arquivo de formato para mapear colunas de uma tabela para campos de arquivo de dados

Histórico de alterações

Conteúdo atualizado
Uma observação importante foi adicionada à descrição de code_page.
Na seção Comentários, foram adicionadas informações sobre importação em massa de dados SQLCHAR, SQLNCHAR ou SQLBINARY.

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

Consultas distribuídas

Outros recursos

Importando e exportando dados em massa

Funções definidas pelo usuário (Mecanismo de Banco de Dados)