Re: User-Defined Function

From: Gert-Jan Strik (sorry_at_toomuchspamalready.nl)
Date: 04/14/04 

Date: Wed, 14 Apr 2004 22:07:50 +0200

Hahaha, yep, you found a way... I shouldn't be surprised, because you
are always up for a challenge! (A little system hacking is necessary,
but what the heck :-)

I must admit that I have also used xp_cmdshell with the 'dir' command on
several occasions, but just to investigate problems...

It doesn't even have to be this elaborate with a linked server. Does the
following code work?

create function f (
   @filename varchar(3900)
) returns datetime as begin
set nocount on
Declare @line varchar(4000)
Declare @cmd varchar(4000)
set @cmd = 'dir ' + quotename(@filename,'"') + ' /TW'

create table #t(output varchar(4000))
insert into #t exec master..xp_cmdshell @cmd

declare c cursor scroll for select output from #t
open c
fetch absolute 6 from c into @line
if @@fetch_status<>0
  set @line=current_timestamp
close c
deallocate c
drop table #t

return (
  select cast(substring(@line,1,16) as datetime)
)
end

But seriously, nobody should ever write an application this way. If it
is to be used for an import then the OP might have a look at DTS
packages and it's possibilities. If it needs to be part of a regular day
to day application I would definitely consider doing this outside the
RDBMS (which was never meant for it), or search the internet for a
suitable extended stored procedure.

Gert-Jan

Steve Kass wrote:
>
> Gert-Jan,
>
> Actually, this is possible, but without question not recommended,
> since it is slow and has all kinds of concurrency problems, at least the
> way I know how to do it. What you can do is use xp_cmdshell to write
> the directory information to a text file through a linked server and
> then read the text file to get the information you want. The linked
> server setup and an example of how to get directory information (but not
> through a function) is at
> http://users.drew.edu/skass/sql/TextDriver.htm. Here's what of that
> you'll need to do:
>
> Create a linked server to any local directory with the text driver:
>
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0', 'e:\txtsrv', NULL, 'Text'
>
> In the directory of the linked server, create a file called schema.ini
> with this section, to define the structure of the text table holding
> directory information:
>
> [directory.txt]
> Format=FixedLength
> CharacterSet=OEM
> ColNameHeader=False
> DateTimeFormat=mm/dd/yyyy hh:nn
> Col1=FileDate DateTime Width 17
> Col2=AMPM Text Width 1
> Col3=Size Currency Width 21
> Col4=Name Text Width 20
>
> Once that is done, the following function will work:
>
> create function f (
> @filename varchar(80)
> ) returns datetime as begin
> declare @cmd varchar(300)
> set @cmd =
> 'dir ' + cast(quotename(@filename,'"') as varchar(320))
> + ' > E:\txtsrv\Directory.txt'
> exec master..xp_cmdshell @cmd
>
> return (
> select dateadd(hour,
> case when ampm='a' then 0 else 12 end,
> FileDate)
> from txtsrv...Directory#txt
> where @filename like '%\' + [Name]
> )
> end
> go
>
> select dbo.f('E:\Program Files\Ocelot\Readme.txt')
> -- in my case, this returns 2001-12-28 04:00:00.000
> go
>
> Let me say again that actually doing this is a really, really, really
> bad idea, in my opinion, at least on anything but a single-user
> development machine! But it can be done...
>
> Steve Kass
> Drew University
>
> Gert-Jan Strik wrote:
>
> >This is not possible with just using a UDF. There may be an extended
> >stored procedure to satisfy your needs, but I would not be surprised if
> >no suitable procedure is currently available. In that case, you would
> >have to write (or buy) your own extended stored procedure.
> >
> >HTH,
> >Gert-Jan
> >
> >
> >"tuand2001@yahoo.com" wrote:
> >
> >
> >>Hi All,
> >>I need to create a sql user-defined function to give me a
> >>last modified date of a physical file on a network.
> >>
> >>input would be a full path of a file.
> >>
> >>Thanks,
> >>Tom
> >>
> >>
> >
> >
> > 

-- 
(Please reply only to the newsgroup)

Relevant Pages

  • Re: User-Defined Function
    ... server setup and an example of how to get directory information (but not ... Create a linked server to any local directory with the text driver: ... declare @cmd varchar ... >have to write your own extended stored procedure. ...
    (microsoft.public.sqlserver.programming)
  • Refreshing and recompiling views fails (linked server)
    ... I have a stored procedure that refreshes and recompiles all views. ... problem is that it fails and stops when it reaches a view containing ... data from a linked server. ... DECLARE @refresh_name VARCHAR ...
    (microsoft.public.sqlserver.programming)
  • Problem with Stored Proc and linked server
    ... I have setup a Sybase Linked Server and am trying to run a stored procedure ... When I try to execute the sproc, it fails with an error telling ... declare @V2 varchar ...
    (microsoft.public.sqlserver.programming)
  • Re: User-Defined Function
    ... You can't access temporary tables within a UDF, ... >It doesn't even have to be this elaborate with a linked server. ... > select castas datetime) ... >>server setup and an example of how to get directory information (but not ...
    (microsoft.public.sqlserver.programming)
  • RE: Stored Procedure to Connect a DBF File
    ... but you might be able to create a Linked Server to the ... after setting up an ODBC connection to it. ... > of DBF file using / with in the Stored Procedure? ...
    (microsoft.public.sqlserver.server)
Quantcast