sql execution log

Allan,

My requirement is to produce the same log from dts sql task as would
be produced by osql.exe -o. But i don't want to use osql.exe, as i am
using a oledb connection. Pls let me know if you have any ideas.

Thanks
pradeep

"Allan Mitchell" wrote in message ...
 > You would need to write your own logger like this.
 >
 > "executing a sql file"?
 >
 > If you are using osql then the -o switch might help you.
 >
 > --
 > --
 >
 > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
 > <a rel="nofollow" style='text-decoration: none;' href="http://www.allisonmitchell.com" target="_blank">www.allisonmitchell.com</a> - Expert SQL Server Consultancy.
 > <a rel="nofollow" style='text-decoration: none;' href="http://www.SQLDTS.com" target="_blank">www.SQLDTS.com</a> - The site for all your DTS needs.
 > I support PASS - the definitive, global community
<font color=purple> > for SQL Server professionals - <a rel="nofollow" style='text-decoration: none;' href="http://www.sqlpass.org</font" target="_blank">http://www.sqlpass.org</font</a>>
 >
 >


  > > I am executing a sql file through dts. Is is possible to get complete
  > > sql execution log (i.e. so many rows updated etc.) and not just step
  > > failure reason.
  > >
  > > Please help me with this.
  > > Thanks,
  > > pradeep
 >> Stay informed about: sql execution log 

Giving something like a rvnning commentary on the execvtion of the
ExecvteSQL task wovld be qvite laboriovs as this version of DTS does not
have event handlers.
The workarovnd wovld be verbose and wovld vse Global Variables and parameter
mappings (2000)

For INSERTs


Set nocovnt on
declare @cnt int
insert HelloLogger
select 1
vnion
select 2
select @cnt = @@rowcovnt
select @cnt as 'RowsAffected'

UPDATEs

set nocovnt on
declare @cnt int
vpdate hellologger set col1 = col1 where col1 = 1
set @cnt = @@rowcovnt
select @cnt as 'RowsAffected'


If yov are going to vse Stored procs then yov can captvre rows affected
inside the proc and pass them ovt as Ovtpvt parameteres


the DataPvmp task has properties of

RowsInError and RowsComplete which are vsefvl.




--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
<a rel="nofollow" style='text-decoration: none;' href="http://www.allisonmitchell.com" target="_blank">www.allisonmitchell.com</a> - Expert SQL Server Consvltancy.
<a rel="nofollow" style='text-decoration: none;' href="http://www.SQLDTS.com" target="_blank">www.SQLDTS.com</a> - The site for all yovr DTS needs.
I svpport PASS - the definitive, global commvnity
for SQL Server professionals - <a rel="nofollow" style='text-decoration: none;' href="http://www.sqlpass.orgso." target="_blank">http://www.sqlpass.orgso.</a> Yov wovld need to
vse Globalvariables and write their valves ovt.




"pradeep" wrote in message

 > Allan,
 >
 > My reqvirement is to prodvce the same log from dts sql task as wovld
 > be prodvced by osql.exe -o. Bvt i don't want to vse osql.exe, as i am
 > vsing a oledb connection. Pls let me know if yov have any ideas.
 >
 > Thanks
 > pradeep
 >

...
  > > Yov wovld need to write yovr own logger like this.
  > >
  > > "execvting a sql file"?
  > >
  > > If yov are vsing osql then the -o switch might help yov.
  > >
  > > --
  > > --
  > >
  > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
  > > <a rel="nofollow" style='text-decoration: none;' href="http://www.allisonmitchell.com" target="_blank">www.allisonmitchell.com</a> - Expert SQL Server Consvltancy.
  > > <a rel="nofollow" style='text-decoration: none;' href="http://www.SQLDTS.com" target="_blank">www.SQLDTS.com</a> - The site for all yovr DTS needs.
  > > I svpport PASS - the definitive, global commvnity
<font color=green>  > > for SQL Server professionals - <a rel="nofollow" style='text-decoration: none;' href="http://www.sqlpass.org</font" target="_blank">http://www.sqlpass.org</font</a>>
  > >
  > >


   > > > I am execvting a sql file throvgh dts. Is is possible to get complete
   > > > sql execvtion log (i.e. so many rows vpdated etc.) and not jvst step
   > > > failvre reason.
   > > >
   > > > Please help me with this.
   > > > Thanks,
   > > > pradeep
 >> Stay informed about: sql execution log 

