+1

Использование возможностей LinkedServer для импорта Excel-файлов

Taras Yaremchenko (Moderator / Admin (RUS)) 6 years ago in Geobank / SQL updated 6 years ago 0

Добрый день!


Хочу поделиться опытом использования Linked Server в Microsoft SQL Server для импорта Excel-файлов в базу данных.


Стандартный инструмент импорта Excel-файлов в Geobank завязывается на структуру файла, в том числе на имя рабочего листа Excel. В моем случае необходимо было провести импорт файла Excel, в котором было множество листов с одинаковой структурой, при этом названия листов от файла к файлу менялось.


В качестве исходного программного обеспечения имелся Microsoft Office 2010 64-bit, Microsoft SQL Server 2008 R2 Express edition 64-bit.


Для поддержки драйверов офиса необходимо было дополнительно установить Microsoft Access Database Engine 2010 Redistributable.


Обратите внимание, что разрядность установленного офиса, SQL сервера и устанавливаемого драйвера должны быть совместимы, т.е. либо все 32-bit, либо все 64-bit. Если у вас установлен офис 32-bit, а сервер 64-bit, то такую процедуру провернуть не получится.


Далее необходимо настроить некоторые параметры ACE OLE DB драйвера следующим запросом:


  1. 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 следующим запросом:


  1. if exists (select 1 from sys.servers where name = 'XlsLnkSrv') exec sp_dropserver @server = 'XlsLnkSrv', @droplogins = 'droplogins'
    <span>
    </span>exec sp_addlinkedserver @server = 'XlsLnkSrv', @srvproduct = 'ACE 12.0', @provider = 'Microsoft.ACE.OLEDB.12.0', @datasrc = N'd:\Гор=-65.xlsx', @provstr = 'Excel 12.0; HDR=No'
    

Где параметр @datasrc = N'd:\Гор=-65.xlsx' - явный путь к файлу Excel, @server = 'XlsLnkSrv' - имя Linked Server. В моем случае в Geobank для параметра @datasrc я использовал подстановочный параметр.

Затем, чтобы получить информацию с любого листа Excel, можно обратиться следующим запросом


  1. select * from openquery (XlsLnkSrv, 'Select * from [''2-65$'']')
    

Где '2-65$' - имя рабочего листа в Excel файле.


Чтобы имена всех рабочих листов в файле Excel, можно выполнить команду:


  1. sp_tables_ex 'XlsLnkSrv'
    

На данном этапе также можно использовать подстановочный параметр с целью выбора интересующих вас листов.


Используя все описанное выше, нетрудно подготовить скрипт, который будет переносить информацию из листов Excel-файла в нужные буферные или основные таблицы.


Более подробно ознакомиться с Linked Server вы можете на официальном сайте Microsoft.

0

SQL: log shipping or replication

azagoskin 1 month ago in Geobank / SQL updated 1 month ago 2

Hey guys,

I need to send data for my Geobank Mobile database from the local core shack SQL server to the main SQL server in the office. What algorithm would you recommend: log shipping or replication? It seems like replication can be quite challenging to set up and maintain… besides, I know that log shipping is working at least for one of my colleagues at many sites. But are there any hidden problems?

Setup:

  • 4 logging laptops are synchronizing logging data to the SQL GBM database at the local server
  • New data is being pushed to the copy of the GBM database on the main SQL server in the office
  • An SQL script populates data from GBM views to the buffer table of the main SQL database (GB)

Thanks in advance,

Andrey

Answer

Dear Andrey,

both approaches are applicable here but I agree with you -- replication maintenance may looks quite challenging here and requires more time and skills from person who provides its setup.

I recommend you to start with log shipping and follow this way.

There are good manual how to setup it for network environment:

https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server?view=sql-server-ver15


Regards, Max