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
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.
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?