Batch Query Execution?

Hi,

I am executing a set of queries. i.e, a batchExecution of queries. I need to know when the batch execution is over so that i can follow up some other process.
Is there any method or event in SQL which can keep track of these things.?
My primary focus is, in knowing the process/BatchQuery completion.
Thanks in AdvanceThere is a SQL Profiler event called SQL:BatchCompleted

See

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_204z.asp

You might also be able to query sysprocesses, for example, and look for changes in "status" or "last_batch".

Be certain you understrand the ins-and-outs of batch processing. See

Batch Processing
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_204z.asp

Also note that in SQL 2005, multiple interleaved batches are allowed on a single connection. This is the so-called "MARS" feature (Multiple Active Result Sets). The new system views needed in SQL 2005 for accurately monitoring MARS activity go beyond what is available in the "sysprocesses" view.

See

Multiple Active Result Sets (MARS) in SQL Server 2005
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MARSinSQL05.asp

Regards,
Clifford Dibble
Program Manager, SQL Server

Batch Jobs

Hi Folks,

I am running batch jobs(Nearly 50 queries) on Production server.It is taking nearly 5 or 6 hours(some times 10 hours also) to get the results.The problem is If I loose VPN connection I am not able to save data.I need to save the data upto the query executed before I loose VPN connection Please help me

You're not saying what those batchjobs are doing, but would it be possible to have each job or query save it's intermediate result in a table or as files..? That may be a way to 'catch up' if you loose connection.

It probably needs som adjusting in the jobs/queries also in order to keep track of done/left to do steps, but perhaps an idea?

/Kenneth

|||hey one suggestion i can give to speed up your batch process is to switch the recovery model of your database to Bulk-Logged. Do this only if you can afford to rerun ur bulk process in an event of failure. Also do a log backup before and after switching to/from Bulk-Logged mode|||

Thanks KeWin

|||

Thanks Mitesh

|||

Hi KeWin,

I am saving values in tables but I have one problem .

Example

I am running 30 queries as batch.I lost VPN connection at 15 query.Upto 14th query I have values in table.After establishing VPN connection I need start from 15 query.Could you please help me how to solve this problem.

|||

Well, your app need some way to know which queries are done, and where to start again if interrupted.

It probably don't need to be anything fancy, but I believe you're the one who can best decide how that should be done.
Your app also need the ability to be able to start at a given query, say the 15th..

I don't know anything more about how this is done from your side, so it's hard to come up with any particular suggestion.

/Kenneth

Batch files

Is there a way to run sql queries using a bat file. I
have some bat files I currently have scheduled to run and
would like to run some queries inside of it.
Thanks,
RandyRandy,
Refer 'osql' in BooksOnLine.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Randy" <sparks70b@.yahoo.com> wrote in message
news:0c1401c35d01$5a6bcab0$a601280a@.phx.gbl...
> Is there a way to run sql queries using a bat file. I
> have some bat files I currently have scheduled to run and
> would like to run some queries inside of it.
> Thanks,
> Randy