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

Откуда:
Сообщений: 57
Ситуация такая: мне надо ежемесячно составлять отчеты (более 10-и на данный момент), данные для которого содержатся в БД MS SQL 2005 ExpressEdition.

Ходить на сервера (2003 и 2008) и руками там запускать скрипты или Managenent Studio - лениво. Тем более, что RDP к серваку нет. И не будет, если что ;)

Хотел сделать так, чтобы мои SELECT-ы отрабатывались обычным планировщиком Windows и файл с результатом в виде xls ну или csv ложился куда-нить в папку, а там уже его заслать куда надо по мылу - раз плюнуть.
Ах да.. сторонние приложения использовать крайне нежелательно.. ну может быть какие-то портейбл-варианты, но и то.. не хотелось бы.

Чего наковырял: bcp - дешево и сердито. А главное встроенное средство и уже всегда есть.

Путем проб и ошибок нарисовал скриптец
bcp.exe
"SELECT opirito_sro_qra_db.dbo.UserCertificate1.CertificateID,
opirito_sro_qra_db.dbo.UserCertificate1.UserID,
opirito_sro_qra_db.dbo.UserCertificate1.StateID,
opirito_sro_qra_db.dbo.UserCertificate1.DisplayName,
opirito_sro_qra_db.dbo.UserCertificate1.SerialNumber,
CONVERT(smalldatetime, opirito_sro_qra_db.dbo.UserCertificate1.IssueDate, 103),
opirito_sro_qra_db.dbo.UserCertificate3.Subject,
opirito_sro_qra_db.dbo.UserCertificate4.EKU
FROM opirito_sro_qra_db.dbo.UserCertificate1
INNER JOIN opirito_sro_qra_db.dbo.UserCertificate3
ON opirito_sro_qra_db.dbo.UserCertificate1.CertificateID = opirito_sro_qra_db.dbo.UserCertificate3.CertificateID
INNER JOIN opirito_sro_qra_db.dbo.UserCertificate4
ON opirito_sro_qra_db.dbo.UserCertificate1.CertificateID = opirito_sro_qra_db.dbo.UserCertificate4.CertificateID
WHERE (opirito_sro_qra_db.dbo.UserCertificate4.EKU LIKE '%1.2.643.2.2.34.6%')
AND (opirito_sro_qra_db.dbo.UserCertificate1.IssueDate BETWEEN CONVERT(smalldatetime, '01.03.2013 00:00:00', 103)
AND CONVERT(smalldatetime, '31.03.2013 23:59:59', 103))"
queryout Result.csv -c -T -S MYSQLSERVER\SQLEXPRESS -C CP1251 -t ;


Делает то, что надо: файло Result.csv содержит именно то, что я хотел. Только вот работает этот скрипт только из командной строки. Из bat-файла - нифига.. обрезает скрипт (есть ограничение на длину командной строки).

Собственно два вопроса:
1. Пo SQL: может есть какие-то более простые способы получить то, что мне нужно?
2. Не по SQL: как бы мне заставить выполняться мой скрипт периодически?
2 апр 13, 16:06    [14127242]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]

Guest
Laroux,
создайте на основе запроса представление и и все сведется к банальному
bcp.exe
"SELECT * from вашепредставление"
queryout Result.csv -c -T -S MYSQLSERVER\SQLEXPRESS -C CP1251 -t ;
2 апр 13, 16:13    [14127297]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Laroux,

Вынесите запрос в хранимую процедуру, вызывайте процедуру с нужными параметрами.
2 апр 13, 16:14    [14127307]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Glory
Member

Откуда:
Сообщений: 104751
Laroux
1. Пo SQL: может есть какие-то более простые способы получить то, что мне нужно?

Узнать, что утилита bcp может читать текст запроса из файла.
И сделать такой файл.
Laroux
2. Не по SQL: как бы мне заставить выполняться мой скрипт периодически?

Использовать любой шедулер. Хотя бы тот, что встроен в ознозно windows
2 апр 13, 16:16    [14127318]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Laroux
Member

Откуда:
Сообщений: 57
Glory
Узнать, что утилита bcp может читать текст запроса из файла.
не умеет

