I need to be able to export the data from a table to a text file using sqlcmd. I need to be able to use Ctrl-A as a column separator. Is this doable, and if so, how? I am assuming the this is a value used by the -s option.
I need to use a batch file to run the sqlcmd utility.
Even that may not be possible directly using sqlcmd's features. I recall having tried to use a multi-character column separator and failing miserably because sqlcmd wanted a single 8-bit character as the separator.
KH, the ASCII code is 1. i.e., CHAR(1). But I have not figured out how to provide other than a single printable character as the value for parameter -s in sqlcmd. Perhaps you could - if you were to compose dynamic SQL and use SQLCMD from SSMS or call xp_cmdshell. But from the windows command line or from a windows bat file, how one would do that is not clear to me.
one kluge... Might be to bcp out a statement (or view) where each row is a single value that is a concatenation all the columns with char(1) embedded between them: col1+char(1)+col2+char(1)[+...] Of course you'll need to convert all non-character columns to varchar as well as coalesce the nulls to empty strings.
Just curious, why do you need to use this particular separator?