Schedule to export the data to csv file

Hi,
I'm new to SQL. Recently I need to setup a schedule job to export data into
csv file every month. I know I can do the schedule part, but I need help
with writing exporting data script.
select * from tb1 to file info.csv
Thanks in advance,
SarahCreate a data transformation services package and schedule it. See DTS in BO
L
for more info.
AMB
"SG" wrote:

> Hi,
> I'm new to SQL. Recently I need to setup a schedule job to export data int
o
> csv file every month. I know I can do the schedule part, but I need help
> with writing exporting data script.
> select * from tb1 to file info.csv
> Thanks in advance,
> Sarah
>
>|||You can do this a couple of ways. The easiest would be to create a DTS
package with the source as your SQL database & the destination as the file.
You can make it a job & schedule it to run at defined intervals. An
alternative is to use oSQL & define the destination. Again you can schedule
it to run it whenever you want. Details about DTS as well as OSQL.exe can be
found in SQL Server Books Online.
Anith|||Thanks Anith and Alejandro for your quick response. I didn't explain my
question properly. I like to gather data from a few tables and export it.
Would I be able to do this as well? I know how to export one table using
DTS, but not from several tables.
Thanks,
sarah
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:4BCB9529-FD4F-4DFC-98F9-A09A8994EC6F@.microsoft.com...
> Create a data transformation services package and schedule it. See DTS in
> BOL
> for more info.
>
> AMB
> "SG" wrote:
>|||>> I know how to export one table using DTS, but not from several tables.
You can do the same with the resultset of a query which involves multiple
tables.
Anith|||Hi Anith,
I'm new to SQL. I don't quite sure how to do the same with resultset of a
query. Would some sql scirpt be involved?
Would you mind giving me more information about it?
Appreciate it.
Sarah
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:evi1NBcSFHA.3516@.TK2MSFTNGP10.phx.gbl...
> You can do the same with the resultset of a query which involves multiple
> tables.
> --
> Anith
>|||>> Would you mind giving me more information about it?
Sure, but did you get a chance to check SQL Server Books Online regarding
how to use DTS? If you haven't, please do.
DTS Import/Export wizard is a simple, easy-to-use interface which among
other functionalities, allows you to transform & transfer data to external
destinations in pre-defined formats like CSV, tab-delimited etc. There are
provisions in DTS wizard to write SQL queries & specify the output formats
which can be used in your case. Also, for commonly found issues, there is an
excellent website: www.sqldts.com
For reference:
http://msdn.microsoft.com/library/e...ls_wiz_8vsj.asp
http://www.sqldts.com/default.aspx?276
Anith

Schedule job, SP and DTS transaction problem

Hi all,
I have a DTS to export a CSV and a SP to call the DTS and schedule the SP to
execute.
My problem is that the DTS have been blocked by the schedule job.
Here are those program:
DTS sql:
exec sp_GetResultForExportCSV
SP:
CREATE PROCEDURE sp_DayEnd
AS
-- insert ...
-- update ...
-- call DTS
EXEC('master.dbo.xp_cmdShell ''DTSRun /~Z0xE6FEF651097....'',
no_output')
Return
Schedule sql:
Begin tran
exec sp_DayEnd
commit tran
If i remove the begin tran, the schedule run successfully. But is it not
possible to have begin tran?
And I have tried the transaction setting of the DTS properties(transaction
on/off, read uncommitted, etc...), all doesn't work with 'Begin tran'. The
DTS always block when running the DTS sql (exec sp_GetResultForExportCSV). I
think the SP and xp_cmdShell DTSRun is not executed within the same
transaction, is it the limitation?
Thanks in advance!
MartinHi,
why you do the Begin and Commit in Schedule sql? not in sp_DayEnd.
I guess since the insert and update have not been commited yet and they
are requiring exclusive lock on the reords, in the same SP, you are trying t
o
export data from the tables that are being locked. That may be the reason
why the master..xp_cmdshell is waiting for the lock to be relase which
actually will never happen. I suggest you to put the following in sp_dayEnd
Begin Tran
Insert...
Update...
Commit Tran
Exec 'master..xp_cmdshell xxxxxxxxxxxxxxxxxxxxxxxx'
HTH
Ed
"Atenza" wrote:

> Hi all,
> I have a DTS to export a CSV and a SP to call the DTS and schedule the SP
to
> execute.
> My problem is that the DTS have been blocked by the schedule job.
> Here are those program:
> DTS sql:
> exec sp_GetResultForExportCSV
>
> SP:
> CREATE PROCEDURE sp_DayEnd
> AS
> -- insert ...
> -- update ...
> -- call DTS
> EXEC('master.dbo.xp_cmdShell ''DTSRun /~Z0xE6FEF651097....'',
> no_output')
> Return
>
> Schedule sql:
> Begin tran
> exec sp_DayEnd
> commit tran
>
> If i remove the begin tran, the schedule run successfully. But is it not
> possible to have begin tran?
> And I have tried the transaction setting of the DTS properties(transaction
> on/off, read uncommitted, etc...), all doesn't work with 'Begin tran'. The
> DTS always block when running the DTS sql (exec sp_GetResultForExportCSV).
I
> think the SP and xp_cmdShell DTSRun is not executed within the same
> transaction, is it the limitation?
> Thanks in advance!
> Martin
>
>