JDBC Driver issue: execution

Showing posts with label execution. Show all posts

Friday, March 30, 2012

Job/DTS Scheduled Execution

Hi all,
Just a quick question...
I have 2 DTS's - one which imports a small amount of data (about 60 rows
max), and another which imports about 20,000 rows (and will increase over
time).
I had a job running the smaller import every two minutes (as it reports real
time data) - I've added the other import to this but it would seem that the
20,000 rows will take more than 2 minutes.
I was wondering if anyone could advise as to what is likely to happen when
the job takes more than 2 minutes to run - I'm "guessing" that 2 minutes
after it started, it'll start again, and start over writing data thats only
just been written with the last import (and round and round it goes) - but
because of the nature of this, wouldn't this cause the server to overload a
bit, ie, it would be constantly starting new executions of the job whilst
the previous execution was running - or does SQL have anything inbuilt to
prevent this.
I'm thinking that my best bet would be to split these apart - have the
smaller import in one job running every two minutes, and the other in a
separate job that runs, perhaps every 5 (will have to time it I guess) ...
Any info on this would be appreciated.
Regards
RobAgent will not start a job if that job is currently executing. I.e., you wil
l not have several
instances of the same job executing at the same time. Does that answer your
question?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rob Meade" <ku.shn.tsews.thbu@.edaem.bor> wrote in message
news:uDRBiAOHGHA.3700@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> Just a quick question...
> I have 2 DTS's - one which imports a small amount of data (about 60 rows m
ax), and another which
> imports about 20,000 rows (and will increase over time).
> I had a job running the smaller import every two minutes (as it reports re
al time data) - I've
> added the other import to this but it would seem that the 20,000 rows will
take more than 2
> minutes.
> I was wondering if anyone could advise as to what is likely to happen when
the job takes more than
> 2 minutes to run - I'm "guessing" that 2 minutes after it started, it'll s
tart again, and start
> over writing data thats only just been written with the last import (and r
ound and round it
> goes) - but because of the nature of this, wouldn't this cause the server
to overload a bit, ie,
> it would be constantly starting new executions of the job whilst the previ
ous execution was
> running - or does SQL have anything inbuilt to prevent this.
> I'm thinking that my best bet would be to split these apart - have the sma
ller import in one job
> running every two minutes, and the other in a separate job that runs, perh
aps every 5 (will have
> to time it I guess) ...
> Any info on this would be appreciated.
> Regards
> Rob
>|||hi Rob,
What about to call the second DTS from the first one? (As last task, of
course)? So this way I would keep one job no more.
"Rob Meade" wrote:

> Hi all,
> Just a quick question...
> I have 2 DTS's - one which imports a small amount of data (about 60 rows
> max), and another which imports about 20,000 rows (and will increase over
> time).
> I had a job running the smaller import every two minutes (as it reports re
al
> time data) - I've added the other import to this but it would seem that th
e
> 20,000 rows will take more than 2 minutes.
> I was wondering if anyone could advise as to what is likely to happen when
> the job takes more than 2 minutes to run - I'm "guessing" that 2 minutes
> after it started, it'll start again, and start over writing data thats onl
y
> just been written with the last import (and round and round it goes) - but
> because of the nature of this, wouldn't this cause the server to overload
a
> bit, ie, it would be constantly starting new executions of the job whilst
> the previous execution was running - or does SQL have anything inbuilt to
> prevent this.
> I'm thinking that my best bet would be to split these apart - have the
> smaller import in one job running every two minutes, and the other in a
> separate job that runs, perhaps every 5 (will have to time it I guess) ...
> Any info on this would be appreciated.
> Regards
> Rob
>
>|||"Tibor Karaszi" wrote ...

> Agent will not start a job if that job is currently executing. I.e., you
> will not have several instances of the same job executing at the same
> time. Does that answer your question?
Hi Tibor,
Yes it does - thank you :o)
Rob|||"Enric" wrote ...

> What about to call the second DTS from the first one? (As last task, of
> course)? So this way I would keep one job no more.
I could do - but of course then I'd have an even longer wait for the first
lot of info...
Cheers though.
Rob

JOB With SSIS Step Fails If Package Contains A Script Task

I have a Job Step defined to execute a SSIS Package. This SSIS package contains a Script Task. The Job fails with the message "Package execution failed. The step failed."

I am logging events in the package and when the packages gets to the Script Task the log reports "The script files failed to load". If I disable the Script Task from the package it executes fine.

Curiously, the package runs successfully with the Scritp task enabled using dtexecui and dtexec from the command line.

Only if I include the Package in a job step with the Script Task enabled does it fail.

Any help would be appreciated.I've no real idea about this Steve except to say have you got the script code pre-compiled? If so, try it without (and vica versa).

-Jamie|||

Sorry for not posting this sooner, Jamie...Yes, setting the Script Task "PrecompileScriptIntoBinaryCode" Property to True resolved the issue.

-Steve

|||hey - did you ever get this to work? Im having the same issue - its a SSIS pkg that ran on one box and I moved to another box (64 bit if that makes a diff) - any info is greatly apprecaited - thanks.|||

