Delphi excecute Stored Procedure in SQL Server without waiting for it

Stef Merlijn
Stef Merlijn used Ask the Experts™
on
Hi,

In SQL Server Express I have a Stored Procedure that will rebuild or reorganize indexes based on their fragmentationlevel.
See: http://sqlfool.com/2011/06/index-defrag-script-v4-1/
This process might take a considerable amount of time in a big database.
This script uses some parameters, but I can set them all to defaults, so I can skip that part.

When my Delphi application starts the procedure, it will wait for completion, resulting in Time-out for query errors. I could extend the time-out but I would still have to wait for completion. Which I don't wait.

I only want to start the procedure and work in my application again. I need no result back. Let SQL Server do the work in the background. The SP has it's own timelimit which can be set.

How can I set this up in a way that meets my needs?
Please keep in mind that I run on SQL Server Express, which might limit my options.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
What you do is to schedule the stored procedure using SQL Agent job and set the timings there. You can get all the information about when the procedure gets executed and if it fails you will get a notification also .

Read more about SQL jobs from -
https://technet.microsoft.com/en-us/library/ms191439(v=sql.110).aspx

Windows scheduler - You can also use windows scheduler the schedule the proc.

Good luck.
Stef MerlijnDeveloper

Author

Commented:
Thanks, but how does this help executing it from Delphi?

Also how often would you suggest to run the SP in a schedule?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You don't have to use delphi at all. Schedule a sql job using the link I provided. In sql job we have to provide the procedure name.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Stef MerlijnDeveloper

Author

Commented:
I don't think SQL-jobs are available in SQL Server Express
Stef MerlijnDeveloper

Author

Commented:
Also: I need to be able to accomplish this for my users as well. So I need to add the scheduled job also from inside my Delphi application.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
No you cannot schedule the jobs from Delphi , you can schedule the SQL job using domain account from SQL Server, Why the users need to run the fragmentation job from the delphi. Its only for the health of the sql, DBAs will have to handle that.
Stef MerlijnDeveloper

Author

Commented:
My customers are stand-alone users with poor computer knowledge. I need to solve this from a distance and automatically.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
So you have to create a SQL job query and deploy that on their DB servers and add notifications to the proper email addresses. This is little bit complex so the person should have at least 2-3 years of DB experience to understand fragmentation, jobs etc.
Stef MerlijnDeveloper

Author

Commented:
Defragmentation can be completely in the background. My customers have no need for information on that. They work on a stand-alone computer (no server).
They only want an updated database so it is as quick as possible.
The script I mentioned above is all that is needed to handle that, but now I need it to run automatically without bothering my customer.

Though I would prefer to execute the SP from inside my application, but that station has passed I think, as it would have the customer wait for the SP to finish, which might take a long time.
Geert GOracle dba
Top Expert 2009

Commented:
Normally a dba does jobs like that

As pawan said, schedule the proc

The schedule commands can also be executed from delphi
Geert GOracle dba
Top Expert 2009

Commented:
Stef MerlijnDeveloper

Author

Commented:
Hi Geert,
In Delphi I could do something like this:
      ADOConnectionSQL.Execute('EXEC dbo.dba_IndexRebuildOrReorganize @executeSQL=1, @minFragmentation=10, @rebuildThreshold=30, @printCommands=0, @debugMode=0, ' +
                               '@printFragmentation=0, @forceRescan=0, @maxDopRestriction=1, @minPageCount=8, @maxPageCount=NULL, @defragDelay=''00:00:05'', ' +
                               '@defragOrderColumn=''range_scan_count'', @defragSortOrder=''DESC'', @excludeMaxPartition=1, @timeLimit=NULL, ' +
                               '@database=' + QuotedStr(ActieveDatabase));

Open in new window

But Delphi will still wait for execution to finish.

Scheduling a job in SQL Server can't be used as I use SQL Server Express.
How would executing a schedule command look like?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
I think you can do something like this but these processes will take time depending on the size of the database. So I am not sure whether your session will expire or not. Good luck :)
Stef MerlijnDeveloper

Author

Commented:
What would happen I a vb-script (*.vbs) would be run from withing Delphi with ShellExecute.
In the scriptfile I could then open an ADOConnection and run the Stored Procedure from there?
ShellExecute(Handle, 'open', 'cscript.exe', '"c:\program files\defrag\defragall.vbs"', nil,  SW_MINIMIZE);

Open in new window

Would Delphi wait for it's execution (to complete the SP)?
Or will it only take the time to start the vb-script?
Sinisa VukSoftware architect
Top Expert 2012

Commented:
Try following code:
...
qry: TAdoQuery;
...
  qry.ConnectionString := ....;

  qry.SQL.Text := 'call lengthy sql'; 
  qry.ExecuteOptions := [eoAsyncExecute, eoExecuteNoRecords];
  qry.ExecSQL;

Open in new window


Using Ado with appropriate execute options will help you.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@Author - i think your last option will work fine. Make the call asynchronously.
Geert GOracle dba
Top Expert 2009

Commented:
Sql Express is free ... and it has it's limitations
bummer,
an sql internal job is built especially for these situations

you obviously made a wrong choice for the database.
Oracle Express limitation is 11G in size.
It doesn't prohibit background jobs

Think about asking a client if the have an SQL Server database
and if you can deploy your schema within that database.
Most often, they will be more than willing.
It costs them less to include it in there existing setup instead of having to maintain and support a single vendor's database.

AsyncExecute, ShellExecute, or whatever ...  
all do the same: start thread, start call ... wait until Jezus Christ returns or procedure is finished ... return
You might have a problem if Jezus Christ returns a little too soon :)

Bottomline:
Bad database choice.
Your requirements don't fit the description of that database.

Either find a workaround (threaded) or change database
Stef MerlijnDeveloper

Author

Commented:
qry.ExecuteOptions := [eoAsyncExecute, eoExecuteNoRecords];
This works perfectly. I get back control in my application immediately.

But, when I shutdown my application I get an EOleException on TADOCommand.ClearActiveConnection. "Can't be executed when running aSynchronically."

Of course I would like to let the reindex-SP to keep running, but close my application without a problem. This also makes me wonder what would happen when the computer is shutdown. How will SQL Server handle that, while indexing is not complete yet?
Oracle dba
Top Expert 2009
Commented:
doesn't work like that

write a service to execute this jobs
place the service on a client application server
(or a specific pc in the client network)

an application server usually doesn't go down a lot
typically once a month for patching

Datasnap is very easy for this:
https://edn.embarcadero.com/article/41330

in your app, call a datasnap procedure

this will let the service run the procedure
and your app is free to do anything you want, like shutdown

think of this like a reporting server.
a client asks for a report
> a job is placed in the queue for the report server
> the jos is executed by the reportserver
> a message is sent (usually via mail) to the requestor, indicating where the report can be found

in this case, you don't really need a return message
Stef MerlijnDeveloper

Author

Commented:
@Pawan Kumar: Thanks for suggesting a Windows schedule.
@Geert Gruwez: I'll go with the DataSnap approach.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial