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

Posted on 2004-11-17
Last Modified: 2008-02-01
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?


Question by:sgriffin
    LVL 10

    Expert Comment

    you can use



    system stored procedures.

    Look in BOL or MSDN for examples and details

    LVL 10

    Expert Comment

    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

    LVL 8

    Expert Comment

    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
    LVL 2

    Author Comment

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

    LVL 10

    Expert Comment

    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
    LVL 8

    Expert Comment

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

    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

    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
    LVL 8

    Accepted Solution


    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
    LVL 2

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now