bcp 导出数据

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
alter PROCEDURE prBCPOutTables
-- Add the parameters for the stored procedure here
@debug int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @chvTable varchar(128),@chvCommand varchar(255)
declare @curTables cursor

set @curTables = cursor for select sys.schemas.name+'.'+sysobjects.name from sysobjects left join sys.schemas on sys.schemas.schema_id= sysobjects.uid where xType='U'

open @curTables

fetch next from @curTables into @chvTable

while(@@fetch_status = 0)
begin
set @chvCommand = 'bcp "AdventureWorks.'+@chvTable+'" out "c:/sql7/backup/'+@chvTable+'.txt" -c -q -S"INFOWAVE-G6SQ9Z" -U"sa" -P"westking"'
if(@debug <> 0)
select @chvCommand as 'chvCommand'
if @debug =0
exec xp_cmdshell @chvCommand, NO_OUTPUT
fetch next from @curTables into @chvTable
end
close @curTables
deallocate @curTables
return 0

-- Insert statements for procedure here

END
GO

注意：--exec xp_cmdshell 'bcp "AdventureWorks.Production.ProductProductPhoto" out "c:/sql7/backup/[Production].[ProductProductPhoto].txt" -c -q -S"INFOWAVE-G6SQ9Z" -U"sa" -P"westking"'
语句中如果AdventureWorks.Production.ProductProductPhoto是[AdventureWorks].[Production].[ProductProductPhoto]这种格式将导致的错误是：Error = [Microsoft][SQL Native Client]共享内存提供程序: 管道的另一端上无任何进程。 如果输出文件的路径没有创建，将出现“Error = [Microsoft][SQL Native Client]无法打开 BCP 主数据文件”这一错误。

以上存储过程将数据库中的数据导出为txt文件，当然也可以是其他形式的文件。该存储过程运行于sqlserver2005环境下，将AdventureWorks数据库表中的数据导出。如有不妥或错误之处，敬请指正，呵呵！