如下是存储过程,不知道哪位大仙能将其转换成SQL语句,多谢!/*
获取文件最后访问日期
@filepath 文件路径,如: c:\1.txt
@filedate 文件最后访问日期 调用示例:
declare @dt varchar(20)
exec getFileLastAccessDate 'c:\1.txt',@dt output
select @dt
*/
create procedure getFileLastAccessDate
@filepath varchar(4000),
@filedate varchar(20) output
as
declare @obj int,@file int
declare @fileexists varchar(10)
exec sp_oacreate 'Scripting.FileSystemObject',@obj output
exec sp_oamethod @obj,'FileExists',@fileexists output,@filepath
if @fileexists='False'
begin
set @filedate='文件不存在'
return
end
exec sp_oamethod @obj,'GetFile',@file output,@filepath
exec sp_oagetproperty @file,'DateLastAccessed',@filedate output
获取文件最后访问日期
@filepath 文件路径,如: c:\1.txt
@filedate 文件最后访问日期 调用示例:
declare @dt varchar(20)
exec getFileLastAccessDate 'c:\1.txt',@dt output
select @dt
*/
create procedure getFileLastAccessDate
@filepath varchar(4000),
@filedate varchar(20) output
as
declare @obj int,@file int
declare @fileexists varchar(10)
exec sp_oacreate 'Scripting.FileSystemObject',@obj output
exec sp_oamethod @obj,'FileExists',@fileexists output,@filepath
if @fileexists='False'
begin
set @filedate='文件不存在'
return
end
exec sp_oamethod @obj,'GetFile',@file output,@filepath
exec sp_oagetproperty @file,'DateLastAccessed',@filedate output
declare @filepath varchar(4000),
declare @filedate varchar(20)
declare @obj int,@file int
declare @fileexists varchar(10)
exec sp_oacreate 'Scripting.FileSystemObject',@obj output
exec sp_oamethod @obj,'FileExists',@fileexists output,@filepathif @fileexists='False'
begin
set @filedate='文件不存在'
return
end
exec sp_oamethod @obj,'GetFile',@file output,@filepath
exec sp_oagetproperty @file,'DateLastAccessed',@filedate outputselect @filepath
--试试
declare @filepath varchar(4000) set @filepath='c:\1.txt'
declare @filedate varchar(20)
declare @obj int,@file int
declare @fileexists varchar(10)
exec sp_oacreate 'Scripting.FileSystemObject',@obj output
exec sp_oamethod @obj,'FileExists',@fileexists output,@filepathif @fileexists='False'
begin
set @filedate='文件不存在'
return
end
exec sp_oamethod @obj,'GetFile',@file output,@filepath
exec sp_oagetproperty @file,'DateLastAccessed',@filedate outputselect @filedate
就是把存储过程的begin end 外面的东西去掉,把参数写成静态的就ok了。
--例如存储过程
create proc procname (@id int)
as
begin
select * from tablename where id=@id
end--改成sql语句
declare @id int set @id=1
select * from tablename where id=@id
declare @dt varchar(20)
exec getFileLastAccessDate 'c:\1.txt',@dt output
select @dt
exec sp_configure 'allow updates',0;
sp_configure 'show advanced options', 1;
go
RECONFIGURE;
go
sp_configure 'ole automation procedures', 1;
go
reconfigure;
godeclare @filepath varchar(4000) set @filepath='c:\1.txt'
declare @filedate varchar(20)
declare @obj int,@file int
declare @fileexists varchar(10)
exec sp_oacreate 'Scripting.FileSystemObject',@obj output
exec sp_oamethod @obj,'FileExists',@fileexists output,@filepath
if @fileexists='False'
begin
set @filedate='文件不存在'
return
end
exec sp_oamethod @obj,'GetFile',@file output,@filepath
exec sp_oagetproperty @file,'DateLastAccessed',@filedate output
select @filedate
/*
2012-2-3 14:15:29
*/
我本地测试没有问题
不知道是不是我环境的问题,我的环境是SQL2000.开启会报如下报错。你看一下。Configuration option 'allow updates' changed from 0 to 0. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 79
The configuration option 'ole automation procedures' does not exist, or it may be an advanced option.
Valid configuration options are:
或者有没有其他sql获取文件更新时间了···
很着急、很着急、很着急、很着急,感谢!万分感谢!
declare @filedate varchar(20)
declare @obj int,@file int
declare @fileexists varchar(10)
exec sp_oacreate 'Scripting.FileSystemObject',@obj output
exec sp_oamethod @obj,'FileExists',@fileexists output,@filepath
if @fileexists='False'
begin
set @filedate='文件不存在'
return
end
exec sp_oamethod @obj,'GetFile',@file output,@filepath
exec sp_oagetproperty @file,'DateLastAccessed',@filedate output
select @filedate
/*
2012-2-3 14:15:29
*/
上面是我开启我本地设置的代码,你不需要执行。