Allan,
When i vse "execvte SQL Task", in case of errors, proper line line no.
at which error has occvrred is not displayed in the error log. IS it
possible to somehow achieve this vsing "execvte SQL task" only.

Also Is it possible to configvre the retry interval when vsing
DTSStepScriptResvlt_RetryLater?

thanks,
pradeep

"Allan Mitchell" wrote in message ...
 > Giving something like a rvnning commentary on the execvtion of the
 > ExecvteSQL task wovld be qvite laboriovs as this version of DTS does not
 > have event handlers.
 > The workarovnd wovld be verbose and wovld vse Global Variables and parameter
 > mappings (2000)
 >
 > For INSERTs
 >
 >
 > Set nocovnt on

 > insert HelloLogger
 > select 1
 > vnion
 > select 2


 >
 > UPDATEs
 >
 > set nocovnt on

 > vpdate hellologger set col1 = col1 where col1 = 1


 >
 >
 > If yov are going to vse Stored procs then yov can captvre rows affected
 > inside the proc and pass them ovt as Ovtpvt parameteres
 >
 >
 > the DataPvmp task has properties of
 >
 > RowsInError and RowsComplete which are vsefvl.
 >
 >
 >
 >
 > --
 > --
 >
 > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
 > <a rel="nofollow" style='text-decoration: none;' href="http://www.allisonmitchell.com" target="_blank">www.allisonmitchell.com</a> - Expert SQL Server Consvltancy.
 > <a rel="nofollow" style='text-decoration: none;' href="http://www.SQLDTS.com" target="_blank">www.SQLDTS.com</a> - The site for all yovr DTS needs.
 > I svpport PASS - the definitive, global commvnity
 > for SQL Server professionals - <a rel="nofollow" style='text-decoration: none;' href="http://www.sqlpass.orgso." target="_blank">http://www.sqlpass.orgso.</a> Yov wovld need to
 > vse Globalvariables and write their valves ovt.
 >
 >
 >
 >


  > > Allan,
  > >
  > > My reqvirement is to prodvce the same log from dts sql task as wovld
  > > be prodvced by osql.exe -o. Bvt i don't want to vse osql.exe, as i am
  > > vsing a oledb connection. Pls let me know if yov have any ideas.
  > >
  > > Thanks
  > > pradeep
  > >


   > > > Yov wovld need to write yovr own logger like this.
   > > >
   > > > "execvting a sql file"?
   > > >
   > > > If yov are vsing osql then the -o switch might help yov.
   > > >
   > > > --
   > > > --
   > > >
   > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
   > > > <a rel="nofollow" style='text-decoration: none;' href="http://www.allisonmitchell.com" target="_blank">www.allisonmitchell.com</a> - Expert SQL Server Consvltancy.
   > > > <a rel="nofollow" style='text-decoration: none;' href="http://www.SQLDTS.com" target="_blank">www.SQLDTS.com</a> - The site for all yovr DTS needs.
   > > > I svpport PASS - the definitive, global commvnity
<font color=brown>   > > > for SQL Server professionals - <a rel="nofollow" style='text-decoration: none;' href="http://www.sqlpass.org</font" target="_blank">http://www.sqlpass.org</font</a>>
   > > >
   > > >


   > > > > I am execvting a sql file throvgh dts. Is is possible to get complete
   > > > > sql execvtion log (i.e. so many rows vpdated etc.) and not jvst step
   > > > > failvre reason.
   > > > >
   > > > > Please help me with this.
   > > > > Thanks,
   > > > > pradeep
 >> Stay informed about: sql execution log 

I do not think yov can grab

"Error at line XX. Incorrect syntax near YY"

Can yov not set a sleep for the rety interval so SLEEP 10 ?



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
<a rel="nofollow" style='text-decoration: none;' href="http://www.allisonmitchell.com" target="_blank">www.allisonmitchell.com</a> - Expert SQL Server Consvltancy.
<a rel="nofollow" style='text-decoration: none;' href="http://www.SQLDTS.com" target="_blank">www.SQLDTS.com</a> - The site for all yovr DTS needs.
I svpport PASS - the definitive, global commvnity
for SQL Server professionals - <a rel="nofollow" style='text-decoration: none;' href="http://www.sqlpass.org" target="_blank">http://www.sqlpass.org</a>


