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 @[email protected], @[email protected] output
set @sql = 'bcp " ' + @sql + ' " queryout D:\numbers\ '[email protected]+ '\ ' + @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
------解决方案--------------------
--搞定了
--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 @[email protected], @[email protected] output
set @mdsql = 'if not exist D:\numbers\ '[email protected]+ ' md D:\numbers\ '[email protected]
--print(@mdsql)
exec master..xp_cmdshell @mdsql
set @sql = 'bcp " ' + @sql + ' " queryout D:\numbers\ '[email protected]+ '\ ' + @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