Get the Query result in *.csv file with '|' Symbol as my delimiter

I found the solution....

We can use the following SQL Command for this...

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT object_name(object_id) As TbLName,name as ColName FROM SandBox.sys.columns  ORDER BY object_name(object_id), column_id" queryout C:\Data\Objects.txt -t"|" -c -T ' 

or else we will do the following Management Studio Settings...

If your using Microsoft Sql Server management studio this is Configurable.
(actually almost the exact same in query analyzer)

Tool--> Options -->Query Results --> Sql Server --> Results to Text.

Change output format from Tab Delimited to Custom Delimited.
Put a Pipe in the Custom Delimiter Box.

Go back to your query select Query --> Results to File. Execute file, Give it a Disk path and a name.

The file will be your name with a .rpt Extension, you can rename it to have a .txt extension.

You obviously have to set the results to text value back to your previous setting if you don't want this permanently.

Works good for a one time once in a while thing. but if you must generate this file frequently I would look at creating a SSIS Package. or as suggested above.

Regards,
GVPrabu

just copy and paste this code in your sql editor
you will find a *.csv file in you C: drive

DECLARE @Ord_ID varchar(50)
DECLARE  @Return varchar(50)
DECLARE @Ord_Amt INT
DECLARE @cmd Nvarchar(2000)
DECLARE @Order CURSOR
SET @Order = CURSOR FOR
SELECT Ord_ID, Ord_Amt FROM OrderDtls
OPEN @Order
FETCH NEXT
FROM @Order INTO @Ord_ID,@Ord_Amt
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = 'echo ' +  @Ord_ID+ ';' + convert(varchar(50), @Ord_Amt) + ' >>c:\myTable.csv'--set @cmd = 'echo ' + @Ord_ID+' >>c:\myTable.csv'
EXEC @Return = MASTER..xp_cmdshell @cmd 
FETCH NEXT
FROM  @Order INTO @Ord_ID,@Ord_Amt
END
CLOSE @Order
DEALLOCATE @Order