All my DTS scheduled Jobs fail after I installed SQL SP3. But if I run the DTS package from Enterprise Manager it executes successfully.
Here is one of the errors:
The job failed. The Job was invoked by Schedule 7 (dtsImportCustomers_0001). The last step to run was step 1 (dtsImportCustomers_0001).
and
Executed as user: SQLADM. DTSRun: Loading... Process Exit Code 1. The step failed.
Thanks for your help!
I am trying to set up a DTS to transfer logging data from one server to another.
The record may already exist at the destination causing a primary key violation. I do not want this error to cause the entire DTS to fail.
When I execute the DTS I created by right clicking and selecting "Execute Package" it shows me 2 errors. Although there are 2 errors the rows that do not have a primary key violation are successfully transfered to the destination database.
Here are the 2 errors I see:
Error 1:
Error at Destination for Row number 97. Errors encountered so far in this task: 97.
The statement has been terminated.
Violation of PRIMARY KEY constraint 'PK_event'. Cannot insert duplicate key object 'event'.
Error 2:
Error at Destination for Row number 198. Errors encountered so far in this task: 198.
The statement has been terminated.
Violation of PRIMARY KEY constraint 'PK_eventDetail'. Cannot insert duplicate key object 'eventDetail'.
These errors make sense, there were 97 duplicate lines in the event table and 198 duplicates in the eventDetail table.
This is the behavior I want. New rows are copied to the destination database.
When I schedule the DTS as a Job in the Enterprise manager things change. When the DTS is executed as a Job (as opposed to me right clicking and selecting "Execute Package"), the job reports a failure and none of the new rows are transfered to the destination database.
Why does the DTS transfer the rows that do not violate the Primary Key constraint when I manually execute it and not when it is executed as a job?
How can I get the DTS to function as desired?
Thanks,
Andy
I have 2 DTS packages that import data from an Access database that have recently started failing. They run fine manually in DTS, but not manually as a job. They get an "unspecified error." These were running fine, until we installed Outlook and started to add Outlook mail to SQL.
Originally, Administrator was the owner and that is how the jobs were run. We changed to SQLAdmin for the SQLAgent to start under, and I changed the owner of the jobs to SQLAdmin. This works for all jobs but these 2. I thought maybe SQLAdmin could not get to the Access database, but it can. I spell out the path for the database, I don't use any mappings. I recreated the jobs logged in as SQLAdmin, and they still do not work as jobs.
Any ideas are much appreciated!! Thanks in advance!!
Karen
I recently changed the SA password on my SQL Server 6.5 installation and discovered that this had caused the Scheduled CmdExec Tasks (defined in SQL Executive) to fail with:
"Process Exit Code 1. Microsoft (R) SQLMaint Utility, Version 6.50.240 Copyright (C) Microsoft Corporation, 1995 - 1996 [Microsoft SQL-DMO] Error 4002: [SQL Server] Login failed"
Changing the password back to the original resolves the problem, but is a less than satisfactory solution.
Any advice on how I can change the SA password and allow CmdExec tasks to continue running would be gratefully appreciated.
I can not get SQLServerAgent to Send Mail. Help!
SQLMail started successfully.
In the server agent properties I use the Test button and I receive the
response.
"Successfully started(and stopped) a mail session with this profile."
Indicating that mail should work.
If I send a email notification from a scheduled job it gives me the error:
NOTE: Failed to notify 'operations' via email
In the SQL Server Agent Error log I get.
"[264] An attempt was made to send an email when no email session has been
established"
xp_sendmail works fine.
Any ideas would be greatly appreciated.
TIA...
I'm thinking of using the SQL Agent Job Scheduler as part of a larger application and I'm wondering if anyone knows of a limit on how many schedules or jobs that can exist on a SQL Server at one time.
Thanks!
hello All
I am trying to figure out a way , looking at the tables in MSDB (SysJobHistory) that if a scheduled job is running and not completed yet , for how long it has been running. I have to look for all the jobs with run_status = 4 (in process). But what i figured out it no history is written till the job is finished or cancelled. Can anyone help me with this
thanks
Alsi
Hi,
I have a scheduled job that run one time a day at 10 pm. My problem is that if the job fail, i want that the job run 10 minutes after until it complete with success.
Any tips on how to do this
Martin
I am running a scheduled DTS which transfers all the rows in a production table to another server every 30 minutes,each time it truncates the table on the second server before transfer.It has been running fine for several days.Will there be any problem in this kind of backup strategy? Do I have to clear any history logs frequently?Or any other problem can happen? Can anyone suggest any precautions,as there will not be any down time allowed.Replication is also not acceptable by the client.
Thanks.
Hi all,
I find some scheduled jobs are switching from an 'enabled' state to a 'disabled' state apparently for no good reason. The job itself still shows as being 'enabled', however the associated scheduled becomes 'disabled'. Does anyone why this would be? Is the problem associated with a paritcular service pack or anything??
Any help is appreciated.
Thanks!
I have a job that runs hourly every day from 7:00 to 19:00.
I'd like to be able to detect the last run of the day.
The problem is, I may change one (or both) or the scheduled run times,
so I don't want to hard code 19:00 into my detection scheme.
I stumbled across the sysjobsschedules table in the msdb database,
and I think the Next_Run_date and Next_Run_time fields will get me where
I need to be.
I'm trying to build a second job that runs at 10 minutes after the hour, 24 hours a day that will somehow detect whether or not the primary job just finished it's last run of the day, and if so, insert some records into a table.
Here's what I have so far...
DECLARE @intFlg1 INTEGER
SET @intFlg1= (SELECT CASE WHEN CONVERT(DATETIME, CAST(next_run_date AS CHAR(8)), 102) = Prod_Plan.dbo.RemoveTime(GETDATE()) THEN 1 ELSE 0 END
FROM msdb.dbo.sysjobschedules
WHERE (name = N'prodplan_importorders'))
IF @intFlg1=0
INSERT INTO BLDOFF_INV_DAILY()
SELECT GETDATE() AS Expr1, Product, Whse, Qty
FROM BldOff_Inv_Hourly
The problem with this is that it will append records every hour after the last run until midnight. I only want it to append them once.
Its not possible to schedule jobs via Express is it? Like I have a process that connects to an Oracle system grabs the data... plays with it some and then inserts it into the MSSQL 2005 server... We need this process to run say every 2 hours... Express cant do that cant it?
View 4 Replies View RelatedToday when I came into work, I noticed all of my jobs failed on a particular server. I tried to manually kick off the packages, but to no avail. Both the job history and the error message from the packages state a timeout error.
Any idea on this? Also, I have no problem running a query from query analyzer...even on a remote machine.
Any help would be appreciated
Trevor
The SQL Server Agent process is running. When I use EM to start a database integrity
check job, it fails with an error message of 22029 and the following:
[LOG] SQLServerAgent security context does not have server-autorestart privileges
I can not find error 22029 in Books on Line. I will go to Microsoft Knowledge base
and research this problem.
In the mean time if someone can help, it is much appreciated.
Thanks to all.
Hello,
It is our yearly procedure to change the sa password.
This time we do it for the first time in ss2005.
After changing the sa password, all jobs fail with the following error:
MessageLogin failed for user 'sa'. [CLIENT: <local machine>]
MessageError: 18456, Severity: 14, State: 8.
When i check the properties of the job i can see (in the general tab) that all jobs are running through Windows authentication.
Any newly created jobs run successfull.
What do i do wrong?
Any help is appreciated.
Best regards,
Harry
Please refer me to a good article for creating alerts for sql jobs failure. Thanks a lot.
View 1 Replies View RelatedI have this backup job created, this job is being called from VB once all the data is finished loading. You need to have SA permissions or be the database owner in order to run the jobs.
How do I get around this, I don't want the user to be db owner or have sa privledges.
Does anyone have any SP to get around this
Thanks for any help
Hi all
I'm stuck here, I hope someone else has come across a problem like this.
About 12 scheduled jobs are running on SQL Server 7 (NT is the operating system)
They are supposed to run under the security context of an account set up to run as a service for both SQL Server Agent and SQL scheduling Agent. This user account was given all the permissions that the person scheduling them has(that's me, also part of sysadmin role). Everything was running fine until one day the server crashed and after it came back up, nothing was working. The error I get is an odbc link error. I was even unable to open up the packages, so I had to recreate them from saved .dts files. I did come to find out that the NT domain admin password was changed as well, but why should that affect the user account under which these jobs run? Anyway, at the moment, nothing runs on the server and I can only manually execute them as packages from my local computer. Please help me to narrow down what could be the problem. The Agent service and sql server services are running so that's not the problem. thanks...
Hello,
Does anyone has a script or knows how to find out whether the schedule job completed or not?
If I schedule the job to backup all databases on server and e-mail me when job completes.
But one time the job got stuck and somehow never completed. Is there a way I can schedule the job to see whether the other job completed or not?
Thanks for the Help
Rea
Had to rename a SQL 2000 box and now the scheduled maint jobs that were created under the previous name of the box cannot be deleted via EM. Can anyone offer help?
View 1 Replies View RelatedWe have DTS Packages that are scheduled to run nightly and show up in EM under JOBS under SQL Server Agent. A password got changed and some of the nightly jobs blew up.
Went into the Packages and entered the new password and successfully executed the packages BUT when I go back to jobs under the SQL Server Agent, the jobs STILL will not run - they fail with a login failure - as if i never fixed the DTS package. HELP
Hello,
Is there a way that I can create an audit trail of changes that are made to scheduled jobs in SQL Server 2000?
Thanks
Hi,
Can anybody help me in solving this issue. We are currently facing an issue in our scheduled jobs. We have a job scheduled in SQL Server 7.0, which executes SPs one by one. The job makes the database to single user mode and starts the clean up job (execute SP, which deletes old records from specified tables). Once this is done, the database is again converted to multi use.Our database size is 8 GB, there is a huge volume of data. our client started the job on one weekend and it continued till tuesday after noon, on wednessday morning, the job got failed.
Please help me in solving this issue. why the job taking so much time to run and how it can be monitored and tuned to work properly.
A quick response will be more appreciated.
Thanks and Regards
Deepak
Running MSSQL 2000 on Windows2000 in an NT domain. We have several jobs created by the web assistant wizard. They run along just fine for days or weeks, then all of a sudden SOME of them (?!) begin to fail with the message "Unable to perform a SETUSER to the requested username 'dbo' because the username is invalid for database 'xxx'" where xxx is of course the db where the proc resides. SQLAgent service is running as a domain admin and has not changed; job is owned by sa and has not changed; the domain admin in question is in sa role and has not changed. Something sure has changed, but what? Sometimes when this happens we can clear it up by deleting the web job and simply re-creating it. Today we have three culprits and none of our usual tricks are working. Why does this happen? How can we fix it? How can we PREVENT it! Thanks for any words of wisdom out there.
View 1 Replies View RelatedHi Guys,
I'm Hoping some one could help me out, I'm in need of creating a web interface to monitor scheduled SQL Jobs, both on sql 2000 & 2005.
I'm new to asp, Can anyone point me in the right direction.Really appreciate it.
Regards
i have a package scheduled in sql server.....
if i change something in the package and save the change...
will the scheduled job then reflect that change? or does the scheduled job have to be deleted and the package re-scheduled in order for the change to take affect?
(for example....i have a scheduled package which pulls some data from an oracle server and populates a sql server tabel. well....the oracle server has changed. so if i open my package and change the oracle server connection and save it, will the scheduled job have this change?)
Getting this message when running a dtsrun step in a scheduled job;
DTSRun: Loading... DTSRun: Executing... Error: -2147220499 (800403ED); Provider Error: 0 (0) Error string: No Steps have been defined for the transformation Package. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 700. Process Exit Code 1. The step failed.
This just started today.. These jobs have been running for months with no problems.
Any help would be appreciated.
When I change the SA password on my server the current Scheduled SQL Jobs no longer work. If I run DTS packages by themselves and not from the Scheduled job window they work fine. I am using the sa as the Owner of the jobs and my SQL Agents services use a Domain Account. If I rebuild the jobs after the sa password is change they work fine. However, I don't want to rebuild 100 jobs every time I change my SA password.How can I fix or get around this problem without rebuilding my jobs?
View 5 Replies View RelatedWe have scheduled jobs, which run every 2 minutes. If the job runs at 1:59am, the next run time is set to 2:01 am. If at 2:00am, the clock is rolled back to 1:00, my scheduled run time is still 2:01am. In this situalion, my 2 minute job will not run until 1 hour and 1 minute later, at 2:01am.
Any suggestions for reseting the next run times on the jobs to the next interval after the clock roles back to 1:00am.
-Matt
Hi
Is it possible to add jobs into the SQL schedule by means of a SQL script. We have 1000 remote servers to update and don't really want to do it manually.
Any help appreciated.
Ian
Hello,
I have a job scheduled within Enterprise manager. How can I configure this job, within enterprise manager, to generate output files having the current system date and time as part of the output file names.
Regards,
Albert
NB: It is not a backup job just a simple query.
Is there a way to refresh AUTOMATICALLY each n seconds, the scheduled jobs instead of right clicking on the scheduled job and selecting 'refresh' ?
An option in SQL server ?
Or and never ending script that refreshes the scheduled jobs ?
Thanks
In the notifications tab of the job I have e-mail operator selected. Click on the box with the elipses to enter the email addresses. It will let me enter 2 email addresses in the e-mail name field. It looks like the field has a limited length...is there a way to manually enter several addresses?
View 1 Replies View Related