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],(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],(112))+']? DEFAULT (CONVERT([varchar],(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?-P222221??-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),-1,112) BEGIN set @Sql='bcp mydb.dbo.people in D:/ftpdata/'+@OpenDate+'.txt -c -Usa -P222221 -t/"|/"' EXEC MASTER..xp_cmdshell @Sql END end

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!