Re: T-SQL and OLE Automation
From: Greg Linwood (g_linwoodQhotmail.com)
Date: 08/17/04
- Next message: Tibor Karaszi: "Re: Really tough Stored Procedure Debug Problem."
- Previous message: Roji. P. Thomas: "Re: Really tough Stored Procedure Debug Problem."
- In reply to: Larry Menzin: "Re: T-SQL and OLE Automation"
- Next in thread: Larry Menzin: "Re: T-SQL and OLE Automation"
- Reply: Larry Menzin: "Re: T-SQL and OLE Automation"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 17 Aug 2004 20:42:14 +1000
Hi Larry
You definitely can achieve this with VB6 ActiveX.exe's or ActiveX.dlls, but
there are probably other methods as well. It would be a pity to cut new
components from VB6 now that VB.Net is so well established as that code will
become a maintenance legacy as soon as it's written. I don't have VB6 handy
on my notebook right now, but I'll have a crack at ripping the code to check
file size as it's pretty straight forward:
(a) Open VB6 & choose ActiveX.dll project type
(b) Name the project VBFileLib - right click on the project properties &
enter the project name there
(c) Rename the class file which is in the project explorer by default to
"cFileChecker"
(d) Replace the code in the code window with"
Option Explicit
Public Function GetFileBytesSize (fileaddress as String) As Long
Dim fs As Object
Dim f As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(fileaddress)
GetFileBytesSize = f.Size
End Function
(e) Click the File menu & select Make VBFileLib.dll (or something like that)
(f) Choose a path to save the .dll & remember where it is.
(g) Once the compiler finishes (should just be a few seconds), you'll have a
.dll which is installed on the machine you compiled it (your workstation).
If you are running your SQL Server on that machine, you can just leave the
.dll where it is & it should be fine. If your SQL Server is another
computer, you need to copy the file to a directory on that server & install
it, using the regsvr32.exe utility. It's pretty easy - you simply open a
command prompt, navigate to the directory you copied the .dll to & issue the
command "regsvr32 VBFileLib.dll" - you should get a message saying that the
registration succeeded.
Then, the TSQL you use to run that code would look something like:
declare @objref integer
, @rv integer
, @filesize integer
, @src varchar(255)
, @desc varchar(255)
exec @rv = sp_OACreate 'VBFileLib.cFileChecker', @objref out
if @rv <> 0
begin
exec sp_OAGetErrorInfo @objref, @src out, @desc out
print 'Error Source: ' + @src + ' Error Description: ' + @desc
goto oleaut_tear_down
end
exec @rv = sp_OAMethod @objref, 'GetFileBytesSize', @filesize out,
'c:\somebigfile.ext'
if @rv <> 0
begin
exec sp_OAGetErrorInfo @objref, @src out, @desc out
print 'Error Source: ' + @src + ' Error Description: ' + @desc
goto oleaut_tear_down
end
select @filesize as "filesize"
oleaut_tear_down:
exec @rv = sp_OADestroy @objref
The Scripting.FileSystemObject library referenced in this example is a
system library provided by Microsoft & you might need to install it as well.
You can read up on it's capabilities in MSDN here:
http://msdn.microsoft.com/library/en-us/vbenlr98/html/vaobjFileSystemObject.asp
Please also keep in mind that I haven't actually run any of this code, so do
post back if there are any problems & I'll try & help further.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Larry Menzin" <LarryMenzin@discussions.microsoft.com> wrote in message
news:A8B18414-98D5-48AC-9D02-6B4D23F32AEF@microsoft.com...
> Greg,
>
> Thanks for the help. I am a bit new at this. My current effort involves
> verifying byte counts of text files to be used in an ETL process. My
simplest
> task would be to pass a directory path/filename to a VB ActiveX.EXE using
> T-SQL and to return the byte count of the file via (Windows API?) back to
SQL
> server to store in a table. I usually find sample code on MSDN for most
> things, but perhaps this will be a little more difficult!
>
> Thanks,
>
> Larry
>
> "Greg Linwood" wrote:
>
> > Hi Larry.
> >
> > To write the VB6 app, you simply create a class library using a project
of
> > either the ActiveX.dll or ActiveX.exe project types. They are
essentially
> > the same in terms of how you go about developing them from the VB side,
> > except that a library created as a .dll will run inside the SQL Server
> > process, therefore sharing & competing for memory with the main SQL
process.
> > A library created as type ActiveX.exe will run out of the SQL Server
> > process, using it's own memory and can therefore be run remotely on
another
> > machine (using DCOM or COM+) and / or under a different user context.
> >
> > Once you choose what library type you want to create, you simply add
class
> > files, add properties & methods to implement whatever behaviour you
need,
> > being careful of the parameter type conversion issues I referred to
earlier.
> > To deploy a .dll, you make it, using the VB6 file/make command, then
copy to
> > the server & use the regsvr32.exe utility program, which is very
> > straight-forward. To deploy an ActiveX.exe, you make it, copy to the
server
> > then simply double click it as it has self-registering code.
> >
> > Creating COM libraries with VB6 is probably better discussed in a VB
> > newsgroup but if you want more help here I'm happy to help. So
definitely
> > post back if you've got more questions. I'm not sure of any specific KB
> > articles that actually show how to create a VB6.dll or ActiveX.exe but
do
> > make sure you understand the difference between an ActiveX.exe and a
> > standard .exe as they're different. You can't access standard .exe's
from
> > SQL Server using OLE. You need either an ActiveX.dll or ActiveX.exe
project
> > to do that.
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Larry Menzin" <LarryMenzin@discussions.microsoft.com> wrote in message
> > news:B54EB985-707F-4B81-AB0D-C49C042EB152@microsoft.com...
> > > Greg,
> > >
> > > Thanks. I saw those pages in BOL. They are examples of SQL server
> > accessing
> > > SQL server via OLE automation. My application is to run VB6 EXE files
via
> > SQL
> > > OLE automation and I'm not sure how to write the application on the
VB6
> > side
> > > to make this automation work. Does MS have anything to help me there?
> > >
> > > Thanks,
> > >
> > > Larry
> > >
> > > "Greg Linwood" wrote:
> > >
> > > > Hi Larry
> > > >
> > > > There's a good example script in Books Online here:
> > > > http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_2ktw.asp
> > > >
> > > > Another article nearby in BOL worth looking at is
> > > > "OLE Automation Return Codes and Error Information", at:
> > > > http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_14_8k6m.asp
> > > >
> > > > Also, do make sure youu carefully check data type compatability
between
> > VB &
> > > > TSQL as many get tripped on this when starting out with TSQL / OLE.
> > > > Basically, an Int is not an Int between these languages - a TSQL int
is
> > a
> > > > VB(6) Long, so do take care when setting out method parameters,
returns
> > etc.
> > > >
> > > > HTH
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "Larry Menzin" <LarryMenzin@discussions.microsoft.com> wrote in
message
> > > > news:4F43C4E3-D9C3-4EA9-BFA9-C6549F144C4C@microsoft.com...
> > > > > I am looking for some sample code (preferably VB) to
> > > > > create an OLE Automation Server that can be accessed via T-
> > > > > SQL stored procedures using the OLE system stored
> > > > > procedures. There appears to be little documentation on
> > > > > how to use this feature. Even a program in VB allowing me
> > > > > to return "hello world" via T-SQL would be very helpful.
> > > > >
> > > > > This seems to be a very powerful feature that may allow me
> > > > > to integrate external applications with SQL Server via T-
> > > > > SQL stored procedures. Use of XP_CMDSHELL only returns a 0
> > > > > or 1 and results in a non-integrated solution.
> > > > >
> > > > > Do you have any ideas on this?
> > > > >
> > > > > Larry Menzin
> > > > > American Techsystems Corp.
> > > > > .
> > > > >
> > > > >
> > > > > --
> > > > > Larry Menzin
> > > > > American Techsystems Corp.
> > > >
> > > >
> > > >
> >
> >
> >
- Next message: Tibor Karaszi: "Re: Really tough Stored Procedure Debug Problem."
- Previous message: Roji. P. Thomas: "Re: Really tough Stored Procedure Debug Problem."
- In reply to: Larry Menzin: "Re: T-SQL and OLE Automation"
- Next in thread: Larry Menzin: "Re: T-SQL and OLE Automation"
- Reply: Larry Menzin: "Re: T-SQL and OLE Automation"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
