Execute ASP Script within T-SQL

[Bwintech]

Hi

I have an asp script that I need to execute as part of a SQL stored procedure, passing paramaters in between. Is this possible?

Thank you kindly for any help.

Regards,
Brett

 

[DLSQL]

Do you mean that your SQL Server Stored Procedure needs to interact with another web server to submit some data for processing and then get the results back?

Your best bet would be to create a component in VB that can open a URL, parse the results and return what you need. Then this object could be instantiated from your sproc by using the sp_oa (I think they all begin like that) stored procedures. sp_oa for Object Automation.

DL
MCDBA, MCSD, MCT, etc.

 

[Bwintech]

Hi

Thanks so much for your reply.

The only reason I need to execute an asp page is because I need to execute a COM object.

Can the object rather be executed via the Sproc sp_oa?

Regards,
Brett

 

[DLSQL]

Yes. Although when I referred to sp_oa I was referring to a set of sprocs: sp_OACreate, sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, and sp_OADestroy (there are others).

If you look these up in the BOL you should get a good idea of how to call a COM component from a sproc or sql script.

Chapter 19 of the Guru's Guide to SQL Server Stored Procedures, XML, and HTML (by Ken Henderson) has some nice examples. (I have no affiliation with the book, author, or publisher).

DL
MCDBA, MCSD, MCT, etc.

 

[Bwintech]

Hi,

Thanks so much for your reply.

I have gotten to a stage whereby I can create an instance of the object but cannot execute any of its methods. I keep on getting an 'Bad Variable Type' error.

The object is written in Delphi I think, the method is below:
function Logon(OperatorGroupID: Integer; const OperatorLogon, OperatorPassword: WideString; out ReturnValue : Integer): HResult; safecall;

My SQL is below: (@ID smallint, @return int, @login varchar, @password varchar)

EXEC @hr = sp_OAMethod @object, 'Logon', @RETURN OUTPUT, @id,@login , @password
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END

Thanks again for your help, muchly appreciated.

Kind Regards,
Brett

 

[DLSQL]

My only suggestion for your error is to carefully examine equivalency of data types between Delphi and SQL. Perhaps the Delphi Integer is 32 bit instead of 16 bit (like small int) or perhaps WideString is expecting Unicode (nvarchar)

HTH,

DL
MCDBA, MCSD, MCT, etc.