SQL export to text file and save...

[Webkins]

I have a SQL 2000 database with 1 table and 1 column. The column name is data. Data type is varchar(3000). Example of the data within is below:

^PR7^FS
^MNY^FS
^FO261,41^GB0,309,161,W^FS
^FO261,41^XGAAAAAAAA,1,1^FS
^FT616,10^A0R,42,50^FDSC-403^FS

With this stored procedure select statement I can select all the information within the table into 1 long string format. This works fine.
When I try to write and save it to a text file all the information is there EXCEPT for the "^" carot symbols. Any ideas why the "^" carots are not appearing into the text file ?

Correct text file example: ^PR7^FS^MNY^FS^FO261,41^GB0,309,161,W^FS^FO261,41^XGAAAAAAAA,1,1^FS^FT616,10^A0R,42,50^FDSC-403^FS
Incorrect text file example, what I am getting: PR7FSMNYFSFO261,41GB0,309,161,WFSFO261,41XGAAAAAAAA,1,1FSFT616,10A0R,42,50FDSC-403FS

If I put the SQL command Print @file_to_push as the last line the stored procedure the string appears correct in SQL.

SET @file_to_push = '' SELECT @file_to_push = @file_to_push + data FROM partspec.dbo.barcode
SET @batch_ftp = '\\usbblms001\common\Labels\barcode.txt'
SET @put = 'echo ' + @file_to_push + '>' + @batch_ftp
EXEC master.dbo.xp_cmdshell @put, no_output
Print @file_to_push

Any ideas, please help, I am very frustrated. Thanks so much.

 

[markros]

Can you do BCP instead?

PluralSight Learning Library