export select query result in to pipe delimiter Textfile

Hello Friends,

I have a table that I need to export to a pipe delimited .txt file.please follow the below step to do that things.

Steps 1: [http://amitpatriwala.wordpress.com/2010/04/02/save-select-query-result-into-textfile/]

Enabled the xp_cmdshell

By default disabled in SQL 2005 for security reasons.

To enable it, use the Surface Area Configuration tool or sp_configure

To enable xp_cmdshell using sp_configure, use below query :

EXEC master.dbo.sp_configure ‘show advanced options’, 1

RECONFIGURE

EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1

RECONFIGURE

Steps 2:

EXEC master..xp_cmdshell ‘bcp “select * from Adbrite..Adtype” queryout “c:\text.txt” -U sa -P sa123-c -t^| -x’

Database Name : Adbrite

Table Name : Adtype

Sql User Name : sa

Sql Password : sa123

delimiter option : t^|

thnx

http://www.ibusiness-management.com/

Save Select query result into Textfile

Here I am explaining how to export query results into text file. Please follow below steps to archive this thing.

Step 1:

Enabled the xp_cmdshell

By default disabled in SQL 2005 for security reasons.

To enable it, use the Surface Area Configuration tool or sp_configure

To enable xp_cmdshell using sp_configure, use below query :

EXEC master.dbo.sp_configure ‘show advanced options’, 1

RECONFIGURE

EXEC master.dbo.sp_configure ‘xp_cmdshell’, 1

RECONFIGURE

Step2:

To save your SELECT query results to a text file, use below query :

EXEC master..xp_cmdshell ‘bcp “select * from adtype.dbo.adbrite” queryout “c:\text.txt” -c -T -x’

or

EXEC master..xp_cmdshell ‘bcp “select * from Adbrite..Adtype” queryout “c:\text.txt” -U sa -P sa123-c -T -x’

Database Name : Adbrite

Table Name : Adtype

Database User Name : sa

Database Password : sa123

Thnx

http://www.ibusiness-management.com/