Delphi excecute Stored Procedure in SQL Server without waiting for it

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.
Stef MerlijnDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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 MerlijnDeveloperAuthor 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 ExpertCommented:
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.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Stef MerlijnDeveloperAuthor Commented:
I don't think SQL-jobs are available in SQL Server Express
Stef MerlijnDeveloperAuthor 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 ExpertCommented:
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 MerlijnDeveloperAuthor Commented:
My customers are stand-alone users with poor computer knowledge. I need to solve this from a distance and automatically.
Pawan KumarDatabase ExpertCommented:
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 MerlijnDeveloperAuthor 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 dbaCommented:
Normally a dba does jobs like that

As pawan said, schedule the proc

The schedule commands can also be executed from delphi
Geert GOracle dbaCommented:
Stef MerlijnDeveloperAuthor 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 ExpertCommented:
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 MerlijnDeveloperAuthor 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 architectCommented:
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 ExpertCommented:
@Author - i think your last option will work fine. Make the call asynchronously.
Geert GOracle dbaCommented:
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 MerlijnDeveloperAuthor 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?
Geert GOracle dbaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stef MerlijnDeveloperAuthor Commented:
@Pawan Kumar: Thanks for suggesting a Windows schedule.
@Geert Gruwez: I'll go with the DataSnap approach.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.