BCP DLL文件导入，用到邹建大哥的【p_binaryIO 】

Question

由于网络、及安全方面的限制，决定用把各种类型的文件存储到一台数据库上，

再把这些二进制数据步到其它数据库，再还原成文件进行文件覆盖

用BCP数据导入的时候，用到了邹建大哥写的【p_binaryIO 】这个存储过程

但在导入的时候老是报错【输入文件存储类型;输入前缀长度；】类似的错

我看了下BCP导出的帮助，加了/C /N /n 这些参数都还是报错，

这问题整了两天，还是没有解决，请大家帮忙看一下

下面附上存储过程源码

此存储过程仅用bcp实现 
  邹建2003.08-----------------*/ 
    /*--调用示例
  --数据导出
  exec p_binaryIO 'zj','','','acc_演示数据..tb','img','c:/zj1.dat' 
  
  --数据导入
  exec p_binaryIO 'zj','','','acc_演示数据..tb','img','c:/zj1.dat','',0 
  --*/ 
  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_binaryIO]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
  drop procedure [dbo].[p_binaryIO] 
  GO 
  Create proc p_binaryIO 
  @servename varchar (30),--服务器名称
  @username varchar (30), --用户名
  @password varchar (30), --密码
  @tbname varchar (500), --数据库..表名
  @fdname varchar (30), --字段名
  @fname varchar (1000), --目录+文件名,处理过程中要使用/覆盖:@filename+_temp 
  @tj varchar (1000)='', --处理条件.对于数据导入,如果条件中包含@fdname,请指定表名前缀
  @isout bit=1 --1导出((默认),0导入
  AS 
  declare @fname_in varchar(1000) --bcp处理应答文件名
      ,@fsize varchar(20) --要处理的文件的大小
      ,@m_tbname varchar(50) --临时表名
      ,@sql varchar(8000) 
  
  --则取得导入文件的大小
  if @isout=1 
    set @fsize='0' 
  else 
  begin 
      create table #tb(可选名 varchar(20),大小 int 
         ,创建日期 varchar(10),创建时间 varchar(20) 
         ,上次写操作日期 varchar(10),上次写操作时间 varchar(20) 
         ,上次访问日期 varchar(10),上次访问时间 varchar(20),特性 int) 
      insert into #tb 
      exec master..xp_getfiledetails @fname 
      select @fsize=大小 from #tb 
      drop table #tb 
      if @fsize is null 
      begin 
         print '文件未找到' 
         return 
      end 
  end 
  
  --生成数据处理应答文件
  set @m_tbname='[##temp'+cast(newid() as varchar(40))+']' 
  set @sql='select * into '+@m_tbname+' from( 
      select null as 类型
      union all select 0 as 前缀
      union all select '+@fsize+' as 长度
      union all select null as 结束
      union all select null as 格式
      ) a' 
  exec(@sql) 
  select @fname_in=@fname+'_temp' 
      ,@sql='bcp "'+@m_tbname+'" out "'+@fname_in 
      +'" /S"'+@servename 
      +case when isnull(@username,'')='' then '' 
     else '" /U"'+@username end 
      +'" /P"'+isnull(@password,'')+'" /c' 
  exec master..xp_cmdshell @sql 
  --删除临时表
  set @sql='drop table '+@m_tbname 
  exec(@sql) 
  
  if @isout=1 
  begin 
      set @sql='bcp "select top 1 '+@fdname+' from ' 
         +@tbname+case isnull(@tj,'') when '' then '' 
         else ' where '+@tj end 
         +'" queryout "'+@fname 
         +'" /S"'+@servename 
         +case when isnull(@username,'')='' then '' 
         else '" /U"'+@username end 
         +'" /P"'+isnull(@password,'') 
         +'" /i"'+@fname_in+'"' 
      exec master..xp_cmdshell @sql 
  end 
  else 
  begin 
      --为数据导入准备临时表
      set @sql='select top 0 '+@fdname+' into ' 
         +@m_tbname+' from ' +@tbname 
      exec(@sql) 
      
      --将数据导入到临时表
     set @sql='bcp "'+@m_tbname+'" in "'+@fname 
         +'" /S"'+@servename 
         +case when isnull(@username,'')='' then '' 
         else '" /U"'+@username end 
         +'" /P"'+isnull(@password,'') 
         +'" /i"'+@fname_in+'"'       --就是这一句，没有执行成功
      exec master..xp_cmdshell @sql 
      
      --将数据导入到正式表中
      set @sql='update '+@tbname 
         +' set '+@fdname+'=b.'+@fdname 
         +' from '+@tbname+' a,' 
         +@m_tbname+' b' 
         +case isnull(@tj,'') when '' then '' 
        else ' where '+@tj end 
      exec(@sql) 
      
      --删除数据处理临时表
      set @sql='drop table '+@m_tbname 
  end 
  --删除数据处理应答文件
  set @sql='del '+@fname_in 
  exec master..xp_cmdshell @sql 
  go 

