时间: 2021-08-13 作者:daque
/* 'bcp' 不是里面或外部吩咐,也不是可运转的步调?
看看在c:\program files\microsoft sql server\80\tools\binn内里有没有bcp.exe这个文献而后看看path内里加c:\program files\microsoft sql server\80\tools\binn这个目次没有.
右键我的电脑--属性--高档--情况变量--在体例变量中找到path--编纂
在变量值的结果加上:
c:\program files\microsoft sql server\80\tools\binn
*/
/**********************************************************************保存进程: p_savexml功效: 生成XML文件参数: 表名归来: 指定目次的xml文献挪用本领: 传播中央表tb_tablecolumn_t生存的表名 exec p_zehuasavexml 'wq_stinfo_b,wq_phy_d','d:\xx.xml' 结果更将来期: 2005-8-17 接洽办法: zlp@zehua.com.cn备注: 按照中央表天生适合规范方法的xml文献**********************************************************************/
/* exec p_zehuasavexml 'wq_stinfo_b,wq_phy_d','d:\xx.xml'*/gocreate proc p_zehuasavexml@tablecodes varchar(8000),@fname varchar(1000)='c:\tmp.xml' --默许生存的xml文献地方asdeclare @sql nvarchar(200),@sql1 varchar(400)declare @cnt int,@table_name varchar(20)declare @i int,@tablecode varchar(20)declare @err int,@src varchar(255),@desc varchar(255)create table ##t(re nvarchar(4000))insert ##tselect re='<?xml version="1.0" encoding="gb2312"?>'union all select '<zehuatableinfoxml>'set @i=charindex(',',@tablecodes)while @i>0begin set @tablecode=left(@tablecodes,@i-1) set @sql='' set @sql=n'select @cnt=count(*),@table_name=max(table_name) from ' set @sql=@sql+n'(' set @sql=@sql+n'select top 100 percent * from tb_tablecolumn_t where ' set @sql=@sql+n'table_code='''+@tablecode+''' and columns_iskey=''√'' ' set @sql=@sql+n'order by columns_sort ' set @sql=@sql+n')' set @sql=@sql+n'a' exec sp_executesql @sql,n'@cnt int output,@table_name varchar(20) output',@cnt output,@table_name output --计划主键的个数,表名
insert ##t select '<'+@tablecode+' table_name="'+@table_name+'" pk="'+cast(@cnt as varchar)+'">' union all select space(4)+'<columns columns_name="'+cast(columns_name as varchar)+'" '+space(8)+'table_code="'+isnull(@tablecode,'')+'" '+space(8)+'columns_visible="'+isnull(columns_visible,'')+'"/>' from tb_tablecolumn_t where table_code=''+@tablecode+'' union all select '</'+@tablecode+'>' set @tablecodes=right(@tablecodes,len(@tablecodes)-@i) set @i=charindex(',',@tablecodes) endif len(@tablecodes)>0begin set @sql='' set @sql=n'select @cnt=count(*),@table_name=max(table_name) from ' set @sql=@sql+n'(' set @sql=@sql+n'select top 100 percent * from tb_tablecolumn_t where ' set @sql=@sql+n'table_code='''+@tablecodes+''' and columns_iskey=''√'' ' set @sql=@sql+n'order by columns_sort ' set @sql=@sql+n')' set @sql=@sql+n'a' exec sp_executesql @sql,n'@cnt int output,@table_name varchar(20) output',@cnt output,@table_name output --计划主键的个数,表名 insert ##t select '<'+@tablecodes+' table_name="'+@table_name+'" pk="'+cast(@cnt as varchar)+'">' union all select space(4)+'<columns columns_name="'+cast(columns_name as varchar)+'" '+space(8)+'table_code="'+isnull(@tablecodes,'')+'" '+space(8)+'columns_visible="'+isnull(columns_visible,'')+'"/>' from tb_tablecolumn_t where table_code=''+@tablecodes+'' union all select '</'+@tablecodes+'>' union all select '</zehuatableinfoxml>'end
exec('master..xp_cmdshell ''bcp ##t out "'+@fname+'" /p"" /c''') drop table ##t
go
/*
尝试情况:
windows 2000 server + sql 2000尝试经过
*/