<script type="text/javascript">
</script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
/*--存为XML
将表/ 查询存储为标准的XML 文件
--*/
/*--调用示例
--用SQL用winows身份验证的情况
execp_savexml@sql='地区资料',@fname='c:/地区资料.xml'
--用指定的用户
execp_savexml@sql='地区资料',@fname='c:/地区资料.xml',@userid='sa'
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_savexml]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_savexml]
GO
createprocp_savexml
@sqlvarchar(8000), --要保存的表/视图/SQL查询语句
@fnamevarchar(1000)='c:/tmp.xml', --保存的XML 文件名
@useridvarchar(50)='', --用户名,如果为nt验证方式,则为''
@passwordvarchar(50)='' --密码
as
declare@errint,@srcvarchar(255),@descvarchar(255)
declare@objint,@constrvarchar(1000)
ifisnull(@userid,'')=''
set@constr='Provider=SQLOLEDB.1;IntegratedSecurity=SSPI;PersistSecurityInfo=False;InitialCatalog='
+db_name()+';DataSource='+@@servername
else
set@constr='Provider=SQLOLEDB.1;PersistSecurityInfo=True;'
+'UserID='+@userid+';Password='+isnull(@password,'')
+';InitialCatalog='+db_name()+';DataSource='+@@servername
exec@err=sp_oacreate'adodb.recordset',@objout
if@err<>0gotolberr
exec@err=sp_oamethod@obj,'open',null,@sql,@constr
if@err<>0gotolberr
set@sql='del'+@fname
execmaster..xp_cmdshell@sql,no_output
exec@err=sp_oamethod@obj,'save',null,@fname,1
if@err<>0gotolberr
exec@err=sp_oadestroy@obj
return
lberr:
execsp_oageterrorinfo0,@srcout,@descout
selectcast(@errasvarbinary(4))as错误号
,@srcas错误源,@descas错误描述
go
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
/*--存为XML
将表/ 查询存储为标准的XML 文件
--*/
/*--调用示例
--用SQL用winows身份验证的情况
execp_savexml@sql='地区资料',@fname='c:/地区资料.xml'
--用指定的用户
execp_savexml@sql='地区资料',@fname='c:/地区资料.xml',@userid='sa'
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_savexml]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_savexml]
GO
createprocp_savexml
@sqlvarchar(8000), --要保存的表/视图/SQL查询语句
@fnamevarchar(1000)='c:/tmp.xml', --保存的XML 文件名
@useridvarchar(50)='', --用户名,如果为nt验证方式,则为''
@passwordvarchar(50)='' --密码
as
declare@errint,@srcvarchar(255),@descvarchar(255)
declare@objint,@constrvarchar(1000)
ifisnull(@userid,'')=''
set@constr='Provider=SQLOLEDB.1;IntegratedSecurity=SSPI;PersistSecurityInfo=False;InitialCatalog='
+db_name()+';DataSource='+@@servername
else
set@constr='Provider=SQLOLEDB.1;PersistSecurityInfo=True;'
+'UserID='+@userid+';Password='+isnull(@password,'')
+';InitialCatalog='+db_name()+';DataSource='+@@servername
exec@err=sp_oacreate'adodb.recordset',@objout
if@err<>0gotolberr
exec@err=sp_oamethod@obj,'open',null,@sql,@constr
if@err<>0gotolberr
set@sql='del'+@fname
execmaster..xp_cmdshell@sql,no_output
exec@err=sp_oamethod@obj,'save',null,@fname,1
if@err<>0gotolberr
exec@err=sp_oadestroy@obj
return
lberr:
execsp_oageterrorinfo0,@srcout,@descout
selectcast(@errasvarbinary(4))as错误号
,@srcas错误源,@descas错误描述
go
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>