Schedule job, SP and DTS transaction problem

Hi all,
I have a DTS to export a CSV and a SP to call the DTS and schedule the SP to
execute.
My problem is that the DTS have been blocked by the schedule job.
Here are those program:
DTS sql:
exec sp_GetResultForExportCSV
SP:
CREATE PROCEDURE sp_DayEnd
AS
-- insert ...
-- update ...
-- call DTS
EXEC('master.dbo.xp_cmdShell ''DTSRun /~Z0xE6FEF651097....'',
no_output')
Return
Schedule sql:
Begin tran
exec sp_DayEnd
commit tran
If i remove the begin tran, the schedule run successfully. But is it not
possible to have begin tran?
And I have tried the transaction setting of the DTS properties(transaction
on/off, read uncommitted, etc...), all doesn't work with 'Begin tran'. The
DTS always block when running the DTS sql (exec sp_GetResultForExportCSV). I
think the SP and xp_cmdShell DTSRun is not executed within the same
transaction, is it the limitation?
Thanks in advance!
MartinHi,
why you do the Begin and Commit in Schedule sql? not in sp_DayEnd.
I guess since the insert and update have not been commited yet and they
are requiring exclusive lock on the reords, in the same SP, you are trying t
o
export data from the tables that are being locked. That may be the reason
why the master..xp_cmdshell is waiting for the lock to be relase which
actually will never happen. I suggest you to put the following in sp_dayEnd
Begin Tran
Insert...
Update...
Commit Tran
Exec 'master..xp_cmdshell xxxxxxxxxxxxxxxxxxxxxxxx'
HTH
Ed
"Atenza" wrote:

> Hi all,
> I have a DTS to export a CSV and a SP to call the DTS and schedule the SP
to
> execute.
> My problem is that the DTS have been blocked by the schedule job.
> Here are those program:
> DTS sql:
> exec sp_GetResultForExportCSV
>
> SP:
> CREATE PROCEDURE sp_DayEnd
> AS
> -- insert ...
> -- update ...
> -- call DTS
> EXEC('master.dbo.xp_cmdShell ''DTSRun /~Z0xE6FEF651097....'',
> no_output')
> Return
>
> Schedule sql:
> Begin tran
> exec sp_DayEnd
> commit tran
>
> If i remove the begin tran, the schedule run successfully. But is it not
> possible to have begin tran?
> And I have tried the transaction setting of the DTS properties(transaction
> on/off, read uncommitted, etc...), all doesn't work with 'Begin tran'. The
> DTS always block when running the DTS sql (exec sp_GetResultForExportCSV).
I
> think the SP and xp_cmdShell DTSRun is not executed within the same
> transaction, is it the limitation?
> Thanks in advance!
> Martin
>
>