I have a stored procedure that gets a SQL query, file path (to output) and file name as parameter which I'm using to build a bcp command and finally executing the same e.g
SET @sql = 'bcp "'
+ '" queryout "
+ '" -c'
+ ' -t,'
+ ' -T'
EXECUTE MASTER..xp_cmdshell @sql, NO_OUTPUT
Upon executing the stored proc it doesn't produce the file specified in the parameter.
I have verified the parameters and they are correct. I did a print to see what's in @sql and say I get the following -
'bcp "SELECT [col1],[col2],[col3] FROM [##results]" queryout "E:\test\test.csv" -c -t, -T'
On executing the command standalone (i.e. not from a stored proc) from SSMS produces the output file correctly e.g.
EXEC MASTER..xp_cmdshell 'bcp "SELECT [col1],[col2],[col3] FROM [##results]" queryout "E:\test\test.csv" -c -t, -T', NO_OUTPUT
Are there any limitations on running BCP utility from a stored proc - am I missing something very obvious?