Execute a windows program in T-SQL

[chrisdq]

Hi all,

I'm wondering Is it possible to execute a windows program using T-SQL and get the return values? If not what would be the best way? I'm using ASP and SQL Server for the website.

I need to write a store procedure script to process customers' credit cards and this needs to call a credit card gateway program and be able to get the return results to determine if the credit card is valid or not.

Any tips/help would be greatly appreciated.

Thanks and have a good day all,
Chris.

 

[SQLDenis]

any reason you wouldn't do this from ASP?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

or Any reason why you wouldn't use a program like viawarp?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[chrisdq]

Thanks all for your answers.

I reason that I'm thinking of using T-SQL for 2 reasons
1) Using T-SQL to process hundreds/thousands records would be faster than from ASP.
2) This needs to be a recurring task that will run weekly.
I haven't thought of how to execute an ASP page to run weekly but I saw that SQL Server has the job scheduling feature to execute a store procedure script.

Any thoughts guys?

Thanks in advance,
Chris.

 

[ptheriault]

2) This needs to be a recurring task that will run weekly.
I haven't thought of how to execute an ASP page to run weekly but I saw that SQL Server has the job scheduling feature to execute a store procedure script.

We use an asp page to call our viawarp application. It is scheduled to run every 5 min through windows task scheduler.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[chrisdq]

ptheriault,

I don't know if it will work with the changable data/records that we have in the database. The whole process needs to be very customizable.
How much does it cost to buy ViaWarp?
We're a small team company I don't think we can afford for the service plus we already the tools for credit card processing (Paypal).

 

[ptheriault]

you could still use the ASP page to make calls to the data and paypal. The point both Denis and I are trying to make, is this work shouldn't be done by the database.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[chrisdq]

I completely understand your point and I think it's possible to do this in ASP, in fact it would be easier for me to do it in ASP.
I'm just trying to find out if this whole thing can be done from T-SQL so that the process would be quicker and it's more secure. With ASP, people can load the page and try to hack/screw with our recurring stuff.

 

[ptheriault]

It is possible. You would have to use xp_cmdshell or use a scheduled job. In the job step you select operating system command.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[chrisdq]

Thanks Paul.

If this is possible then I'm just curious to know why you would rather recommend me to implement this in ASP than T-SQL?

Thanks again for all your help.

 

[SQLDenis]

>>I'm just curious to know why you would rather recommend me to implement this in ASP than T-SQL?

because the function of a RDBMS is to serve and store data not do all kind of other stuff and waste precious CPU cycles

When you do all that stuff you are not supposed to do you will run into performance issues sooner or later. what if you get a runaway process or something that locks your harddrive?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

If this is possible then I'm just curious to know why you would rather recommend me to implement this in ASP than T-SQL?

I couldn't have said it better than Denis did.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[chrisdq]

>>because the function of a RDBMS is to serve and store data not do all kind of other stuff and waste precious CPU cycles.

But If I use ASP to do things then doesn't it also use CPU cycles as well or running into "runaway process"?
ASP is a server side language so it will use the same server to process data as well as running it in SQL.

 

[SQLDenis]

well I have my SQL servers running on its own servers of course
Most sites are configured this way unless you have a small site without lots of traffic

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

But If I use ASP to do things then doesn't it also use CPU cycles as well or running into "runaway process"?
ASP is a server side language so it will use the same server to process data as well as running it in SQL.

Your db servers should be dedicated to just the database.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[chrisdq]

yeahh it's a good idea to host ASP server and SQL server separately.

Thanks for all the help.
You guys rock.

 

[chrisdq]

Do you guys have any suggestions for executing an ASP page as a recurring task?
Would pasting the ASP code into the Job Scheduling in SQL Server work? I haven't tried it so I don't know if it can load include files and other external stuff.

 

[ptheriault]

>>Would pasting the ASP code into the Job Scheduling in SQL Server work?
No,
execute your asp page from windows task scheduler.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[chrisdq]

Awesome.

Thanks.