1

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?

3

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;

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.

  • I ran the script after enabling the procedure and got this: Sqlcmd: Error: Error occurred while opening or operating on file C:\Users\scarsen\Documents\all_emails.csv (Reason: Access is denied). it's my own folder. – Stl Aug 1 at 14:46
  • yah, SQL Server won't have access to your own account since it runs under it's own account that doesn't have access to your folder. If SQL Server is running under an Active Directory account, you'll need to create a folder and give that account access to it. – Max Vernon Aug 1 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.