Auditing Stored Procedure Progress

[DavidLemire]

Hi friends,

This is my first post on this forum, as I've just registered for TekTips. I hope you are a great community, and if that is the case, I'll do my best to keep this community healthy!

Ok, down to the point. I have an Excel report that calls a stored procedure. This SP then returns multiple result sets to the ADO object. However, I have this problem. Depending on the timeframe selected, the time to run the SP can expand from 20 seconds to about 10 minutes. In Query Analyzer, I can monitor easily the progress of SP by adding some "Print" statements. I want to know if it's possible to return those "Print" statements as the SP progresses, to the ADO object, so I can display those statements in Excel (like a progression).


Any help on that would be greatly appreciated !

--
David Lemire, Consultant

 

[TJRTech]

How about the following thread:

http://www.tek-tips.com/viewthread.cfm?qid=1060168

TJR

 

[DavidLemire]

Thanks for your fast reply.

That is an idea a coworker already gave me. I never tried multithreading with a VBA application, but the point is that multiple users may be running the same report at the same time.

In that case, I would have the same table name.

That leaves me with two things:
1. I run the SP as a separate thread then I find a way to name uniquely the temporary table.
2. I find a way to return the "print" statements, which implies returning events from Microsoft SQL Server.

Any suggestions?

---
David Lemire, Consultant
System Technologies for Industry Inc.

 

[TJRTech]

I think your big challenge will be the multithreading in the VBA app. You might need a mechanism for executing the stored procedure asynchronously.

One such mechanism is to use SQL JOBS for this. There are a number of system stored procedures for creating, deleting, checking the status of and running SQL jobs.

It's more complicated, but then your app sets up a custom (unique id'ed) job for executing the SP, runs the job, can poll to present status back to the application, and even remove the job. It all requires that the SQLAgent service be running on your SQL Server.

Take a look at the SQL Books Online, lookup: sp_add_job,
sp_delete_job procs and the sysjobhistory and related master db tables.

TJR

 

[DavidLemire]

Thanks!

So basically, what you are telling me is that I can run my SP (which will become a job) through a system SP, and that I can monitor this job? And that is done throught SQLAgent service?

If so, that is exactly what I want to do and I'll look at that as soon as I can!

---
David Lemire, Consultant
System Technologies for Industry Inc.

 

[TJRTech]

Yeah, that kinda what I am saying.

You call sp_add_job and a few additional system SPs to setup the job and its schedule so that it runs immediately, and probably only once.

Then, you query the status of your job, using the ID returned when you created it by querying sysjobhistory.

Lastly, you asked about SQLAgent. You don't actually do all this through SQLAgent. SQLAgent is merely a service that runs on your SQL Server that is the dispatcher of SQL jobs.

TJR