Problem scheduling job
From: Patrick Rouse (PatrickRouse_at_discussions.microsoft.com)
Date: 01/28/05
- Next message: Randy: "deadlock in agent job"
- Previous message: Nikhil Patel: "instead of triggers and blob columns in views"
- Next in thread: Adam Machanic: "Re: Problem scheduling job"
- Reply: Adam Machanic: "Re: Problem scheduling job"
- Reply: Andrew J. Kelly: "Re: Problem scheduling job"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 28 Jan 2005 08:57:02 -0800
I have a script that I wrote (please pardon my less than beautiful code) to
dump some aggregate data into a table for reporting purposes. It populates a
few temp tables, then inserts into the destination table.
This script works fine in QA, but when I try to run it from a SA Job it
fails with the following error:
Line 12: Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170).
NOTE: The step was retried the requested number of times (1) without
succeeding. The step failed.
Here's my script:
--begin script
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #NotStarted
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 1
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberNotStarted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #NSTotals
FROM #NotStarted
GROUP BY owner
ORDER BY OWNER ASC
--------------------------------------------------------------------------------
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status
INTO #InProgress
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 3
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberInProgress,CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #ProgTotals
FROM #InProgress
GROUP BY owner
ORDER BY OWNER ASC
-----------------------------------------------------------------------------
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status,
CONVERT(char(10), T.followup_date, 101)AS FUDate
INTO #Overdue
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE T.status = '3'
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberOverdue, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #ODTotals
FROM #Overdue
WHERE FUDate < GETDATE()
GROUP BY owner
ORDER BY OWNER ASC
-----------------------------------------------------------------------------
USE NGEPMProd
GO
SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.status,
CONVERT(char(10), T.completion_date, 101) AS CompletedDate
INTO #Completed
FROM tasks T
INNER JOIN task_type_mstr TTM ON
TTM.task_type_id = T.task_type_id
INNER JOIN task_users_assigned_to TU ON
TU.task_id = T.task_id
WHERE t.status = 2
--att tasks completed in the last 24 hours
AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
date like this '20050127'
ORDER BY TU.user_id ASC
SELECT Owner, COUNT(OWNER) AS NumberCompleted, CONVERT(char(10), GETDATE(),
101)AS [DATE]
INTO #CompTotals
FROM #Completed
GROUP BY owner
ORDER BY OWNER ASC
-------------------------------------------------------------------------------------------------------------------------
--insert records into permanent table for reports
INSERT INTO task_totals_collector
SELECT UM.user_id AS Employee, NS.NumberNotStarted AS NotStarted,
PT.NumberInProgress AS InProgress,CT.NumberCompleted AS Completed,
CONVERT(char(10), GETDATE(), 101)AS CalcDate, OT.NumberOverdue AS Overdue
FROM user_mstr UM
LEFT JOIN #NSTotals NS
ON UM.user_id=NS.owner
LEFT JOIN #ProgTotals PT ON
UM.user_id=PT.owner
LEFT JOIN #CompTotals CT ON
UM.user_id=CT.owner
LEFT JOIN #ODTotals OT ON
UM.user_id=OT.owner
ORDER BY UM.user_id ASC
--end of script
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com
- Next message: Randy: "deadlock in agent job"
- Previous message: Nikhil Patel: "instead of triggers and blob columns in views"
- Next in thread: Adam Machanic: "Re: Problem scheduling job"
- Reply: Adam Machanic: "Re: Problem scheduling job"
- Reply: Andrew J. Kelly: "Re: Problem scheduling job"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
