T-SQL Script: call

Showing posts with label call. Show all posts

Wednesday, March 7, 2012

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
>
>

Saturday, February 25, 2012

Schedule cmdExec

How can I schedule a exe program from SQL server jobs ?
What is the CmdExec command I need to put in in order to
run a exe program call Test.exeJust put in the name and path to the exec file, like below:
C:\test.exe
Note that this file is executed on the *server*. Also, the program cannot open any windows (it need
to be a what we call "console" program).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Travis" <anonymous@.discussions.microsoft.com> wrote in message
news:135601c4ad16$9b197880$a301280a@.phx.gbl...
> How can I schedule a exe program from SQL server jobs ?
> What is the CmdExec command I need to put in in order to
> run a exe program call Test.exe|||My test.exe is created using vb6 for doing some extra
process.
I already try the "C:\Test.exe" but it not runnable.
>--Original Message--
>Just put in the name and path to the exec file, like
below:
>C:\test.exe
>Note that this file is executed on the *server*. Also,
the program cannot open any windows (it need
>to be a what we call "console" program).
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Travis" <anonymous@.discussions.microsoft.com> wrote in
message
>news:135601c4ad16$9b197880$a301280a@.phx.gbl...
>> How can I schedule a exe program from SQL server jobs ?
>> What is the CmdExec command I need to put in in order
to
>> run a exe program call Test.exe
>
>.
>|||If you right-click the job and select "history", and in this dialog check "View Step Details", what
do you have for your jobstep?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Travis" <anonymous@.discussions.microsoft.com> wrote in message
news:283a01c4ad1c$700546a0$a601280a@.phx.gbl...
> My test.exe is created using vb6 for doing some extra
> process.
> I already try the "C:\Test.exe" but it not runnable.
>>--Original Message--
>>Just put in the name and path to the exec file, like
> below:
>>C:\test.exe
>>Note that this file is executed on the *server*. Also,
> the program cannot open any windows (it need
>>to be a what we call "console" program).
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Travis" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:135601c4ad16$9b197880$a301280a@.phx.gbl...
>> How can I schedule a exe program from SQL server jobs ?
>> What is the CmdExec command I need to put in in order
> to
>> run a exe program call Test.exe
>>
>>.|||When I check on the Windows Task Manager,I notice that the
program was start up but I don't know why it is not
runnable.
No jobs history is available because the program already
been executed but I'm sure the program it not run the
process because it takes very long time to execute
and I not get the desired result I need.
If I run the Test.exe manually , it takes about few seconds
to finish the process.
I need to manually terminate the Job since it seems like
hanging there.
Any other setting I need to configure ?
TQ for your info
>--Original Message--
>If you right-click the job and select "history", and in
this dialog check "View Step Details", what
>do you have for your jobstep?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Travis" <anonymous@.discussions.microsoft.com> wrote in
message
>news:283a01c4ad1c$700546a0$a601280a@.phx.gbl...
>> My test.exe is created using vb6 for doing some extra
>> process.
>> I already try the "C:\Test.exe" but it not runnable.
>>--Original Message--
>>Just put in the name and path to the exec file, like
>> below:
>>C:\test.exe
>>Note that this file is executed on the *server*. Also,
>> the program cannot open any windows (it need
>>to be a what we call "console" program).
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Travis" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:135601c4ad16$9b197880$a301280a@.phx.gbl...
>> How can I schedule a exe program from SQL server
jobs ?
>> What is the CmdExec command I need to put in in order
>> to
>> run a exe program call Test.exe
>>
>>.
>
>.
>|||Are you 100% certain that the program doesn't display any window, or message box?`The symptoms you
describe is exactly what we see if we try starting programs that tries to display windows...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:287301c4af7c$e0e05420$a301280a@.phx.gbl...
> When I check on the Windows Task Manager,I notice that the
> program was start up but I don't know why it is not
> runnable.
> No jobs history is available because the program already
> been executed but I'm sure the program it not run the
> process because it takes very long time to execute
> and I not get the desired result I need.
> If I run the Test.exe manually , it takes about few seconds
> to finish the process.
> I need to manually terminate the Job since it seems like
> hanging there.
> Any other setting I need to configure ?
> TQ for your info
>>--Original Message--
>>If you right-click the job and select "history", and in
> this dialog check "View Step Details", what
>>do you have for your jobstep?
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Travis" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:283a01c4ad1c$700546a0$a601280a@.phx.gbl...
>> My test.exe is created using vb6 for doing some extra
>> process.
>> I already try the "C:\Test.exe" but it not runnable.
>>--Original Message--
>>Just put in the name and path to the exec file, like
>> below:
>>C:\test.exe
>>Note that this file is executed on the *server*. Also,
>> the program cannot open any windows (it need
>>to be a what we call "console" program).
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>http://www.solidqualitylearning.com/
>>
>>"Travis" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:135601c4ad16$9b197880$a301280a@.phx.gbl...
>> How can I schedule a exe program from SQL server
> jobs ?
>> What is the CmdExec command I need to put in in order
>> to
>> run a exe program call Test.exe
>>
>>.
>>
>>.|||I m sure the program did not process ...because my program will just take few
seconds to complete and will display a msgbox after that ..
I wait for few minutes after execute the jobs but it seems like no process..
what is other posibility to cause it ?
"Tibor Karaszi" wrote:
> Are you 100% certain that the program doesn't display any window, or message box?`The symptoms you
> describe is exactly what we see if we try starting programs that tries to display windows...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:287301c4af7c$e0e05420$a301280a@.phx.gbl...
> > When I check on the Windows Task Manager,I notice that the
> > program was start up but I don't know why it is not
> > runnable.
> >
> > No jobs history is available because the program already
> > been executed but I'm sure the program it not run the
> > process because it takes very long time to execute
> > and I not get the desired result I need.
> >
> > If I run the Test.exe manually , it takes about few seconds
> > to finish the process.
> >
> > I need to manually terminate the Job since it seems like
> > hanging there.
> >
> > Any other setting I need to configure ?
> >
> > TQ for your info
> >
> >>--Original Message--
> >>If you right-click the job and select "history", and in
> > this dialog check "View Step Details", what
> >>do you have for your jobstep?
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://www.solidqualitylearning.com/
> >>
> >>
> >>"Travis" <anonymous@.discussions.microsoft.com> wrote in
> > message
> >>news:283a01c4ad1c$700546a0$a601280a@.phx.gbl...
> >> My test.exe is created using vb6 for doing some extra
> >> process.
> >> I already try the "C:\Test.exe" but it not runnable.
> >>
> >>--Original Message--
> >>Just put in the name and path to the exec file, like
> >> below:
> >>
> >>C:\test.exe
> >>
> >>Note that this file is executed on the *server*. Also,
> >> the program cannot open any windows (it need
> >>to be a what we call "console" program).
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>http://www.karaszi.com/sqlserver/default.asp
> >>http://www.solidqualitylearning.com/
> >>
> >>
> >>"Travis" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >>news:135601c4ad16$9b197880$a301280a@.phx.gbl...
> >> How can I schedule a exe program from SQL server
> > jobs ?
> >> What is the CmdExec command I need to put in in order
> >> to
> >> run a exe program call Test.exe
> >>
> >>
> >>.
> >>
> >>
> >>
> >>.
> >>
>
>|||If the program completes and displays a message box then it
does have a window or message box displaying. The command
will appear to hang but it is actually waiting for a
response to the message box or can't create the window. A
service such as SQL Server or SQL Server Agent can't respond
to user input (such as clicking the ok button on a message
box). You need to remove the message box. The exe can't have
any windows or user input.
-Sue
On Mon, 11 Oct 2004 19:49:07 -0700, Travis
<Travis@.discussions.microsoft.com> wrote:
>I m sure the program did not process ...because my program will just take few
>seconds to complete and will display a msgbox after that ..
>I wait for few minutes after execute the jobs but it seems like no process..
>what is other posibility to cause it ?
>"Tibor Karaszi" wrote:
>> Are you 100% certain that the program doesn't display any window, or message box?`The symptoms you
>> describe is exactly what we see if we try starting programs that tries to display windows...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <anonymous@.discussions.microsoft.com> wrote in message
>> news:287301c4af7c$e0e05420$a301280a@.phx.gbl...
>> > When I check on the Windows Task Manager,I notice that the
>> > program was start up but I don't know why it is not
>> > runnable.
>> >
>> > No jobs history is available because the program already
>> > been executed but I'm sure the program it not run the
>> > process because it takes very long time to execute
>> > and I not get the desired result I need.
>> >
>> > If I run the Test.exe manually , it takes about few seconds
>> > to finish the process.
>> >
>> > I need to manually terminate the Job since it seems like
>> > hanging there.
>> >
>> > Any other setting I need to configure ?
>> >
>> > TQ for your info
>> >
>> >>--Original Message--
>> >>If you right-click the job and select "history", and in
>> > this dialog check "View Step Details", what
>> >>do you have for your jobstep?
>> >>
>> >>--
>> >>Tibor Karaszi, SQL Server MVP
>> >>http://www.karaszi.com/sqlserver/default.asp
>> >>http://www.solidqualitylearning.com/
>> >>
>> >>
>> >>"Travis" <anonymous@.discussions.microsoft.com> wrote in
>> > message
>> >>news:283a01c4ad1c$700546a0$a601280a@.phx.gbl...
>> >> My test.exe is created using vb6 for doing some extra
>> >> process.
>> >> I already try the "C:\Test.exe" but it not runnable.
>> >>
>> >>--Original Message--
>> >>Just put in the name and path to the exec file, like
>> >> below:
>> >>
>> >>C:\test.exe
>> >>
>> >>Note that this file is executed on the *server*. Also,
>> >> the program cannot open any windows (it need
>> >>to be a what we call "console" program).
>> >>
>> >>--
>> >>Tibor Karaszi, SQL Server MVP
>> >>http://www.karaszi.com/sqlserver/default.asp
>> >>http://www.solidqualitylearning.com/
>> >>
>> >>
>> >>"Travis" <anonymous@.discussions.microsoft.com> wrote in
>> >> message
>> >>news:135601c4ad16$9b197880$a301280a@.phx.gbl...
>> >> How can I schedule a exe program from SQL server
>> > jobs ?
>> >> What is the CmdExec command I need to put in in order
>> >> to
>> >> run a exe program call Test.exe
>> >>
>> >>
>> >>.
>> >>
>> >>
>> >>
>> >>.
>> >>
>>

Wednesday, March 7, 2012

Schedule job, SP and DTS transaction problem

Saturday, February 25, 2012

Schedule cmdExec

Schedule cmdExec

T-SQL Script

Blog Archive

About Me