Juantana,

Yes, set your Script Task "PrecompileScriptIntoBinaryCode" Property=True and then open and close your script. Save, redeploy and it should work. Let me know if you have any questions.

-Steve

|||

Hi,

Inspite of putting the PrecompileScriptintoBinarycode Property = True, it is not working. Do you have any suggestions?

Thanks.

|||After settings Precompile to True, you need to open and close script editor(s) to actually precompile the script in the task. Then re-deploy the pockage to target system.|||Also remove all the break points in the code, having break points prevents from recompilation. I guess this issue would be fixed by SP2|||Just an FYI I had all my scripts set to PrecompileScriptIntoBinaryCode=True however when developing in 32bit and deploying to 64bit seemingly randomly I will get the "script failed to load" error. I just open the script and compile it again and save and that usually does the trick.|||

Also if the problem still persists and you keep getting the same error I am sure you are not taking the dtsx file from bin folder to execute. The file created by designer (Business Intelligence Development Studio) is only useful for debug and development mode. After building the solution one should use dtsx created in bin folder. This solution worked for me.

Thanks

Mohit

|||NOTE: I got this same error message with Precompile option set to True.

Problem was that a variable name script used was not passed in. Go figure|||

I have about 30 dtxs that I would like to recompile its scripts to solve this issue. I would like to avoid to open each package, then open each script in the package and do the save to recomplie.

Is there a way to recompile from the command line?

Thanks!

|||

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

|||

Chris Honcoop wrote:

Previously I said "randomly" but I think I have it figured out when this error occurs:
If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

Sounds like it might be a bug. Could you submit it at http://connect.microsoft.com?

-Jamie

|||

I too have similar doubt as that of Paulino. I've 50+ packages with 60+ script tasks. Is there any way, I can compile using a command in command prompt? Any help would be highly appreciated.

My Regards

JOB With SSIS Step Fails If Package Contains A Script Task

I have a Job Step defined to execute a SSIS Package. This SSIS package contains a Script Task. The Job fails with the message "Package execution failed. The step failed."

I am logging events in the package and when the packages gets to the Script Task the log reports "The script files failed to load". If I disable the Script Task from the package it executes fine.

Curiously, the package runs successfully with the Scritp task enabled using dtexecui and dtexec from the command line.

Only if I include the Package in a job step with the Script Task enabled does it fail.

Any help would be appreciated.I've no real idea about this Steve except to say have you got the script code pre-compiled? If so, try it without (and vica versa).

-Jamie|||

Sorry for not posting this sooner, Jamie...Yes, setting the Script Task "PrecompileScriptIntoBinaryCode" Property to True resolved the issue.

-Steve

Juantana,

Yes, set your Script Task "PrecompileScriptIntoBinaryCode" Property=True and then open and close your script. Save, redeploy and it should work. Let me know if you have any questions.

-Steve

|||

Hi,

Inspite of putting the PrecompileScriptintoBinarycode Property = True, it is not working. Do you have any suggestions?

Thanks.

Thanks

Mohit

|||NOTE: I got this same error message with Precompile option set to True.

Problem was that a variable name script used was not passed in. Go figure|||

I have about 30 dtxs that I would like to recompile its scripts to solve this issue. I would like to avoid to open each package, then open each script in the package and do the save to recomplie.

Is there a way to recompile from the command line?

Thanks!

|||

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

|||

Chris Honcoop wrote:

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

Sounds like it might be a bug. Could you submit it at http://connect.microsoft.com?

-Jamie

sql

JOB With SSIS Step Fails If Package Contains A Script Task

I have a Job Step defined to execute a SSIS Package. This SSIS package contains a Script Task. The Job fails with the message "Package execution failed. The step failed."

I am logging events in the package and when the packages gets to the Script Task the log reports "The script files failed to load". If I disable the Script Task from the package it executes fine.

Curiously, the package runs successfully with the Scritp task enabled using dtexecui and dtexec from the command line.

Only if I include the Package in a job step with the Script Task enabled does it fail.

Any help would be appreciated.I've no real idea about this Steve except to say have you got the script code pre-compiled? If so, try it without (and vica versa).

-Jamie|||

Sorry for not posting this sooner, Jamie...Yes, setting the Script Task "PrecompileScriptIntoBinaryCode" Property to True resolved the issue.

-Steve

Juantana,

Yes, set your Script Task "PrecompileScriptIntoBinaryCode" Property=True and then open and close your script. Save, redeploy and it should work. Let me know if you have any questions.

-Steve

|||

Hi,

Inspite of putting the PrecompileScriptintoBinarycode Property = True, it is not working. Do you have any suggestions?

Thanks.

Thanks

Mohit

|||NOTE: I got this same error message with Precompile option set to True.

Problem was that a variable name script used was not passed in. Go figure|||

I have about 30 dtxs that I would like to recompile its scripts to solve this issue. I would like to avoid to open each package, then open each script in the package and do the save to recomplie.

