Re: Querying File Size in SQL Server

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Mark (anonymous_at_discussions.microsoft.com)
Date: 09/20/04 

Date: Mon, 20 Sep 2004 07:05:08 -0700

I can write a VB Script that can take care of files. BUt
the thing is there will be multiple files in this location
and I would want to return a collection object (Array)
from VB. How would I get a handle on the collection object
in SQL Server since SQL Server doesn't support Arrays.

Are there any specific restrictions the way you create a
DLL in VB for it to be used as an Extended Proc in SQL
Server. I have never created an extended proc earlier. Any
insights will be helpful. I looked at BOL and didn't find
it useful in extended proc topic.

Or is there any other way to handle this situation? Does
someone have similar code to capture file size in VB and
have it used in SQL Server for mailing alerts??

Thanks in Advance.

Mark

>-----Original Message-----
>1. You can create a extended stored procedure with C++ or
VB. Let this
>extended stored proc get the file handle and checking of
file size. Then
>return 1 or 0 for indication of sending email using
XPSMPT.
>
>2. You can use CDONTS object to send email as below:
>
> Set objNewMail = Server.CreateObject("CDONTS.NewMail")
> objNewMail.BodyFormat = 0
> objNewMail.MailFormat = 0
> objNewMail.From = emailFrom
> objNewMail.To = emailTo
> objNewMail.Subject = "email subject"
>
> objNewMail.Body = HTMLText
> objNewMail.Send
> Set objNewMail = Nothing
>
>"Mark" <anonymous@discussions.microsoft.com> wrote in
message
>news:431c01c49ebf$adb4dcb0$a601280a@phx.gbl...
>> I need to achieve following task.
>>
>> I need to monitor the size of a file in OS at a certain
>> location. If the file exists and the current size is
over
>> 5 GB, I need to send an alert email.
>>
>> 1. How would you accomplish this? Should I use XPSMPT to
>> send email from SQL Server? If so, how do you get a
handle
>> on File Size from SQL Server??
>>
>> 2. I have tried using files filesystemObject as well. As
>> you can see from the following code.
>>
>> ' VBScript source code BEGINS
>> Dim fso, fldr, f1, fc
>> Set fso = CreateObject("Scripting.FileSystemObject")
>> Set fldr = fso.GetFolder("I:\Development\SQL")
>>
>> ' Get a File object to query.
>> Set f1 = fso.GetFile("\\UNC\SQL$\TestDB.BAK")
>>
>> WScript.Echo "Folder name is: " & fldr.Name & vbCRLF &
>> f1.name & "::" & f1.size
>>
>> Set fc = fldr.Files
>> Dim s
>>
>> For Each f2 in fc
>> s = s & f2.name & "::Size::" & f2.size & vbCRLF
>> Next
>> ' VBScript source code ENDS
>>
>> I can check the size here, but how do I send email now??
>>
>> I am kinda stuck as to how should I proceeed?? I don't
>> have to use XPSMTP, I am just looking for all ideas that
>> anyone can throw here.
>>
>> If someone has some sample code for me, I will be glad
to
>> have that tried here.
>>
>> Thanks in Advance.
>
>
>.
>

Relevant Pages

  • Re: Extended SP ?
    ... You cannot do this in SQL Server 2000. ... procedures in amanged code and even triggers can be written in .NET ... "Ashkan Daie" wrote in message ... That is why I am pursuing the extended proc method. ...
    (microsoft.public.sqlserver.programming)
  • Re: Querying File Size in SQL Server
    ... Set objNewMail = Nothing ... > on File Size from SQL Server?? ... > ' VBScript source code BEGINS ... > Dim fso, fldr, f1, fc ...
    (microsoft.public.sqlserver.programming)
  • Lookback connection inside an Extended Proc using ADO
    ... I am trying to do a loopback connection to Sql Server from inside an ... Extended Stored Procedure that uses the TAdo... ... is executed after the call to the extended proc. ...
    (borland.public.delphi.database.ado)
  • Querying File Size in SQL Server
    ... on File Size from SQL Server?? ... ' VBScript source code BEGINS ... Dim fso, fldr, f1, fc ... Set fldr = fso.GetFolder ...
    (microsoft.public.sqlserver.programming)