Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS SQL export CSV & schedule It

Posted on 2016-10-25
9
Medium Priority
?
151 Views
Last Modified: 2016-11-15
I just have a simple query that I want to run 5:00 pm PST Daily and export the data in csv format.
Can you show me the easiest way using SQL Server agent?
0
Comment
Question by:ITsolutionWizard
9 Comments
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41859752
You can create a SQL Job with the below code..

DECLARE  @Command NVARCHAR(4000)=''
	
SET @Command = 'bcp " ' + 'SELECT CreditReport FROM [PAWAN].[ABCInvestment].data.CreditReport WHERE 1 = 1 ' + '"' + ' queryout "E:\Pawan' +  '.csv' + '"' + ' -T -N'
 
PRINT @Command
 
EXEC xp_cmdshell   @Command

Open in new window

0
 
LVL 4

Accepted Solution

by:
Daniel Jones earned 2000 total points
ID: 41859760
You can set up an SQL server agent job of type cmdexec to execute the bcp utility.
First try the bcp command by itself and remember to specify the -c argument for char instead of the default bin.


Wrap this command into a scheduled job
EXEC  master..xp_cmdshell 'SQLCMD -S server\SQLSERVERDEV2005 -E -Q "SELECT TOP 10 * FROM pubs.dbo.authors" -b -o c:\myoutput.txt', no_output
0
 
LVL 1

Author Comment

by:ITsolutionWizard
ID: 41859761
oh sorry. I just find out our sql does not have sql job agent feature.
what is our option?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41859773
You can enable it, Right click on the SQL Server Agent and start the service.
0
 
LVL 1

Author Comment

by:ITsolutionWizard
ID: 41859776
It does not have it. This is azure and I do not know why we do not have it
0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41859779
try this MSDN link... It has steps you need...

https://blogs.msdn.microsoft.com/ms-tech_learning_with_prasant/2014/07/09/scheduling-job-on-sql-azure/

or you may need to create a windows service..

Hope it helps !!
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 41859923
It does not have it. This is azure and I do not know why we do not have it
Can't you do it in the opposite direction? In the target server (where you want the .csv file to be stored) schedule a task to export the data from Azure.
0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41871741
Hi ITsolutionWizard,
Appreciate feedback on this?
0
 
LVL 38

Expert Comment

by:Pawan Kumar
ID: 41884414
Hi,

Is this done ?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question