sql server dts: sql execution log -- sql server dts

Re: sql execution log

pradeep.mittal NO[at]SPAM citigroup.com
1/29/2004 5:43:02 PM

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

[quoted text, click to view]

Re: sql execution log

Allan Mitchell
1/30/2004 7:37:49 AM

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.

[quoted text, click to view]

Re: sql execution log

pradeep.mittal NO[at]SPAM citigroup.com
2/5/2004 8:20:35 PM

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

[quoted text, click to view]

Re: sql execution log

Allan Mitchell
2/6/2004 7:12:28 AM

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

[quoted text, click to view]

Re: sql execution log

pradeep.mittal NO[at]SPAM citigroup.com
2/15/2004 8:59:44 PM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.