在线等待各位答复，怎么写目录名称是变量的bcp语句呢？

ziggy000

declare @user varchar(100)
declare @pw varchar(100)
set @user = 'sa'
set @pw = '16898758'
declare @qushi varchar(20)
declare @sheng varchar(20)
declare @sql varchar(1000)
declare cur_qushi scroll cursor for select distinct 区市 from 手机话段移动话段 for read only
open cur_qushi
fetch next from cur_qushi into @qushi
while @@fetch_status = 0
begin
set @sql = 'select Col001 from mobile.dbo.blacklist where left(Col001, 7) in(select 号段 from mobile.dbo.手机话段移动话段 where 区市 = ''' + @qushi + ''')'
exec outsheng @qushi1=@qushi, @sheng1=@sheng output
set @sql = 'bcp "' + @sql + '" queryout D:\numbers\'+@sheng+'\' + @qushi + '.txt -c -U' + @user + ' -P' + @pw
exec master..xp_cmdshell @sql
fetch next from cur_qushi into @qushi
end
close cur_qushi
deallocate cur_qushi


请大家指正，我的错误是：SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]无法打开 BCP 主数据文件
NULL

我的手机话段移动话段表格式为：
区号     省份     区市      号段
0510        江苏        无锡        1340000
0510        江苏        无锡        1340001
0510        江苏        无锡        1340002
0510        江苏        无锡        1340003
0510        江苏        无锡        1340004
025        江苏        南京        1340005
025        江苏        南京        1340006
025        江苏        南京        1340007
0511        江苏        镇江        1340008
我的blacklist表的格式为：
Col001
13709499546
13999045904
13566036035
13608334588
13575856588
13883743004
13977378668

ziggy000

我的outsheng 存储过程代码如下：

USE mobile
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'outsheng' AND type = 'p')
   DROP procedure outsheng
go
create procedure outsheng
@qushi1 varchar(20),
@sheng1 varchar(20) output
as
select  @sheng1=省份  from mobile.dbo.手机话段移动话段 where 区市=@qushi1
return
go

aimar7357

不知道错误

kxiaper

--搞定了
--BCP不能帮你建立目录
declare @user varchar(100)
declare @pw varchar(100)
set @user = 'sa'
set @pw = '16898758'
declare @qushi varchar(20)
declare @sheng varchar(20)
declare @mdsql varchar(1000)
declare @sql varchar(1000)
declare cur_qushi scroll cursor for select distinct 区市 from 手机话段移动话段 for read only
open cur_qushi
fetch next from cur_qushi into @qushi
while @@fetch_status = 0
begin
set @sql = 'select Col001 from mobile.dbo.blacklist where left(Col001, 7) in(select 号段 from mobile.dbo.手机话段移动话段 where 区市 = ''' + @qushi + ''')'
exec outsheng @qushi1=@qushi, @sheng1=@sheng output
set @mdsql ='if not exist D:\numbers\'+@sheng+' md D:\numbers\'+@sheng
--print(@mdsql)
exec master..xp_cmdshell @mdsql
set @sql = 'bcp "' + @sql + '" queryout D:\numbers\'+@sheng+'\' + @qushi + '.txt -c -U' + @user + ' -P' + @pw
--print(@sql)
exec master..xp_cmdshell @sql
fetch next from cur_qushi into @qushi
end
close cur_qushi
deallocate cur_qushi

我是杀人狂

学习了.

kxiaper

--假设目录D:\numbers已存在
--建立测试数据
if object_id('手机话段移动话段') is not null drop table 手机话段移动话段
create table 手机话段移动话段(区号 varchar(100),省份 varchar(100),区市 varchar(100),号段 varchar(100))
insert 手机话段移动话段
select '0510','江苏','无锡','1340000' union all
select '0510','江苏','无锡','1340001'union all
select '0510','江苏','无锡','1340002'union all
select '0510','江苏','无锡','1340003'union all
select '0510','江苏','无锡','1340004'union all
select '025','江苏','南京','1340005'union all
select '025','江苏','南京','1340006'union all
select '025','江苏','南京','1340007'union all
select '0511','江苏','镇江','1340008'union all
select '020','广东','广州','1300008'
go

if object_id('blacklist') is not null drop table blacklist
create table blacklist(Col001 varchar(100))
insert blacklist
select '13400009546' union all
select '13999045904' union all
select '13566036035' union all
select '13608334588' union all
select '13575856588' union all
select '13883743004' union all
select '13977378668' union all
select '13000088668'
go

--建立存储过程
USE mobile
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'outsheng' AND type = 'p')
   DROP procedure outsheng
go
create procedure outsheng
@qushi1 varchar(20),
@sheng1 varchar(20) output
as
select  @sheng1=省份  from mobile.dbo.手机话段移动话段 where 区市=@qushi1
return
go

--写游标
declare @user varchar(100)
declare @pw varchar(100)
set @user = 'sa'
set @pw = 'ggs'
declare @qushi varchar(20)
declare @sheng varchar(20)
declare @mdsql varchar(1000)
declare @sql varchar(1000)
declare cur_qushi scroll cursor for select distinct 区市 from 手机话段移动话段 for read only
open cur_qushi
fetch next from cur_qushi into @qushi
while @@fetch_status = 0
begin
set @sql = 'select Col001 from mobile.dbo.blacklist where left(Col001, 7) in(select 号段 from mobile.dbo.手机话段移动话段 where 区市 = ''' + @qushi + ''')'
exec outsheng @qushi1=@qushi, @sheng1=@sheng output
set @mdsql ='if not exist D:\numbers\'+@sheng+' md D:\numbers\'+@sheng
--print(@mdsql)
exec master..xp_cmdshell @mdsql
set @sql = 'bcp "' + @sql + '" queryout D:\numbers\'+@sheng+'\' + @qushi + '.txt -c -U' + @user + ' -P' + @pw
--print(@sql)
exec master..xp_cmdshell @sql
fetch next from cur_qushi into @qushi
end
close cur_qushi
deallocate cur_qushi

--查看结果
--到D:\numbers目录可以看到“江苏”和“广东”两个目录
--“江苏”目录里有两个文件：南京.txt、无锡.txt（内容：13400009546）
--“江苏”目录里有一个文件：广东.txt（内容：13000088668）

smallwarrior

路径加引号：
set @sql = 'bcp "' + @sql + '" queryout ＂D:\numbers\'+@sheng+'\' + @qushi + '.txt＂ -c -U' + @user + ' -P' + @pw

忆茹天使

exec xp_cmdshell 'bcp "select * from recodata.dbo.章节表"
queryout "c:\11.txt" -c -Usa -Sserver -P,'