[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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
9
Medium Priority
?
671 Views
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?

Cheers

Stephen!
0
Comment
Question by:sgriffin
  • 3
  • 3
  • 2
8 Comments
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 12603144
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
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 12603182
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
 
LVL 8

Expert Comment

by:SashP
ID: 12603349
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Author Comment

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

0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 12604099
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
 
LVL 8

Expert Comment

by:SashP
ID: 12606514
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
 
LVL 8

Accepted Solution

by:
SashP earned 250 total points
ID: 12606648
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
 
LVL 2

Author Comment

by:sgriffin
ID: 12864992
Sorry , have been sick , will answer it today..
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 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