Problem scheduling job

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

Problem running Proc from Job

I have a stored procedure that runs fine using the Query Analyzer:

exec sp_ProcessRecords

However, when I create a job to run the stored proc once an hour, the job fails, with the following message:

Executed as user: sa. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

I don't think it's a permission problem, since the job runs as sa.

I don't understand why it would work if I run it manually, but not when it runs as a job.

Any help would be greatly appreciated.Check your parameters that are being passed to the stored procedure. This means that some parameter that is being passed it too long for the datatype and will be truncated.|||Originally posted by rnealejr
Check your parameters that are being passed to the stored procedure. This means that some parameter that is being passed it too long for the datatype and will be truncated.

Unfortunately, the stored proc called from the job does not take any parameters.

The odd thing is that the stored proc will work fine if it is run manually from the Query Analyzer. The error only occurs when the proc is run from the job, using the exact syntax!

I'm at a loss...|||Can you post the stored proc code - or describe what it is doing ? If you are doing inserts/updates then the same problem can occur.sql

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed
."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SCSC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/defaul...kb;en-us;818899
http://support.microsoft.com/defaul...kb;en-us;829444
http://support.microsoft.com/defaul...kb;en-us;812995
http://support.microsoft.com/defaul...kb;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step fail
ed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>sql

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SC
SC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/default...b;en-us;818899
http://support.microsoft.com/default...b;en-us;829444
http://support.microsoft.com/default...b;en-us;812995
http://support.microsoft.com/default...b;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SCSC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
http://support.microsoft.com/default.aspx?scid=kb;en-us;829444
http://support.microsoft.com/default.aspx?scid=kb;en-us;812995
http://support.microsoft.com/default.aspx?scid=kb;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>

Problem Jobs with send mails

hello people,
i have a stored procedure that send mails masive to users, this stored
procedures succesfully execute manually, but programming job for execute
this stored procedurre not send mail, but histoyr job its succesfully.
what its happening?Hi Jesus
You don't say how you are sending the emails, but if the procedure does not
raise an error and only returns an status code then you would need to check
this status code asnd raise your own error to make the step fail.
HTH
John
"Jesus Suarez" wrote:

> hello people,
> i have a stored procedure that send mails masive to users, this stored
> procedures succesfully execute manually, but programming job for execute
> this stored procedurre not send mail, but histoyr job its succesfully.
> what its happening?
>
>|||hello
send mail using xpsmtp, but i dont undertand you, the storede procedure not
error, only if execute this stored procedured using jobs
"John Bell" <jbellnewsposts@.hotmail.com> escribi en el mensaje
news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...[vbcol=seagreen]
> Hi Jesus
> You don't say how you are sending the emails, but if the procedure does
> not
> raise an error and only returns an status code then you would need to
> check
> this status code asnd raise your own error to make the step fail.
> HTH
> John
> "Jesus Suarez" wrote:
>|||Did you specify an output file for the job step and check for messages in th
at output file?
According to http://sqldev.net/xp/xpsmtp.htm, any error from xp_smtp_sendmai
l should return a return
code, also accompanied by an error message.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jesus Suarez" <suarez.lopezjesus@.gmail.com> wrote in message
news:%230LivVP$GHA.4980@.TK2MSFTNGP04.phx.gbl...
> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure no
t error, only if execute
> this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribi en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
>|||Hi Jesus
xp_smtp_sendmail will return 1 if there is a problem, you may want to create
a test job that just sends a test email and selects the return code.
e.g.
DECLARE @.rc int
EXEC @.rc = xp_smtp_sendmail....
SELECT @.rc
Add a log file in the advanced tab of the step so that you have a copy of
the output from the step, this may give you some indication of any error. Yo
u
may also need to change the SQL Agent service account to be a domain account
if network access is an issue.
John
"Jesus Suarez" wrote:

> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure no
t
> error, only if execute this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
>
>

Problem Jobs with send mails

hello people,
i have a stored procedure that send mails masive to users, this stored
procedures succesfully execute manually, but programming job for execute
this stored procedurre not send mail, but histoyr job its succesfully.
what its happening?Hi Jesus
You don't say how you are sending the emails, but if the procedure does not
raise an error and only returns an status code then you would need to check
this status code asnd raise your own error to make the step fail.
HTH
John
"Jesus Suarez" wrote:
> hello people,
> i have a stored procedure that send mails masive to users, this stored
> procedures succesfully execute manually, but programming job for execute
> this stored procedurre not send mail, but histoyr job its succesfully.
> what its happening?
>
>|||hello
send mail using xpsmtp, but i dont undertand you, the storede procedure not
error, only if execute this stored procedured using jobs
"John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
> Hi Jesus
> You don't say how you are sending the emails, but if the procedure does
> not
> raise an error and only returns an status code then you would need to
> check
> this status code asnd raise your own error to make the step fail.
> HTH
> John
> "Jesus Suarez" wrote:
>> hello people,
>> i have a stored procedure that send mails masive to users, this stored
>> procedures succesfully execute manually, but programming job for execute
>> this stored procedurre not send mail, but histoyr job its succesfully.
>> what its happening?
>>|||Did you specify an output file for the job step and check for messages in that output file?
According to http://sqldev.net/xp/xpsmtp.htm, any error from xp_smtp_sendmail should return a return
code, also accompanied by an error message.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jesus Suarez" <suarez.lopezjesus@.gmail.com> wrote in message
news:%230LivVP$GHA.4980@.TK2MSFTNGP04.phx.gbl...
> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure not error, only if execute
> this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
>> Hi Jesus
>> You don't say how you are sending the emails, but if the procedure does not
>> raise an error and only returns an status code then you would need to check
>> this status code asnd raise your own error to make the step fail.
>> HTH
>> John
>> "Jesus Suarez" wrote:
>> hello people,
>> i have a stored procedure that send mails masive to users, this stored
>> procedures succesfully execute manually, but programming job for execute
>> this stored procedurre not send mail, but histoyr job its succesfully.
>> what its happening?
>>
>|||Hi Jesus
xp_smtp_sendmail will return 1 if there is a problem, you may want to create
a test job that just sends a test email and selects the return code.
e.g.
DECLARE @.rc int
EXEC @.rc = xp_smtp_sendmail....
SELECT @.rc
Add a log file in the advanced tab of the step so that you have a copy of
the output from the step, this may give you some indication of any error. You
may also need to change the SQL Agent service account to be a domain account
if network access is an issue.
John
"Jesus Suarez" wrote:
> hello
> send mail using xpsmtp, but i dont undertand you, the storede procedure not
> error, only if execute this stored procedured using jobs
> "John Bell" <jbellnewsposts@.hotmail.com> escribió en el mensaje
> news:FA1FF0E8-207A-4D7D-9886-CA18B58CC286@.microsoft.com...
> > Hi Jesus
> >
> > You don't say how you are sending the emails, but if the procedure does
> > not
> > raise an error and only returns an status code then you would need to
> > check
> > this status code asnd raise your own error to make the step fail.
> >
> > HTH
> >
> > John
> >
> > "Jesus Suarez" wrote:
> >
> >> hello people,
> >>
> >> i have a stored procedure that send mails masive to users, this stored
> >> procedures succesfully execute manually, but programming job for execute
> >> this stored procedurre not send mail, but histoyr job its succesfully.
> >>
> >> what its happening?
> >>
> >>
> >>
>
>