Export tab delimited query results into textfile (using SQLCMD or like that) ?

Question

Hello,

I want to export a query result to a single textfile. I did it with SQLCMD with success, but this time it's importat to export the results in a tab separated format.

How can I make that?
Until now I used the following command:

SQLCMD -S hostname -i test.sql -o output.txt -k

And the test.sql file looks like:

SELECT RowNumber,
EventClass,
Textdata,
.
.
.
FROM table

The tab is chr(9) in ASCII. In oracle I could make something like this:

<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:70.85pt 70.85pt 70.85pt 70.85pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} -->

"spool /home/oracle/audit.log

 

select USERID || chr(9) ||

USERHOST || chr(9) ||

ACTION# || chr(9) ||

SES$ACTIONS  || chr(9) ||

DBID || chr(9) ||

SQLBIND  || chr(9) ||

from SYS.AUD$

/

spool off

exit"

 

But it's not working with SQL Server 2005. Can you help me to modify this query to get a tab separated result?
Thank you.

<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:70.85pt 70.85pt 70.85pt 70.85pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1

Answer 1

You can use bcp utility
http://msdn.microsoft.com/en-us/library/ms162802.aspx

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

Thank you for the answer.

The problem is this bcp is difficult to me at first. I get an output, but it's looks very unstructured, with lots on unwanted information.

Like "set ansi_nulls on" and other stuff. I only want to extract the field contents.

And I didn't see the way to make tab separated the output.

Can you help me a little more? (and I appreciate any help form others too :) ).

Answer 3

I don't understand why you got info like "set ansi_nulls on" ,etc.
Here is a simple example:
Assuming you want get all data in character type, field terminator is tab '\t' (0x09) , and row terminator is '\n' (0x0d 0x0a)

If you use Trusted connect:
bcp pubs.dbo.employees out data.txt -S urSvr -c -T
or you use sql user logon  account:
bcp pubs.dbo.employees out data.txt -S urSvr -c -U usrName  -P urPwd

by default, column terminator is \t and row terminator is \n.
And you can use -t, -r option to specifiy column terminator and row terminator

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 4

Okay I get the problem now. The "set ansi_nulls on" line is the content of one of the fields, so its completely ok to see that.

The problem now is that the output wraps the content in multiple lines. The goal is to put every row from the table into a textfile, where one row is in one single line.
With SQLCMD this can be done with the -x1 option. Can you tell me how can I organize the output file to a one row-one line structure?

Thank you.

Answer 5

You can use -r option to specify the row terminator. If you don't ,  the row terminator is '\n' by default

Please check your output file again (some GUI tool like notepad will wrap a line if it is too long).

---

This posting is provided "AS IS" with no warranties, and confers no rights