• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

MS SQL export CSV & schedule It

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
ITsolutionWizard
Asked:
ITsolutionWizard
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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
 
Daniel JonesData Research AnalystCommented:
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
 
ITsolutionWizardAuthor Commented:
oh sorry. I just find out our sql does not have sql job agent feature.
what is our option?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Pawan KumarDatabase ExpertCommented:
You can enable it, Right click on the SQL Server Agent and start the service.
0
 
ITsolutionWizardAuthor Commented:
It does not have it. This is azure and I do not know why we do not have it
0
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Hi ITsolutionWizard,
Appreciate feedback on this?
0
 
Pawan KumarDatabase ExpertCommented:
Hi,

Is this done ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now