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

Откуда:
Сообщений: 55
всем салют.

Вопрос такой. Хотелось бы осуществить сабж чем-нибудь вроде такого запроса:

SELECT * INTO [target_sheet] FROM [source_table]

В качестве таргета может быть excel, text, html - неважно.

Между Jet'овскими БД подобные запросы проходят легко, типа:
SELECT * INTO [Tab1] IN 'c:\Target.xls' 'Excel 8.0;' FROM [source_table]

Можно ли провернуть нечто подобное (или хотя бы отдаленно напоминающее) с SQL Server'ом?

Был бы благодарен за любые идеи.
5 ноя 02, 23:06    [72937]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Trong
Member

Откуда: Novosibirsk
Сообщений: 759
Вот пример из MSDN для Oracle:
EXEC sp_addlinkedserver 'OracleSvr', 

'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')


Аналогичным образом через ODBC целяется что угодно.
6 ноя 02, 08:08    [72975]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Trong
Member

Откуда: Novosibirsk
Сообщений: 759
Черт! Ошибся топиком ... Сорри.
6 ноя 02, 08:09    [72976]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
xoxoji
Member

Откуда: Новосибирск
Сообщений: 14
BOL!
+++++++++++++++++++++++++++++++++++

USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO

==========================================
USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
GO

USE pubs
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS a
GO


USE pubs
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c INNER JOIN
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
AS o
ON c.CustomerID = o.CustomerID
GO
6 ноя 02, 10:59    [73091]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
digital_pilot
Member

Откуда:
Сообщений: 55
2 xoxoji

Спасибо за ответ. К сожалению, это не совсем то - OPENROWSET'ами можно из ёкселя только выборку делать, а мне нужно вставку. Вставка через OPENROWSET и подобные ему вещи не идет.

В любом случае, я тут уже накропал вариантец, так что тему можно закрывать. Вариант такой: создается коннекшн через провайдера Microsoft.Jet.OLEDB.4.0 к какой-нибудь базе-пустышке, XLS или MDB. Потом через этот коннекшн просто пропихивается нужный SQL-стэйтмент, который неявно коннектится к SQL-серверу и целевой екселевской базе:

SELECT * INTO
[Excel 8.0;Database=d:\Target.xls].[Sheet1]
FROM
[ODBC;Driver=SQL Server;Database=MyDB;Server=MyServer;Trusted_Connection=Yes;].[dbo.MyTable]

Вместо ёкселя в качестве таргета можно также использовать HTML или TXT. Тогда 2-я строчка будет выглядеть приблизительно так:

[HTML Export;Database=d:\].[Target.htm]
или
[Text;Database=d:\].[Target.txt]
6 ноя 02, 14:02    [73251]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Dwarf
Member

Откуда: Moscow
Сообщений: 85
Например:
exec master..xp_cmdshell "bcp db_name.dbo.table_name out c:\table_name.csv -T -C 1251 -f c:\bcp.fmt"
--- bcp.fmt ---
7.0
2
7.0
2
1 SQLCHAR 0 64 "," 1 name
2 SQLCHAR 0 255 "\r\n" 2 value
-----------------
6 ноя 02, 15:03    [73314]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Dwarf
Member

Откуда: Moscow
Сообщений: 85
Упс...
--- bcp.fmt ---
7.0
2
1 SQLCHAR 0 64 "," 1 name
2 SQLCHAR 0 255 "\r\n" 2 value
-----------------
В BOL'е есть описание формата. Или запустить bcp из командной строки и согласиться на предложение сгенерить файл .fmt (после ответов на вопросы :))
6 ноя 02, 15:07    [73320]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Glory
Member

Откуда:
Сообщений: 104751
Вставка через OPENROWSET и подобные ему вещи не идет.
Очень даже идет

UPDATE OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=x:\xxx.xls;Extended Properties=Excel 8.0')...[sheet1$] set mycolumn = 'xxx' where ...

INSERT OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=x:\xxx.xls;Extended Properties=Excel 8.0')...[sheet1$] values('aaa', 'bbb')
6 ноя 02, 16:29    [73401]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
digital_pilot
Member

Откуда:
Сообщений: 55
2 Dwarf
спасибо. на крайняк мож пришлось бы и так :)

2 Glory
я неточно выразился. Да, инсерты и апдейты через OPENDATASOURCE идут, но "SELECT * INTO ... FROM ..." не прокатывает.

В любом случае, вопрос решен. Благодарю всех откликнувшихся.
8 ноя 02, 02:39    [73766]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Glory
Member

Откуда:
Сообщений: 104751
Как ни странно, но у меня
SELECT * INTO #t FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=x:\xxx.xls;Extended Properties=Excel 8.0')...[sheet1$]
прекрасно работает

