question

satya avatar image
satya asked

Dump SQl server table data into XLS.

Hi, I have to write a SP, that have to dump the one month old data into XLS sheet from one table. Please suggest me on how to dump the data from sql server table into XLS sheet. Thanks in advance
excelexport-data
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Usman Butt avatar image
Usman Butt answered
The easiest way is to do it through `SSIS`. Since it is a bit difficult to explain it without the screenshots, here is a an [example][1] to get you started. Another option is `BCP` but that may not suit you. Another way to get it done is through using `OPENROWSET`. Here is an [example][2] how to use it. [1]: http://SSIS:+Export+data+from+Sql+Server+2005+to+Excel [2]: http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

satya avatar image satya commented ·
Thank you very much "Usman" and I have one more question. I need to read the data from XLS sheet from sql server. Kindly help me on this.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
You can use SSIS and OPENROWSET for the same. An example is [here][1] [1]: http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205
0 Likes 0 ·
satya avatar image satya commented ·
Thank you "Usman" for your quick response. Is it possible by using T-SQL code or any system stored procedure other than using MSBI tools.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
OPENROWSET, OPENQUERY, LINKEDSERVER etc are the other options. Please visit [this][1]. [1]: http://support.microsoft.com/kb/321686
0 Likes 0 ·
satya avatar image
satya answered
Hi Usman, when I am trying to configure a Excel linked server as mentioned in the given reference site its giving the below error. Could you please explain me why it is giving this error. Create failed for LinkedServer 'XLSSHEET-LINKEDSERVER'. (SqlManagerUI) ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The server 'XLSSHEET-LINKEDSERVER' already exists. (Microsoft SQL Server, Error: 15028)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.