SqlServer2005存储过程执行BCP命令

USE [ZL_011]
GO
/****** 对象:  StoredProcedure [dbo].[renametab]    脚本日期: 05/08/2013 10:49:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[renametab] @tab nvarchar(2000) as
declare @OpenDate varchar(8)
declare @str nvarchar(4000)
declare @str1 nvarchar(4000)
declare @tabname varchar(500)
SELECT @OpenDate=convert(varchar(8),getdate(),112)
set @tabname=@tab+CONVERT([varchar],getdate(),(112))
set @str1='EXEC sp_rename '+''''+@tab+''''+','+''''+@tabname+''''
exec sp_executesql @str1
--print @str1
set @str='CREATE TABLE '+@tab+'([id] [int] IDENTITY(1,1) NOT NULL primary key,[001] [varchar](12) NOT NULL,[003] [nvarchar](50) NULL,[009] [nvarchar](50) NULL,[013] [nvarchar](255) NULL,[014] [nvarchar](255) NULL,[015] [nvarchar](50) NULL,[016] [nvarchar](50) NULL,[101] [int] NULL,[102] [nvarchar](255) NULL,[103] [nvarchar](50) NULL,[104] [nvarchar](255) NULL,[105] [nvarchar](255) NULL,[106] [nvarchar](255) NULL,[107] [nvarchar](255) NULL,[108] [nvarchar](255) NULL,[109] [nvarchar](255) NULL,[110] [nvarchar](255) NULL,[201] [int] NOT NULL,[301] [nvarchar](254) NOT NULL,[303] [nvarchar](254) NULL,[304] [nvarchar](100) NULL,[305] [ntext] NULL,[306] [nvarchar](254) NULL,[307] [nvarchar](254) NULL,[308] [nvarchar](100) NULL,[309] [nvarchar](255) NULL,[310] [nvarchar](255) NULL,[312] [nvarchar](254) NULL,[318] [nvarchar](255) NULL,[320] [int] NULL,[506] [nvarchar](50) NULL,[507] [ntext] NULL,[508] [nvarchar](255) NULL,[509] [nvarchar](100) NULL,[510] [nvarchar](255) NULL,[timestamp] [varchar](12) NOT NULL CONSTRAINT [timestamp'+CONVERT([varchar],getdate(),(112))+']  DEFAULT (CONVERT([varchar],getdate(),(112))),[state] [int] NOT NULL)'
exec sp_executesql @str
--print @str
select @str='bcp "SELECT  ''011'' +[001] FROM ZL_011.dbo.+@tabname+" queryout c:/ZL_011'+@OpenDate+'.txt -T -c'
--print @str

EXEC master..xp_cmdshell @str

------------------------------------------------------------------------------------------------------

//

1. begin  
2.   
3.     declare @OpenDate varchar(8)   
4.     DECLARE @Sql      VARCHAR(1000)   
5.     -- Add the parameters for the stored procedure here  
6.     SELECT @OpenDate=convert(varchar(8),dateadd(dd,-1,getdate()),112)     
7.   
8.     BEGIN  
9.         set @Sql='bcp  mydb.dbo.people  in   D:/ftpdata/'+@OpenDate+'.txt  -c -Usa -P111111  -t/"|/"'  
10.         EXEC MASTER..xp_cmdshell @Sql  
11.     END  
12.   
13. end  

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[BcpDataToPeople] as begin declare @OpenDate varchar(8) DECLARE @Sql VARCHAR(1000) -- Add the parameters for the stored procedure here SELECT @OpenDate=convert(varchar(8),dateadd(dd,-1,getdate()),112) BEGIN set @Sql='bcp mydb.dbo.people in D:/ftpdata/'+@OpenDate+'.txt -c -Usa -P111111 -t/"|/"' EXEC MASTER..xp_cmdshell @Sql END end