PS
Microsoft SQL Server 2000 - 8.00.679 (Intel X86) Aug 26 2002 15:09:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
8 ноя 02, 09:49    [73782]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
digital_pilot
Member

Откуда:
Сообщений: 55
2 Glory

:) эт-то и у меня работает. Но мне наоборот нужно было, вставлять ИЗ SQL Server'а в Excel.
8 ноя 02, 13:42    [73815]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Glory
Member

Откуда:
Сообщений: 104751
Вы будете смеятся, но скрипт

create table #t(f1 varchar(10), f2 varchar(20))

insert #t select 'aaa', 'bbb' union select 'ccc', 'ddd'
INSERT OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=x:\xxx.xls;Extended Properties=Excel 8.0')...[sheet1$] select * from #t
drop table #t

то же работает.

А если же вы хотите создавать в запросе Excel файлы и книги, то для этого есть DTS
8 ноя 02, 14:05    [73817]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
digital_pilot
Member

Откуда:
Сообщений: 55
2 Glory

не, смеяться не буду, т.к. ничего удивительного в этом скрипте нет. Но в том-то и дело, что он уже готовую таргетовую ексель-таблицу предполагает. Мне такое не подходило.

Вы правы насчет DTS, я было уже и хотел через них делать, но на DTS начальством было наложено вето. Поэтому пришлось делать как сделал.
8 ноя 02, 17:43    [73870]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Glory
Member

Откуда:
Сообщений: 104751
Можно попробовать запустить через xp_cmdshell простенький VB Script, который создаст Excel-файл, ну а дальше уже OPENDATASOURCE ....
Это в теории
8 ноя 02, 19:08    [73876]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Dennis_L
Member

Откуда: Латвия
Сообщений: 126
У меня код
create table #t(f1 varchar(10), f2 varchar(20))

insert #t select 'aaa', 'bbb' union select 'ccc', 'ddd'
INSERT OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=x:\xxx.xls;Extended Properties=Excel 8.0')...[sheet1$] select * from #t
drop table #t

тоже не работает :(
ошибка
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  

[OLE/DB provider returned message: Unspecified error]


Едиственное не понял где надо создавать файл d:\xxx.xls на диске d рабочей станции откуда запускаю скрипт или на сервере попробовал и там и там результат одинаков...

А такую возможность очень бы хотелось использовать ...
8 ноя 02, 19:26    [73882]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
digital_pilot
Member

Откуда:
Сообщений: 55
2 Glory

угу, все дело, в общем-то, в VB-программе и происходило. Но структуру таргетовой таблицы больно лениво создавать. Поэтому сделал все через Jet.
9 ноя 02, 03:00    [73911]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Glory
Member

Откуда:
Сообщений: 104751
2Dennis_L
- файл ищется сервером относительно себя
- Запустите запрос предварительно выполнив dbcc traceon(7300)

2digital_pilot
Опять же теоритически можно создать Excel файл используя процедуры sp_OA*. Правда мучают сомнения по поводу надежности и эффективности такого способа.
9 ноя 02, 13:40    [73937]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Dennis_L
Member

Откуда: Латвия
Сообщений: 126
После dbcc traceon(7300) у меня все заработало !
Но эти запросыя я тестировал на запасном сервере ...

Теперь вот думаю переделать пару продцедур на основном серевере и мне интересно на что конкретно влияет флаг 7300 ?? если я его установлю на основном сервере это не как не может вызвать сбой в выполнении какихто операций либо изменить чтнибудь в безопасности сервера и т.д. ???
3 дек 02, 16:42    [85747]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
guest2002
Member

Откуда:
Сообщений: 21
См. компоненты OLE XLSFile для работы с Excel-ем.
<a href="http://smsoft.esmartweb.com/pxlsole.htm">
http://smsoft.esmartweb.com/pxlsole.htm
</a>
3 дек 02, 20:35    [85875]     Ответить | Цитировать Сообщить модератору
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
Glory
Member

Откуда:
Сообщений: 104751
мне интересно на что конкретно влияет флаг 7300 ??

Here
3 дек 02, 21:10    [85885]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Экспорт MS SQL Server -> Excel без DTS  [new]
philips
Member

Откуда:
Сообщений: 365
create table #t(f1 varchar(10), f2 varchar(20))

insert #t select 'aaa', 'bbb' union select 'ccc', 'ddd'

INSERT OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=x:\xxx.xls;Extended Properties=Excel 8.0')...[sheet1$] select * from #t

drop table #t



тоже не работает :(
ошибка

Msg 7308, Level 16, State 1, Line 5
Невозможно использовать поставщик OLE DB "Microsoft.Jet.OLEDB.4.0" для распределенных запросов, поскольку поставщик настроен на работу в потоке контейнера с одним потоком.
29 мар 12, 17:11    [12335213]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить