Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4   вперед  Ctrl      все
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Да понятно, где ошибка. Грязные хаки это нехорошо. Ща.
29 апр 13, 11:58    [14243258]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Смотрел конечно. Но локализовать место с ошибкой не получается, потому, что фунция возвразает нварчар в 20000 сиволов длиной. На глазок не разберешься =(

Также, мне кажется, что если для одной таблицы возвращается рабочий запрос, а для другой нет, то дело в том как эта функция работает стаблицами.

Еще раз отмечу, что в дебрях XQuery я пока не разбираюсь. Может там какие то названия полей недопутсимые, или даже значения полей, или еще какая то магия ?

Притом что 'AS s' в тексте встречается только один раз...
29 апр 13, 12:00    [14243267]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Glory
Member

Откуда:
Сообщений: 104751
Cammomile
о локализовать место с ошибкой не получается, потому, что фунция возвразает нварчар в 20000 сиволов длиной. На глазок не разберешься =(

Msg 102, Level 15, State 1, Line 103
Incorrect syntax near 's'.
29 апр 13, 12:02    [14243281]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Это когда через exec(q)
А когда копипаста из грида в новое окно - все в одну строку.

Вам бы лишь придраться!
29 апр 13, 12:04    [14243296]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Glory
Member

Откуда:
Сообщений: 104751
Cammomile
А когда копипаста из грида в новое окно - все в одну строку.

Какой грид при print @q ?
29 апр 13, 12:06    [14243307]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Ох мать моя- женщина, отец в тапках.

Ну конечно я делал принт и даже резалт ту файл. Но моя студия с педальным приводов, запущенная на электроннике мк51, обрезает результат вывода, что при принте что при резалт ту файл. И вместо означенной ошибки я имею какой-нибудь "анклозед куатейшн мэрк" =)
29 апр 13, 12:08    [14243322]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Cammomile
Ох мать моя- женщина, отец в тапках.

Ну конечно я делал принт и даже резалт ту файл. Но моя студия с педальным приводов, запущенная на электроннике мк51, обрезает результат вывода, что при принте что при резалт ту файл. И вместо означенной ошибки я имею какой-нибудь "анклозед куатейшн мэрк" =)

Напишите цикл, который будет PRINT'овать кусками по 4000 символов.
29 апр 13, 12:16    [14243379]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Я лучше подожду ответа от Мниора, бо
автор
Да понятно, где ошибка. Грязные хаки это нехорошо. Ща.
=) Все равнов грязных хаках ИксКвери я не бум-бум
29 апр 13, 12:18    [14243396]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Профтыкал:
CREATE FUNCTION [dbo].[fnExcelQuery] (
	 @Object	SysName
	,@Query		NVarChar(max)
) RETURNS NVarChar(max) AS BEGIN RETURN (
SELECT	(
SELECT	  CASE	WHEN C.column_id = 1
		THEN ' '
		ELSE '
	,'	END + 'NULL AS [text()],'
	+ CASE	WHEN C.system_type_id IN (48,52,56,59,60,62,106,108,122,127)
							THEN '''Number''  '
		WHEN C.system_type_id IN (58,61)	THEN '''DateTime'''
		WHEN C.system_type_id = 104		THEN '''Boolean'' '
							ELSE '''String''  '
		END + 'AS [Cell/Data/@s:Type],'
	+ QuoteName(C.name) + Space(Max(Len(C.name))OVER() - Len(C.name)) + ' AS [Cell/Data/text()]'
		AS [Row/text()]
	, IsNull('<Column s:StyleID="'
	+ CASE	WHEN C.system_type_id IN (48,52,56,59,62,106,108,127)
							THEN 'Number'
		WHEN C.system_type_id IN (60,122)	THEN 'Currency'
		WHEN C.system_type_id IN (58,61)	THEN 'DateTime'
		WHEN C.system_type_id = 104		THEN 'Boolean'
		END + '" />','<Column />')
		AS [Style/text()]
	,'<Cell><Data s:Type="String">' + (SELECT C.name AS [*] FOR XML Path('')) + '</Data></Cell>'
		AS [Name/text()]
FROM	sys.columns C
WHERE	C.[object_id] = Object_ID(@Object)
ORDER BY C.column_id
FOR XML Path(''),Type).query('(<Row>{/Row/text()}</Row>,<Style>{/Style/text()}</Style>,<Name>{/Name/text()}</Name>)').value('fn:concat("
;WITH XMLNAMESPACES(Default ''urn:schemas-microsoft-com:office:spreadsheet'',''urn:schemas-microsoft-com:office:spreadsheet'' AS s)
SELECT	N''<?xml version=""1.0"" encoding=""UTF-16""?><?mso-application progid=""Excel.Sheet""?>'' + Convert(NVarChar(max),IsNull((
SELECT	",(/Row/text())[1],"
FROM	",sql:variable("@Object"),sql:variable("@Query"),"
FOR XML Path(''Row''),Root(''Table''),Type),'''').query(''
<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:s=""urn:schemas-microsoft-com:office:spreadsheet"">
 <Styles>
  <Style s:ID=""Boolean""><NumberFormat s:Format=""True/False"" /></Style>
  <Style s:ID=""Currency""><NumberFormat s:Format=""Currency"" /></Style>
  <Style s:ID=""Number""><NumberFormat s:Format=""General Number"" /></Style>
  <Style s:ID=""DateTime""><NumberFormat s:Format=""General Date"" /></Style>
 </Styles>
 <Worksheet s:Name=""",sql:variable("@Object"),""">
  <Table>
   ",(/Style/text())[1],"   &lt;Row&gt;
   ",(/Name/text())[1],"   &lt;/Row&gt;
   {/Table/Row}
  </Table>
 </Worksheet>
</Workbook>''))")','NVarChar(max)')
) END
GO
А хак (xml строкой), чтоб не генерилось определение неймспейса.
29 апр 13, 12:26    [14243470]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Glory
Member

