How to get bank reports through web application?

Question

Where I work there are some procedures in the database (SQL Server 2008) that take hours to run and return large amounts of information that are always copied to a spreadsheet and sent to the end user.

There are even some desktop applications that can run them but this is not very practical;

Processed example:

Is there any way to automate these reports through a web application?

It would be perfect if there were some way for the user to fire the procedure and let him run through the web so that at another time he could re-enter the site and pick up the report.

I know a little bit about Asp.net, but I have no idea if you can do that...

Currently reports are left running on the user’s machine through a desktop application that generates an Excel file or through SQL Server Management Studio itself where whoever is giving the support connects to the database...

Let the procedure run on the server

And when it finishes running, copies the result to an Excel spreadsheet and sends it to the requested user.

But several of these procedures are time consuming and so I would like you to have a way to let the user do this for a web application.

Any idea?

Answer 1

For this answer I will have to give general lines, because the detailed implementation would be very extensive.

What we have in your question is a Stored Procedure In Microsoft SQL Server it is called, processes information and returns a table that is converted into a spreadsheet. Even before writing a web application to deal with this, it is possible to promote some modifications at the level of Stored Procedure which could greatly help in your work.

1. Create a report request table

I don’t know what your data format looks like, but I think of something like this:

CREATE TABLE REQUISICAO_RELATORIO
(
    REQUISICAO_RELATORIO_ID INT PRIMARY KEY IDENTITY,
    USUARIO_ID INT, -- Eu estou chutando que o usuário tenha isso, só para exemplo.
    DATA_REQUISICAO DATETIME DEFAULT getdate(),
    STATUS VARCHAR(100), -- Pode ser 'Iniciado', 'Concluido', 'Falha',
    TIPO_RELATORIO VARCHAR(100) -- Aparentemente tem tipos de relatórios. Use esse campo para preencher.
)

Each report request will insert a row in this table. A Stored Procedure will process the data normally and, when finishing the task, will update the data from this table.

2. Enter the return data in a table referenced by REQUISICAO_RELATORIO

Make a new table by inserting a foreign key column that references REQUISICAO_RELATORIO. This new table will have exactly the same return data as in your Excel spreadsheet.

To select the data, you can even select all the data at any time, more or less like this:

SELECT * FROM TABELA_COM_DADOS_DO_EXCEL
WHERE REQUISICAO_RELATORIO_ID = 1

Or using other fields. The important thing is to preserve the data to be selected at any time.

3. Create tasks that read REQUISICAO_RELATORIO and execute the Stored Procedure

To learn how to create tasks in SQL Server, see this link.

So you can schedule processing and you don’t need to keep a Microsoft SQL Server Management Studio window.

Note that after these 3 steps, the implementation of a web application becomes completely optional.