I often use BCP as a quick/easy alternative to SQL Server Integration Services Packages- creating a view or stored procedure to return the desired records then a SQL job to execute BCP using an Operating system (CmdExec) step type.
This works pretty well then a second step can be used to e-mail the BCP output using sp_send_dbmail or a similar alternative. Now I want to archive the BCP output by appending a prefix / suffix to the BCP output filename.
I already use batch files for various tasks and so know that I can pass %date:~6,4%%date:~3,2%%date:~0,2% to the command line to get the date in format YYYYMMDD. So let’s try adding that to the BCP command line:
bcp "exec ebms_sql.dbo.sp_Missing_Tax_Schemes" QUERYOUT "\fs01ReportsMissing_Tax_Scheme_"%date:~6,4%%date:~3,2%%date:~0,2%".csv" -T -S "localhost" -t "," –c
Let’s try it:
Error: SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file. Process Exit Code 1. The step failed.
OK- so it must be a permissions error? Nope- permissions are fine. Let’s trying running it from the a command prompt:
Hmmm- that works?! Let’s try snooping what’s going on when the SQL job runs with Sysinternals Process Monitor:
Right- the environment variables aren’t being played out- so the filename containing special characters is invalid. How can we force the command prompt to interpret differently? Let’s try cmd /c on the off chance:
cmd /c bcp "exec ebms_sql.dbo.sp_Missing_Tax_Schemes" QUERYOUT "\fs01ReportsMissing_Tax_Scheme_"%date:~6,4%%date:~3,2%%date:~0,2%".csv" -T -S "localhost" -t "," –c
Success- it works!
Now my only remaining frustration is that BCP won’t output a row with column headers (I do a weird union thing to get around this for the time being, but will look further into this at a later date).