*存为XML
将表/查询存储为标准的XML文件
*/
/*调用示例
用SQL用winows身份验证的情况
exec p_savexml @sql=地区资料@fname=c:\地区资料xml
用指定的用户
exec p_savexml @sql=地区资料@fname=c:\地区资料xml@userid=sa
*/
if exists (select * from dbosysobjects where id = object_id(N[dbo][p_savexml]) and OBJECTPROPERTY(id NIsProcedure) = )
drop procedure [dbo][p_savexml]
GO
create proc p_savexml
@sql varchar() 要保存的表/视图/SQL查询语句
@fname varchar()=c:\tmpxml 保存的XML文件名
@userid varchar()=用户名如果为nt验证方式则为
@password varchar()= 密码
as
declare @err int@src varchar()@desc varchar()
declare @obj int@constr varchar()
if isnull(@userid)=
set @constr=Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=
+db_name()+;Data Source=+@@servername
else
set @constr=Provider=SQLOLEDB;Persist Security Info=True;
+User ID=+@userid+;Password=+isnull(@password)
+;Initial Catalog=+db_name()+;Data Source=+@@servername
exec @err=sp_oacreate adodbrecordset@obj out
if @err<> goto lberr
exec @err=sp_oamethod @objopennull@sql@constr
if @err<> goto lberr
set @sql=del +@fname
exec masterxp_cmdshell @sqlno_output
exec @err=sp_oamethod @objsavenull@fname
if @err<> goto lberr
exec @err=sp_oadestroy @obj
return
lberr:
exec sp_oageterrorinfo @src out@desc out
select cast(@err as varbinary()) as 错误号
@src as 错误源@desc as 错误描述
go