Answer 1

版本为

SQL SERVER 2008 R2

Answer 2

好古老的东东，估计是生成 bcp 的格式文件的时候没有对

Answer 3

是有点古老啊，我看都是03年的东东

现在有新的解决方案没？现在把文件导入导出到数据库里好像也还是只能用BCP吧

格式文件也是按照里面生成的，我看到格式文件是用/c格式，但导出时没有显式标明是什么格式

Answer 4

看了一下，应该是这个xp_getfiledetails

这个存储过程好像2005开始就没有提供了，这样导致取不到文件大小

其实现在导入是很简单的，不用那么复杂，直接用 OPENROWSET 就可以了（导出没有现成的）

SELECT *
  FROM OPENROWSET(BULK 'X:\你的文件', SINGLE_BLOB) T(c)
 

Answer 5

是的

你说的这种方法我试了

用select ... into...

SELECT *
  FROM OPENROWSET(BULK 'X:\你的文件', SINGLE_BLOB) T(c)

这种方法导入很简单，也可以正常导进去，但重新导出成文件的时候，他会在每个文件（二进制）前面加上一个串前缀

而且我试了不同类型的文件，前缀是不同的，所以导致导出成文件的时候失败了

至于你说到的xp_getfiledetails，我目前是直接把那个文件的大小写死进行测试的

Answer 6

重新写了一个救命，可以参考一下

USE tempdb;
GO

CREATE TABLE dbo.tb(
	id int
		PRIMARY KEY,
	value varbinary(max)
);
GO

-- 导入
INSERT dbo.tb
SELECT
	id = 1,
	value = T.c
FROM OPENROWSET(
		BULK N'e:\temp\TEST.rar',
		SINGLE_BLOB
	) T(c)
;
GO

-- 导出
DECLARE
	@file_name nvarchar(260),
	@file_content varbinary(max)
;
-- 这里面设置好要导出的文件名和文件内容就行了，其他地方不用改
SELECT TOP 1
	@file_name = N'e:\temp\test_xx.rar',	-- 导出的文件名
	@file_content = value
FROM dbo.tb
;

-- 导出处理
DECLARE
	@__$sql nvarchar(max),

	@__$server_instance sysname,
	@__$cmd_bcp nvarchar(4000),
	@__$bcp_output_path nvarchar(260),
	@__$bcp_fmt_file nvarchar(260),
	@__$bcp_fmt_file_content nvarchar(2000)
	
;

