Scheduled Job Not Working
May 19, 2006
I have created a scheduled job in the enterprise manager, which checks around 20 different date columns and replaces any non-dates with NULL using the following syntax: -
update policy set [exp] =null where isdate (substring([exp],4,2)+'-'+left([exp],2)+'-'+'200'+right ([exp],1 ))=0
go
update policy set [eff] =null where isdate (substring([eff],4,2)+'-'+left([eff],2)+'-'+'200'+right ([eff],1 ))=0
go
update policy set [written] =null where isdate (substring([written],4,2)+'-'+left([written],2)+'-'+'200'+right ([written],1 ))=0
go
etc......
When I start the job, if fails within 2 seconds. If I copy the syntax in to the query analyzer it works fine.
All help appreciated.
View 7 Replies
ADVERTISEMENT
Feb 2, 2006
Hi,
I have created a DTS job that contains one 'Execute SQL Task' job. This SQL task runs a cmd file. The cmd file runs a few windows commands and then runs a Micorosoft Access Macro. Once finished both access and the cmd screen close down.
If I open up the DTS job and execute it manually, it works fine (takes about 1/2 an hour to run). My problem is that when I schedule the DTS job, the job starts up at the correct time but it never actually starts running the cmd file and it gives no error. It says Executing until I actually stop the job manually.
The Job details are:
Type: Operating System Command [CmdExec]
Command: DTSRun /~Z0x2F3FF84472BB6E7FF356EB006BA1AEC62C95AB3BF506F3 4A241F228CE148AB09DBC66B8651A450B725E6C4E6A1D328E4 EC2F2C0F8E323F1C7D501FD5B8FD00E25656514AF2224407DB 1C569163CBE383A8E7D8BE4974A0911F5CEB
The DTS details are:
C:BatchesDTSrunofUpdatesqlpmi.cmd
The cmd program details are:
echo on
:Start
if not exist "c:appsCI_Databasespmipmiload.mdb" exit
rem cleanup unfinsihed runs
if exist "c:appsCI_Databasespmipmiload.mdb" if exist "c:appsCI_Databasespmipmiloadold.mdb" del "c:appsCI_Databasespmipmiloadold.mdb"
rem main file locked assume its being work on so don't run this job.
if exist "c:appsCI_Databasespmipmiload.ldb" exit
rem if a tmp exits then the last job never completed - continue to add to tmp file
if exist "c:appsCI_Databasespmipmiloadtmp.mdb" goto Load
copy "c:appsCI_Databasespmipmiload.mdb" "c:appsCI_Databasespmipmiloadtmp.mdb"
:Load
"C:Program Filesmicrosoft office 2003OFFICE11msaccess.exe" "c:appsCI_Databasespmipmiloadtmp.mdb"/x updatedata
rem Compact databases
"C:Program Filesmicrosoft office 2003OFFICE11msaccess.exe" "c:appsCI_Databasespmipmiloadtmp.mdb"/compact
ren "c:appsCI_Databasespmipmiload.mdb" "pmiloadold.mdb"
ren "c:appsCI_Databasespmipmiloadtmp.mdb" "pmiload.mdb"
if exist "c:appsCI_Databasespmipmiload.mdb" if exist "c:appsCI_Databasespmipmiloadold.mdb" del "c:appsCI_Databasespmipmiloadold.mdb"
All help is greatly appreciated as this has been bugging me for some time now.
Thanks
Sam
View 2 Replies
View Related
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Jul 23, 2005
Hi!I scheduled a DTS-Import from MySQL, whenever I run it manually(Right-Click on the DTS package) it runs through without any problems.But firing it by a schedule doesn't work!?Just to exclude any issues regarding users/roles, I created a DTS toexport files to my desktop to an EXCEL-sheet. Manually export as wellas scheduled export works fine.My Application Log shows me following error message:Event Type:WarningEvent Source:SQLSERVERAGENTEvent Category:Job EngineEvent ID:208Date:6/8/2005Time:10:05:02 AMUser:N/AComputer:*****Description:SQL Server Scheduled Job 'ImportFromMySQL'(0xC89612CE034F6642BD585B048DBC0F06) - Status: Failed - Invoked on:2005-06-08 10:05:02 - Message: The job failed. The Job was invoked bySchedule 22 (ImportFromMySQL). The last step to run was step 1(ImportFromMySQL).Anybody know what's wrong!?
View 11 Replies
View Related
Jun 19, 2005
Hi guys,i want to make a scheduled job that inserts 10 user defined row each minute continously in enterprise manager
View 1 Replies
View Related
Dec 27, 2001
I have designed a DTS Package and it can be run successfully from Enterprise Manager. However, when I schedule the DTS package to run as a job then it fails with an error message of "Error string: The system cannot find the file specified.".
Anyone any idea as to why the job cannot find the DTS package?
Can it be something to do with SQL Server 7 and 2000 tools as 2000 Client tools have recently been installed onto my PC and the package and job ran fine when I had designed everything in SQL Server 7.
Thanks in advance
Vince.
View 1 Replies
View Related
Mar 1, 2002
Hi,
I am trying to do an automatic backup of my database and for some reason it does not do it.
I have it set to backup daily at 4:00 pm.
Please let me know if you know why it is not backing up on it's own.
The Server manager is on always and a manual backup is not a problem for me,
only the automatic.
Thanks very much.
View 1 Replies
View Related
Mar 12, 2002
Hello
I have a number of DTS packages which when run manually complete successfully however, when run as scheduled tasks they always fail. Can anyone offer any advice?
Many thanks
View 3 Replies
View Related
Jul 27, 2001
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!
View 1 Replies
View Related
Aug 9, 2000
Hello,
I have many dts packages scheduled as jobs . job always fails when executed. It runs fine if i execute the dts package.
the follwing is the error message
Error: -2147217887 (80040E21); Provider Error: 0 (0) Error string: Errors occurred Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
any help would be welcome
thenk you
View 2 Replies
View Related
Mar 6, 2000
When I create a DTS to import data from Visual FoxPro it will work if I run immeadiately, but when I schedule it to run at a specific time it will Fail.
Any ideas why??
Thanks for any input,
Tom
View 1 Replies
View Related
Mar 24, 2000
I have a DTS that executes fine except when run as a scheduled event. Where can I research the error codes that occur below?
DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_10 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart: Copy Data from FirmDirExp to [CG_Directory].[dbo].[FirmDirExp] Step DTSRun OnError: Copy Data from FirmDirExp to [CG_Directory].[dbo].[FirmDirExp] Step, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 700 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 700 Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8) Error string: The Microsoft Jet database engine ca... Process Exit Code 1. The step failed.
View 1 Replies
View Related
Nov 15, 2000
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
View 5 Replies
View Related
Sep 6, 2000
Hi,
we run a nightly job processes, this job depends on the data entered from the frontend, since yesterday we have been entering lot of data in it, so the job that ran last night 10pm(09/05/00) is still running now and its next schedule time 10pm today(09/06/00), if the same job is already running since yesterday and if it still runs till 10pm today, will it starts again as per the schedule or it will not run, since the same job is running since yesterday.
Thanks,
Madhuker.
View 3 Replies
View Related
Oct 10, 2000
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
View 1 Replies
View Related
Oct 4, 2000
In SQL 6.5, when a scheduled job failed, you could see the error message in the history. In SQL 7.0, it simply tells you which was the last step to run. Is there a place which will report the actual error message generated by the task?
View 4 Replies
View Related
Sep 28, 2000
Hi All,
I have created a DTS package on a developement server that connects to our Exchange server and downloads customer service e-mail and inserts them into a table. This is done using a VB script.
When I right click on the job and execute it, it runs fine and we can see the mails after they show up in the table.
However, when I schedule the job to run at 15 minute intervals it fails with a vb runtime error. When I copy the job to my personal machine and schedule it, it runs just fine and again we see the mails in the table.
The server has the latest version of the scrrun.dll as well as IE 5.0.
I have messed around with this for 3 days now and have gotten MSFT involved as well.
Anyone seen this before? Any ideas or help will be greatly appreciated.
Brad Keck
View 1 Replies
View Related
Aug 20, 2000
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.
View 2 Replies
View Related
Aug 16, 2000
Why is it that I can run my DTS package locally howeverv when I try to run it as a Job it always fails? I do realize that the sqladmin account is used to run the job and it has all the permissions needed.
Any suggestions?
Also if I kick the job off from my local system it states it cannot find the batch file that I am trying to run? Im running it on the server however it treats it like im running it locally?
Very frustrated..
Thanks
David
View 5 Replies
View Related
Aug 16, 2000
I have just taken away a tremendous amount of rights from our developers, but would like some of the developers to still have rights to manage the scheduled job. Short of making them a system administrator I can not seem to find a predefined role that will do this. Is there one? And if not what system stored procedures or xp would you need to give them rights to, to view and run the scheduled jobs in enterprise manager.
View 1 Replies
View Related
Sep 15, 1999
If I have 2 scheduled tasks set for the same time (perhaps accidentally), will the
SQL Executive start 1 and queue the other one until the first is complete and
then run the 2nd task? Or will they both be started simultaneously?
Thanks!
Toni Eibner
View 2 Replies
View Related
Sep 15, 2004
Hiya,
Got a little question for future reference. I have a cursor that does a grant for roles, users, etc. for all tables in a DB. I can manually run it in query analyzer and it does fine. The issue is that if I try to create it as a SQL Agent job and just paste the script into the command box it returns the error:
Line 1: Incorrect syntax near 'xxx yyy'. [SQLSTATE 42000] (Error 170) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Line 1: Incorrect syntax near 'xxx yyy'. [SQLSTATE 42000] (Error 170). The step failed.
The Cursor is written as follows:
declare @cStatement varchar(255)
declare G_cursor CURSOR for select 'grant select on[' + convert(varchar(64),name) + ']to "xxx yyy"' from sysobjects
where (type = 'U' or type = 'V') and uid = 1
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
I figure this has something to do with the quoted_identifier option or something simple like that, but I can't put my finger on it....In Query Analyzer this will error out if I don't have double quotes around the DB role "xxx yyy" because of the space in the role name.
I corrected the error by recreating the role name without a space, but I have some other places I'd like to be able to use this where I won't have the luxury of recreating the role if it has a space in the name.
Any takers?:confused:
View 1 Replies
View Related
Sep 17, 2004
I have the below code which works to rebuild indexes on a large db. I can run it from QA, but not as a job or as a sp in a job. I get the same error
Executed as user: ADCsqlexec. Retrieving Table List for DB Development [SQLSTATE 01000] (Message 0) ReIndexing Table Development..cms_appointments [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Updating Statistics on Table Development..cms_appointments [SQLSTATE 01000] (Message 0) Could not complete cursor operation because the set options have changed since the cursor was declared. [SQLSTATE 42000] (Error 16958). The step failed.
Here is the tsql, any ideas
SET QUOTED_IDENTIFIER OFF
/* Start with master DB */
USE master
/* Create Variables */
DECLARE @DBName CHAR(64)
DECLARE @TableName CHAR(64)
DECLARE @FQTableName CHAR(64)
DECLARE @TempVar CHAR(256)
/* Create DB List */
DECLARE DBCursor CURSOR FOR
SELECT name
FROM master..sysdatabases where name = 'development'
OPEN DBCursor
FETCH NEXT
FROM DBCursor
INTO @DBName
/* Create Database Loop */
WHILE @@FETCH_STATUS = 0
BEGIN
/* Retrieve Table List */
PRINT 'Retrieving Table List for DB ' + @DBName
EXEC ('SELECT name AS TableName INTO ##TableNames FROM [' + @DBName + ']..sysobjects WHERE type = ''U''')
/* Open Table List */
DECLARE TableCursor CURSOR FOR
SELECT TableName
FROM ##TableNames
OPEN TableCursor
FETCH NEXT
FROM TableCursor
INTO @TableName
/* Create Table Loop */
WHILE @@FETCH_STATUS = 0
BEGIN
/* Add DB Name to Table Name */
SELECT @FQTableName = QUOTENAME(RTRIM(@DBName)) + '..' + QUOTENAME(RTRIM(@TableName))
SELECT @TableName = RTRIM(@DBName) + '..' + RTRIM(@TableName)
/* fix from ms */
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
-- DBCC CHECKTABLE(mytable)
/* ReIndex Table */
PRINT 'ReIndexing Table ' + @TableName
DBCC DBREINDEX(@TableName)
/* Update Statics on Table */
PRINT 'Updating Statistics on Table ' + @TableName
EXEC ('UPDATE STATISTICS ' + @FQTableName)
/* Get Next Table Name */
FETCH NEXT
FROM TableCursor
INTO @TableName
END
/* Close Table Cursor */
CLOSE TableCursor
DEALLOCATE TableCursor
/* Remove Tempory Table */
DROP TABLE ##TableNames
/* Get Next Table Name */
FETCH NEXT
FROM DBCursor
INTO @DBName
END
/* Close DB Curosor */
CLOSE DBCursor
DEALLOCATE DBCursor
/* Finished */
View 3 Replies
View Related
Feb 12, 1999
I have been running the following production job successfully for a long time.
It now fails, and the Task History Last Error Message displays 'No Message'.
The log file ( C:MSSQLLOGMaint_TombV50.txt) shows it ran successfully,
with a Return Code 0.
SQLMAINT.EXE -D TombV50 -RebldIdx 10
-CkDB -CkAl -CkTxtAl -CkCat
-UpdSts -Rpt C:MSSQLLOGMaint_TombV50.txt
It looks like SQL Executive burps for some reason.
Any ideas, comments, suggestions?
Thanks,
sb
View 1 Replies
View Related
Dec 17, 2007
Hello,
I'm new to SQL so this should be an easy question. All i want to do is create a recuring task. I want to copy data from one SQL server to another. I've created a package to accomplish this and when i execute it manually it works. What doesn't work is scheduling to execute a regular intervals. As a test i set it up to execute every minute every day (This however will change to once a day when i prove it works) and for some reason it never executes. Any ideas? By the way i created the package using the DTS export wizzard.
View 2 Replies
View Related
Jun 23, 1999
To all,
If I have a scheduled tasks that is owned by 'sa', how can I assign permissions to allow another user, even the database dbo, to register the SQL server and view the scheduled tasks?
Thanks in Advance,
Ed Molinari
View 1 Replies
View Related
Apr 24, 2002
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!
View 1 Replies
View Related
Oct 30, 1998
I created a scheduled task on SQL server 6.5 which is actually dump system DB.
The problem I have is the scheduled task did not run with no error messsage returned. I have tried to force it run in different schedule modes. Nothing happened. However I can dump system DB through SEM Backup/Restore which runs OK!
Any body has idea why scheduled task does not run ?
Thanks
View 1 Replies
View Related
Nov 26, 2007
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
View 1 Replies
View Related
Jun 30, 2004
I've scheduled a job to run on a certain schedule, but the Last Run Status date comes back very oddly, a couple years out of synch, the other jobs scheduled report back just fine.
Anyone seen this behavior?
Edward R Hunter, Data Application Designer
comScore Networks, Inc.
View 1 Replies
View Related
Jan 19, 2005
Hi,
I've got a DTS package with a single step that runs an exe via Process Task. The DTS runs fine when I execute it manually, but when I schedule the package, the job says it runs successfully, but does not execute the package. Now, a few facts:
The DTS is owned by user1
The Job is owned by user1
All security credentials match up
The exe is on a network with full unc path specified
SQLAgent service is running in user1's context
All user input is on the actual SQL server, but exe is on network
When I change the DTS from running the exe to performing a simple query, the job executes the DTS just fine. When I move the exe from the network and place it locally on the server, the job still won't execute the DTS. What am I missing here? This is driving me crazy!!
View 4 Replies
View Related
Mar 3, 2005
How can I tell when a DTS package is scheduled to run?
If I right click on the name, and select "Schedule Package", it brings up
default data (12:00 am Daily), and I KNOW that's not the scheduled time.
Thanks
View 2 Replies
View Related
