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?
Best Answer
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):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):
You'd only need to enable it once per SQL Server. In case you want to reverse the above change, simply run this:
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 likexp_cmdshell
.