Exporting data from SQL Server to Excel can be achieved in a variety of ways.
Some of these options include
- Data Transformation Services (DTS)
- SQL Server Integration Services (SSIS)
- Bulk Copy (BCP)
Data Transformation Services (SQL Server 2000) and SQL Server Integration Services (SQL Server 2005) offers a GUI where widgets can be dragged and dropped. Each option has advantages and disadvantages, but all can do the job. It is just a matter of your comfort level with the tools and the best solution to meet the need.
4. Another option that is available directly via the T-SQL language is the OPENROWSET command (SQL Server 2000 and SQL Server 2005). This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL. Below outlines the full syntax available:
INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘SELECT Name, Date FROM [Sheet1$]’)
SELECT [Name], GETDATE() FROM master.dbo.sysobjects
5. CREATING REPORT WITH EXCEL 2007 WITH DIRECT CONNECTION TO SQL SERVER
In this walkthrough, I would introduce an easy way to achieve the same task without writing single line of query.
Below are screenshots captured during this walkthrough, so that anyone can have exact picture of what have to be done to achieve the goal.
Start Excel 2007.
Go to menu “Data”.
Follow the steps below:
You can see there are lots of Data Selection and Report Formating options are available in Excel Data menu, through which you can achieve various tasks.
In my next blog article, i will demonstrate the use of SQL Server Analysis Services (MDX Queries & Cubes) to be used as Data Source for creating PIVOT and Cross Tab reports.
The world is not enough