Is there a way to recompile from the command line?

Thanks!

|||

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

|||

Chris Honcoop wrote:

Previously I said "randomly" but I think I have it figured out when this error occurs:
If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

Sounds like it might be a bug. Could you submit it at http://connect.microsoft.com?

-Jamie

JOB With SSIS Step Fails If Package Contains A Script Task

I have a Job Step defined to execute a SSIS Package. This SSIS package contains a Script Task. The Job fails with the message "Package execution failed. The step failed."

I am logging events in the package and when the packages gets to the Script Task the log reports "The script files failed to load". If I disable the Script Task from the package it executes fine.

Curiously, the package runs successfully with the Scritp task enabled using dtexecui and dtexec from the command line.

Only if I include the Package in a job step with the Script Task enabled does it fail.

Any help would be appreciated.I've no real idea about this Steve except to say have you got the script code pre-compiled? If so, try it without (and vica versa).

-Jamie|||

Sorry for not posting this sooner, Jamie...Yes, setting the Script Task "PrecompileScriptIntoBinaryCode" Property to True resolved the issue.

-Steve

Juantana,

Yes, set your Script Task "PrecompileScriptIntoBinaryCode" Property=True and then open and close your script. Save, redeploy and it should work. Let me know if you have any questions.

-Steve

|||

Hi,

Inspite of putting the PrecompileScriptintoBinarycode Property = True, it is not working. Do you have any suggestions?

Thanks.

Thanks

Mohit

|||NOTE: I got this same error message with Precompile option set to True.

Problem was that a variable name script used was not passed in. Go figure|||

I have about 30 dtxs that I would like to recompile its scripts to solve this issue. I would like to avoid to open each package, then open each script in the package and do the save to recomplie.

Is there a way to recompile from the command line?

Thanks!

|||

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

|||

Chris Honcoop wrote:

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

Sounds like it might be a bug. Could you submit it at http://connect.microsoft.com?

-Jamie

JOB With SSIS Step Fails If Package Contains A Script Task

I have a Job Step defined to execute a SSIS Package. This SSIS package contains a Script Task. The Job fails with the message "Package execution failed. The step failed."

I am logging events in the package and when the packages gets to the Script Task the log reports "The script files failed to load". If I disable the Script Task from the package it executes fine.

Curiously, the package runs successfully with the Scritp task enabled using dtexecui and dtexec from the command line.

Only if I include the Package in a job step with the Script Task enabled does it fail.

Any help would be appreciated.I've no real idea about this Steve except to say have you got the script code pre-compiled? If so, try it without (and vica versa).

-Jamie|||

Sorry for not posting this sooner, Jamie...Yes, setting the Script Task "PrecompileScriptIntoBinaryCode" Property to True resolved the issue.

-Steve

Juantana,

Yes, set your Script Task "PrecompileScriptIntoBinaryCode" Property=True and then open and close your script. Save, redeploy and it should work. Let me know if you have any questions.

-Steve

|||

Hi,

Inspite of putting the PrecompileScriptintoBinarycode Property = True, it is not working. Do you have any suggestions?

Thanks.

Thanks

Mohit

|||NOTE: I got this same error message with Precompile option set to True.

Problem was that a variable name script used was not passed in. Go figure|||

I have about 30 dtxs that I would like to recompile its scripts to solve this issue. I would like to avoid to open each package, then open each script in the package and do the save to recomplie.

Is there a way to recompile from the command line?

Thanks!

|||

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

|||

Chris Honcoop wrote:

Previously I said "randomly" but I think I have it figured out when this error occurs:
If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

Sounds like it might be a bug. Could you submit it at http://connect.microsoft.com?

-Jamie

JOB With SSIS Step Fails If Package Contains A Script Task

I have a Job Step defined to execute a SSIS Package. This SSIS package contains a Script Task. The Job fails with the message "Package execution failed. The step failed."

I am logging events in the package and when the packages gets to the Script Task the log reports "The script files failed to load". If I disable the Script Task from the package it executes fine.

Curiously, the package runs successfully with the Scritp task enabled using dtexecui and dtexec from the command line.

Only if I include the Package in a job step with the Script Task enabled does it fail.

Any help would be appreciated.I've no real idea about this Steve except to say have you got the script code pre-compiled? If so, try it without (and vica versa).

-Jamie|||

Sorry for not posting this sooner, Jamie...Yes, setting the Script Task "PrecompileScriptIntoBinaryCode" Property to True resolved the issue.

-Steve

Juantana,

Yes, set your Script Task "PrecompileScriptIntoBinaryCode" Property=True and then open and close your script. Save, redeploy and it should work. Let me know if you have any questions.

-Steve

|||

Hi,

Inspite of putting the PrecompileScriptintoBinarycode Property = True, it is not working. Do you have any suggestions?

Thanks.

Thanks

Mohit

|||NOTE: I got this same error message with Precompile option set to True.

Problem was that a variable name script used was not passed in. Go figure|||

