SqlServer之xp_cmdshell_使用以及配备(转)

转自:

SQL Server的导出导入形式有:


  1. 在SQL Server中提供了导入导出的分界面操作。
  2. 在分界面操作中又分【复制贰个或多少个表或视图的数额】和【编写查询以钦点要传输的多少】三种情势,第一种是直接对表、视图举办一切字段、记录进行导出,而第三种正是足以经过SQL语句来支配导出导入的字段和行。
  3. 使用 轻易但管用的SQL脚本 中的【表复制】那其间的点子。
  4. 再一种正是在指令行中使用bcp命令来导入导出多少,供给专门表明的是,那是对天意据量导入导出就好的艺术。

    –整个表导出(out)
    bcp 数据库名.dbo.表名 out
    c:\currency.txt -S”数据库实例” -U”顾客” -P”密码” -c 

    –使用SQL语句导出(queryout)
    bcp “select * from 数据库名.dbo.表名” queryout c:\currency.txt -S
    数据库实例 -U”顾客” -P”密码” -c

    –设置字段分隔符和行分隔符(-c -t”,”
    -r”\n”),不想输入字段类型等请同盟-c一同利用
    bcp “select * from 数据库名.dbo.表名” queryout c:\currency.txt -S
    数据库实例 -U”客户” -P”密码” -c -t”,” -r”\n”

    –钦定每批导入数据的行数、钦点服务器发出或收取的各种互连网数据包的字节数(-k
    -b四千 -a65535)
    bcp “select * from 数据库名.dbo.表名” queryout c:\currency.txt -S
    数据库实例 -U”客商” -P”密码” -c -t”,” -r”\n” -k -b5000-a65535

    –在查询深入分析器上实行(EXEC master..xp_cmdshell)
    EXEC master..xp_cmdshell ‘bcp “select * from 数据库名.dbo.表名”
    queryout c:\currency.txt -S 数据库实例 -U”客户” -P”密码” -c’

    –把SQL语句生成八个.sql文件,然后调用
    –注:路线的文书夹名称中间不可能有空格
    exec master..xp_cmdshell ‘osql -S 数据库实例 -U 顾客 -P 密码 -i   
    C:\cmdshellTest.sql’  

    –将数据导入到currency表中
    EXEC master..xp_cmdshell ‘bcp 数据库名.dbo.表名 in c:\currency.txt
    -c -T’
    –导入数据也一致可以采纳-F和-L选项来摘取导入数据的记录行。
    EXEC master..xp_cmdshell ‘bcp 数据库名.dbo.表名 in c:\currency.txt
    -c -F 10 -L 13 -T’

图片 1

摘要:

在选取命令xp_cmdshell的时候须要安装权限:

下文将享用应用sql脚本输出excel的方法
 
此脚本能够采取于 表或视图生成excel的点子,
若需使用sql脚本输出excel数据,大家可将sql脚本生成视图或临时表,

图片 2

下一场再输出excel

输出excel语法简要介绍:
exec
master..xp_cmdshell ‘bcp
[数据库名称].[架构名].[表名] out [excel寄放地点全路径] -c -q
-S”服务器Ip” -U “sql用户名” -P “sql密码”‘

---例1:
exec 
master..xp_cmdshell 'bcp test.dbo.tableName out d:\test.xls -c -q -S"." -U "sa" -P "erp"'
---将数据库test中tablName输出值d盘test.xls文件
---服务器地址.
---sql账户sa
---sql密码erp
--------------------------------------------
例2: 根据动态文件名输出excel的方法
declare @name varchar(30) ---动态文件名
set @name ='d:\test123.xlsx'

exec 
('master..xp_cmdshell ''bcp test.dbo.tableName out '+@name+' -c -q -S"." -U "sa" -P "erp"''') ---拼接操作语句,并采用exec执行

注意事项:

*1 使用脚本输出的excel ,无表头
2
可动态文件名和动态sa账户密码生成相关消息
3
假如出口sql脚本的数量至excel,大家需先将sql脚本生成的数目缓存至表中,然后输出表数据至excel

xp_cmdshell相关权限需展开
*

/*MSsql2007 怎么着启用xp_cmdshell
私下认可景况下,sql
server2006安装完后,xp_cmdshell是剥夺的(恐怕是安枕无忧着想),假诺要运用它,可按以下步骤
*/
— 允许配置高级选项
EXEC sp_configure ‘show advanced options’, 1
GO
— 重新配置
RECONFIGURE
GO
— 启用xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 1
GO
–重新配置
RECONFIGURE
GO

–实践想要的xp_cmdshell语句
Exec xp_cmdshell ‘query user’
GO

–用完后,要记得将xp_cmdshell禁止使用(出于安全思索)
— 允许配置高端选项
EXEC sp_configure ‘show advanced options’, 1
GO
— 重新配置
RECONFIGURE
GO
— 禁用xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 0
GO
–重新配置
RECONFIGURE
GO

图片 3

图片 4

 

 

 

 

遇上的不当:

1、发生以下错误:
[Error][Microsoft][Native]Error = [Microsoft][SQL Native
Client]没辙展开 BCP 主数据文件

选用如下命令:
EXEC xp_cmdshell ‘ECHO %USERDOMAIN%\%USERNAME%’
返回 :NT AUTHORITY\NETWORK SERVICE

然后在安顿管理器(configuration manager)里面包车型地铁SQL
server二〇〇六服务里展开,见到登入内置账号为Network service,
改成local system难点一蹴而就。

2、SQLState = 22018, NativeError = 0
Error = [Microsoft][SQL Native Client]对此造型表明无效的字符值

一经是表与表之间的数据导入,可用 -N, 只怕 -w, 而不用用 -c
用-c的话, 如果导出的某部列中的数据中蕴藏分隔符, 则会导致你 bcp
导入的时候退步
-N 可能 -w 不会有其一难题

 

bcp用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件
  [-m 最大错误数]             [-f 格式化文件]         [-e
错误文件]
  [-F 首行]                       [-L 末行]                  [-b
批大小]
  [-n 本机类型]                 [-c 字符类型]            [-w
宽字符类型]
  [-N 将非文本保持为本机类型] [-V 文件格式版本]     [-q
带引号的标志符]
  [-C 代码页表达符]           [-t 字段终止符]       [-r
行终止符]
  [-i 输入文件]                   [-o 输出文件]         [-a
数据包大小]
  [-S 服务器名称]              [-U 用户名]            [-P 密码]
  [-T 可信连接]                  [-v 版本]                [-科雷傲允许使用区域安装]
  [-k 保留空值]                  [-E 保留标志值]
  [-h”加载提醒”]                 [-x 生成 xml 格式化文件]

 

 

其它:

新建查询->输入SQL查询语句,实施获得必要的结果,在询问结果栏点击鼠标右键->将结果另存为 
导出文件(*csv)即可导出为 逗号分割的excel文件。 
此刻,由于尚未分列,所以应先新建八个空白的Excel文书档案,然后采纳张开刚才保存的的文书; 
下一场它会冒出文本导入步骤,选中分割符号,下一步,选中
逗号,预览区域健康,然后在下一步和姣好,保存为excel文件

相关文章