export table to excel

how should I go about exporting a couple of columns of data from a specific database table to an excel spreadsheet stored locally on a client machine (the one the script is being executed from). I am using SQL server 2000 SP3.

I'm fairly new to using SQL server, and not experienced at all with Transact-SQL.

using enterprise manager from client manager... there is export task wizard... you can easily achieve this.....

mandip

|||while i can do this from my pc, there are some clients that will need to be doing the same thing who will not have enerprise manager on their computer...|||

If these other clients have MS Access, you can link the SQL Server table to Access, and they can use the Export feature within Access. (Right-click on the table name, choose "Export...", etc.) You could also write an Access query if you wish to control the columns that will be exported, as well as the column titles and sort order.

Dan

|||they may or may not have access, this would also require me show them how to do this (show users who are not reliably familiar with access or mysql). is it possibe to create an application or script that could do this?|||

I'm sure that you could write a .NET web application. Such web applications written by my coworkers typically call stored procedures that contain the desired SELECT statement. As such, the stored procedure returns a table. If multiple SELECT statements are in the stored procedure, it returns multiple tables.

You may need additional software to convert the SELECT output to an Excel spreadsheet. Perhaps you can do that with SSIS. You might consider asking that in the SSIS forum.

What software can you expect users to have on their PCs?

|||they will be using xp machines with .netframework and the basic microsoft office supplies (word, excel, outlook, maybe access), generally office 2003|||

You may wish to explore using BCP.exe (perhaps installing the BCP utility on the users computers. Then you could create a batch file for the users to run.

With BCP running locally, you can easily output to a local file.

Otherwise, you could create a Stored Procedure that uses SLQCmd.exe, BCP, and XCopy to create file on the server, and then move that file to the local computer. However, this option will be more problematic due to network security concerns.

|||

Arnie,

Is BCP a "free" utility, or are there licensing considerations concerning placing BCP on all the users' computers?

Dan

|||

BCP can be used on any properly licensed SQL Server, and it can be used by anyone having access to that server and either a CAL, or the server accessed is covered with a processor license.

As far as I am aware, it is freely distributable to licensed users in your organization. However, for the definitive answer related to licensing questions, call the licensing folks:

Licensing –Microsoft, Contact
(800) 426-9400

From: http://www.microsoft.com/sql/howtobuy/faq.mspx

Do I need a separate license to run the SQL Server tools and analysis services? A.

No, a separate license is not required. However, any device that has SQL Server tools or technologies installed must have a valid SQL Server license.

|||

Wraithzshadow,

1) Create an ODBC on the local PC pointing to the SQL server.

2) Open Excel spread sheet

3) Select Data > import external data > new database query

4) Select the ODBC created in step one, Click OK

5) Select Table or view from the list, Click Add then Click Close

6) Select the columns needed from the table

7) Click the return data icon (door with arrow)

The data is displayed in the spread sheet. The good thing about this is you can update the data by right clicking into any cell containing data and selecting refresh data or by setting the query to refresh on open.