I have about 30 dtxs that I would like to recompile its scripts to solve this issue. I would like to avoid to open each package, then open each script in the package and do the save to recomplie.

Is there a way to recompile from the command line?

Thanks!

|||

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

|||

Chris Honcoop wrote:

Previously I said "randomly" but I think I have it figured out when this error occurs:
If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

Sounds like it might be a bug. Could you submit it at http://connect.microsoft.com?

-Jamie

JOB With SSIS Step Fails If Package Contains A Script Task

I have a Job Step defined to execute a SSIS Package. This SSIS package contains a Script Task. The Job fails with the message "Package execution failed. The step failed."

I am logging events in the package and when the packages gets to the Script Task the log reports "The script files failed to load". If I disable the Script Task from the package it executes fine.

Curiously, the package runs successfully with the Scritp task enabled using dtexecui and dtexec from the command line.

Only if I include the Package in a job step with the Script Task enabled does it fail.

Any help would be appreciated.I've no real idea about this Steve except to say have you got the script code pre-compiled? If so, try it without (and vica versa).

-Jamie|||

Sorry for not posting this sooner, Jamie...Yes, setting the Script Task "PrecompileScriptIntoBinaryCode" Property to True resolved the issue.

-Steve

Juantana,

Yes, set your Script Task "PrecompileScriptIntoBinaryCode" Property=True and then open and close your script. Save, redeploy and it should work. Let me know if you have any questions.

-Steve

|||

Hi,

Inspite of putting the PrecompileScriptintoBinarycode Property = True, it is not working. Do you have any suggestions?

Thanks.

Thanks

Mohit

|||NOTE: I got this same error message with Precompile option set to True.

Problem was that a variable name script used was not passed in. Go figure|||

I have about 30 dtxs that I would like to recompile its scripts to solve this issue. I would like to avoid to open each package, then open each script in the package and do the save to recomplie.

Is there a way to recompile from the command line?

Thanks!

|||

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

|||

Chris Honcoop wrote:

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

Sounds like it might be a bug. Could you submit it at http://connect.microsoft.com?

-Jamie

sql

JOB With SSIS Step Fails If Package Contains A Script Task

I have a Job Step defined to execute a SSIS Package. This SSIS package contains a Script Task. The Job fails with the message "Package execution failed. The step failed."

I am logging events in the package and when the packages gets to the Script Task the log reports "The script files failed to load". If I disable the Script Task from the package it executes fine.

Curiously, the package runs successfully with the Scritp task enabled using dtexecui and dtexec from the command line.

Only if I include the Package in a job step with the Script Task enabled does it fail.

Any help would be appreciated.I've no real idea about this Steve except to say have you got the script code pre-compiled? If so, try it without (and vica versa).

-Jamie|||

Sorry for not posting this sooner, Jamie...Yes, setting the Script Task "PrecompileScriptIntoBinaryCode" Property to True resolved the issue.

-Steve

Juantana,

Yes, set your Script Task "PrecompileScriptIntoBinaryCode" Property=True and then open and close your script. Save, redeploy and it should work. Let me know if you have any questions.

-Steve

|||

Hi,

Inspite of putting the PrecompileScriptintoBinarycode Property = True, it is not working. Do you have any suggestions?

Thanks.

Thanks

Mohit

|||NOTE: I got this same error message with Precompile option set to True.

Problem was that a variable name script used was not passed in. Go figure|||

I have about 30 dtxs that I would like to recompile its scripts to solve this issue. I would like to avoid to open each package, then open each script in the package and do the save to recomplie.

Is there a way to recompile from the command line?

Thanks!

|||

Previously I said "randomly" but I think I have it figured out when this error occurs:

If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

|||

Chris Honcoop wrote:

Previously I said "randomly" but I think I have it figured out when this error occurs:
If I am working on a package, open a different package and copy a script object and paste into the one I am working on it will not work in 64-bit without a recompile (but will work in 32 bit).

Sounds like it might be a bug. Could you submit it at http://connect.microsoft.com?

-Jamie

Friday, March 9, 2012

job execution user change and ownership question

Hi
I am running my sql server 2000 server and agent as a service with domain
admin user.
Is this recommended?
Who should be the job owner? sa or application user?
Can the job be run by any other user other than the user who started the
sqlserver agent.
TKS
MangeshHi
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:907D7E49-4B58-4D9F-B3D4-149D45B9B94C@.microsoft.com...
> Hi
> I am running my sql server 2000 server and agent as a service with domain
> admin user.
> Is this recommended?
No! A domain admin is over privileged, restrict the account to what you need
to do.
Check out the requirements for this account at
http://msdn.microsoft.com/library/d.../>
ew_6k1f.asp
You may also want to read some of the information on:
http://www.sqlsecurity.com/DesktopD...index=0&tabid=1

> Who should be the job owner? sa or application user?
This will depend what the job does e.g if you need to restrict the job to
the privleges of the application user or if the job needs higher
permissions.

> Can the job be run by any other user other than the user who started the
> sqlserver agent.
>
Yes, look at sp_start_job to run the job at a non-scheduled time. Check out
books online for information about the context in which jobs are run.

