Arquivo
T-SQL: Importar dados do Excel e Access usando MDAC – ACE
O Microsoft SQL Server possue ferramentas para fazer o trabalho de importação e exportação de dados do Excel, Access entre outras fontes.
Porém, no meu caso desejo executar um script importação e/ou exportação.
Como fazer isso usando T-SQL?
Para isso é necessário ter instalado o Provider no servidor do SQL Server.
- Microsoft.Jet.OLEDB.4.0 – depreciado
- Microsoft.ACE.OLEDB.12.0
- Microsoft.ACE.OLEDB.16.0
O Jet OLEDB foi depreciado, mas existem diversos códigos antigos de exemplos com ele. Podemos aproveitar esses códigos, substituindo pelo Microsft ACE OLEDB.
Office 2010 – ACE 12 – Microsoft Access Database Engine 2010 Redistributable
Office 2016 – ACE 16 – Microsoft Access Database Engine 2016 Redistributable
Para conferir a instalação entre no SQL Managment Studio – Server Objects – Linked Servers – Providers
-- Ativa Linked Server
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
-- Ativa Permissão ao Provider versão ACE 12
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess',1
GO
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters',1
GO
Caso recebe algumas mensagem referente a permissão de acesso, ou erro de impossibilidade de criar linked server, leia Permissions needed to set up linked server with out-of-process provider
-- Ativa Permissão ao Provider versão ACE 16
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess',1
GO
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters',1
GO
-- Usa o Provider Jet ( Depeciado ) para acessar XLS
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Excel\File.xls; Extended Properties=Excel 5.0')...[Sheet1$]
GO
-- Usa o Provider ACE 12 para acessar XLS / XLSX
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\ExcelFiles\File2003.xls; Extended Properties=Excel 8.0')...[Sheet1$]
GO
-- Usa o Provider ACE 16 para acessar XLS / XLSX
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0','Data Source=C:\ExcelFiles\File2016.xlsx; Extended Properties=Excel 12.0')...[Sheet1$]
GO
Resultado
Importante!!!
O arquivo está na unidade “C:” local do SERVIDOR SQL Server.
Para abrir arquivo da rede, use o caminho COMPLETO, conforme abaixo:
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=\\NomeDoPC\NomePastaCompartilhada\NomeArquivo.xlsx; Extended Properties=Excel 8.0')...[NomePlanilha$]SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0','Data Source=\\NomeDoPC\NomePastaCompartilhada\NomeArquivo.xlsx; Extended Properties=Excel 12.0')...[NomePlanilha$]fonte:
Query Excel file source through Linked Server
How to use Excel with SQL Server linked servers and distributed queries
Configuiring a Linked Microsoft Access Server on SQL 2005
Microsoft.Jet.OLEDB4.0 – 32bit and 64bit ( Deprecated )
Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
Cannot create instance of “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” on x64 Vista with x64 SQL Server 2008 – sp_MSSet_oledb_prop
How to Import and Export SQL Server data to an Excel file
T-SQL OpenDataSource on SQL Server 2017
Import data from Excel to SQL Server or Azure SQL Database
Permissions needed to set up linked server with out-of-process provider
Recursos removidos ou preteridos no Windows Server 2012
Se você está estudando sobre Windows Server 2012 ou se pretende instalar seu aplicativos nele. Fique atento:
Recursos removidos do Windows Server 2012
Os recursos e funcionalidades a seguir foram retirados desta versão do Windows Server 2012. Aplicativos, códigos ou o uso que dependem desses recursos não funcionarão nesta versão a menos que você implante um método alternativo.
As versões do Microsoft SQL Server anteriores à 7.0 não são mais suportadas. Os computadores com o Windows Server 2012 que se conectarem a computadores com o SQL Server 6.5 (ou anterior) receberão uma mensagem de erro.
O suporte para Visual Studio Analyzer 2003 no ODBC, OLEDB e ADO foi removido.
O programa de Ajuda do Windows Help (especificamente, WinHlp32.exe, o arquivo executável que abre arquivos de ajuda *.hlp) foi removido do Windows desde o Windows Server 2008. Antes, eram disponibilizados pacotes de download que forneciam essa funcionalidade (veja http://support.microsoft.com/kb/917607). A partir desta versão, nenhum download será fornecido para que você possa exibir arquivos *.hlp no Windows Server 2012. Para este lançamento importante, a Ajuda do Windows também não é suportada atualmente no Windows® 8.
Recursos preteridos a partir do Windows Server 2012
Os seguintes recursos e funcionalidades foram preteridos a partir desta versão. Eventualmente, eles serão completamente removidos do produto, mas ainda estarão disponíveis nesta versão, algumas vezes, com determinadas funcionalidades removidas. Comece a planejar agora a implantação de métodos alternativos para qualquer aplicativo, código, ou utilização que dependa desses recursos.
Sistemas de gerenciamento de banco de dados
- O suporte de ODBC para aplicativos e drivers de 16 e 32 bits foi preterido. Em vez disso, utilize as versões 64 bits.
- O suporte de ODBC/OLEDB para Microsoft Oracle foi preterido. Migre para drivers e fornecedores disponibilizados pela Oracle.
- Jet Red RDBMS e drivers de ODBC foram preteridos.
O serviço de Dados Remotos foi preterido. Migre para a API de Serviços Web do Windows.
- O suporte do ODBC/OLEDB para SQL foi depreciado para versões além do SQL Server 7 e SQL 2000. Migre para o SQL Native Client (SNAC) para utilizar recursos fornecidos pelo SQL Server 2005, SQL Server 2008 e versões posteriores.
- O SQLXMLX foi preterido. Migre o código para utilizar o SQLXML.
- Os elementos do esquema XDR (Reduzido por Dados de XML) foram preteridos. Migre aplicativos Web que utilizam este esquema para o esquema XML compatíveis com Padrões W3C.
- O recurso de padrão de XSL do MSXML3 foi preterido. Migre aplicativos Web que utilizam este recurso para o conjunto de recursos de Linguagem XPath com Padrões W3C.
Para ver a lista completa entre no link abaixo.
Fonte: http://technet.microsoft.com/pt-br/library/hh831568.aspx
Conectividade 