Waiting for your reply online, how to write a bcp statement whose directory name is a variable?

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 District from cell phone segment mobile segment 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 number segment from mobile.dbo. mobile segment mobile segment where city ==' '+ @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


Please correct me, my error is: SQLState = S1000, NativeError = 0
Error = [Microsoft] [ODBC SQL Server Driver] could not open the BCP master data file
NULL

My mobile phone table table format is:
Area code Province city
0510 Jiangsu Wuxi 1340000
0510 Jiangsu Wuxi 1340001
0510 Jiangsu Wuxi 1340002
0510 Jiangsu Wuxi 1340003
0510 Jiangsu Wuxi 1340004
025 Nanjing, Jiangsu 1340005
025 Nanjing, Jiangsu 1340006
025 Nanjing, Jiangsu 1340007
0511 Zhenjiang, Jiangsu 1340008
The format of my blacklist table is:
Col001
13709499546
13999045904
13566036035
13608334588
13575856588
13883743004
13977378668

ziggy000

My outsheng stored procedure code is as follows:

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 = Province from mobile.dbo. mobile segment mobile segment where city == qq1
return
go

aimar7357

Don't know the error

kxiaper

--nailed it
--BCP cannot help you build directories
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 Districts from cell phone segment mobile segment 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 number segment from mobile.dbo. mobile segment where mobile segment where district city =' '' + @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

我是杀人狂

learned.

kxiaper

-Assuming directory D:\numbers already exists
-Establish test data
if object_id ('Mobile segment mobile segment') is not null drop table Mobile segment mobile segment
create table Mobile segment mobile segments
insert Mobile Talk Mobile Talk
select '0510', 'Jiangsu', 'Wuxi', '1340000' union all
select '0510', 'Jiangsu', 'Wuxi', '1340001'union all
select '0510', 'Jiangsu', 'Wuxi', '1340002'union all
select '0510', 'Jiangsu', 'Wuxi', '1340003'union all
select '0510', 'Jiangsu', 'Wuxi', '1340004'union all
select '025', 'Jiangsu', 'Nanjing', '1340005'union all
select '025', 'Jiangsu', 'Nanjing', '1340006'union all
select '025', 'Jiangsu', 'Nanjing', '1340007'union all
select '0511', 'Jiangsu', 'Zhenjiang', '1340008'union all
select '020', 'Guangdong', 'Guangzhou', '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

-Establish a stored procedure
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 = Province from mobile.dbo. mobile segment mobile segment where city == qq1
return
go

--Write cursor
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 Districts from cell phone segment mobile segment 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 number segment from mobile.dbo. mobile segment where mobile segment where district city =' '' + @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

--View Results
--Go to the D:\numbers directory, you can see the two directories "Jiangsu" and "Guangdong"
-There are two files in the "Jiangsu" directory: Nanjing.txt, Wuxi.txt (Contents: 14000095546)
-There is a file in the "Jiangsu" directory: Guangdong.txt (content: 13000088668)

smallwarrior

Path quoted:
set @sql = 'bcp "' + @sql + '" queryout "D:\numbers\' + @ sheng + '\' + @qushi + '.txt" -c -U' + @user + '-P' + @pw

忆茹天使

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