Discussion:
sql execution log
(too old to reply)
pradeep
2004-01-29 03:40:19 UTC
Permalink
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
Allan Mitchell
2004-01-29 06:59:15 UTC
Permalink
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)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Post by pradeep
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
pradeep
2004-01-30 01:43:02 UTC
Permalink
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
Post by Allan Mitchell
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)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Post by pradeep
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
Allan Mitchell
2004-01-30 07:37:49 UTC
Permalink
Giving something like a running commentary on the execution of the
ExecuteSQL task would be quite laborious as this version of DTS does not
have event handlers.
The workaround would be verbose and would use Global Variables and parameter
mappings (2000)

For INSERTs


Set nocount on
declare @cnt int
insert HelloLogger
select 1
union
select 2
select @cnt = @@rowcount
select @cnt as 'RowsAffected'

UPDATEs

set nocount on
declare @cnt int
update hellologger set col1 = col1 where col1 = 1
set @cnt = @@rowcount
select @cnt as 'RowsAffected'


If you are going to use Stored procs then you can capture rows affected
inside the proc and pass them out as Output parameteres


the DataPump task has properties of

RowsInError and RowsComplete which are useful.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.orgso. You would need to
use Globalvariables and write their values out.
Post by pradeep
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
Post by Allan Mitchell
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)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Post by pradeep
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
pradeep
2004-02-06 04:20:35 UTC
Permalink
Allan,
When i use "execute SQL Task", in case of errors, proper line line no.
at which error has occurred is not displayed in the error log. IS it
possible to somehow achieve this using "execute SQL task" only.

Also Is it possible to configure the retry interval when using
DTSStepScriptResult_RetryLater?

thanks,
pradeep
Post by Allan Mitchell
Giving something like a running commentary on the execution of the
ExecuteSQL task would be quite laborious as this version of DTS does not
have event handlers.
The workaround would be verbose and would use Global Variables and parameter
mappings (2000)
For INSERTs
Set nocount on
insert HelloLogger
select 1
union
select 2
UPDATEs
set nocount on
update hellologger set col1 = col1 where col1 = 1
If you are going to use Stored procs then you can capture rows affected
inside the proc and pass them out as Output parameteres
the DataPump task has properties of
RowsInError and RowsComplete which are useful.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.orgso. You would need to
use Globalvariables and write their values out.
Post by pradeep
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
Post by Allan Mitchell
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)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Post by pradeep
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
Allan Mitchell
2004-02-06 07:12:28 UTC
Permalink
I do not think you can grab

"Error at line XX. Incorrect syntax near YY"

Can you not set a sleep for the rety interval so SLEEP 10 ?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Post by pradeep
Allan,
When i use "execute SQL Task", in case of errors, proper line line no.
at which error has occurred is not displayed in the error log. IS it
possible to somehow achieve this using "execute SQL task" only.
Also Is it possible to configure the retry interval when using
DTSStepScriptResult_RetryLater?
thanks,
pradeep
Post by Allan Mitchell
Giving something like a running commentary on the execution of the
ExecuteSQL task would be quite laborious as this version of DTS does not
have event handlers.
The workaround would be verbose and would use Global Variables and parameter
mappings (2000)
For INSERTs
Set nocount on
insert HelloLogger
select 1
union
select 2
UPDATEs
set nocount on
update hellologger set col1 = col1 where col1 = 1
If you are going to use Stored procs then you can capture rows affected
inside the proc and pass them out as Output parameteres
the DataPump task has properties of
RowsInError and RowsComplete which are useful.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.orgso. You would need to
use Globalvariables and write their values out.
Post by pradeep
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
Post by Allan Mitchell
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)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Post by pradeep
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
pradeep
2004-02-16 04:59:44 UTC
Permalink
thanks Allan.

I actually didn;t get how to set retry interval using sleep. If i use following
code retry interval is still not set. Please advice.

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
IF NOT(fso.FileExists("C:\MyFile.txt")) THEN
Wscript.Sleep 5000
Main = DTSStepScriptResult_RetryLater
END IF
ELSE
Main = DTSStepScriptResult_ExecuteTask
END IF


Thanks
pradeep
Post by Allan Mitchell
I do not think you can grab
"Error at line XX. Incorrect syntax near YY"
Can you not set a sleep for the rety interval so SLEEP 10 ?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Post by pradeep
Allan,
When i use "execute SQL Task", in case of errors, proper line line no.
at which error has occurred is not displayed in the error log. IS it
possible to somehow achieve this using "execute SQL task" only.
Also Is it possible to configure the retry interval when using
DTSStepScriptResult_RetryLater?
thanks,
pradeep
Post by Allan Mitchell
Giving something like a running commentary on the execution of the
ExecuteSQL task would be quite laborious as this version of DTS does not
have event handlers.
The workaround would be verbose and would use Global Variables and
parameter
Post by pradeep
Post by Allan Mitchell
mappings (2000)
For INSERTs
Set nocount on
insert HelloLogger
select 1
union
select 2
UPDATEs
set nocount on
update hellologger set col1 = col1 where col1 = 1
If you are going to use Stored procs then you can capture rows affected
inside the proc and pass them out as Output parameteres
the DataPump task has properties of
RowsInError and RowsComplete which are useful.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.orgso. You would need
to
Post by pradeep
Post by Allan Mitchell
use Globalvariables and write their values out.
Post by pradeep
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
Post by Allan Mitchell
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)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Post by pradeep
I am executing a sql file through dts. Is is possible to get
complete
Post by pradeep
Post by Allan Mitchell
Post by pradeep
Post by Allan Mitchell
Post by pradeep
sql execution log (i.e. so many rows updated etc.) and not just
step
Post by pradeep
Post by Allan Mitchell
Post by pradeep
Post by Allan Mitchell
Post by pradeep
failure reason.
Please help me with this.
Thanks,
pradeep
Zodiac
2010-04-08 15:05:49 UTC
Permalink
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+%22DTSStepScriptResult_RetryLater%22&source=bl&ots=0Q-m5HjDvh&sig=zuWt_eBdJxSbl6T1WHteZLCal2I&hl=ru&ei=hO29S96KBuGUOLPfwY4K&sa=X&oi=book_result&ct=result&resnum=10&ved=0CDcQ6AEwCQ#v=onepage&q&f=fals

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

Posted via DevelopmentNow.com Group
http://www.developmentnow.com/g/

Loading...