Re: T-SQL and OLE Automation

From: Greg Linwood (g_linwoodQhotmail.com)
Date: 08/16/04 

Date: Tue, 17 Aug 2004 07:04:36 +1000

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.
> >
> >
> >

Relevant Pages

  • Re: Create Storedprocedure with VB6? Is this possible?
    ... VB6 and ADO no .NET ... can SQL Server Management Studio." ... Can VB6 create a stored procedure on the fly. ... something similar) exactly what you're sending to the db engine (via ADO), ...
    (microsoft.public.vb.database.ado)
  • Re: Walkthrough Instruction Error
    ... What I said was a VB6 app can do threading using activex.exe but that is out of process. ... What Olaf implied was that his external library written in some other language isn't out of process, yet what he did say was his threading was not light weight and that his library uses pipes. ... What's Olaf's libraries do *Exactly* is hard to tell because he won't put the source code out there, but we do know there is also marshalling occurring. ...
    (microsoft.public.vb.general.discussion)
  • Re: T-SQL and OLE Automation
    ... components from VB6 now that VB.Net is so well established as that code will ... Choose a path to save the .dll & remember where it is. ... If you are running your SQL Server on that machine, ... >> Greg Linwood ...
    (microsoft.public.sqlserver.programming)
  • Re: Compact SQL Database and ADO
    ... SQL Server 3.5 Compact 3.5 SP1 Database ... Set prmID = .CreateParameter("plngID", adInteger, ... Set prmText = .CreateParameter("pstrText", adVarWChar, ... VB6 to crash. ...
    (microsoft.public.data.ado)
  • Re: Calling COM object from Stored Procedure
    ... I'd recommend that VB6.0 is probably the easiest route for simple libraries ... VB.Net is significantly easier to program certain ... >I know the next release of SQL Server allows .Net stored procedures, ...
    (microsoft.public.sqlserver.programming)