Hi,
I'm trying to setup some code to export data to a text file. I want to run this daily and ideally like it as a procedure in the database so have looked at BCP. Below is where I've got to. When I run this from a query is says it executed successfully, but
when I check my output folder the test.txt is not there.
Can anyone see where I'm going wrong?
Thanks,
Bassmann
declare @logtext
char(200)
declare @cmd
char(200)
set @logtext
= '"SELECT ASXCode, [Open], High, Low, [Close], Volume FROM tbl_ASXStats WHERE YearMonthDay_Number = LastUpdate"'
SET @cmd
= 'bcp '
+ @logtext +
' queryout "M:\Data\ASX\Export\test.txt" -F 0 -t '','' -r ''\n'''
EXEC
master..XP_CMDSHELL
@cmd
Are you checking the folder on the server or on your client? Keep in mind that this file would be on the server.
Also, keep in mind that xp_cmdshell will always report that it completed. It has no idea on the outcome of any tasks you did in the shell.
I'd start by running:
exec master.dbo.xp_cmdshell 'dir M:\Data\ASX\Export\'
Find out if the simple DIR command works for that directory. If it does GREAT!
Now take a look at the command in DOS. Get it from here:
declare @logtext char(200)
declare @cmd char(200)
SET @logtext = '"SELECT ASXCode, [Open], High, Low, [Close], Volume FROM tbl_ASXStats WHERE YearMonthDay_Number = LastUpdate"'
SET @cmd = 'bcp ' + @logtext + ' queryout "M:\Data\ASX\Export\test.txt" -F 0 -t '','' -r ''\n'''
SELECT @CMD
How does the command look?
Other recent topics