>
> TKS
> Mangesh
John

job execution user change and ownership question

Hi
I am running my sql server 2000 server and agent as a service with domain
admin user.
Is this recommended?
Who should be the job owner? sa or application user?
Can the job be run by any other user other than the user who started the
sqlserver agent.
TKS
MangeshHi
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:907D7E49-4B58-4D9F-B3D4-149D45B9B94C@.microsoft.com...
> Hi
> I am running my sql server 2000 server and agent as a service with domain
> admin user.
> Is this recommended?
No! A domain admin is over privileged, restrict the account to what you need
to do.
Check out the requirements for this account at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
You may also want to read some of the information on:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabindex=0&tabid=1
> Who should be the job owner? sa or application user?
This will depend what the job does e.g if you need to restrict the job to
the privleges of the application user or if the job needs higher
permissions.
> Can the job be run by any other user other than the user who started the
> sqlserver agent.
>
Yes, look at sp_start_job to run the job at a non-scheduled time. Check out
books online for information about the context in which jobs are run.
>
> TKS
> Mangesh
John

job execution user change and ownership question

Hi
I am running my sql server 2000 server and agent as a service with domain
admin user.
Is this recommended?
Who should be the job owner? sa or application user?
Can the job be run by any other user other than the user who started the
sqlserver agent.
TKS
Mangesh
Hi
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:907D7E49-4B58-4D9F-B3D4-149D45B9B94C@.microsoft.com...
> Hi
> I am running my sql server 2000 server and agent as a service with domain
> admin user.
> Is this recommended?
No! A domain admin is over privileged, restrict the account to what you need
to do.
Check out the requirements for this account at
http://msdn.microsoft.com/library/de...rview_6k1f.asp
You may also want to read some of the information on:
http://www.sqlsecurity.com/DesktopDe...ndex=0&tabid=1

> Who should be the job owner? sa or application user?
This will depend what the job does e.g if you need to restrict the job to
the privleges of the application user or if the job needs higher
permissions.

> Can the job be run by any other user other than the user who started the
> sqlserver agent.
>
Yes, look at sp_start_job to run the job at a non-scheduled time. Check out
books online for information about the context in which jobs are run.

>
> TKS
> Mangesh
John

job execution Time

i observed a strange problem in my production setup. i have a job which updates usage metrics (for reporting) which is scheduled to run once in a day. (the job invokes an sp to do this. the sp refers two tables to retrieve/update information, say TableA and TableB).

the job normally takes an average of 25 seconds to complete. all of a sudden the job execution time increased to 6 minutes and 52 seconds. now, the average job execution time is 8 minutes. there is no table/sp change in the DB

the only thing i observed is that one of the tables referred by the sp has 30,000 records added to it, on the day from which the job execution time increaed to 6 minutes.

i have updated the statistics on the Table, but the execution time remains unchanged. can any one suggest any possible causes for such a scenario.

i expect a few hints with which i can explore my production DB and find out the causes for the increased execution time for the sp.

Pl discuss...

Thanks in advancethe only thing i observed is that one of the tables referred by the sp has 30,000 records added to it, on the day from which the job execution time increaed to 6 minutes.

I think you may have answered your own question! If the columns that your stored procedures are referencing are not indexed, they will likely have to perform table scans. You mentioned that 30,000 rowd were added...how many did you start off with.

Have you tried generating an execution plan using Query Analyzer? That will give you a good indicaton of where the problem lies.

Job Execution sometimes failed

