easy sql data 2 excel?

Question

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: 125564 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

ALFKIAlfreds Futterkiste Maria Anders

ANATRAna Trujillo Emparedados y helados Ana Trujillo

ANTONAntonio Moreno Taquería Antonio Moreno

AROUTAround the Horn Thomas Hardy

BERGSBerglunds snabbköp Christina Berglund

BLAUSBlauer See Delikatessen Hanna Moos

BLONPBlondesddsl 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]