Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mvanwyk
Yak Posting Veteran
99 Posts |
 Posted - 2009-08-17 : 10:08:43
|
| Hi Team.I have been tasked to export about 60000 rows of data to a CSV everyday.The query to extract data will look like...select * from [Message] where createdate between '2009-08-17 00:00:00.001' and '2009-08-17 23:59:00.001'Is there something in SQL (DTS) or integration that can do this for me.Please note*The Dates will change every day, and i need run the export the next day for the previous day. |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-08-18 : 05:32:16
|
| To get the data for a date range, use- the getdate() function in a stored procedure/sql- SYSDATE in SSIS- DATE in DOSTo extract the data, you can use:- BCP with queryout- sqlcmd- SSIS |
 |
|
|
mvanwyk
Yak Posting Veteran
99 Posts |
Posted - 2009-08-18 : 07:36:57
|
| Thanks for the post.What i would like to do is schedule this.So once everything is extracted into a csv i need to compress it and then mail it.Can BCP maybe do this?Cheers |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-08-18 : 10:18:12
|
| I'd suggest you build a SSIS package that - extracts the data to a file (Data Flow Task)- compresses the file (Execute Process Task)- then mail it (Send Mail Task)Schedule the job to run using SQL Agent.If you don't want to use SSIS, create separate steps in a SQL Agent job to:- Use BCP to extract the data out of SQL Server into a file- compress the file, using a command line utility- send the email with attachment using sp_send_dbmailYou'll probably require: - your SMTP server details - a command line compression utility installed on the server- read/write access to the area where the file will be written/compressed |
|
|
|
pkuchaliya
Starting Member
49 Posts |
Posted - 2009-08-19 : 07:15:05
|
| You can create a procedure and put it on the scheduling.pankaj |
|
|
|
|
|
|
Export Schedule