SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sqlcmd using Ctrl-A
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gmorey
Starting Member

2 Posts

Posted - 10/02/2013 :  09:56:11  Show Profile  Reply with Quote
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.

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 10/02/2013 :  10:52:36  Show Profile  Reply with Quote
I don't think that Ctrl-A will result in any character (or code) in the resulting file - so there would be no separator. Unless you mean you want to use these 6 characters as the separator: "Ctrl-A"?

Be One with the Optimizer
TG
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 10/02/2013 :  10:58:18  Show Profile  Reply with Quote
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.
Go to Top of Page

gmorey
Starting Member

2 Posts

Posted - 10/02/2013 :  11:05:59  Show Profile  Reply with Quote
I actually need to be able to do Control + A as a column seperator (the control key plus A). Not the literal "Ctrl-A".
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 10/03/2013 :  04:36:51  Show Profile  Reply with Quote
quote:
Originally posted by gmorey

I actually need to be able to do Control + A as a column seperator (the control key plus A). Not the literal "Ctrl-A".



what is the ASCCI code for that ?


KH
Time is always against us

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 10/03/2013 :  08:42:16  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 10/03/2013 :  09:58:32  Show Profile  Reply with Quote
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?

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000