Saving SP Query results to a text file

[stapet]

Hello, I am having trouble with my stored procedure. Any help would be appreciated.

I am passing parameters to a SP, inserting them into a temp table and then selecting all from the temp table to save to a text file.

Here is my code:
SET @UploadDir = '\\MyServer\testdata\'
SET @UploadFileName = 'vouchers.txt'

SET @SQL = ' bcp "' + 'Select * From #tmpCashAdvances"' + 'queryout ' + @UploadDir + @UploadFileName + ' -c -SDAISY -Usa -Psql'
EXEC master..xp_cmdshell @SQL

When I try to execute this SP from the Query Analzer I receive the following error message:

Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL

 

[vadimg]

well, your bcp statement does not have closing quotes "

also, you should probably use:

select @SQL (before executing it) while you're troubleshooting your procedure -- that way you'll easily pick up mistakes.

 

[stapet]

Thanks for taking a look. I changed the SP to just output the variables I'm passing, instead of saving them to a temp table and then outputting. I appreciate the help.