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

Hi ,

I want to call a VBS which updates another legacy DB via a registered DLL from TSQL.

I can run a VBS file outside of TSQL which does the udpate but data needs to be passed from TSQL?

Any ideas?

Cheers

Stephen!
LVL 2
sgriffinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RichardCorrieCommented:
you can use
 sp_OACreate

sp_OASetProperty

sp_OAMethod
sp_OAGetErrorInfo

system stored procedures.

Look in BOL or MSDN for examples and details

/Richard
0
RichardCorrieCommented:
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
0
SashPCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

0
RichardCorrieCommented:
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
0
SashPCommented:
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
0
SashPCommented:
sgriffin,

For an example of create object or in TSQL sp_OACreate

declare @FileName varchar(255)
declare @doc varchar(8000)
DECLARE @FS int, @hr int, @file int

set @FileName = 'c:\TSQL_FSO.txt'  
SET @doc =
'Line 1
Line 2
Line 3
'

EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @hr <> 0 select 'Error Occured creating FileSystemObject', @hr

--Open a file
execute @hr = sp_OAMethod @FS, 'CreateTextFile', @file OUT, @FileName
IF @hr <> 0 select 'Error Occured creating FileSystemObject', @hr

--Write Text1
execute @hr = sp_OAMethod @file, 'WriteLine', Null, @doc
IF @hr <> 0 select 'Error Occured Writing Line', @hr

execute @hr = sp_OAMethod @file, 'Close', Null
IF @hr <> 0 select 'Error Occured Closing File', @hr

EXECUTE @hr = sp_OADestroy @file
EXECUTE @hr = sp_OADestroy @FS
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sgriffinAuthor Commented:
Sorry , have been sick , will answer it today..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.