3

I am trying to export my sql query results which I would like to do it automatically when you execute the query. I have seen examples of using output to which I tried to use but this would give me a syntax error as the examples did Select * From dbo.anyTable Output to but for me where I have a where and inner join in my query I have a ) a after it which is what I think is the problem. I know it is possible to save it by right clicking the mouse and save results as, but this is not what I want ideally as I would like to automate the whole system I am trying to do.

For example I would like to try and do this with part of my query:

Select top 1 a.NAME, COUNT(*) OVER() AS totalRows
From (Select de.NAME From dbo.DEPLOYMENT_ENVIRONMENT as de
Inner join dbo.DEPLOYMENT_RESULT as dr 
on dr.ENVIRONMENT_ID = de.ENVIRONMENT_ID
Where @filecontent = de.ENVIRONMENT_ID) a
OUTPUT TO @myPath
FORMAT TEXT
QUOTE '"'
WITH COLUMN NAMES;

EDIT I have the following query but it doesn't produce a file or any error's what am I doing wrong?

Set @OutputFilePath = 'C:\DeploymentPipelines'
Set @ExportSQL = 'EXEC master.dbo.xp_cmdshell ''bcp 
"Select top 1 a.NAME, COUNT(*) OVER() AS totalRows
From (Select de.NAME From dbo.DEPLOYMENT_ENVIRONMENT as de
Inner join dbo.DEPLOYMENT_RESULT as dr 
on dr.ENVIRONMENT_ID = de.ENVIRONMENT_ID
Where @filecontent = de.ENVIRONMENT_ID) a"
queryout "' + @OutputFilePath + '\results4.csv" -T -c -t -S DEV-BUILD01\SQLSERVER'''
Exec(@ExportSQL)
Ciaran Donoghue
  • 800
  • 3
  • 22
  • 46
  • You may need to use [SQL Job](https://msdn.microsoft.com/en-us/library/ms190268.aspx). – Null Jan 10 '17 at 10:06
  • Furthermore it might be possible to write a small cmd script, which executes your sql script and writes the output to a file... but I agree: SQL Job might be better. – Tyron78 Jan 10 '17 at 10:35
  • That edit, does that select statement actually run on it's own? looks like a syntax error here with a double comma; @a.NAME, , COUNT(*) – Rich Benner Jan 10 '17 at 11:30
  • Try simplifying the example query (SELECT TOP 1 * From Table). I've also got a hunch that it needs to all be on one row, give that a go too. – Rich Benner Jan 10 '17 at 11:36
  • When you say didn't work, what exactly do you mean? try from a basic example (i'll update my answer) and see what happens. – Rich Benner Jan 10 '17 at 11:47
  • thanks I got it there now – Ciaran Donoghue Jan 10 '17 at 11:48
  • Excellent, in case others have problems and find this answer, what was the problem you were having and how did you fix it? – Rich Benner Jan 10 '17 at 11:49

1 Answers1

8

I've got some stored procs that do this for modifying .csv files, I found that using bcp was my best method for doing this in a TSQL script. The syntax is like this (taken from one of my live examples;

DECLARE @OutputFilePath nvarchar(max); SET @OutputFilePath = 'C:\Users\VirtualMachine1\Desktop\MasterFullOutput\Phase03'

DECLARE @ExportSQL nvarchar(max); SET @ExportSQL = N'EXEC master.dbo.xp_cmdshell ''bcp "SELECT TextData FROM DataConversionDB.dbo.DataScripts ORDER BY RowNumber" queryout "' + @OutputFilePath + '\OutputData.csv" -T -c -t -S WIN-SIITTJOB7OV'''

EXEC(@ExportSQL)

You will have to make sure that your SQL Server service login has access to the file path where you are outputting to (not your own permissions, the permissions of the SQL Server NT Service).

Here's some additional reading around the bcp command;

https://msdn.microsoft.com/en-GB/library/aa337544.aspx

https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/

Using bcp utility to export SQL queries to a text file

To narrow this down, give this example a go and see what happens;

DECLARE @ExportSQL nvarchar(max);

SET @ExportSQL = 'EXEC ..xp_cmdshell ''bcp "SELECT TOP 1 FROM sys.objects " queryout "C:\DeploymentPipelines\results4.csv" -T -c -t -S DEV-BUILD01\SQLSERVER'''

Exec(@ExportSQL)
Community
  • 1
  • 1
Rich Benner
  • 7,873
  • 9
  • 33
  • 39
  • Yeah that seems great but I don't need to export any data from the table so do I need to include @DataTableName – Ciaran Donoghue Jan 10 '17 at 10:44
  • Sure, I just have that in there because this was part of a cursor that imported a file for each table within a range. I'll update the answer to remove that part of the file name. Also, "WIN-SIITTJOB7OV" is my SQL Server name, you'll want to change this to match yours. – Rich Benner Jan 10 '17 at 10:50
  • Yeah, there's a few tricks with the permissions that can be a pain but once your SQL Server has the correct permissions this works well. – Rich Benner Jan 10 '17 at 10:52
  • I have add the information but it doesn't produce any error or file I will show you what I have done now – Ciaran Donoghue Jan 10 '17 at 11:21
  • 1
    GREAT Thanks - Just what i needed, and worked with my StoredProcedures as well :-) – JanBorup Jun 24 '21 at 11:23