Dear All,
I have a problem with sql server jobs.
The jobs are executing DTS Package in scheculed manner.
The problem is the sometimes the schedule failed, and i don't know what
made the jobs failed.
And I have tried to trace it, but still coundn't find the problem.
Does anyone can help me out?
Thanks
Robert LieTry switching on Package logging in your DTS Packages. Right click in the
DTS Package, choose Package Properties and then Logging. When the job fails
again, you can then check the Package log (right-click the DTS Package in
Enterprise Manager, then choose package logs...). Hopefully you will find
the answer in there.
--
Jacco Schalkwijk
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:%2397xWRfuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Dear All,
> I have a problem with sql server jobs.
> The jobs are executing DTS Package in scheculed manner.
> The problem is the sometimes the schedule failed, and i don't know what
> made the jobs failed.
> And I have tried to trace it, but still coundn't find the problem.
> Does anyone can help me out?
> Thanks
> Robert Lie|||This is the error message when the job failed:
Executed as user: <domain>/<user>. ...un OnStart:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_10 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTS... Process
Exit Code -1073741819. The step failed.
Thanks
Robert Lie
Jacco Schalkwijk wrote:
> Try switching on Package logging in your DTS Packages. Right click in the
> DTS Package, choose Package Properties and then Logging. When the job fails
> again, you can then check the Package log (right-click the DTS Package in
> Enterprise Manager, then choose package logs...). Hopefully you will find
> the answer in there.
>|||For the pump, you can set it to allow a certain number of errors, then log
these errors to a file, this should help you find what the pump is failing
on
If it is a Transform Data Task...Select The Properties..Then the Options
Tab...Enter an Exception File....Set Max Error Count to SOmething Like
9999...Run The Step...Review The Exception Log
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:OrpONQguFHA.1472@.TK2MSFTNGP15.phx.gbl...
> This is the error message when the job failed:
> Executed as user: <domain>/<user>. ...un OnStart:
> DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_10 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart: DTSStep_DTSDataPumpTask_1
> DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
> DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
> DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
> DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
> DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
> DTSRun OnStart: DTSStep_DTS... Process Exit Code -1073741819. The step
> failed.
>
> Thanks
> Robert Lie
> Jacco Schalkwijk wrote:
>> Try switching on Package logging in your DTS Packages. Right click in the
>> DTS Package, choose Package Properties and then Logging. When the job
>> fails again, you can then check the Package log (right-click the DTS
>> Package in Enterprise Manager, then choose package logs...). Hopefully
>> you will find the answer in there.|||Ok. I'll try your idea anyway.
I want to tell you how the DTS Package works:
1. Disable ALL contraint from all tables in a database
2. If success then continue to delete all the tables
3. Fill the deleted tables from another tables from another server
4. Update some values in certain tables
Maybe you see something wrong in that work flow?
Thanks
Robert Lie
David J. Cartwright wrote:
> For the pump, you can set it to allow a certain number of errors, then log
> these errors to a file, this should help you find what the pump is failing
> on
> If it is a Transform Data Task...Select The Properties..Then the Options
> Tab...Enter an Exception File....Set Max Error Count to SOmething Like
> 9999...Run The Step...Review The Exception Log
> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
> news:OrpONQguFHA.1472@.TK2MSFTNGP15.phx.gbl...
>>This is the error message when the job failed:
>>Executed as user: <domain>/<user>. ...un OnStart:
>>DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6
>>DTSRun OnStart: DTSStep_DTSExecuteSQLTask_10 DTSRun OnFinish:
>>DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart: DTSStep_DTSDataPumpTask_1
>>DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
>>DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
>>DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
>>DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
>>DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
>>DTSRun OnStart: DTSStep_DTS... Process Exit Code -1073741819. The step
>>failed.
>>
>>Thanks
>>Robert Lie
>>Jacco Schalkwijk wrote:
>>Try switching on Package logging in your DTS Packages. Right click in the
>>DTS Package, choose Package Properties and then Logging. When the job
>>fails again, you can then check the Package log (right-click the DTS
>>Package in Enterprise Manager, then choose package logs...). Hopefully
>>you will find the answer in there.
>
>|||do you actualy delete/recreate the table, or do you use TRUNCATE, and also,
why dont you do the updates in the DTS Transformation, maybe then you can
keep contrainsts
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:ei%23VHiguFHA.2620@.TK2MSFTNGP10.phx.gbl...
> Ok. I'll try your idea anyway.
> I want to tell you how the DTS Package works:
> 1. Disable ALL contraint from all tables in a database
> 2. If success then continue to delete all the tables
> 3. Fill the deleted tables from another tables from another server
> 4. Update some values in certain tables
> Maybe you see something wrong in that work flow?
> Thanks
> Robert Lie
>
> David J. Cartwright wrote:
>> For the pump, you can set it to allow a certain number of errors, then
>> log these errors to a file, this should help you find what the pump is
>> failing on
>> If it is a Transform Data Task...Select The Properties..Then the Options
>> Tab...Enter an Exception File....Set Max Error Count to SOmething Like
>> 9999...Run The Step...Review The Exception Log
>> "Robert Lie" <robert.lie24@.gmail.com> wrote in message
>> news:OrpONQguFHA.1472@.TK2MSFTNGP15.phx.gbl...
>>This is the error message when the job failed:
>>Executed as user: <domain>/<user>. ...un OnStart:
>>DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
>>DTSStep_DTSExecuteSQLTask_6 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_10
>>DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
>>DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
>>DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
>>DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
>>DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_2 DTSRun OnStart: DTSStep_DTSDataPumpTask_3
>>DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
>>DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTS... Process Exit
>>Code -1073741819. The step failed.
>>
>>Thanks
>>Robert Lie
>>Jacco Schalkwijk wrote:
>>Try switching on Package logging in your DTS Packages. Right click in
>>the DTS Package, choose Package Properties and then Logging. When the
>>job fails again, you can then check the Package log (right-click the DTS
>>Package in Enterprise Manager, then choose package logs...). Hopefully
>>you will find the answer in there.
>>

Job Execution sometimes failed

Job execution logged as an error

