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

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


"pradeep" <pradeep.mittal (AT) citigroup (DOT) com> wrote

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" <allan (AT) no-spam (DOT) sqldts.com> wrote

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.




"pradeep" <pradeep.mittal (AT) citigroup (DOT) com> wrote

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

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

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


"pradeep" <pradeep.mittal (AT) citigroup (DOT) com> wrote

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

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Pradeep,
Here is a description of how to restart a package after a specified delay:
http://books.google.md/books?id=_mh5...page&q&f=false

From http://www.developmentnow.com/g/103_...cution-log.htm

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