`ё, Гость333: так процедуру или представление-таки? Заодно может подскажете, где про создание такой вот процедуры/представления прочесть?

Спасибо
2 апр 13, 16:19    [14127339]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Glory
Member

Откуда:
Сообщений: 104751
Laroux
Glory
Узнать, что утилита bcp может читать текст запроса из файла.
не умеет

Да. перепутал.
2 апр 13, 16:21    [14127359]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]

Guest
Laroux,

что вам удобнее можно и то и то. где - в документации
2 апр 13, 16:23    [14127370]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Laroux
Member

Откуда:
Сообщений: 57
Действительно, с представлениями оказалось все просто элементарно

bcp.exe "SELECT * from opirito_sro_qra_db.dbo.ViewCert" queryout Contacts.csv -c -T -S MYSQLSERVER\SQLEXPRESS -C CP1251 -t ;


огромное спасибо за помощь
2 апр 13, 16:51    [14127530]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Laroux
Действительно, с представлениями оказалось все просто элементарно

bcp.exe "SELECT * from opirito_sro_qra_db.dbo.ViewCert" queryout Contacts.csv -c -T -S MYSQLSERVER\SQLEXPRESS -C CP1251 -t ;


огромное спасибо за помощь

А как будет выглядеть запрос, когда вам понадобятся данные за апрель?
2 апр 13, 16:55    [14127564]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Гость333,

ну Вы и западло (с) По добраму еснно
2 апр 13, 16:57    [14127575]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Laroux
Member

Откуда:
Сообщений: 57
Хороший вопрос, кстати... я думал над этим - но пока ничего толкового в голову не пришло. Тем более я не особо спец в SQL.

На крайний случай думал вообще убрать условие отбора по дате да смотреть потом отчеты нарастающим итогом тупо
2 апр 13, 16:57    [14127579]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]

Guest
Гость333,
что спросили то и ответили ))
ну пускай учиться человек. ещё узнает что можно и по почте отсылать, так вопросами замучает)
2 апр 13, 16:59    [14127587]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Laroux
Member

Откуда:
Сообщений: 57
Насчет почты не замучаю, не переживайте))) Ибо у меня с этих серверов закрыто все. Могу только файлы скинуть в одно определенное место
2 апр 13, 17:01    [14127596]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Laroux
Member

Откуда:
Сообщений: 57
Оффтоп: прошу прощения заранее... А не редактируются тут сообщения? Свои, естественно
2 апр 13, 17:02    [14127603]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Laroux,

нет, редактировать вроде нельзя
2 апр 13, 17:06    [14127627]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Laroux,

Кстати, почему бы вам не открыть для себя псевдонимы таблиц и не переписать запрос примерно так :-)
SELECT c1.CertificateID, c1.UserID, c1.StateID, c1.DisplayName, c1.SerialNumber,
CONVERT(smalldatetime, c1.IssueDate, 103), c3.Subject, c4.EKU
FROM opirito_sro_qra_db.dbo.UserCertificate1 c1
INNER JOIN opirito_sro_qra_db.dbo.UserCertificate3 c3
ON c1.CertificateID = c3.CertificateID
INNER JOIN c4 c4
ON c1.CertificateID = c4.CertificateID
WHERE (c4.EKU LIKE '%1.2.643.2.2.34.6%')
AND (c1.IssueDate BETWEEN CONVERT(smalldatetime, '01.03.2013 00:00:00', 103)
AND CONVERT(smalldatetime, '31.03.2013 23:59:59', 103))

Вдруг это поместится в ограничения bat-файлов.
2 апр 13, 17:07    [14127638]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Laroux
Member

Откуда:
Сообщений: 57
Да, да.. Знаю про такую фишку, просто получилось так, что я этот скрипт начал с мелочи и постепенно он разросся до безобразия. А я как-то и не стал его "оптимизировать" - руки еще не дошли
2 апр 13, 17:10    [14127645]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Laroux
Оффтоп: прошу прощения заранее... А не редактируются тут сообщения? Свои, естественно

Редактируются при помощи "сообщить модератору" :-) Ну, понятно, со всякими пустяками не стоит их беспокоить, а вот если засветили конфиденциальную информацию или ещё что-то...
2 апр 13, 17:10    [14127646]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Laroux
Member

Откуда:
Сообщений: 57
Сам терпеть не могу народ, который хочет, чтобы за них все сделали. Но и знаю, что вам раз плюнуть))

Если кто поможет - респект и уважуха))

Вот мое представление
SELECT     dbo.UserCertificate1.CertificateID, dbo.UserCertificate1.UserID, dbo.UserCertificate1.StateID, dbo.UserCertificate1.DisplayName, dbo.UserCertificate1.SerialNumber, 
                      CONVERT(smalldatetime, dbo.UserCertificate1.IssueDate, 103) AS Expr1, dbo.UserCertificate3.Subject, dbo.UserCertificate4.EKU
FROM         dbo.UserCertificate1 INNER JOIN
                      dbo.UserCertificate3 ON dbo.UserCertificate1.CertificateID = dbo.UserCertificate3.CertificateID INNER JOIN
                      dbo.UserCertificate4 ON dbo.UserCertificate1.CertificateID = dbo.UserCertificate4.CertificateID
WHERE     (dbo.UserCertificate4.EKU LIKE '%1.2.643.2.2.34.6%') AND (dbo.UserCertificate1.IssueDate BETWEEN CONVERT(smalldatetime, '24.11.2012 00:00:00', 103) AND 
                      CONVERT(smalldatetime, '29.11.2012 00:00:00', 103))


Скрипт будет выполняться 1 числа каждого месяца. Как мне сюда вставить переменные, чтобы выборка была каждый раз за предыдущий месяц?

P.S. Пока писал мне показалось, что не так уж и просто это...
2 апр 13, 17:12    [14127654]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]

Guest
Laroux,

функция getdate() возвращает текущую дату - дальше сами сообразите?
2 апр 13, 17:20    [14127692]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Laroux
Вот мое представление

Как мне сюда вставить переменные

В представление — никак.
Сделайте, например, функцию:
CREATE FUNCTION dbo.MyFunc(@EkuMask VARCHAR(100), @DateBegin DATETIME, @DateEnd DATETIME)
RETURNS TABLE
AS
RETURN
( SELECT ...
  FROM ...
  WHERE c4.EKU LIKE @EkuMask
       AND c1.IssueDate BETWEEN @DateBegin AND @DateEnd
);

И вызывайте эту функцию:
SELECT * FROM dbo.MyFunc('%1.2.643.2.2.34.6%', '2013-03-01T00:00:00', '2013-03-31T23:59:59')
2 апр 13, 17:20    [14127697]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Laroux
Member

Откуда:
Сообщений: 57
Ох.. с функцией что-то уже непонятное для меня началось - нужно будет учить мат.часть.

А с getdata(): это можно все-таки в представление зафигачить?
2 апр 13, 17:23    [14127712]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Laroux
Как мне сюда вставить переменные, чтобы выборка была каждый раз за предыдущий месяц?

А, проблема в предыдущем месяце. Ну тогда можно заменить условие "dbo.UserCertificate1.IssueDate BETWEEN ..." на такое:
dbo.UserCertificate1.IssueDate >= DATEADD(MONTH, DATEDIFF(MONTH, '20000101', GETDATE()) - 1, '20000101')
AND
dbo.UserCertificate1.IssueDate < DATEADD(MONTH, DATEDIFF(MONTH, '20000101', GETDATE()), '20000101')
2 апр 13, 17:26    [14127734]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Laroux
Member

Откуда:
Сообщений: 57
Гость333, просто снимаю шляпу... великолепно.

Я до такого сам точно не додумался.. ну не в этом году уж точно)))
2 апр 13, 17:35    [14127788]     Ответить | Цитировать Сообщить модератору
 Re: запросы в xls  [new]
Laroux
Member

Откуда:
Сообщений: 57
Гость333, а Вы не могли бы чуточку буквально вкратце описать конструкцию... я, что-то, ничего не могу в ней понять. Но работает)))
2 апр 13, 17:44    [14127837]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить