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"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
> --att tasks completed in the last 24 hours
> AND T.completion_date >= DATEADD(hh, -24,GETDATE())-- or enter a specific
> date like this '20050127'
Patrick,
The only thing I can see that might be causing that error is this part; I'm
not sure if this is just wrapped because of the news client, though... You
should double-check that to make sure. I can't find the string '20050128'
in your code anywhere... Do you have some dynamic SQL that's not posted?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||No, I think it's getting this from GETDATE(). The database I'm working with
has a lot of dates stored as CHAR(8), which drives me nuts, as I can ORDER B
Y
on these columns. In my query I CONVERT them to DATETIME, but they're still
formatted as 'YYYYMMDD'.
"Adam Machanic" wrote:
> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
> Patrick,
> The only thing I can see that might be causing that error is this part; I'
m
> not sure if this is just wrapped because of the news client, though... You
> should double-check that to make sure. I can't find the string '20050128'
> in your code anywhere... Do you have some dynamic SQL that's not posted?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||One thing I can suggest is to put brackets ( [ ] ) around all your table and
column names as you have a lot of reserved words as object names.
Andrew J. Kelly SQL MVP
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
>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|||I put brackets around the tables named user_id, and then ran just the first
section (up to the first --), which fails at the first GETDATE with
this error:
Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
failed.
Like I said, it works fine in QA, just not in a job. :(
"Andrew J. Kelly" wrote:
> One thing I can suggest is to put brackets ( [ ] ) around all your table a
nd
> column names as you have a lot of reserved words as object names.
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:F723A708-5FF4-4E8A-9F41-38A49BB08BE4@.microsoft.com...
>
>|||Patrick,
I haven't followed the whole thread but do you actually have "GO" in your
SQL step. "GO" is a batch terminator used by QA and is not part of T-SQL.
So, you would want to take it out.
-oj
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>I put brackets around the tables named user_id, and then ran just the first
> section (up to the first --), which fails at the first GETDATE with
> this error:
> Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
> failed.
> Like I said, it works fine in QA, just not in a job. :(
> "Andrew J. Kelly" wrote:
>|||Good to know, but I removed the USE DatabaseName & GO statements and still
get the same error.
"Patrick Rouse" wrote:
> No, I think it's getting this from GETDATE(). The database I'm working wi
th
> has a lot of dates stored as CHAR(8), which drives me nuts, as I can ORDER
BY
> on these columns. In my query I CONVERT them to DATETIME, but they're stil
l
> formatted as 'YYYYMMDD'.
> "Adam Machanic" wrote:
>|||First off as OJ mentions , do you have the GO's in the actual code? How you
know it is the GETDATE() that is failing or are you assuming because it is a
date type value in the error message? Here is the first block from your
original post:
<<<<<
--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
I can't tell from this post but it looks like you don't have a space between
the end of the CONVERT and the AS. What happens if you set the date to a
variable and use the variable in the select instead?
Andrew J. Kelly SQL MVP
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>I put brackets around the tables named user_id, and then ran just the first
> section (up to the first --), which fails at the first GETDATE with
> this error:
> Incorrect syntax near '20050128'. [SQLSTATE 42000] (Error 170). The step
> failed.
> Like I said, it works fine in QA, just not in a job. :(
> "Andrew J. Kelly" wrote:
>|||Patrick,
Make it easier on us if you would script out the job and post it here. ;-)
(you can use EM to do that).
There's probably a silly syntax error.
-oj
"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:58F52F80-E014-4CF7-8868-9A919AA4EBEA@.microsoft.com...
> Good to know, but I removed the USE DatabaseName & GO statements and still
> get the same error.
>|||I assume it's the get date, because that returns the date listed in the erro
r
I posted, and the error states on "line 12" which is where GETDATE is
specified.
"Andrew J. Kelly" wrote:
> First off as OJ mentions , do you have the GO's in the actual code? How y
ou
> know it is the GETDATE() that is failing or are you assuming because it is
a
> date type value in the error message? Here is the first block from your
> original post:
> <<<<<
> --begin script
> USE NGEPMProd
> GO
> SELECT TTM.desc_30 AS TaskType, TU.user_id AS Owner, TU.createdate,T.statu
s
> 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
> I can't tell from this post but it looks like you don't have a space betwe
en
> the end of the CONVERT and the AS. What happens if you set the date to a
> variable and use the variable in the select instead?
> --
> Andrew J. Kelly SQL MVP
>
> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:5B11E86A-08E5-4027-926E-84AE2A3AFBEA@.microsoft.com...
>
>
sql