I have a special necessity in which I need that the result of failed jobs in
SQL Server Agent be logged as errors in Windows Event Viewer.
Normally they are logged as Warnings.
We developed a solution that uses active scripting for writing the errors in
Event Viewer. However, the policy configured prevents the execution of active
scripting.
Is there any other way of writing the result of failed jobs as errors in
Event Viewer?
I was hoping that there was a configuration that could be done to change the
type of event written in Event Viewer.
ThanksMaybe something using a job step of raiserror with log as a
failure branch in the job might work for you.
-Sue
On Tue, 03 Oct 2006 15:51:32 GMT, "jaimefelipe" <u27450@.uwe>
wrote:
>I have a special necessity in which I need that the result of failed jobs in
>SQL Server Agent be logged as errors in Windows Event Viewer.
>Normally they are logged as Warnings.
>We developed a solution that uses active scripting for writing the errors in
>Event Viewer. However, the policy configured prevents the execution of active
>scripting.
>Is there any other way of writing the result of failed jobs as errors in
>Event Viewer?
>I was hoping that there was a configuration that could be done to change the
>type of event written in Event Viewer.
>Thanks|||Thanks for your reply Sue.
I tried the command sugested but, in order to run it, the job has to be
configured as sysadmin and this is not allowed in my company.
Is there other way?
Sue Hoegemeier wrote:
>Maybe something using a job step of raiserror with log as a
>failure branch in the job might work for you.
>-Sue
>>I have a special necessity in which I need that the result of failed jobs in
>>SQL Server Agent be logged as errors in Windows Event Viewer.
>[quoted text clipped - 12 lines]
>>Thanks|||xp_logevent?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jaimefelipe" <u27450@.uwe> wrote in message news:673a0cf792feb@.uwe...
> Thanks for your reply Sue.
> I tried the command sugested but, in order to run it, the job has to be
> configured as sysadmin and this is not allowed in my company.
> Is there other way?
>
> Sue Hoegemeier wrote:
>>Maybe something using a job step of raiserror with log as a
>>failure branch in the job might work for you.
>>-Sue
>>I have a special necessity in which I need that the result of failed jobs in
>>SQL Server Agent be logged as errors in Windows Event Viewer.
>>[quoted text clipped - 12 lines]
>>Thanks
>|||Tibor Karaszi wrote:
>xp_logevent?
>> Thanks for your reply Sue.
>[quoted text clipped - 13 lines]
>>Thanks
I order to run it, the user also has to be sysyadmin or db_owner of the
master database and there is a policy in my company that does not allow it.
So that does not solve my problem yet, but thanks.|||> I order to run it, the user also has to be sysyadmin or db_owner
Did you try it? Below executes just fine on my 2005 installation:
CREATE LOGIN x WITH PASSWORD = 'y'
CREATE USER x
GRANT EXECUTE ON xp_logevent TO x
EXECUTE AS LOGIN = 'X'
GO
EXEC xp_logevent 50001, 'My error', 'ERROR'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jaimefelipe" <u27450@.uwe> wrote in message news:673aaf26ce58d@.uwe...
> Tibor Karaszi wrote:
>>xp_logevent?
>> Thanks for your reply Sue.
>>[quoted text clipped - 13 lines]
>>Thanks
>
> I order to run it, the user also has to be sysyadmin or db_owner of the
> master database and there is a policy in my company that does not allow it.
> So that does not solve my problem yet, but thanks.
>

Job Execution Issue - SQL Server 2005

All,
I have a job set up to automatically execute an SSIS package. The
package itself, when ran, works fine. But when the job runs, it fails.
I looked at the sysjobhistory table in the 'msdb' database to see what
the error was. There were two entries:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\System. The package execution failed. The
step failed.
Ok, so then I went into services and changed the SQLSERVERAGENT to run
as "Administrator" instead of "System".
So, I ran the job again. Got an error. Here's what the sysjobhistory
table said this time:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\Administrator. The package execution
failed. The step failed.
WTF'!?
Thanks in advance,
JeremyProbably best asked over in the dts forum.
You need some more detail, turn on logging, but could it have something to
do with the "protection levels" of SSIS packages? Can you run it from the
command line on the same machine where it fails? Was it developed on a
different machine from where it fails? Look into how SSIS encrypts
"sensitive information" (mostly passwords), and how that has to be handled
for deployment. Also look to see who owns the job, there's something about
job owner and cmd steps, or something like that.
I've been having the same fun over the past few days. Got mine working, but
not at all certain I could enumerate all the hurdles passed.
Josh
"jrcapp@.cre8iveweb.com" wrote:
> All,
> I have a job set up to automatically execute an SSIS package. The
> package itself, when ran, works fine. But when the job runs, it fails.
> I looked at the sysjobhistory table in the 'msdb' database to see what
> the error was. There were two entries:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\System. The package execution failed. The
> step failed.
> Ok, so then I went into services and changed the SQLSERVERAGENT to run
> as "Administrator" instead of "System".
> So, I ran the job again. Got an error. Here's what the sysjobhistory
> table said this time:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\Administrator. The package execution
> failed. The step failed.
>
> WTF'!?
> Thanks in advance,
> Jeremy
>|||Thanks for the reply, Josh! I'll play around w/some of your
suggestions and get back to you.
Thanks,
Jeremy

Job Execution Issue - SQL Server 2005

