Using TSQL , want to call VBS from TSQL or include code in TSQL to open CreateObject("a.example") and call functions within a.example

you can use
 sp_OACreate

sp_OASetProperty

sp_OAMethod
sp_OAGetErrorInfo

system stored procedures.

Look in BOL or MSDN for examples and details

/Richard

eg
declare @hmsg Int, @PrpValue varchar(10), @Return varchar(100)
set @propvalue = 'MyValue'

-- Create reference to your COM object

exec sp_OACreate 'Module.Class', @hmsg output

-- set a property
exec sp_OaSetProperty @hmsg, 'PropertyName', @PropValue

--Execute Function
Exec sp_OAMethod @hmsg, 'MethodName. @Return

-- tidy up
exec sp_OADestroy @hmsg

/Richard

Hi Stephen

Is the DLL the only way to update the database?

If no then ... What is the legacy database?  Have you considered DTS?  Have you considered a linked server?

I would not recommend a linked server generally if the remote server is not an enterprise database (which it sounds like it is not) however depending upon your particular situation it may be appropriate.

DTS however may be perfect if some amount of latency is not a problem, if latency is an issue it is not difficult to start a DTS routine from TSQL.

If you must use the DLL then you have little choice than to create an external process that you will call from SQL Server, unless the DLLs meet the necessary inteface requirements you will not be able to call it from SQL Server, they will not unless they have been designed specifically to function as a SQL extended procedure.

However you could write a COM object (using VB, C++, ... whatever your preference) and call the COM component from SQL Server.

Perhaps a better solution is to code a NT Service application that monitors the SQL Server and inserts into the legacy database using the DLL, that way you are removing the necessity of SQL Server needing to call the DLL.  It should be a fairly simple process to create a table to act as a que to receive the required updates, then use the service to process records in the que and submit them to the DLL, then mark them as processed or delete them once the DLL has confirmed the update, this way there is no chance that data that should be submitted to the DLL will not be even if the service crashes.

You will need to detail your requirements to get further help.

Cheers Sash

Its actually a 3rd party DLL that we need to call from TSQL.. this then updates the Database of the 3rd Party...

then use the SP_oa method
if the 3rd party DLL is comple, you may want to think about creting a VB DLL wrapper that access data from the SQL asn then calls the 3rd party DLL - as you have dne with the VBs
/Richard

Richard,
The sp_OA method only works if the dll exposes an OLE interface.  If not you cannot use it.

Stephen,
You can call a vbs script very simply using xp_cmdshell however providing access to this extended proc is a security risk so be careful.
Have a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

If you can access the DLL via vbscript then my limited knowledge of vbscript tends to lean me towards the fact that the DLL may be an OLE object as I did not think that the "declare" command was available in vbscript.

You may be able to recode the vbscript into TSQL however I am always reluctant to use COM objects with SQL Server as the risk of hung processes is introduced.

Cheers Sash

Sorry , have been sick , will answer it today..