Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Delphi excecute Stored Procedure in SQL Server without waiting for it

Posted on 2017-10-13
20
Medium Priority
?
63 Views
Last Modified: 2017-10-16
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.
0
Comment
Question by:Stef Merlijn
  • 9
  • 6
  • 4
  • +1
20 Comments
 
LVL 35

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 42329783
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.
0
 

Author Comment

by:Stef Merlijn
ID: 42329812
Thanks, but how does this help executing it from Delphi?

Also how often would you suggest to run the SP in a schedule?
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 42329817
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.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Stef Merlijn
ID: 42329819
I don't think SQL-jobs are available in SQL Server Express
0
 

Author Comment

by:Stef Merlijn
ID: 42329823
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.
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 42329875
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.
0
 

Author Comment

by:Stef Merlijn
ID: 42329882
My customers are stand-alone users with poor computer knowledge. I need to solve this from a distance and automatically.
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 42329886
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.
0
 

Author Comment

by:Stef Merlijn
ID: 42329898
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.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 42330609
Normally a dba does jobs like that

As pawan said, schedule the proc

The schedule commands can also be executed from delphi
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 42330610
0
 

Author Comment

by:Stef Merlijn
ID: 42330876
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?
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 42330933
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 :)
0
 

Author Comment

by:Stef Merlijn
ID: 42331032
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?
0
 
LVL 28

Expert Comment

by:Sinisa Vuk
ID: 42332064
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.
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 42332224
@Author - i think your last option will work fine. Make the call asynchronously.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 42332380
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
0
 

Author Comment

by:Stef Merlijn
ID: 42332382
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?
0
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 1000 total points
ID: 42332414
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
0
 

Author Closing Comment

by:Stef Merlijn
ID: 42332558
@Pawan Kumar: Thanks for suggesting a Windows schedule.
@Geert Gruwez: I'll go with the DataSnap approach.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question