Re: Massive DTS job (5 days and i am nervous)

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 06/19/04 

Date: Sat, 19 Jun 2004 08:45:39 -0400

If you have SQL Server 2000 SP3 (or SP3a), then you may be able to get that
with fn_get_sql(). Check out the BOL for more details. In future, you can
simply run the Profiler and trace the SP:StmtStarted events. 

-- 
   Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON   Canada
www.pinnaclepublishing.com/sql
.
"N" <raasen@hotmail.com> wrote in message
news:%23Y6MRnfVEHA.212@TK2MSFTNGP12.phx.gbl...
Hi
I have inherited a DTS job that takes about 5 days to run.
This job is made up of about 20 stored procedures and some vbscript and so
on.
There is a bit of a process log that is kept in a table which is basically a
log of a start of a stored proc and then logs an end time for the stored
proc. The stored procs are made up of loads of sql statements. Is there any
way i could see which statement is being executed at the current point in
time? dbcc inputbuffer just gives me the "exec stored_proc" command used in
the SQL task. I can see that something is happening as the disk space usage
is increasing and the CPUTime and DISKIo are going up for the spid executing
the sql task. There are also no blks for any of the processes so i am
certain nothing is blocking anything. I however, would like to know which
sql statement is being executed just to satisfy myself that this thing is
not hanging on one sql statement the entire time....
any ideas?

Relevant Pages

  • Re: Massive DTS job (5 days and i am nervous)
    ... Inserts are done as a transaction. ... Columnist, SQL Server Professional ... The way this was written and i am using the current stored proc being executed as an example is that no transactions are used. ... would like to know which> sql statement is being executed just to satisfy myself that this thing is> not hanging on one sql statement the entire time.... ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL string problem
    ... clean up your parameters before sending them to SQL. ... > You must "escape" any single quotes when sending SQL statements ... > Just pass your SQL statement through this function when opening your ... >> Here is the full SQL statement that is assigned to the strSQL ...
    (microsoft.public.excel.programming)
  • Re: SQL Statement or Cursor
    ... > Your post seems to ask whether you're better off using a cursor or a sql ... > you can achieve this in a SQL Statement, but I'll offer a caution to you ... SQL Server's tsql doesn't have a rownum ... >> Initial Result Set but lacking Incrementing number. ...
    (microsoft.public.sqlserver.programming)
  • Re: Populating a list -- table structure?
    ... this clears up a lot of issues and jargon with SQL. ... I think I'll have to try to get a query that will take the BKitIDs and the KitIDs and bring that information together. ... Queries (just shows the QBE grid for convenience -- ... and It really helps to use Aliases for tablenames as it makes the SQL statement shorter. ...
    (microsoft.public.access.forms)
  • Re: Emailing a Report
    ... CTRL-G to Goto the debuG window -- look at the SQL statement ... The Sub LoopAgmtsSendEmail is highlighted by the Debugger. ...
    (microsoft.public.access.modulesdaovba)
Loading