Образовательный проект «SnakeProject» Михаила Козлова

⇒ SQL\T-SQL ⇐

CISCO

Voice(Asterisk\Cisco)

Microsoft

Powershell

Python

FreeBSD and Nix

1С

Общая

WEB Разработка

ORACLE SQL \ JAVA

Мото

Стрельба, пневматика, оружие

Саморазвитие и психология

Импорт\Экспорт в или из Excel в MS SQL Server

--В примерах использовался MS SQL Server 2012 и Excel 2007

--В этом примере сделаем импортэкспорт из Excel в MS SQL и обратно

--Служба ms sql server была запущена не под системной учеткой
--Ставил учетку пользователя, имеющего доступ к файлу и папке

--Установим драйвер Microsoft.ACE.OLEDB.12.0
--http://www.microsoft.com/en-us/download/details.aspx?id=13255
--После установки для 86x платформ
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

--Создаем linked server для работы с Excel файлом
--Более полная докуметация 
--http://msdn.microsoft.com/en-us/library/aa259589(v=sql.80).aspx
EXEC sp_addlinkedserver
    @server = 'ExcelServer',
    @srvproduct = 'Excel', 
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:1231.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

--Выборка из Excel
--l1 - такое название я дал листу в excel файле
SELECT * FROM ExcelServer...[l1$]
SELECT * FROM OPENQUERY(ExcelServer, 'SELECT * FROM [l1$]')

--Вставка данных в EXCEL
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:1231.xlsx;','SELECT * FROM [l1$]')
SELECT 1, 2, 3

--Столкнулся с проблемой:SQL Server заблокировал доступ к STATEMENT "OpenRowset/OpenDatasource" компонента "Ad Hoc Distributed Queries", поскольку он отключен в результате настройки конфигурации безопасности сервера. Использование "Ad Hoc Distributed Queries" может быть разрешено администратором при помощи хранимой процедуры sp_configure
--Решил так:
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

--Более полная документация есть в этой статье
----http://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm

На этом все, видео можно увидеть на моем канале YouTube

<font face="&quot;courier" new,="" courier,="" monospace"="">

Комментарии пользователей