Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Автоматический экспорт результатов запроса в Excel  [new]
JMLabs
Member

Откуда:
Сообщений: 66
Помогите, пожалуйста, советом как можно выгружать результат запроса select в файл excel. По сабжу написано много материала, но конкретного решения как это сделать нет. Везде описано как сделать разовый экспорт через GUI или иначе, но автоматического решения, чтобы сделать шедулер я не нашел. Вот, например, статья, которая вроде как описывает решение, но совершенно непонятно как это сделать, примеров нет: https://habrahabr.ru/company/oda/blog/263323/
17 апр 17, 16:43    [20408277]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
buven
Member

Откуда:
Сообщений: 792
JMLabs
Помогите, пожалуйста, советом как можно выгружать результат запроса select в файл excel. По сабжу написано много материала, но конкретного решения как это сделать нет. Везде описано как сделать разовый экспорт через GUI или иначе, но автоматического решения, чтобы сделать шедулер я не нашел. Вот, например, статья, которая вроде как описывает решение, но совершенно непонятно как это сделать, примеров нет: https://habrahabr.ru/company/oda/blog/263323/



Гугл.
Первая ссылка говорит как сделать нормально, вторая - ваши примеры.
Я бы смотрел в сторону SSIS всетки.
17 апр 17, 18:10    [20408560]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
rnk
Member

Откуда:
Сообщений: 126
SSIS?
17 апр 17, 18:20    [20408585]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
JMLabs
Member

Откуда:
Сообщений: 66
Спасибо за советы, но ни один из примеров http://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file я запустить не смог

пример 1
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

выдает ошибку:

Msg 7308, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

хотя я сделал:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

4-й пример
EXEC sp_makewebtask 
    @outputfile = 'd:\testing.xls', 
    @query = 'Select * from Database_name..SQLServerTable', 
    @colheaders =1, 
    @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

не работает из-за отсутствия хранимки sp_makewebtask

5-й пример
EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

выдает странное сообщение

User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile

Что касается SSIS, то мне не понятно как этот проект можно запускать в шедулере и как передавать в него входные данные
18 апр 17, 10:20    [20409814]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
rnk
Member

Откуда:
Сообщений: 126
JMLabs,
вместо 'Microsoft.Jet.OLEDB.4.0' попробуй 'Microsoft.ACE.OLEDB.12.0' или 'Microsoft.ACE.OLEDB.16.0'
18 апр 17, 11:23    [20410063]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
JMLabs
Member

Откуда:
Сообщений: 66
Подниму тему. Свою задачу практически решил на SSIS. Все работает отлично кроме одного - не могу настроить формат конечного xlsx. Все числа сохраняются как строки (у меня шаблон с названиями заголовков). Пробовал совет со скрытой строкой, но все равно не могу добиться форматирование как валюта. Может у кого-то есть рецепт?
22 дек 17, 01:26    [21054090]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
iii2
Member

Откуда:
Сообщений: 202
Если речь идет о простой табличке в excel - проще всего формировать excel xml файл (файл формата excel xml 2003), и помещать его в filetable.
Вообще никаких сторонних средств не надо. Ни SSIS, ни провайдеров, ничего.
Один TSQL и функционал db engine. Only.

По тому, как сформировать файлик в формате excel xml - тут есть пара замечательных тем.
22 дек 17, 09:23    [21054308]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
[quot iii2.
Один TSQL и функционал db engine. Only.
[/quot]
Технически, это, кончено, возможно.
Но, к сожалению, SQL сервер будет формировать XML файл не под правами пользователя, запустившего запрос, а под правами аккаунта, под которым запущен SQL сервер.
А с точки зрения информационной безопасности, не рекомендую давать аккаунту, под которым выполняется SQL сервер, права на любые директории, общие папки и иные ресурсы, которые ему не нужны для штатной работы.
22 дек 17, 10:52    [21054561]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
dtyu
Guest
SSRS

https://docs.microsoft.com/ru-ru/sql/reporting-services/report-builder/exporting-to-microsoft-excel-report-builder-and-ssrs

нагрузка уходит на отдельный сервер, расписания, права доступа, пересылка по почте или доступ через http
22 дек 17, 10:59    [21054590]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
iii2
Member

Откуда:
Сообщений: 202
ptr128
iii2
Один TSQL и функционал db engine. Only.


Технически, это, кончено, возможно.
Но, к сожалению, SQL сервер будет формировать XML файл не под правами пользователя, запустившего запрос, а под правами аккаунта, под которым запущен SQL сервер.
А с точки зрения информационной безопасности, не рекомендую давать аккаунту, под которым выполняется SQL сервер, права на любые директории, общие папки и иные ресурсы, которые ему не нужны для штатной работы.[/quot]
Честно говоря, не понимаю, о чем Вы.
Речь идет о версиях 2012+ и о функционале filestream, точнее - о надстройке над ним filetable.
Папка на диске отображается как таблица специального вида в базе данных. Снаружи она видится как сетевой ресурс, который существует, только когда запущен sql сервер.
Разграничение доступа к этой сетевой папке делается изнутри MSSQLSERVER, и позволяет, собственно, творить что угодно, с единственным ограничением, что это работает с windows аккаунтами.
Соответственно, запись изнутри сервера в таблицу - тоже подчиняется сикуеловской политике безопастности.
И формироваться запись будет именно под теми правами, которые даны.
Как это физически живет в ОС и сервере - не имеет никакого значения.
О чем речь то?!
22 дек 17, 16:06    [21055651]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
iii2
ptr128
А с точки зрения информационной безопасности, не рекомендую давать аккаунту, под которым выполняется SQL сервер, права на любые директории, общие папки и иные ресурсы, которые ему не нужны для штатной работы.

Речь идет о версиях 2012+ и о функционале filestream

Во-первых, про версию не было ни слова. А у многих (сужу по нашим клиентам) еще 2008R2 стоит.

iii2
Разграничение доступа к этой сетевой папке делается изнутри MSSQLSERVER, и позволяет, собственно, творить что угодно, с единственным ограничением, что это работает с windows аккаунтами.

Во-вторых, это для меня новость. Можете более подробно рассказать, как разраничивается доступ внутри FileTable между пользователями, чтобы пользователь не мог увидеть то, что выгружают остальные пользователи и не мог переписать файл, ранее выгруженный другим пользователем? MS SQL научился управлять ACL в файлах FileTable?
22 дек 17, 16:33    [21055726]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
iii2
Member

Откуда:
Сообщений: 202
ptr128
iii2
Разграничение доступа к этой сетевой папке делается изнутри MSSQLSERVER, и позволяет, собственно, творить что угодно, с единственным ограничением, что это работает с windows аккаунтами.

Во-вторых, это для меня новость. Можете более подробно рассказать, как разраничивается доступ внутри FileTable между пользователями, чтобы пользователь не мог увидеть то, что выгружают остальные пользователи и не мог переписать файл, ранее выгруженный другим пользователем? MS SQL научился управлять ACL в файлах FileTable?

Да элементарно. Разнесите это по отдельным папкам, т.е. по отдельным filetable.
25 дек 17, 08:14    [21059252]     Ответить | Цитировать Сообщить модератору
 Re: Автоматический экспорт результатов запроса в Excel  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
iii2
ptr128
Можете более подробно рассказать, как разраничивается доступ внутри FileTable между пользователями

Да элементарно. Разнесите это по отдельным папкам, т.е. по отдельным filetable.

Вы вообще представляете себе, сколько это работы в администрировании, даже при сотне пользователей, не говоря уже о тысяче или более? Спасибо, не надо.
25 дек 17, 08:45    [21059305]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить