exporting a sql view to a csv file

Question

Whilst I can just about script to pull data via queries, I'm finding it very difficult to figure out how to export the results of a query to a .csv file and save in a network storage location (novice when it comes to the more technical side!). I've tried various things like the bcp command or using the export wizard or sqlcmd but I can't get anything to work! I also want to be able to automate the export i.e. run an agent job to create/run this export weekly. An ex-colleague had set this up but couldn't get it to work. When I run this:

USE [DataInsight]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

BEGIN

sqlcmd -S londata01\raisers -d DataInsight -E -s, -W -w 65535 -Q "SET NOCOUNT on; select * from dbo.All_Email_Addresses" -s "," -o "C:\Users\scarsen\Documents\all_emails.csv"

END

whereby londata01\raisers is the server name, datainsight the DB and all_email addresses a table. I get

Incorrect syntax near 'sqlcmd' when run it.

I turned on SQLCMDMode in SSMS and still get this error.

Tried this:

bcp "select * from datainsight.dbo.All_Email_Addresses" queryout 'C:\Users\scarsen.csv' –c –t , –S londata01\raisers –T

but get Incorrect syntax near 'queryout'.

Because I want to automate for a weekly output, I can use an agent job so need to be able to create export within SSMS (I think)

I also tried something that I can't remember using master..xp_cmdshell but a message came up saying I didn't have security settings enabled (or similar)

I also tried saving an export via the wizard to an SSIS but get a message saying storing or modifying packages requires runtime and db to the the same version.

In a nutshell, tried lots of things with my limited knowledge but wondered if anyone might be able to help?

Answer 1

bcp is a command-line command utility - you can't call it as a T-SQL command. However, if you enable xp_cmdshell, you can run it via the command-line using this code inside a SQL Server Agent Job (or query window in SQL Server Management Studio):

DECLARE @cmd varchar(1000);
SET @cmd = 'sqlcmd -S londata01\raisers -d DataInsight -E -s, -W -w 65535 -Q "SET NOCOUNT on; select * from dbo.All_Email_Addresses" -s "," -o "C:\Users\scarsen\Documents\all_emails.csv"';
EXEC sys.xp_cmdshell @cmd;

In the example above, we're exporting data into the C:\Users\scarsen\Documents folder. In order for this to work from inside SQL Server, the SQL Server Service Account must have access to this folder.

You can enable xp_cmdshell at the server using these T-SQL commands, which you'd run via a query window in SQL Server Management Studio (SSMS):

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

You'd only need to enable it once per SQL Server. In case you want to reverse the above change, simply run this:

EXEC sys.sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

There really is no need to reverse the show advanced options configuration setting. It doesn't have any bearing at all on anything other than letting you configure things like xp_cmdshell.