Currently we generate month statements (bank monthly statements) from asp.net application.
1. Data is in sql server tables
2. we use SSRS reports with parameters to run report and generate pdf files
3. Have batch file that calls SSRS report passing parameter values
What we did is
asp.net application calls stored procedure.
In stored procedure we call batch file by using xp_Cmdshell and passing parameter values in a loop.
We do this for each client\account in a loop.
Security team want to disable using xp_cmdshell due to security issues. So we are planning to re-architect this.
We were thinking of moving all the logic into web service (like running SSRS reports and generating pdf statements) etc
our asp.net application web server is in DMZ zone. Sys admins do not opens ports from this server. We have only one port open to talk to database server which is sitting on our end.
That is the reason, we had to implement through database.
What are the best options for this kind of requirement.
some one suggested introducing middle tier between web server and sql server. Then middle tier can start the process by spawning sub process.