All,
I have a job set up to automatically execute an SSIS package. The
package itself, when ran, works fine. But when the job runs, it fails.
I looked at the sysjobhistory table in the 'msdb' database to see what
the error was. There were two entries:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\System. The package execution failed. The
step failed.
Ok, so then I went into services and changed the SQLSERVERAGENT to run
as "Administrator" instead of "System".
So, I ran the job again. Got an error. Here's what the sysjobhistory
table said this time:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\Administrator. The package execution
failed. The step failed.
WTF'!?
Thanks in advance,
JeremyProbably best asked over in the dts forum.
You need some more detail, turn on logging, but could it have something to
do with the "protection levels" of SSIS packages? Can you run it from the
command line on the same machine where it fails? Was it developed on a
different machine from where it fails? Look into how SSIS encrypts
"sensitive information" (mostly passwords), and how that has to be handled
for deployment. Also look to see who owns the job, there's something about
job owner and cmd steps, or something like that.
I've been having the same fun over the past few days. Got mine working, but
not at all certain I could enumerate all the hurdles passed.
Josh
"jrcapp@.cre8iveweb.com" wrote:

> All,
> I have a job set up to automatically execute an SSIS package. The
> package itself, when ran, works fine. But when the job runs, it fails.
> I looked at the sysjobhistory table in the 'msdb' database to see what
> the error was. There were two entries:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\System. The package execution failed. The
> step failed.
> Ok, so then I went into services and changed the SQLSERVERAGENT to run
> as "Administrator" instead of "System".
> So, I ran the job again. Got an error. Here's what the sysjobhistory
> table said this time:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\Administrator. The package execution
> failed. The step failed.
>
> WTF'!?
> Thanks in advance,
> Jeremy
>|||Thanks for the reply, Josh! I'll play around w/some of your
suggestions and get back to you.
Thanks,
Jeremy

Job Execution Issue - SQL Server 2005

All,
I have a job set up to automatically execute an SSIS package. The
package itself, when ran, works fine. But when the job runs, it fails.
I looked at the sysjobhistory table in the 'msdb' database to see what
the error was. There were two entries:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\System. The package execution failed. The
step failed.
Ok, so then I went into services and changed the SQLSERVERAGENT to run
as "Administrator" instead of "System".
So, I ran the job again. Got an error. Here's what the sysjobhistory
table said this time:
1) The job failed. The Job was invoked by User DOMAIN\Administrator.
The last step to run was step 1 (Execute LBL Package).
2) Executed as user: DOMAIN\Administrator. The package execution
failed. The step failed.
WTF?!?
Thanks in advance,
Jeremy
Probably best asked over in the dts forum.
You need some more detail, turn on logging, but could it have something to
do with the "protection levels" of SSIS packages? Can you run it from the
command line on the same machine where it fails? Was it developed on a
different machine from where it fails? Look into how SSIS encrypts
"sensitive information" (mostly passwords), and how that has to be handled
for deployment. Also look to see who owns the job, there's something about
job owner and cmd steps, or something like that.
I've been having the same fun over the past few days. Got mine working, but
not at all certain I could enumerate all the hurdles passed.
Josh
"jrcapp@.cre8iveweb.com" wrote:

> All,
> I have a job set up to automatically execute an SSIS package. The
> package itself, when ran, works fine. But when the job runs, it fails.
> I looked at the sysjobhistory table in the 'msdb' database to see what
> the error was. There were two entries:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\System. The package execution failed. The
> step failed.
> Ok, so then I went into services and changed the SQLSERVERAGENT to run
> as "Administrator" instead of "System".
> So, I ran the job again. Got an error. Here's what the sysjobhistory
> table said this time:
> 1) The job failed. The Job was invoked by User DOMAIN\Administrator.
> The last step to run was step 1 (Execute LBL Package).
> 2) Executed as user: DOMAIN\Administrator. The package execution
> failed. The step failed.
>
> WTF?!?
> Thanks in advance,
> Jeremy
>
|||Thanks for the reply, Josh! I'll play around w/some of your
suggestions and get back to you.
Thanks,
Jeremy

Friday, March 30, 2012

Job/DTS Scheduled Execution

JOB With SSIS Step Fails If Package Contains A Script Task

JOB With SSIS Step Fails If Package Contains A Script Task

JOB With SSIS Step Fails If Package Contains A Script Task

JOB With SSIS Step Fails If Package Contains A Script Task

JOB With SSIS Step Fails If Package Contains A Script Task

JOB With SSIS Step Fails If Package Contains A Script Task

JOB With SSIS Step Fails If Package Contains A Script Task

JOB With SSIS Step Fails If Package Contains A Script Task

Friday, March 9, 2012

job execution user change and ownership question

job execution user change and ownership question

job execution user change and ownership question

job execution Time

Job Execution sometimes failed

Job Execution sometimes failed

Job Execution sometimes failed

Job execution logged as an error

Job Execution Issue - SQL Server 2005

Job Execution Issue - SQL Server 2005

Job Execution Issue - SQL Server 2005

JDBC Driver issue

Blog Archive

About Me