easy sql data 2 excel?

Question

Post reply

easy sql data 2 excel?

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125255 More actions · Answer 1

SKYBVI (9/17/2010)

@ eric

Ya, true , you have to give them a login and permission too.

About the refresh page, there is a option in connection properties,

refresh after __ minutes and also

refresh data while opening the file.

I guess they should make easier the work of the user.

Regards,

Sushant

Clicking the Refresh button is easy enough for the Excel user, but my point was that it's perhaps too easy. The user could bring the server to it's knees, if it's long running query, and they keep clicking on Refresh, and even more so if they configure the data to auto-refresh every few minutes. You have to be careful about what SQL is behind that Excel sheet.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

From a SQL 2000 Server.

The following assumes you are using EXCEL 2003 or earlier. If you are using EXCEL 2007 or later then the command must be altered to use the proper EXCEL driver.

1. The Excel data sheet must exist.

2. The column headings in the sheet must be the names of the columns being exported.

3. Excel must be installed on the server which contains SQL Server and the database.

The use open Rowset to perform the export:

In this example I was using the Northwind DB.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',



'Excel 8.0;Database=C:\Temp\TestK.xls', 'SELECT * FROM [Sheet1$]')

SELECT CustomerId,CompanyName,ContactName FROM dbo.Customers

In the above code



C:\Temp\TestK.xls'  -- Is the path to the existing Excel Spread sheet 

with the colum headings

CustomerId,CompanyName,ContactName - Columns from which the data is selected and returned.

This is a small sample of the data written to the specified Excel file

CustomerId CompanyName ContactName



ALFKI	Alfreds Futterkiste	                 Maria Anders

ANATR	Ana Trujillo Emparedados y helados     	 Ana Trujillo

ANTON	Antonio Moreno Taquería	                 Antonio Moreno

AROUT	Around the Horn	                         Thomas Hardy

BERGS	Berglunds snabbköp	                 Christina Berglund

BLAUS	Blauer See Delikatessen	                 Hanna Moos

BLONP Blondesddsl père et fils Frédérique Citeaux

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]