Откуда:
Сообщений: 104751
Cammomile
Но моя студия с педальным приводов, запущенная на электроннике мк51, обрезает результат вывода, что при принте что при резалт ту файл. И вместо означенной ошибки я имею какой-нибудь "анклозед куатейшн мэрк" =)

Так может начать с изучения настроек студии ?
29 апр 13, 12:28    [14243488]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Не, не стоит.

Кстати я отловил где отваливается

У меня там в значении поля АПОСТРОФ !!! Стало быть на

<Data s:Type="String">Prodname SuperProd's goodies </Data> оно вылетает!
29 апр 13, 12:41    [14243590]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Отдельное спасибо Гость333 за идею с принтами =)
29 апр 13, 12:43    [14243609]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
,'<Cell><Data s:Type="String">' + (SELECT REPLACE(C.name,'''','') AS [*] FOR XML Path('')) + '</Data></Cell>'

Так победим!
29 апр 13, 12:48    [14243649]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Cammomile
У меня там в значении поля АПОСТРОФ !!!
Чёрд!
Опять двадцать пять.

Cammomile
REPLACE(C.name,'''','')
Нифига!
+ Последняя версия
CREATE FUNCTION [dbo].[fnExcelQuery] (
	 @Object	SysName
	,@Query		NVarChar(max)
) RETURNS NVarChar(max) AS BEGIN RETURN ((
SELECT	  CASE	WHEN C.column_id = 1
		THEN ' '
		ELSE '
	,'	END + 'NULL AS [text()],'
	+ CASE	WHEN C.system_type_id IN (48,52,56,59,60,62,106,108,122,127)
							THEN '''Number''  '
		WHEN C.system_type_id IN (58,61)	THEN '''DateTime'''
		WHEN C.system_type_id = 104		THEN '''Boolean'' '
							ELSE '''String''  '
		END + 'AS [Cell/Data/@s:Type],'
	+ QuoteName(C.name) + Space(Max(Len(C.name))OVER() - Len(C.name)) + ' AS [Cell/Data/text()]'
		AS [Row/text()]
	, IsNull('<Column s:StyleID="'
	+ CASE	WHEN C.system_type_id IN (48,52,56,59,62,106,108,127)
							THEN 'Number'
		WHEN C.system_type_id IN (60,122)	THEN 'Currency'
		WHEN C.system_type_id IN (58,61)	THEN 'DateTime'
		WHEN C.system_type_id = 104		THEN 'Boolean'
		END + '" />','<Column />')
		AS [Style/text()]
	,'<Cell><Data s:Type="String">' + RePlace((SELECT C.name AS [*] FOR XML Path('')),'''','&apos;') + '</Data></Cell>'
		AS [Name/text()]
FROM	sys.columns C
WHERE	C.[object_id] = Object_ID(@Object)
ORDER BY C.column_id
FOR XML Path(''),Type).query('(<Row>{/Row/text()}</Row>,<Style>{/Style/text()}</Style>,<Name>{/Name/text()}</Name>)').value('fn:concat("
;WITH XMLNAMESPACES(Default ''urn:schemas-microsoft-com:office:spreadsheet'',''urn:schemas-microsoft-com:office:spreadsheet'' AS s)
SELECT	N''<?xml version=""1.0"" encoding=""UTF-16""?><?mso-application progid=""Excel.Sheet""?>'' + Convert(NVarChar(max),IsNull((
SELECT	",(/Row/text())[1],"
FROM	",sql:variable("@Object"),sql:variable("@Query"),"
FOR XML Path(''Row''),Root(''Table''),Type),'''').query(''
<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:s=""urn:schemas-microsoft-com:office:spreadsheet"">
 <Styles>
  <Style s:ID=""Boolean""><NumberFormat s:Format=""True/False"" /></Style>
  <Style s:ID=""Currency""><NumberFormat s:Format=""Currency"" /></Style>
  <Style s:ID=""Number""><NumberFormat s:Format=""General Number"" /></Style>
  <Style s:ID=""DateTime""><NumberFormat s:Format=""General Date"" /></Style>
 </Styles>
 <Worksheet s:Name=""",sql:variable("@Object"),""">
  <Table>
   ",(/Style/text())[1],"   &lt;Row&gt;
   ",(/Name/text())[1],"   &lt;/Row&gt;
   {/Table/Row}
  </Table>
 </Worksheet>
</Workbook>''))")','NVarChar(max)')
) END
GO
30 апр 13, 15:42    [14249976]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Красаучик! Жаль, правда, что больших объемах данных эта нетленка неочень =(
У меня тут 50к строк на 27 мегабайт чистого текста, этой штукой разворачивается в 250+ МБ эксемеля.

Большая просадка по скоростям. Приходится пилить CSV
30 апр 13, 16:20    [14250196]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Кстати, а почему эксель 2010 стандартный не может открытьб сгенериророванный xml? Оффис 7й норм съедает, а 2010 ломается. Там какие-то другие заголовки? можно сделать так чтобы открывалось во всех боле-менее "новых" экселях ? Скажем, начиная с 2007
30 апр 13, 16:23    [14250221]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Glory
Member

Откуда:
Сообщений: 104751
Cammomile
У меня тут 50к строк на 27 мегабайт чистого текста, этой штукой разворачивается в 250+ МБ эксемеля.

А что за маньяк захотел увидеть 50 000 строк в Excel ?
Что он с ними будет делать ?
30 апр 13, 16:24    [14250228]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
* И что тут делает входной п-р ,@Query NVarChar(max)
30 апр 13, 16:25    [14250230]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Glory
Cammomile
У меня тут 50к строк на 27 мегабайт чистого текста, этой штукой разворачивается в 250+ МБ эксемеля.

А что за маньяк захотел увидеть 50 000 строк в Excel ?
Что он с ними будет делать ?

Да есть тут одна широкоизвестная продуктовая кампания...
Понятия не имею, но бизнес такой бизнес.
Мое дело их хотелки выполнять. =)
30 апр 13, 16:32    [14250270]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Glory
Member

Откуда:
Сообщений: 104751
Cammomile
Да есть тут одна широкоизвестная продуктовая кампания...
Понятия не имею, но бизнес такой бизнес.
Мое дело их хотелки выполнять. =)

Ни один нормальный человек не способен адекватно работать с 50 000 строк в экселе.
Да еще который занимает 250Мб.
30 апр 13, 16:35    [14250289]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Именно поэтому я в итоге отказался от ИКСМЛ и порезал отчет в некоем аналитическом разрезе на куски. Но тем не менее бузинесс хочеть иметь "простыню".

А за уговоры бизнеса не делать глупостей мне тут не платят.
30 апр 13, 16:39    [14250326]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Glory
Member

Откуда:
Сообщений: 104751
Cammomile
А за уговоры бизнеса не делать глупостей мне тут не платят.

Бизнесс не знает, что такое xml, cvs и прочая
Сомневаюсь, что именно бизнесс поставил вам такое ТЗ.
30 апр 13, 16:43    [14250360]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
Бизнес поставил задачу "отобрать данные по критерием и предоставить их в виде эксель"
Притом, что:
- отчетников нет и не будет
- никаких средств разработки по нет и не будет

Есть MS SQL SERVER 2005 (reporting services не установлены и не будут установлены) и, соответсвенно, MS SQL SERVER MANAGEMENT STUDIO


Стало быть, инструментарий и набор подходов крайне ограничены. Если у вас есть решение, как 50-70 тысяч строк резалтсета пропихнуть в эксель каким-нибудь изящным и быстрым способом, то я с нетерпением жду ваших советов.
30 апр 13, 16:55    [14250456]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Glory
Member

Откуда:
Сообщений: 104751
Cammomile
Бизнес поставил задачу "отобрать данные по критерием и предоставить их в виде эксель"
Притом, что:
- отчетников нет и не будет
- никаких средств разработки по нет и не будет

Суровый у вас бизнесс - и формат диктует и средства выполнения.
30 апр 13, 16:59    [14250489]     Ответить | Цитировать Сообщить модератору
 Re: Автоматизация вывода отчетов в файлы xls (Excel)  [new]
Glory
Member

Откуда:
Сообщений: 104751
Cammomile
Если у вас есть решение, как 50-70 тысяч строк резалтсета пропихнуть в эксель каким-нибудь изящным и быстрым способом, то я с нетерпением жду ваших советов.

bcp в текстовый файл csv.
Который замечательно открывается excel-ем
30 апр 13, 17:00    [14250498]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить