bcp Utility write to remote server?

Question

According to some recommendations i use bcp Utility to write SQL server Table to .cvs file so i could later transfer the data to informix table with the same structure .

My SQLServer Stored Procedure :

ALTER PROCEDURE [dbo].[TestCVS]
AS
BEGIN
declare @sql nvarchar(4000)

select @sql = 'bcp "select * from ML..gmp4vacationbalance" queryout c:\ss\Tom.cvs -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
END

---

I have four questions concerning this procedure :

1- How to allow this procedure to write to remote server instead of local server @@servername,because it's not secure to allow specific server to access my sql server ?

2-How to allow to filter the query under specific condition : say i want to write query like this :

select * from ML..gmp4vacationbalance where balance_date = @date AND emp_num = @empNum

3-when i execute the procedure i get data like this:

Why the third column appear corrupted like this , it's varchar desc written in arabic ?

4-When i want to delimit by pipe | instead of comma , like this

select @sql = 'bcp "select * from ML..gmp4vacationbalance" queryout c:\ss\Tom.cvs -c -t| -T -S' + @@servername

I get the following error :

Answer 1

Question 1: Writing from a remote server

I assume you meant to say "from" not "to". Specify the server name in form ServerName\InstanceName instead of using @@servername, you will need to have permissions to access the other server (since you are using a trusted connection -T)

Question 2: How to add parameters to the BCP statement

BCP is a command line utility not part of Transact-SQL. You can't add parameters. You can format the command line executed. You'll have to make your parameters @date and @empNum strings concatenate them with the rest of the SQL string for the command line

Question 3: Wrong characters in output

Instead of -c use -w to output Unicode characters

Question 4: Pipe not working

A common problem with BCP, simply quote the pipe like this -t"|" to make that the record separator