bcp实用程序写入远程服务器? [英] bcp Utility write to remote server?

问题描述

根据一些建议，我使用 bcp实用工具编写SQL server表到.cvs文件，以便以后可以将数据传输到具有相同结构的informix表.

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 .

我的SQLServer存储过程:

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-如何允许此过程写入remote server而不是本地服务器@@servername，因为不允许特定服务器访问我的sql服务器并不安全?

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-如何在特定条件下过滤查询: 说我想写这样的查询:

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-执行程序时，我得到的数据是这样的:

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

为什么第三列看起来像这样损坏，它是用arabic编写的varchar desc?

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

4-当我想用竖线|而不是逗号,来定界

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

我收到以下错误:

推荐答案

问题1:从远程服务器写入

我假设您要说的是从" ，而不是至" .以格式ServerName\InstanceName而不是@@servername来指定服务器名称，您将需要具有访问其他服务器的权限(因为您使用的是受信任的连接-T)

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)

问题2:如何向BCP语句添加参数

BCP是一个命令行实用程序，不是Transact-SQL的一部分.您无法添加参数.您可以格式化执行的命令行.您必须将参数@date和@empNum字符串与命令行的其余SQL字符串连接起来

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

问题3:输出中的字符错误

使用-w代替-c输出Unicode字符

Instead of -c use -w to output Unicode characters

问题4:管道不起作用

BCP的一个常见问题，只需引用这样的管道-t"|"以使记录分隔符

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

这篇关于bcp实用程序写入远程服务器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！