- 浏览: 1639737 次
文章分类
最新评论
-
yyz420911451:
这题有错呀;__cplusplus,前面是俩下划线 俩下划线, ...
如何判断一段程序是由C编译程序还是由C++编译程序编译的 -
zhikook:
...
android surfaceflinger研究----SurfaceFlinger loop -
望月怀远:
精彩啊,写的太好了
Delphi 编写ActiveX控件(OCX控件)的知识和样例
SQL Server数据导入导出工具BCP详解及xp_cmdshell
SQL Server数据导入导出工具BCP详解及xp_cmdshell
开发者在线 Builder.com.cn 本文关键词: SQL Server 导入导出 bcp exec xp_cmdshell
BCP是SQL Server中负责导入导出数据的一个命令行工具,它是基于DB-Library的,并且能以并行的方式高效地导入导出大批量的数据。BCP可以将数据库的表或视图直接导出,也能通过SELECT FROM语句对表或视图进行过滤后导出。在导入导出数据时,可以使用默认值或是使用一个格式文件将文件中的数据导入到数据库或将数据库中的数据导出到文件中。
<wbr></wbr>
下面将详细讨论如何利用BCP导入导出数据。
<wbr></wbr>
1. BCP的主要参数介绍
<wbr></wbr>
BCP共有四个动作可以选择。
<wbr></wbr>
(1) 导入。
<wbr></wbr>
这个动作使用in命令完成,后面跟需要导入的文件名。
<wbr></wbr>
(2) 导出。
<wbr></wbr>
这个动作使用out命令完成,后面跟需要导出的文件名。
<wbr></wbr>
(3) 使用SQL语句导出。
<wbr></wbr>
这个动作使用queryout命令完成,它跟out类似,只是数据源不是表或视图名,而是SQL语句。
<wbr></wbr>
(4) 导出格式文件。
<wbr></wbr>
这个动作使用format命令完成,后而跟格式文件名。
<wbr></wbr>
下面介绍一些常用的选项:
<wbr></wbr>
-f format_file
<wbr></wbr>
format_file表示格式文件名。这个选项依赖于上述的动作,如果使用的是in或out,format_file表示已经存在的格式文件,如果使用的是format则表示是要生成的格式文件。
<wbr></wbr>
-x
<wbr></wbr>
这个选项要和-f format_file配合使用,以便生成xml格式的格式文件。
<wbr></wbr>
-F first_row
<wbr></wbr>
指定从被导出表的哪一行导出,或从被导入文件的哪一行导入。
<wbr></wbr>
-L last_row
<wbr></wbr>
指定被导出表要导到哪一行结束,或从被导入文件导数据时,导到哪一行结束。
<wbr></wbr>
-c
<wbr></wbr>
使用char类型做为存储类型,没有前缀且以"t"做为字段分割符,以"n"做为行分割符。
<wbr></wbr>
-w
<wbr></wbr>
和-c类似,只是当使用Unicode字符集拷贝数据时使用,且以nchar做为存储类型。
<wbr></wbr>
-t field_term
<wbr></wbr>
指定字符分割符,默认是"t"。
<wbr></wbr>
-r row_term
<wbr></wbr>
指定行分割符,默认是"n"。
<wbr></wbr>
-S server_name[ instance_name]
<wbr></wbr>
指定要连接的SQL Server服务器的实例,如果未指定此选项,BCP连接本机的SQL Server默认实例。如果要连接某台机器上的默认实例,只需要指定机器名即可。
<wbr></wbr>
-U login_id
<wbr></wbr>
指定连接SQL Sever的用户名。
<wbr></wbr>
-P password
<wbr></wbr>
指定连接SQL Server的用户名密码。
<wbr></wbr>
-T
<wbr></wbr>
指定BCP使用信任连接登录SQL Server。如果未指定-T,必须指定-U和-P。
<wbr></wbr>
-k
<wbr></wbr>
指定空列使用null值插入,而不是这列的默认值。
<wbr></wbr>
2. 如何使用BCP导出数据
<wbr></wbr>
(1) 使用BCP导出整个表或视图。
<wbr></wbr>
BCP AdventureWorks.sales.currency out c:currency1.txt -c -U"sa" -P"password" --使用密码连接
<wbr></wbr>
或
<wbr></wbr>
BCP AdventureWorks.sales.currency out c:currency1.txt -c -T --使用信任连接
<wbr></wbr>
下面是上述命令执行后的输出结果
<wbr></wbr>
Starting copy...
<wbr></wbr>
105 rows copied.
<wbr></wbr>
Network packet size (bytes): 4096
<wbr></wbr>
Clock Time (ms.) Total : 10 Average : (10500.00 rows per sec.)
<wbr></wbr>
下面是currency1.txt的部分内容
<wbr></wbr>
AED Emirati Dirham 1998-06-01 00:00:00.000
<wbr></wbr>
AFA Afghani 1998-06-01 00:00:00.000
<wbr></wbr>
... ... ...
<wbr></wbr>
... ... ...
<wbr></wbr>
ZWD Zimbabwe Dollar 1998-06-01 00:00:00.000
<wbr></wbr>
在使用密码登录时需要将-U后的用户名和-P后的密码加上双引号。
<wbr></wbr>
注:BCP除了可以在控制台执行外,还可以通过调用SQL Server的一个系统存储过程xp_cmdshell以SQL语句的方式运行BCP。如上述第一条命令可改写为
<wbr></wbr>
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency out c:currency1.txt -c -U"sa" -P"password"'
执行xp_cmdshell后,返回信息以表的形式输出。为了可以方便地在SQL中执行BCP,下面的命令都使用xp_cmdshell执行BCP命令。
<wbr></wbr>
(2) 对要导出的表进行过滤。
<wbr></wbr>
BCP不仅可以接受表名或视图名做为参数,也可以接受SQL做为参数。通过SQL语句可以对要导出的表进行过滤,然后导出过滤后的记录。
<wbr></wbr>
EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:currency2.txt -c -U"sa" -P"password"'<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>(xp表示本存储过程是扩展的存储过程,extend procedure,cmdshell是command shell的意思,也就是直接敲命令执行。)</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr></wbr>
BCP还可以通过简单地设置选项对导出的行进行限制。
<wbr></wbr>
EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:currency2.txt -F 10 -L 13 -c -U"sa" -P"password"'
<wbr></wbr>
这条命令使用了两个参数-F 10和-L 13,表示从SELECT TOP 20 * FROM AdventureWorks.sales.currency所查出来的结果中取第10条到13条记录进行导出。
<wbr></wbr>
3. 如何使用BCP导出格式文件
<wbr></wbr>
BCP不仅可以根据表、视图导入导出数据,还可以配合格式文件对导入导出数据进行限制。格式文件以纯文本文件形式存在,分为一般格式和xml格式。用户可以手工编写格式文件,也可以通过BCP命令根据表、视图自动生成格式文件。
<wbr></wbr>
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:currency_format1.fmt -c -T'
<wbr></wbr>
上述命令将currency表的结构生成了一个格式文件currency_format1.fmt,下面是这个格式文件的内容。
<wbr></wbr>
9.0
<wbr></wbr>
3
<wbr></wbr>
1 SQLCHAR 0 6 "t" 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS
<wbr></wbr>
2 SQLCHAR 0 100 "t" 2 Name SQL_Latin1_General_CP1_CI_AS
<wbr></wbr>
3 SQLCHAR 0 24 "rn" 3 ModifiedDate
<wbr></wbr>
<wbr></wbr>
这个格式文件记录了这个表的字段(共3个字段)类型、长度、字符和行分割符和字段名等信息。
<wbr></wbr>
BCP还可以通过-x选项生成xml格式的格式文件。
<wbr></wbr>
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:currency_format2.fmt -x -c -T'
xml格式文件所描述的内容和普通格式文件所描述的内容完全一样,只是格式不同。
<wbr></wbr>
4. 如何使用BCP导入数据
<wbr></wbr>
BCP可以通过in命令将上面所导出的currency1.txt和currency2.txt再重新导入到数据库中,由于currency有主键,因此我们将复制一个和currency的结构完全一样的表。
<wbr></wbr>
SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM AdventureWorks.sales.currency
将数据导入到currency1表中
<wbr></wbr>
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -c -T'
导入数据也同样可以使用-F和-L选项来选择导入数据的记录行。
<wbr></wbr>
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -c -F 10 -L 13 -T'
在导入数据时可以根据已经存在的格式文件将满足条件的记录导入到数据库中,不满足则不导入。如上述的格式文件中的第三个字段的字符长度是24,如果某个文本文件中的相应字段的长度超过24,则这条记录将不被导入到数据库中,其它满足条件的记录正常导入。
<wbr></wbr>
使用普通的格式文件
<wbr></wbr>
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -f c:currency_format1.fmt -T'
使用xml格式的格式文件
<wbr></wbr>
EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -x -f c:currency_format2.fmt -T'
<wbr></wbr>
总结
<wbr></wbr>
BCP命令是SQL Server提供的一个快捷的数据导入导出工具。使用它不需要启动任何图形管理工具就能以高效的方式导入导出数据。当然,它也可以通过xp_cmdshell在SQL语句中执行,通过这种方式可以将其放到客户端程序中(如delphi、c#等)运行,这也是使客户端程序具有数据导入导出功能的方法之一。
<wbr></wbr>
<wbr></wbr>
xp_cmdshell<wbr><wbr></wbr></wbr>
<wbr>以操作系统命令行解释器的方式执行给定的命令字符串,并以文本行方式返回任何输出。授予非管理用户执行<wbr><wbr>xp_cmdshell<wbr><wbr>的权限。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>说明<wbr><wbr><wbr><wbr>在<wbr><wbr>Microsoft&reg;<wbr><wbr>Windows&reg;<wbr><wbr>95<wbr><wbr>或<wbr><wbr>Microsoft<wbr><wbr>Windows<wbr><wbr>98<wbr><wbr>操作系统中执行<wbr><wbr>xp_cmdshell<wbr><wbr>时,将不把<wbr><wbr>xp_cmdshell<wbr><wbr>的返回代码设置为唤醒调用的可执行文件的进程退出代码。返回代码始终为<wbr><wbr>0。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>语法<wbr><wbr></wbr></wbr></wbr>
<wbr>xp_cmdshell<wbr><wbr>{'command_string'}<wbr><wbr>[,<wbr><wbr>no_output]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>参数<wbr><wbr></wbr></wbr></wbr>
<wbr>'command_string'<wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>是在操作系统命令行解释器上执行的命令字符串。command_string<wbr><wbr>的数据类型为<wbr><wbr>varchar(255)<wbr><wbr>或<wbr><wbr>nvarchar(4000),没有默认值。command_string<wbr><wbr>不能包含一对以上的双引号。如果由<wbr><wbr>command_string<wbr><wbr>引用的文件路径或程序名称中有空格,则需要使用一对引号。如果使用嵌入空格不方便,可考虑使用<wbr><wbr>FAT<wbr><wbr>8.3<wbr><wbr>文件名作为解决办法。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>no_output<wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>是可选参数,表示执行给定的<wbr><wbr>command_string,但不向客户端返回任何输出。<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>返回代码值<wbr><wbr></wbr></wbr></wbr>
<wbr>0(成功)或<wbr><wbr>1(失败)<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>结果集<wbr><wbr></wbr></wbr></wbr>
<wbr>执行下列<wbr><wbr>xp_cmdshell<wbr><wbr>语句将返回当前目录的目录列表。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>xp_cmdshell<wbr><wbr>'dir<wbr><wbr>*.exe'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>行以<wbr><wbr>nvarchar(255) <wbr><wbr>列的形式返回。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>执行下列<wbr><wbr>xp_cmdshell<wbr><wbr>语句将返回随后的结果集:<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>xp_cmdshell<wbr><wbr>'dir<wbr><wbr>*.exe',<wbr><wbr>NO_OUTPUT<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>下面是结果:<wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>The<wbr><wbr>command(s)<wbr><wbr>completed<wbr><wbr>successfully.<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>注释<wbr><wbr></wbr></wbr></wbr>
<wbr>xp_cmdshell<wbr><wbr>以同步方式操作。在命令行解释器命令执行完毕之前,不会返回控制。<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>当授予用户执行权限时,用户能在<wbr><wbr>Microsoft<wbr><wbr>Windows<wbr><wbr>NT&reg;<wbr><wbr>命令行解释器上执行运行<wbr><wbr>Microsoft<wbr><wbr>SQL<wbr><wbr>Server&#8482;<wbr><wbr>的帐户有权执行的任何操作系统命令。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>默认情况下,只有<wbr><wbr>sysadmin<wbr><wbr>固定服务器角色的成员才能执行此扩展存储过程。但是,也可以授予其他用户执行此存储过程的权限。<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>当作为<wbr><wbr>sysadmin<wbr><wbr>固定服务器角色成员的用户唤醒调用<wbr><wbr>xp_cmdshell<wbr><wbr>时,将在运行<wbr><wbr>SQL<wbr><wbr>Server<wbr><wbr>服务的安全上下文中执行<wbr><wbr>xp_cmdshell。当用户不是<wbr><wbr>sysadmin<wbr><wbr>组的成员时,xp_cmdshell<wbr><wbr>将模拟使用<wbr><wbr>xp_sqlagent_proxy_account<wbr><wbr>指定的<wbr><wbr>SQL<wbr><wbr>Server<wbr><wbr>代理程序的代理帐户。如果代理帐户不能用,则<wbr><wbr>xp_cmdshell<wbr><wbr>将失败。这只是针对于<wbr><wbr>Microsoft&reg;<wbr><wbr>Windows<wbr><wbr>NT&reg;<wbr><wbr>4.0<wbr><wbr>和<wbr><wbr>Windows<wbr><wbr>2000。在<wbr><wbr>Windows<wbr><wbr>9.x<wbr><wbr>上,没有模拟,且<wbr><wbr>xp_cmdshell<wbr><wbr>始终在启动<wbr><wbr>SQL<wbr><wbr>Server<wbr><wbr>的<wbr><wbr>Windows<wbr><wbr>9.x<wbr><wbr>用户的安全上下文下执行。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>说明<wbr><wbr><wbr><wbr>在早期版本中,获得<wbr><wbr>xp_cmdshell<wbr><wbr>执行权限的用户在<wbr><wbr>MSSQLServer<wbr><wbr>服务的用户帐户上下文中运行命令。可以通过配置选项配置<wbr><wbr>SQL<wbr><wbr>Server,以便对<wbr><wbr>SQL<wbr><wbr>Server<wbr><wbr>无<wbr><wbr>sa<wbr><wbr>访问权限的用户能够在<wbr><wbr>SQLExecutiveCmdExec<wbr><wbr>Windows<wbr><wbr>NT<wbr><wbr>帐户的上下文中运行<wbr><wbr>xp_cmdshell。在<wbr><wbr>SQL<wbr><wbr>Server<wbr><wbr>7.0<wbr><wbr>中,该帐户称为<wbr><wbr>SQLAgentCmdExec。现在,不是<wbr><wbr>sysadmin<wbr><wbr>固定服务器角色成员的用户将在该帐户上下文中运行命令,而无需再进行配置更改。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>权限<wbr><wbr></wbr></wbr></wbr>
<wbr>xp_deletemail<wbr><wbr>的执行权限默认授予<wbr><wbr>sysadmin<wbr><wbr>固定服务器角色的成员,但可以授予其他用户。<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>重要<wbr><wbr><wbr><wbr>如果为<wbr><wbr>MSSQLServer<wbr><wbr>服务选用的<wbr><wbr>Windows<wbr><wbr>NT<wbr><wbr>帐户不是本地管理员组的成员,则非<wbr><wbr>sysadmin<wbr><wbr>固定服务器角色成员的用户将无法执行<wbr><wbr>xp_cmdshell。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>示例<wbr><wbr></wbr></wbr></wbr>
<wbr>A.<wbr><wbr>返回可执行文件列表<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>下例显示执行目录命令的<wbr><wbr>xp_cmdshell<wbr><wbr>扩展存储过程。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>EXEC<wbr><wbr>master..xp_cmdshell<wbr><wbr>'dir <wbr><wbr>*.exe'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>B.<wbr><wbr>使用<wbr><wbr>Windows<wbr><wbr>NT<wbr><wbr>net<wbr><wbr>命令<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>下例显示<wbr><wbr>xp_cmdshell<wbr><wbr>在存储过程中的使用。下例先用<wbr><wbr>net<wbr><wbr>send<wbr><wbr>通知用户<wbr><wbr>SQL<wbr><wbr>Server<wbr><wbr>即将关闭,然后用<wbr><wbr>net<wbr><wbr>pause<wbr><wbr>暂停服务器,最后用<wbr><wbr>net<wbr><wbr>stop<wbr><wbr>关闭服务器。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>CREATE<wbr><wbr>PROC<wbr><wbr>shutdown10<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>AS<wbr><wbr></wbr></wbr></wbr>
<wbr>EXEC<wbr><wbr>xp_cmdshell<wbr><wbr>'net<wbr><wbr>send<wbr><wbr>/domain:SQL_USERS<wbr><wbr>''SQL<wbr><wbr>Server<wbr><wbr>shutting<wbr><wbr>down<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr>in<wbr><wbr>10<wbr><wbr>minutes.<wbr><wbr>No<wbr><wbr>more<wbr><wbr>connections<wbr><wbr>allowed.',<wbr><wbr>no_output<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>EXEC<wbr><wbr>xp_cmdshell<wbr><wbr>'net<wbr><wbr>pause<wbr><wbr>sqlserver'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>WAITFOR<wbr><wbr>DELAY<wbr><wbr>'00:05:00'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>EXEC<wbr><wbr>xp_cmdshell<wbr><wbr>'net<wbr><wbr>send<wbr><wbr>/domain:<wbr><wbr>SQL_USERS<wbr><wbr>''SQL<wbr><wbr>Server<wbr><wbr>shutting<wbr><wbr>down<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr>in<wbr><wbr>5<wbr><wbr>minutes.',<wbr><wbr>no_output<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>WAITFOR<wbr><wbr>DELAY<wbr><wbr>'00:04:00'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>EXEC<wbr><wbr>xp_cmdshell<wbr><wbr>'net<wbr><wbr>send<wbr><wbr>/domain:SQL_USERS<wbr><wbr>''SQL<wbr><wbr>Server<wbr><wbr>shutting<wbr><wbr>down<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr>in<wbr><wbr>1<wbr><wbr>minute.<wbr><wbr>Log<wbr><wbr>off<wbr><wbr>now.',<wbr><wbr>no_output<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>WAITFOR<wbr><wbr>DELAY<wbr><wbr>'00:01:00'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>EXEC<wbr><wbr>xp_cmdshell<wbr><wbr>'net<wbr><wbr>stop<wbr><wbr>sqlserver',<wbr><wbr>no_output<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>C.<wbr><wbr>不返回输出<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>下例使用<wbr><wbr>xp_cmdshell<wbr><wbr>执行命令字符串,且不向客户端返回输出。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>USE<wbr><wbr>master<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>EXEC <wbr><wbr>xp_cmdshell<wbr><wbr>'copy<wbr><wbr>c:\sqldumps\pubs.dmp<wbr><wbr>\\server2\backups\sqldumps',<wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr>NO_OUTPUT<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>D.<wbr><wbr>使用返回状态<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>在下例中,xp_cmdshell<wbr><wbr>扩展存储过程也给出了返回状态。返回代码值存储在变量<wbr><wbr>@result<wbr><wbr>中。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>DECLARE<wbr><wbr>@result<wbr><wbr>int<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>EXEC<wbr><wbr>@result<wbr><wbr>=<wbr><wbr>xp_cmdshell<wbr><wbr>'dir<wbr><wbr>*.exe'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>IF<wbr><wbr>(@result<wbr><wbr>=<wbr><wbr>0)<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr>PRINT<wbr><wbr>'Success'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>ELSE<wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr>PRINT<wbr><wbr>'Failure'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>E.<wbr><wbr>将变量内容写入文件<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>下例将当前目录内容写入当前服务器目录下名为<wbr><wbr>dir_out.txt<wbr><wbr>的文件中。<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>DECLARE<wbr><wbr>@cmd<wbr><wbr>sysname,<wbr><wbr>@var<wbr><wbr>sysname<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>SET<wbr><wbr>@var<wbr><wbr>=<wbr><wbr>'dir<wbr><wbr>/p'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>SET<wbr><wbr>@cmd<wbr><wbr>=<wbr><wbr>'echo<wbr><wbr>'<wbr><wbr>+<wbr><wbr>@var<wbr><wbr>+<wbr><wbr>'<wbr><wbr>><wbr><wbr>dir_out.txt'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>EXEC<wbr><wbr>master..xp_cmdshell<wbr><wbr>@cmd<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>例如:<wbr><wbr></wbr></wbr></wbr>
<wbr>1、写成存储过程,建立作业定时备份~~~<wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>--在sql中映射一下就可以了<wbr><wbr></wbr></wbr></wbr>
<wbr>exec<wbr><wbr>master..xp_cmdshell<wbr><wbr>'net<wbr><wbr>use<wbr><wbr>z:<wbr><wbr>\\yizhi\D$ <wbr><wbr>"密码"<wbr><wbr>/user:yizhi\administrator'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>--备份;with<wbr><wbr>init覆盖|noinit添加<wbr><wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>backup<wbr><wbr>database<wbr><wbr>库名<wbr><wbr>to<wbr><wbr>disk='E:\H_BACKUP.bak'<wbr><wbr>with<wbr><wbr>init<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>--COPY<wbr><wbr></wbr></wbr></wbr>
<wbr>exec<wbr><wbr>master..xp_cmdshell<wbr><wbr>'copy<wbr><wbr>E:\H_BACKUP.bak<wbr><wbr>z:'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>--删除(这句可以去掉)<wbr><wbr></wbr></wbr></wbr>
<wbr>--exec<wbr><wbr>master..xp_cmdshell<wbr><wbr>'del<wbr><wbr>E:\H_BACKUP.bak'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr></wbr></wbr></wbr>
<wbr>--完成后删除映射<wbr><wbr></wbr></wbr></wbr>
<wbr>exec<wbr><wbr>master..xp_cmdshell<wbr><wbr>'net<wbr><wbr>use<wbr><wbr>z:<wbr><wbr>/delete'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>2、操作DOC下的查询SQL语句,也就是osql的用法<wbr><wbr></wbr></wbr></wbr>
<wbr>用法:<wbr><wbr>osql<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-U<wbr><wbr>登录<wbr><wbr>ID]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-P<wbr><wbr>密码]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-S<wbr><wbr>服务器]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-H<wbr><wbr>主机名]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-E<wbr><wbr>可信连接]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-d<wbr><wbr>使用数据库名称]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-l<wbr><wbr>登录超时值]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-t<wbr><wbr>查询超时值]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-h<wbr><wbr>标题]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-s<wbr><wbr>列分隔符]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-w<wbr><wbr>列宽]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-a<wbr><wbr>数据包大小]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-e<wbr><wbr>回显输入]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-I<wbr><wbr>允许带引号的标识符]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-L<wbr><wbr>列出服务器]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-c<wbr><wbr>命令结束]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-D<wbr><wbr>ODBC<wbr><wbr>DSN<wbr><wbr>名称]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-q<wbr><wbr>"命令行查询"]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-Q<wbr><wbr>"命令行查询"<wbr><wbr>并退出]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-n<wbr><wbr>删除编号方式]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-m<wbr><wbr>错误级别]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-r<wbr><wbr>发送到<wbr><wbr>stderr<wbr><wbr>的消息]<wbr><wbr><wbr><wbr>[-V<wbr><wbr>严重级别]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-i<wbr><wbr>输入文件]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-o<wbr><wbr>输出文件]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-p<wbr><wbr>打印统计信息]<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>[-b<wbr><wbr>出错时中止批处理]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-X[1]<wbr><wbr>禁用命令,[退出的同时显示警告]]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-O<wbr><wbr>使用旧<wbr><wbr>ISQL<wbr><wbr>行为禁用下列项]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><EOF><wbr><wbr>批处理<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>自动调整控制台宽度<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>宽消息<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>默认错误级别为<wbr><wbr>-1<wbr><wbr>和<wbr><wbr>1<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr><wbr><wbr><wbr><wbr>[-?<wbr><wbr>显示语法摘要]<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>exec<wbr><wbr>master..xp_cmdshell<wbr><wbr>'osql<wbr><wbr>-U<wbr><wbr>sa<wbr><wbr>-P<wbr><wbr>19850709<wbr><wbr>-S<wbr><wbr>(Local)<wbr><wbr>-i<wbr><wbr>E:\Study\SQL\Data\SqlText\cmdshellTest.sql'<wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>注:路径的文件夹名称中间不能有空格</wbr>
<wbr></wbr>
相关推荐
SQLServer 数据 导入导出 BCP详解。。。。。。。。。
bcp是微软SQLServer数据库系统的命令行工具,用于进行大数据量的导入导出,该工具简单实用、效率极高,本文总结了bcp命令操作的各种设置开关功能含义和使用方法,可以为需要的用户提供有益帮助。
SQL语句导入导出大全 /** 导入文本文件 EXEC master..xp_cmdshell bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword /** 导出文本文件 EXEC master..xp_cmdshell bcp dbname..tablename out ...
sql server 2008 导入导出数据大全 /******* 导出到excelEXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q - S"GNETDATA/GNETDATA" -U"sa" -P""' /*********** 导入ExcelSELECT * ...
sql server 中 BCP使用方法详解,快速从SQL SERVER中导入导出数据的命令
sql server数据导入导出 指定要连接的SQL Server服务器的实例,如果未指定此选项,bcp连接本机的SQL Server默认实例。如果要连接某台机器上的默认实例,只需要指定机器名即可。
在SQL Server中主要有三种方式导入导出数据:使用Transact-SQL对数据进行处理;调用命令行工具BCP处理数据;使用数据转换服务(DTS)对数据进行处理。这三种方法各有其特点,下面就它们的主要特点进行比较。
数据导出:在ServerDbConfig.cfg文件里配置好数据库访问所需要的信息,在sql文件下配置好要导出的SqlFiles,执行批处理DBUpdator_Normal.bat,就可以获得导出数据的文本形式,同样的配置导入工具的相关信息,...
bcp是SQL Server中负责导入导出数据的一个命令行工具,它是基于DB-Library的,并且能以并行的方式高效地导入导出大批量的数据。bcp可以将数据库的表或视图直接导出,也能通过SELECT FROM语句对表或视图进行过滤后...
SQL的BCP详解,有关BCP导入和导出的详细说明
EXEC master..xp_cmdshell ’bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""’ /*********** 导入Excel SELECT * FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’, ’...
BCP批量导入excel数据到sqlserver相关资料整理
bcp导入导出SQL Server和Sybase数据库数据
sql server bcp 导入 到处 详细资料 参数
sqlserver bcp导出到excel 测试可用
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""' /*********** 导入Excel SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0'...
MS SQL Server数据库SQL语句导入导出大全,包括与其他数据库和文件的数据的导入导出。 /******* 导出到excel EXEC master..xp_cmdshell ‘bcp SettleDB.dbo.shanghu out c:temp1.xls -c -q -S“GNETDATA/...
SQL2005的bcp命令-批量快速导入导出数据,公司内部人员写的资料,希望对大家有帮助
批量复制程序(BCP)是Microsoft SQL Server中的一个命令行实用程序。使用BCP,我们可以导入并可从SQL Server数据库中快速而轻松地导出数据。任何使用这个功能的数据库管理员都承认BCP是一个关键工具。