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!

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

Execute Process Task Error on copy

Hi,

I am trying to run a very simple copy command from an execute process task:

copy O:\myFolder\myFile*.txt D:\myFolder

I have a working directory set also.

However, there is an error icon on the task, and if I attempt to run it I get the following error:

Error at Execute Process Task [Execute Process Task]: File/Process "" does not exist in directory "copy O:\myFolder\myFile*.txt D:\myFolder".

Error at Execute Process Task: There were errors during task validation.

This command works fine from the command line. What am I doing wrong here?

Thanks

O:\myFolder\myFile*.txt D:\myFolder

The above should go in the arguments parameter box if you're not doing that.|||

It seems that it's looking for an executable, like a .bat file. It doesn't seem to recognize the dos copy command.

for example:

Executable: copy

Arguments: o:\myFolder\myFile.txt D:\myFolder

but it still complains that copy is not an executable.

So, it seems I have to put this in a batch file, which seems dumb, or I need to use the File System Task, but I don't know how to specify which files to copy using this method.

?

|||Instead, try using the full path to xcopy

c:\windows\xcopy.exe|||

xcopy is not installed

where can one find just the "copy" command exe?

|||

sadie519590 wrote:

xcopy is not installed

where can one find just the "copy" command exe?

xcopy is not installed? You're kidding me? It's distributed with Windows.

There is no "copy.exe" as it's an internal command within "command.exe or cmd.exe."|||

Actually you are correct. I was thinking of robocopy.

At any rate, SSIS is complaining it can't find the xcopy exe. Never mind at this point, I'm not going to use it for now.

But for future use, it seems to me that there should be a way to use the File System task to specify file names with wildcards?

|||

sadie519590 wrote:

Actually you are correct. I was thinking of robocopy.

At any rate, SSIS is complaining it can't find the xcopy exe. Never mind at this point, I'm not going to use it for now.

But for future use, it seems to me that there should be a way to use the File System task to specify file names with wildcards?

You need to specify the full path to xcopy.exe in the Executable parameter..|||But I'm still wondering if I can use wild cards to specify files names in the File System Task when deleting files? Seems like this would be easier approach if possible.|||

sadie519590 wrote:

But I'm still wondering if I can use wild cards to specify files names in the File System Task when deleting files? Seems like this would be easier approach if possible.

I think the way to do this is to use a foreach loop to scan the source directory for the files you want to copy (you can use a wildcard there) and then have it populate some variables (source filename, etc...). Then, inside the foreach loop, you use a filesystem task to perform the operation for each file found by the foreach loop.

Does that make sense?|||Yes, thanks. I'm surprised there's no built-in way to do this. Product request, I guess?|||

sadie519590 wrote:

Yes, thanks. I'm surprised there's no built-in way to do this. Product request, I guess?

Sure thing! http://connect.microsoft.com/sqlserver/feedback

Execute Process Task arguments

We are attempting to use SSIS execute process task.

In a cmd.exe session the executable runs with normal arguments:

mycmd < myscript.txt > foo.txt 2>error.txt

We have attempted to several permutations of this without success. We declared variables and assigned them to stdin, stdout, stderr. However, mycmd opens in the SSIS session as if no arguments have been supplied.

Is there some expression that is required to connect the stdin/out/err variables in the Arguments within Execute Process Task. The help/examples are terse.

Redirections using < and > are not arguments, they are special symbols treated by cmd.exe.

To get the same behavior from Execute Process Task, you'll need to read content of myscript.txt into a variable and redirect program input to this variable; redirect output and errors to two other variables, then after program ends save these variables into foo.txt and error.txt.

But the easiest way to solve it is to use the 'cmd.exe' as the task executable, and supply your command (/C mycmd < myscript.txt > foo.txt 2>error.txt) as agruments for cmd.exe (/C added to instruct cmd.exe to executed specified command).|||

Thanks! We somehow missed the /C switch and that solved the problem.

Loading myscript.txt into a variable for use by stdin seems a bit problematic in our situation since it could be several hundred lines long and contain a wide spectrum of characters related to regular expressions that just seem like the perfect opportunity to tip it over.

We may look at the use of variables in the argument and see if we can move the scrum ahead.

|||Hello.

I'm trying to use the "Execute Process" task to compress several ASCII files in a folder in one ZIP archive. The command line for it looks as follows:

c:\programme\7zip\7z.exe a -bd U:\Projekte\TFG\Software\Log_storage\SCD_Demo_isHASH_34.zip SCD_Demo_isHASH_*.log

The result file has size of 2 KB.

When I put this command in the "Arguments" property of the "Execute Process" component (with "/C" switch and cmd.exe as executable), I get an empty ZIP file. What could be a reason for it? Has anyone already experienced such behaviour?

Regards,
Andrey