Executing an asp page from sql server.

Hi;

I don't know if this is the right forum, if not please move it.

I have an asp page using fso etc to create txt files on server. I want to call this asp page from sql server for example a table is updated. I mean I want to execute or call this file inside a trigger so a table is updated sql server will execute that asp page and create the text files i needed automatically.

Any help will be appriciated.

Thanks...

hey,

Triggers behave synchronously which means that you will have to wait for the external application (which you probably would need) to come back for the transaction to commit. In common this leads to very bad performance as well as a lot of ugly error retrieving if anything goes wrong. I would suggest using the following approach. After inserting the data into the table, write a log entry in a separate table. Write an application which can retrieve this information and call the asp page if needed. The scheduling of this application can be then either done on an OS basis (like the AT command) or SQl Server Agent, depends on where you want to have the control and wheter you have SQL Server Agent in place (e.g. It does not exists on SQL Server Express). That would act in a asynchronous way, not blocking the original transaction.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Assumption : Using SQL Server 2000

Yes.. As Jens K. Suessmeyer suggested you can convert your logic on Triggers itself.

If you don't want to write the code on SQL Server then you can write those logic as
components (ActiveX DLL - just copy paste the VB script from ASO) and you can register those on your SQL Server box, reuse it
from the SQL Server.

Declare @.oComp int
Declare @.HResult int
Declare @.Result as Varchar(8000)

Exec @.HResult = sp_OACreate 'YourCompPackageName.YourComponentName', @.oComp Output;

If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oComp, 'YourMethod', @.Result Output;

EXEC sp_OADestroy @.oComp;

If you don't want to write a component and still want to execute from SQL Server. the
following code will do. But it will degrade your performance.(NOT RECOMANDED)

Declare @.oHttp int
Declare @.HResult int
Declare @.Output as Varchar(8000)
Exec @.HResult = sp_OACreate 'MSXML2.XMLHttp', @.oHttp Output, 1

if @.HResult = 0
Begin
Exec @.HResult = sp_OAMethod @.oHttp, 'Open', NULL, 'POST', 'http://localhost', 'false'
If @.HResult = 0
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, ''
If @.HResult = 0
Exec @.HResult = sp_OAGetProperty @.oHttp, 'ResponseText', @.Output OUTPUT
If @.HResult = 0
Select @.Output
End

EXEC sp_OADestroy @.oHttp

|||

Thanks for both of yours replies, I will try.

Have a nice day.

|||I tried the example and it works great.

How would you POST XML data (or any data) to the page? I have tried the following but keep getting errors on the 'Send'. Is there something I'm missing?

Exec @.HResult = sp_OAMethod @.oHttp, 'Send', NULL, '<field name="id"/>'
AND
Exec @.HResult = sp_OAMethod @.oHttp, 'Send', '<field name="id"/>'

Thanks for any help!
sql