Using BCP in a SQL query

Question

I have a stored procedure that updates several columns in a table and then selects out those columns so I can save them out for a report I run. Currently I am doing the exporting and saving manually though by setting column size in 'Query Options' to 333, turning off all the other options, putting the results to text and then saving the file out with the name and format. I know that there must be a way to automate this though the export and save process though.

I looked into using a job with Vb script, but this is a simple report that is run on demand and not on a schedule; so a job seemed to be overkill. The other option I found was BCP, which looks like what I need. I only know of this as a CMD tool though and can't quite figure out how to leverage this within a SQL query. In case it is needed here is an example of what my stored procedure is doing. Any help is appreciated and thank you in advance. I am using SQL 2008 R2 btw.

 UPDATE Table#1 SET Column1 = '' WHERE Column1 = 'XX'

UPDATE Table#1 SET Column2 = '' WHERE Column2 = '000000'



 SELECT   Column1 + 
          Column2 + 
          Column3  
 FROM Table#1 Where Column4 = 'T'

Please provide some more information of your table design. Do you just want to export some data on demand? If so, is this a flat table? — Shnugo, Jul 13 '15 at 21:11
The table is a flat table that is comprised of data that is taken from several other normalized tables(this is done by a different stored proc I have written). What I do with this proc is then update a few of the columns so the people who get this report can understand it and then export out the data that they need. I hope this helps. — Jared H, Jul 13 '15 at 21:26
Did you think of ODC via Excel (if this is appropriate for you)? You can define SQL-Statements there and the result will be taken into an Excel-Sheet. If needed you could store this as CSV too. — Shnugo, Jul 13 '15 at 21:37
I had thought of that, but the people that use this need the results in a text format. — Jared H, Jul 13 '15 at 21:45
One problem could be, that you need certail rights with BCP. I myself use Excel to retrieve list data and use a tiny VBA-programm to write this "as csv" on closing automatically. — Shnugo, Jul 13 '15 at 21:49
The permissions shouldn't be a problem my main issue is that I just can't decipher how to call to BCP from within SQL server. In CMD it's as simple as BCP, your select statement and then queryout to your file. Within SQL seems to require more than that though and everything I have found seems to be written just for using it through command line even though I had thought is was SQL feature. — Jared H, Jul 13 '15 at 22:16
Well, I do not understand... BCP is something you call from a bat file or something similar. The only issue is to know the command and its paramters and switches. Your query result will be copied into a file. From inside SQL you are bound to the machine the Server is running on... What is your problem? — Shnugo, Jul 13 '15 at 22:29

score 3 · Accepted Answer · answered Jul 15 '15 at 17:58

BCP is a command line utility, however it can be access through SQL with the use of XP Command Shell so you will need xp_cmdshell enabled. The following is a parameterized example of how to call this inside of SQL

DECLARE @Server varchar(50) = 'ServerName' /* Source SQL Server */
DECLARE @FileName varchar(50) = ''
DECLARE @SourceProc varchar(50) = ''
DECLARE @sql varchar(8000)
DECLARE @RunDate varchar(10) = REPLACE(CONVERT(date, getdate()), '-','')
DECLARE @FilePath as varchar(255) = '\\UNCPathtoOutputFile\'
DECLARE @StartDate as Date = (select dateadd(month,datediff(month,0,GETDATE())-2,0))--'1/1/2013' /* Example of required Date as parameter */
DECLARE @EndDate as Date = (SELECT dateadd(month,datediff(month,0,GETDATE())-0,-1))--'2/1/2013' /* Example of required Date as parameter */
--  BCP Export
    SET @SourceProc = '[Schema].[StoredProcName]'
    SET @FileName = 'SomeOutputFile' + @RunDate + '.txt'
    SET @FilePath = @FilePath + @FileName
    SET @sql = 'bcp "EXEC [Database].' + @SourceProc + ' ''' + CONVERT(varchar(10),@StartDate, 101) + ''',''' + CONVERT(varchar(10),@EndDate, 101) + '''" QUERYOUT "' + @FilePath + '" -c -t"|" -r\n -S' + @Server + ' -T'

    --PRINT(@SQL)
    exec master.dbo.xp_cmdshell @sql

Awesome, thank you so much. I knew there had to be a way to access it, but couldn't find it anywhere. This was very helpful. — Jared H, Jul 16 '15 at 18:19

Using BCP in a SQL query

1 Answers1