question

rsheik avatar image
rsheik asked

Exporting SQLSERVER Table to Excel

Hi.. I know that.... I am asking a routine Question.. but consider it..(i am having some practical doubts) 1)I have a table in SQLSERVER 2)I need to export table to Excel including Headers(With same data types) 3)Exporting (procedure) Should run at 12.00 am everyday. 4)Everyday, Exported output file should be updated with new data(old data should be deleted) 5)What to do?? If Customer has opened the Excel file at 12.00am .... What are the ways to handle this..Please discuss..
export-data
2 comments
10 |1200

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

Anyone...Please Suggest me... steps for above Situation (in SQL SERVER Express Edition)..
0 Likes 0 ·
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
The best way to do this would be to use SQL Server Integration Services (SSIS) to define a precise export process. You can schedule that using SQL Agent and it will do everything you need. Here's the introduction to [SSIS in the Microsoft documentation][1]. On a side note, this is a question that gets asked a lot. You could just search in the search box above to see where others have asked the same question and the answers they got. It's a much more efficient approach. [1]: http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx
2 comments
10 |1200

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

Express wasn't part of the original definition. So, short answer, yes and no. Yes, Express can be a target or destination for SSIS. No, if all you have is Express you can't develop SSIS packages or execute them. To execute them you need Standard or better for SQL Server.
2 Likes 2 ·
can i have SSIS service in SQL SERVER Express Edition ????
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Because you are in Express, you have an extremely limited tool set at your disposal. You can run [sqlcmd.exe][1], which is available in Express, to export to a comma delimited file format and name that as XLS. But you'll be limited simply creating that file. You won't be able to target specific pages in Excel or create tables or anything like that. You'll be able to create a file that can be opened by Excel that will look like Excel. Scheduling it is an issue since Express doesn't have Agent. So you'll need to call sqlcmd.exe from the [Windows Scheduler][2]. That's the bare bones of what you need to do. You could be much more extensive in functionality with the appropriate tool set. Another option, if you don't mind quite a bit of programming, is to put PowerShell to work. You can query the database directly through PowerShell and then there are a number of methods you can use to write to an Excel file. [Here's one example][3]. But, using PowerShell, you'll be able to do pretty much anything you want, it'll just take a bunch of code. [1]: http://technet.microsoft.com/en-us/library/ms162773.aspx [2]: http://msdn.microsoft.com/en-us/library/windows/desktop/aa383614(v=vs.85).aspx [3]: http://yellowonline.tweakblogs.net/blog/8083/creating-excel-files-with-powershell.html
10 |1200

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.