SqlServer之xp_cmdshell_使用以及配置

对大数据量导入导出就好的办法，就是在命令行中使用bcp命令来导入导出数据。

/*MSsql2005以上 如何启用xp_cmdshell
默认情况下,sql server2005以上安装完后,xp_cmdshell是禁用的(可能是安全考虑),如果要使用它,可按以下步骤
*/

-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO

-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO

/*执行内容

-- 把 YTHR 中的Employee 表的数据导出到D:\estTemp.xls(或d:\TEST.txt） , 导出以后会覆盖原有文件的内容

-- 设置字段分隔符和行分隔符(-c -t"," -r"\n"),不想输入字段类型等请配合-c一起使用

exec master..xp_cmdshell'bcp " select * from [Test].[dbo].[employee]" queryout d:\TEST.txt -c -T -t" , " -r"\n"'

exec master..xp_cmdshell'bcp " select DEPTID AS 数量,salary as 单价,cood as 名称 from [Test].[dbo].[employee]" queryout d:\estTemp.xls -c -T -q'

--1.把表字段转为中文和数据一起导出

go
CREATE TABLE product(productid int,productname varchar(50)) 
insert into product
select 1,'衣服' union
select 2,'帽子' union
select 3,'鞋子' union
select 4,'裤子' union
select 5,'袜子'

declare @count int,@i int,@name varchar(50),@t int
declare @sql varchar(500)

declare @DbTable varchar(100)
SET @DbTable = 'tempTable'
IF EXISTS (SELECT 1
FROM sysobjects
WHERE name = @DbTable
AND TYPE = 'u')
begin
PRINT 'EXISTS '
end;
ELSE
begin
exec('CREATE TABLE tempTable(id int default(''1''))')
exec('insert into tempTable values(''1'')')
PRINT 'NOT EXISTS '
end;

select name,colid from syscolumns where id=object_id('product')
select @count= COUNT(*) from syscolumns where id=object_id('product')
set @i=@count
if @count>0
begin
while @i>0
begin
select @name=name from syscolumns where id=object_id('product') and colid=@i
select @t=count(*) from syscolumns where id=object_id('temptable') and name=@name
if @t=0
begin
set @sql= 'alter table temptable add'+' ' + @name +' ' +'varchar(50)';
print (@sql)
exec(@sql)
set @i=@i-1
--exec(@sql)
--update tempTable set @name="'"+@name+"'" where id='1'
set @sql='update tempTable set ' +@name+'= '''+@name+''' where id=''1'''
print (@sql)
exec(@sql)
end
else
begin
print '123'
return;
end
end
set @count=@count-1
begin
exec master..xp_cmdshell'bcp "select CASE when productid=''productid'' then ''数量'' END,CASE when productname=''productname'' then ''名称'' END from [Test].[dbo].[tempTable] union all select ltrim(productid) as productid,productname from [Test].[dbo].[product]" queryout d:\estTemp888.xls -c -T -q'
end;
end;

 -- 2.把表字段内容和数据一起导出

declare @sql varchar(100)

select @sql=isnull(@sql+',''','''')+name+'''' from syscolumns where id=object_id('product')

exec('select '+@sql+' union all select ltrim(productid),productname from product')

*/

--用完后,要记得将xp_cmdshell禁用(出于安全考虑)

-- 禁用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO

 

遇见的错误：

1、发生以下错误：
[Error][Microsoft][Native]Error = [Microsoft][SQL Native Client]无法打开 BCP 主数据文件

使用如下命令：
EXEC xp_cmdshell 'ECHO %USERDOMAIN%\%USERNAME%'
返回 ：NT AUTHORITY\NETWORK SERVICE

然后在配置管理器（configuration manager)里面的SQL server2005服务里打开，看到登陆内置账号为Network service,
改成local system问题解决。