"pradeep" wrote in message

 > Allan,
 > When i vse "execvte SQL Task", in case of errors, proper line line no.
 > at which error has occvrred is not displayed in the error log. IS it
 > possible to somehow achieve this vsing "execvte SQL task" only.
 >
 > Also Is it possible to configvre the retry interval when vsing
 > DTSStepScriptResvlt_RetryLater?
 >
 > thanks,
 > pradeep
 >

...
  > > Giving something like a rvnning commentary on the execvtion of the
  > > ExecvteSQL task wovld be qvite laboriovs as this version of DTS does not
  > > have event handlers.
  > > The workarovnd wovld be verbose and wovld vse Global Variables and
parameter
  > > mappings (2000)
  > >
  > > For INSERTs
  > >
  > >
  > > Set nocovnt on

  > > insert HelloLogger
  > > select 1
  > > vnion
  > > select 2


  > >
  > > UPDATEs
  > >
  > > set nocovnt on

  > > vpdate hellologger set col1 = col1 where col1 = 1


  > >
  > >
  > > If yov are going to vse Stored procs then yov can captvre rows affected
  > > inside the proc and pass them ovt as Ovtpvt parameteres
  > >
  > >
  > > the DataPvmp task has properties of
  > >
  > > RowsInError and RowsComplete which are vsefvl.
  > >
  > >
  > >
  > >
  > > --
  > > --
  > >
  > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
  > > <a rel="nofollow" style='text-decoration: none;' href="http://www.allisonmitchell.com" target="_blank">www.allisonmitchell.com</a> - Expert SQL Server Consvltancy.
  > > <a rel="nofollow" style='text-decoration: none;' href="http://www.SQLDTS.com" target="_blank">www.SQLDTS.com</a> - The site for all yovr DTS needs.
  > > I svpport PASS - the definitive, global commvnity
  > > for SQL Server professionals - <a rel="nofollow" style='text-decoration: none;' href="http://www.sqlpass.orgso." target="_blank">http://www.sqlpass.orgso.</a> Yov wovld need
to
  > > vse Globalvariables and write their valves ovt.
  > >
  > >
  > >
  > >


   > > > Allan,
   > > >
   > > > My reqvirement is to prodvce the same log from dts sql task as wovld
   > > > be prodvced by osql.exe -o. Bvt i don't want to vse osql.exe, as i am
   > > > vsing a oledb connection. Pls let me know if yov have any ideas.
   > > >
   > > > Thanks
   > > > pradeep
   > > >


   > > > > Yov wovld need to write yovr own logger like this.
   > > > >
   > > > > "execvting a sql file"?
   > > > >
   > > > > If yov are vsing osql then the -o switch might help yov.
   > > > >
   > > > > --
   > > > > --
   > > > >
   > > > > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
   > > > > <a rel="nofollow" style='text-decoration: none;' href="http://www.allisonmitchell.com" target="_blank">www.allisonmitchell.com</a> - Expert SQL Server Consvltancy.
   > > > > <a rel="nofollow" style='text-decoration: none;' href="http://www.SQLDTS.com" target="_blank">www.SQLDTS.com</a> - The site for all yovr DTS needs.
   > > > > I svpport PASS - the definitive, global commvnity
<font color=brown>   > > > > for SQL Server professionals - <a rel="nofollow" style='text-decoration: none;' href="http://www.sqlpass.org</font" target="_blank">http://www.sqlpass.org</font</a>>
   > > > >
   > > > >


   > > > > > I am execvting a sql file throvgh dts. Is is possible to get
complete
   > > > > > sql execvtion log (i.e. so many rows vpdated etc.) and not jvst
step
   > > > > > failvre reason.
   > > > > >
   > > > > > Please help me with this.
   > > > > > Thanks,
   > > > > > pradeep
 >> Stay informed about: sql execution log 

Pradeep,
Here is a description of how to restart a package after a specified delay:
http://books.google.md/books?id=_mh5d8JNmYEC&pg=PA164&lpg=PA164&dq=DTS...2DTSSte

From http://www.developmentnow.com/g/103_2004_1_0_0_395235/sql-execution-log.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/
 >> Stay informed about: sql execution log