-- 格式文件
SELECT
	@__$server_instance = CONVERT(sysname, SERVERPROPERTY(N'ServerName')),

	@__$bcp_output_path = REVERSE(LTRIM(RTRIM(@file_name))),
	@__$bcp_output_path = REVERSE(STUFF(@__$bcp_output_path, 1, CHARINDEX(N'\', @__$bcp_output_path), N'')),
	@__$bcp_fmt_file = @__$bcp_output_path
					+ N'\bcp_96B56A06-3740-468A-999C-C16C9405300C.fmt',

	@__$bcp_fmt_file_content = N'SELECT CONVERT(varchar, 0x382E300D0A310D0A312053514C494D41474520302030202222203120) + N'
							+ QUOTENAME(N'value', N'''')
							+ N' + CONVERT(varchar, 0x2022220D0A)',

	@__$cmd_bcp = N'bcp "'
				+ @__$bcp_fmt_file_content
				+ N'" QUERYOUT "'
				+ @__$bcp_fmt_file
				+ N'" /T /c'
				+ N' /S '
				+ QUOTENAME(@__$server_instance, N'"')
;

DECLARE @tb_bcp_result TABLE(
	re nvarchar(4000)
);
EXEC master..xp_create_subdir @__$bcp_output_path;
DELETE FROM @tb_bcp_result;
INSERT @tb_bcp_result
EXEC master.dbo.xp_cmdshell @__$cmd_bcp;

IF NOT EXISTS(
		SELECT * FROM @tb_bcp_result
		WHERE re LIKE N'1 rows copied.%'
			OR re LIKE N'已复制 1 行。%'
	)
BEGIN
	SELECT *
	FROM @tb_bcp_result
	;
	
	RAISERROR(
		N'generate bcp format file error',
		16, 1
	) WITH NOWAIT;

	RETURN;
END;

-- 导出
DECLARE
	@__$bcp_output_table sysname
;
SELECT
	@__$bcp_output_table = QUOTENAME(N'##tb_bcp_' + CONVERT(char(36), NEWID())),
	@__$sql = N'
CREATE TABLE '
			+ @__$bcp_output_table
			+ N'(
	name nvarchar(260),
	value varbinary(max)
);
INSERT '
			+ @__$bcp_output_table
			+ N'(
name, value
)
VALUES(
@file_name, @file_content
)';

EXEC sp_executesql
	@__$sql,
	N'
		@file_name nvarchar(260),
		@file_content varbinary(max)
	',
	@file_name,
	@file_content
;


SET @__$sql = N'
WITH
SQL AS(
SELECT
	*,
	sql = N''SELECT TOP 1 value FROM ''
		+ @__$bcp_output_table
		+ N'' WITH(NOLOCK) ''
FROM '
			+ @__$bcp_output_table
			+ N' WITH(NOLOCK)
)
SELECT
@__$cmd_bcp = N''BCP "''
	+ sql
	+ N''" QUERYOUT "''
	+ name
	+ N''" /T /f"''
	+ @__$bcp_fmt_file
	+ N''" /S ''
	+ QUOTENAME(@__$server_instance, N''"'')

FROM SQL
;
';
EXEC sp_executesql
	@__$sql,
	N'
		@__$bcp_output_table sysname,
		@__$bcp_fmt_file nvarchar(260),
		@__$server_instance sysname,
		@__$cmd_bcp nvarchar(4000) OUTPUT
	',
	@__$bcp_output_table,
	@__$bcp_fmt_file,
	@__$server_instance,
	@__$cmd_bcp OUTPUT
;


DELETE FROM @tb_bcp_result;
INSERT @tb_bcp_result
EXEC master.dbo.xp_cmdshell @__$cmd_bcp;

IF NOT EXISTS(
		SELECT * FROM @tb_bcp_result
		WHERE re LIKE N'1 rows copied.%'
			OR re LIKE N'已复制 1 行。%'
	)
BEGIN
	SELECT * FROM @tb_bcp_result;
	RAISERROR(
		N'error for execute:
%s',
		16, 1,
		@__$cmd_bcp
	) WITH NOWAIT;
END;

-- 删除导出产生的临时表和文件
IF OBJECT_ID(N'tempdb..' + @__$bcp_output_table, N'Table') IS NOT NULL
BEGIN;
	SET @__$sql = N'DROP TABLE'
				+ @__$bcp_output_table
	;
	EXEC sp_executesql
		@__$sql
	;
END;

SET
	@__$cmd_bcp = N'del "'
				+ @__$bcp_fmt_file
				+ N'"';
DELETE FROM @tb_bcp_result;
INSERT @tb_bcp_result
EXEC master.dbo.xp_cmdshell @__$cmd_bcp;
GO

-- 删除测试表
DROP TABLE dbo.tb;

Answer 7

这几天忙其它事情了

大概试了一下可以是可以的

但好像非常慢，自己再仔细研究一下

有个问题想问一下邹建大哥，原来的方式【p_binaryIO】为什么不行，是什么原因？

我相信原来肯定是可以的，是SQL SERVER 版本的原因吗？？

Answer 8

邹建大哥新写的没有任何问题，速度也挺快的