Problem with Jobs... Plz help!

I have several jobs that run simple T-SQL scripts...
Most of them, fail, with the following problem:
"Executed as user: dba. The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (Error
242) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Cursor
is not open. [SQLSTATE 42000] (Error 16917). The step failed."
Some parts are specific from a example job I'll post here, but the "char to
datetime conversion" error, appears in all of them.. There is transcript from
the Job that raised that error:
/***********/
DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
varchar (10), @.IP varchar (50), @.cmd varchar (500)
IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
BEGIN
DECLARE cur_opv CURSOR FOR
select prot_nr_prt
,prot_nr_ano
,dias
,dh_recebimeto
,uaie_ds_sigla
from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
OPEN cur_opv
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
END
CLOSE cur_opv
DEALLOCATE cur_opv
END
/***********/
It's important to note that, the ONLY time when this script deal with some
datetime type, is on the DATEPART function on the If. This If is just to see
if it is 10 o'clock... It wasn't me who wrote this script, because it's
stupid to control the date by the script and not by the job schedule..
Anyways, I prefered to keep this way... But I don't think that those line of
code could be the problem...
These job errors are making me crazy! If someone know something about, plz
help me, I can send some other samples of jobs returning errors!
Thanks!!!
Rafa?
I don't see the problem either so try this...
1. check the view, make sure the problem is NOT in the view... Select from
the view etc in query analyzer. then
2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
xp_cmdshell stuff - just get the loop going.)
3. add the IF statement with the datepart...
Sneak up on the problem in query analyzer...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> I have several jobs that run simple T-SQL scripts...
> Most of them, fail, with the following problem:
> "Executed as user: dba. The conversion of a char data type to a datetime
> data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
(Error
> 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
Cursor
> is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> Some parts are specific from a example job I'll post here, but the "char
to
> datetime conversion" error, appears in all of them.. There is transcript
from
> the Job that raised that error:
> /***********/
> DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> varchar (10), @.IP varchar (50), @.cmd varchar (500)
> IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> BEGIN
> DECLARE cur_opv CURSOR FOR
> select prot_nr_prt
> ,prot_nr_ano
> ,dias
> ,dh_recebimeto
> ,uaie_ds_sigla
> from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> OPEN cur_opv
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> END
> CLOSE cur_opv
> DEALLOCATE cur_opv
> END
> /***********/
> It's important to note that, the ONLY time when this script deal with some
> datetime type, is on the DATEPART function on the If. This If is just to
see
> if it is 10 o'clock... It wasn't me who wrote this script, because it's
> stupid to control the date by the script and not by the job schedule..
> Anyways, I prefered to keep this way... But I don't think that those line
of
> code could be the problem...
> These job errors are making me crazy! If someone know something about, plz
> help me, I can send some other samples of jobs returning errors!
> Thanks!!!
> Rafa
|||The view is Pretty Simple...
It don't even have a datetime field... Look at it's structure:
prot_nr_prtint
prot_nr_anosmallint
diasint
dh_recebimetovarchar(10)
uaie_ds_siglavarchar(12)
The strange thing about this, is that the same error occurs on other jobs...
And when I copy the T-SQL Statement to QA, it runs perfectly...
I don't know if it can be some Database option conflicting with some
statement the people are using on the procs...
"Wayne Snyder" wrote:

> I don't see the problem either so try this...
> 1. check the view, make sure the problem is NOT in the view... Select from
> the view etc in query analyzer. then
> 2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
> xp_cmdshell stuff - just get the loop going.)
> 3. add the IF statement with the datepart...
> Sneak up on the problem in query analyzer...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Rafa?" <Rafa@.discussions.microsoft.com> wrote in message
> news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> (Error
> Cursor
> to
> from
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> see
> of
>
>
sql

Problem with Jobs... Plz help!

I have several jobs that run simple T-SQL scripts...
Most of them, fail, with the following problem:
"Executed as user: dba. The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (
Error
242) Associated statement is not prepared [SQLSTATE HY007] (Error 0) C
ursor
is not open. [SQLSTATE 42000] (Error 16917). The step failed."
Some parts are specific from a example job I'll post here, but the "char to
datetime conversion" error, appears in all of them.. There is transcript fro
m
the Job that raised that error:
/***********/
DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
varchar (10), @.IP varchar (50), @.cmd varchar (500)
IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
BEGIN
DECLARE cur_opv CURSOR FOR
select prot_nr_prt
,prot_nr_ano
,dias
,dh_recebimeto
,uaie_ds_sigla
from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
OPEN cur_opv
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"''
'
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"''
'
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"
'''
exec (@.cmd)
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
END
CLOSE cur_opv
DEALLOCATE cur_opv
END
/***********/
It's important to note that, the ONLY time when this script deal with some
datetime type, is on the DATEPART function on the If. This If is just to see
if it is 10 o'clock... It wasn't me who wrote this script, because it's
stupid to control the date by the script and not by the job schedule..
Anyways, I prefered to keep this way... But I don't think that those line of
code could be the problem...
These job errors are making me crazy! If someone know something about, plz
help me, I can send some other samples of jobs returning errors!
Thanks!!!
Rafa?I don't see the problem either so try this...
1. check the view, make sure the problem is NOT in the view... Select from
the view etc in query analyzer. then
2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
xp_cmdshell stuff - just get the loop going.)
3. add the IF statement with the datepart...
Sneak up on the problem in query analyzer...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rafa" <Rafa@.discussions.microsoft.com> wrote in message
news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> I have several jobs that run simple T-SQL scripts...
> Most of them, fail, with the following problem:
> "Executed as user: dba. The conversion of a char data type to a datetime
> data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
(Error
> 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
Cursor
> is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> Some parts are specific from a example job I'll post here, but the "char
to
> datetime conversion" error, appears in all of them.. There is transcript
from
> the Job that raised that error:
> /***********/
> DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> varchar (10), @.IP varchar (50), @.cmd varchar (500)
> IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> BEGIN
> DECLARE cur_opv CURSOR FOR
> select prot_nr_prt
> ,prot_nr_ano
> ,dias
> ,dh_recebimeto
> ,uaie_ds_sigla
> from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> OPEN cur_opv
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providncias h ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> END
> CLOSE cur_opv
> DEALLOCATE cur_opv
> END
> /***********/
> It's important to note that, the ONLY time when this script deal with some
> datetime type, is on the DATEPART function on the If. This If is just to
see
> if it is 10 o'clock... It wasn't me who wrote this script, because it's
> stupid to control the date by the script and not by the job schedule..
> Anyways, I prefered to keep this way... But I don't think that those line
of
> code could be the problem...
> These job errors are making me crazy! If someone know something about, plz
> help me, I can send some other samples of jobs returning errors!
> Thanks!!!
> Rafa|||The view is Pretty Simple...
It don't even have a datetime field... Look at it's structure:
prot_nr_prt int
prot_nr_ano smallint
dias int
dh_recebimeto varchar(10)
uaie_ds_sigla varchar(12)
The strange thing about this, is that the same error occurs on other jobs...
And when I copy the T-SQL Statement to QA, it runs perfectly...
I don't know if it can be some Database option conflicting with some
statement the people are using on the procs...
"Wayne Snyder" wrote:

> I don't see the problem either so try this...
> 1. check the view, make sure the problem is NOT in the view... Select from
> the view etc in query analyzer. then
> 2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT t
he
> xp_cmdshell stuff - just get the loop going.)
> 3. add the IF statement with the datepart...
> Sneak up on the problem in query analyzer...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Rafa?" <Rafa@.discussions.microsoft.com> wrote in message
> news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> (Error
> Cursor
> to
> from
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> '"'''
> see
> of
>
>

Problem with Jobs... Plz help!

I have several jobs that run simple T-SQL scripts...
Most of them, fail, with the following problem:
"Executed as user: dba. The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (Error
242) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Cursor
is not open. [SQLSTATE 42000] (Error 16917). The step failed."
Some parts are specific from a example job I'll post here, but the "char to
datetime conversion" error, appears in all of them.. There is transcript from
the Job that raised that error:
/***********/
DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
varchar (10), @.IP varchar (50), @.cmd varchar (500)
IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
BEGIN
DECLARE cur_opv CURSOR FOR
select prot_nr_prt
,prot_nr_ano
,dias
,dh_recebimeto
,uaie_ds_sigla
from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
OPEN cur_opv
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
'/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP + '"'''
exec (@.cmd)
FETCH NEXT FROM cur_opv
INTO @.prt, @.ano, @.dias, @.data, @.IP
END
CLOSE cur_opv
DEALLOCATE cur_opv
END
/***********/
It's important to note that, the ONLY time when this script deal with some
datetime type, is on the DATEPART function on the If. This If is just to see
if it is 10 o'clock... It wasn't me who wrote this script, because it's
stupid to control the date by the script and not by the job schedule..
Anyways, I prefered to keep this way... But I don't think that those line of
code could be the problem...
These job errors are making me crazy! If someone know something about, plz
help me, I can send some other samples of jobs returning errors!
Thanks!!!
Rafa®I don't see the problem either so try this...
1. check the view, make sure the problem is NOT in the view... Select from
the view etc in query analyzer. then
2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
xp_cmdshell stuff - just get the loop going.)
3. add the IF statement with the datepart...
Sneak up on the problem in query analyzer...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> I have several jobs that run simple T-SQL scripts...
> Most of them, fail, with the following problem:
> "Executed as user: dba. The conversion of a char data type to a datetime
> data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
(Error
> 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
Cursor
> is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> Some parts are specific from a example job I'll post here, but the "char
to
> datetime conversion" error, appears in all of them.. There is transcript
from
> the Job that raised that error:
> /***********/
> DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> varchar (10), @.IP varchar (50), @.cmd varchar (500)
> IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> BEGIN
> DECLARE cur_opv CURSOR FOR
> select prot_nr_prt
> ,prot_nr_ano
> ,dias
> ,dh_recebimeto
> ,uaie_ds_sigla
> from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> OPEN cur_opv
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
'"'''
> exec (@.cmd)
> FETCH NEXT FROM cur_opv
> INTO @.prt, @.ano, @.dias, @.data, @.IP
> END
> CLOSE cur_opv
> DEALLOCATE cur_opv
> END
> /***********/
> It's important to note that, the ONLY time when this script deal with some
> datetime type, is on the DATEPART function on the If. This If is just to
see
> if it is 10 o'clock... It wasn't me who wrote this script, because it's
> stupid to control the date by the script and not by the job schedule..
> Anyways, I prefered to keep this way... But I don't think that those line
of
> code could be the problem...
> These job errors are making me crazy! If someone know something about, plz
> help me, I can send some other samples of jobs returning errors!
> Thanks!!!
> Rafa®|||The view is Pretty Simple...
It don't even have a datetime field... Look at it's structure:
prot_nr_prt int
prot_nr_ano smallint
dias int
dh_recebimeto varchar(10)
uaie_ds_sigla varchar(12)
The strange thing about this, is that the same error occurs on other jobs...
And when I copy the T-SQL Statement to QA, it runs perfectly...
I don't know if it can be some Database option conflicting with some
statement the people are using on the procs...
"Wayne Snyder" wrote:
> I don't see the problem either so try this...
> 1. check the view, make sure the problem is NOT in the view... Select from
> the view etc in query analyzer. then
> 2. copy out the entire cursor loop in QA and test it. THEN (leaving OUT the
> xp_cmdshell stuff - just get the loop going.)
> 3. add the IF statement with the datepart...
> Sneak up on the problem in query analyzer...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Rafa®" <Rafa@.discussions.microsoft.com> wrote in message
> news:AB7004F1-6029-4DF0-BB5F-965587234C87@.microsoft.com...
> > I have several jobs that run simple T-SQL scripts...
> > Most of them, fail, with the following problem:
> >
> > "Executed as user: dba. The conversion of a char data type to a datetime
> > data type resulted in an out-of-range datetime value. [SQLSTATE 22007]
> (Error
> > 242) Associated statement is not prepared [SQLSTATE HY007] (Error 0)
> Cursor
> > is not open. [SQLSTATE 42000] (Error 16917). The step failed."
> >
> > Some parts are specific from a example job I'll post here, but the "char
> to
> > datetime conversion" error, appears in all of them.. There is transcript
> from
> > the Job that raised that error:
> >
> > /***********/
> >
> > DECLARE @.prt varchar (10), @.ano varchar (4), @.dias varchar (5), @.data
> > varchar (10), @.IP varchar (50), @.cmd varchar (500)
> > IF datepart (hh, getdate()) = 10-- or datepart (hh, getdate()) = 15
> > BEGIN
> > DECLARE cur_opv CURSOR FOR
> > select prot_nr_prt
> > ,prot_nr_ano
> > ,dias
> > ,dh_recebimeto
> > ,uaie_ds_sigla
> > from int_prot_jur.dbo.vw_OPV_AVISO_ANDAMENTO
> > OPEN cur_opv
> > FETCH NEXT FROM cur_opv
> > INTO @.prt, @.ano, @.dias, @.data, @.IP
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > set @.cmd = 'xp_cmdshell ''net send ssouza "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send fyueda "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providencia há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send rolima "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send pbarbosa "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send agallo "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> > set @.cmd = 'xp_cmdshell ''net send mntorres "A OPV do processo ' + @.prt +
> > '/' + @.ano + ', foi recebida em ' + @.data + ' e se encontra aguardando
> > providências há ' + @.dias + ' dias, o processo se encontra no ' + @.IP +
> '"'''
> > exec (@.cmd)
> >
> > FETCH NEXT FROM cur_opv
> > INTO @.prt, @.ano, @.dias, @.data, @.IP
> > END
> > CLOSE cur_opv
> > DEALLOCATE cur_opv
> > END
> >
> > /***********/
> >
> > It's important to note that, the ONLY time when this script deal with some
> > datetime type, is on the DATEPART function on the If. This If is just to
> see
> > if it is 10 o'clock... It wasn't me who wrote this script, because it's
> > stupid to control the date by the script and not by the job schedule..
> > Anyways, I prefered to keep this way... But I don't think that those line
> of
> > code could be the problem...
> >
> > These job errors are making me crazy! If someone know something about, plz
> > help me, I can send some other samples of jobs returning errors!
> >
> > Thanks!!!
> > Rafa®
>
>

Problem with Jobs

Hello all,

I am having a problem with the sql

DB on the company that I work for.

I got message under job history

on Enterprise manager is :

"The job failed.The Job was invoked by Schedule 15 (Replication

agent schedule.).The last step to run

was step 1 (Run agent.)"

and in the event log is:

Event ID: 208

Source: SQLAgent$(company DB)

"sql server Schedule (company DB)

status:Failed"

Please advice.

Thanks in advance

Kind regards

This relates to replication issues, which can be quite complex depending on what you're trying to do. You should review your replication scheme to ensure that all the data is going where it should. If the replication isn't happening, your files will grow on the distributor until it runs out of room.

If you're new to replication, check here:

http://msdn2.microsoft.com/en-us/library/ms151198.aspx

Problem with job permissions

SO here's the deal - I have a developer that needs to be able to schedule jobs, but that I do NOT want to give System admin privelges to. She has DBO privelges on the DBs she needs, and is a member of Bulk Insert and Process ADmin server roles. She has the abiltiy to schedule jobs but if she creates one that uses CmdExe steps they fail with the message:

Executed as user: MCC_GENESIS\SQLSrv. The process could not be created for step 1 of job 0xB793D66AE8016647AC64FFEB3EE2E202 (reason: A required privilege is not held by the client). The step failed.

I have configured the SQL Agent to use a proxy account to run scripts if the user is not SysAdmin, and I am using the same domian account that starts the MSSQL service as the proxy. The account is a DOMAIN admin (I know bad idea, but that's what they gave me to work with.)

So here is the question - why do jobs fail when they are owned by the debveloper, but run when owned by SA - both are using the same account, (SA using account that starts server, develped using it thorugh proxy)

Any advie woudl be greatly appreciatedDid you reset the SQLAgentCmdExec permissions ?|||Originally posted by rnealejr
Did you reset the SQLAgentCmdExec permissions ?

I actually found the answer on anotehr forum, I had to grant the user permisson to replace a process level token. Once I did that, everythign worked fine.

Thanks for the reply :)|||Would you mind posting the solution - for future reference ?|||The solution was just that - granting the NTUser ID permission to replace proces level tokens - this can be done with policies or local system permissions. I think by default this is granted already, but the organization I am consulting for had it locked down. Once that was done, non- SA users could use CmdExe steps in their jobs.|||I was asked by another user to post the solution:

You first have to set up a Proxy account for running scripts as a non-sysadmin,

1. Open Enterprise manager.
2. Click on the server name -> Management
3. Right Click on SQL Server Agent
4. Choose the Job System tab
5. Uncheck the box that says "Only users with sysadmin privleges can execute CMdExec and ActiveScripting job steps.
6. You will be prompted to enter log in information - enter the system account info you want to use (Domain or Local) to run scripts - it should be at least a local admin account for the box, I normally use the same logon as that used by the MSSQL service.

then Verify/Change permissions.

1. Open Administrative tools (You will need admin rights on the server to do this)
2. Go to Local Security Policy ( this can also be done at the domain level, speak to your LAN admin about how they would like this setup)
3. Click on "user rights Assignment" in the left hand pane,
4. In the right hand pane, scroll down until you find " replace a process level token".
5. Double click on "Replace a Process level token", and add the proxy user account (Step 6 above).
6. Apply the change. You may have to restart the MSSQL and SQL Server Agent services for the change to take place, I am not sure.|||Originally posted by cdols
These are the exact steps I'm using to setup the Proxy account however, when I attempt to enter the login information for the Proxy I get the following error: "Unable to set the SQL Agent proxy account because of the reason listed below. 'Error executing extended stored procedure: Specified user can not login' "

What's strange is that this is the same account that the Server & Server Agent services use (and they are working fine). This domain account has also been added to SQL as a login with system admin privileges. The account has also been made a local administrator on the server.

In terms of testing the account, I can connect successfully to SQL with Query Analyzer using this domain account.

I've actually reloaded the OS & reinstalled SQL2000 and still have the problem (OS is 2000 Server and SQL2K is SP3).

What I have found for solutions so far assume that you actually CAN set the Proxy account.

Anyone have an idea what's going on?

Doug|||Hi,

Was you able to find the solution?

Thanks,
Leosql

Problem with displaying workflow?

Hi,

I just recognized a strange displaying problem in one of my SSIS jobs.

I created job which contains a sequence container. Within the sequence container there is one "Execute SQL Task" and one "Foreach Loop Container". Within the "Foreach Loop Container" there are 4 task which are connected with precedence constraints.

Now my problem is that if I load the SSIS job and open the "sequence container" while the "Foreach Loop Container" is already open then the precedence constraint won't be displayed. If I close and open the "Foreach loop container" again then the precedence constraints will be displayed again.

My first assumption was that it might be a problem with the display drivers of the computer ....however the problem appears also on any other computer.

Does anyone know how to solve this display problem without closeing/reopening the container?

Thanks,

StSt

StSt,

You just have to lump it I'm afraid. It happens from time to time.

-Jamie

Problem with date and time function

I am using MSSQL 2000. I building a search page in which all jobs listed since the past seven days will be selected sequentially. The dropdownlist goes from 0-7, if i select 1 it should give yestedays jobs , if i select 2 it should give all the jobs within two days and so on upto seven days. I am using this Sql Statement but its not working out. Has anyone got any idea about this problem?
SELECT * FROM TableName WHERE ColumnName BETWEEN DATEADD(dd,-1, GETDATE( )) AND GETDATE( )
Thanx in AdvanceYou probably want a stored proc that would look something like this:

CREATE PROCEDURE GetJobs
(
@.Days INT -- Days before the current date as a positive integer.
)
AS
SET @.Days = @.Days * -1
DECLARE @.StartDate DATETIME
DECLARE @.EndDate DATETIME
SET @.StartDate = DATEADD(day, @.Days, CONVERT(VARCHAR(12), GETDATE()))
SET @.EndDate = DATEADD(second, -1, DATEADD(day, @.Days + 1, CONVERT(VARCHAR(12), GETDATE())))

SELECT * FROM TableName WHERE ColumnName BETWEEN @.StartDate AND @.EndDate

|||'Cept for that I didn't read your post carefully enough. To getthe jobs from all previous days, instead of a single selected day, it'dlook something like this:

CREATE PROCEDURE GetJobs
(
@.Days INT -- Days before the current date as a positive integer.
)
AS
SET @.Days = @.Days * -1
DECLARE @.StartDate DATETIME
DECLARE @.EndDate DATETIME
SET @.StartDate = DATEADD(day, @.Days, CONVERT(VARCHAR(12), GETDATE()))
SET @.EndDate = DATEADD(second, -1, CONVERT(VARCHAR(12), GETDATE()))

SELECT * FROM TableName WHERE ColumnName BETWEEN @.StartDate AND @.EndDate

|||Xander, I suggest that this might work better:

CREATE PROCEDURE GetJobs
(
@.Days INT -- Days before the current date as a positive integer.
)
AS
SET @.Days = @.Days * -1
DECLARE @.Now
DECLARE @.StartDate DATETIME
DECLARE @.EndDate DATETIME
SELECT @.Now = GETDATE()
SET @.StartDate = DATEADD(day, @.Days, CONVERT(VARCHAR(12),@.Now))
SET @.EndDate = CONVERT(char(8), @.Now,112)

SELECT * FROM TableName WHERE ColumnName >= @.StartDate AND ColumnName < @.EndDate

What you suggested could give unexpected results if it is a fewmilliseconds before midnight. What I have done takes millisecondsout of the equation. Also, it's good to take a snapshot of thecurrent time in the beginning and use that static date/time for theremainder of the procedure. This way you don't need to worryabout the stored procedure crossing date boundaries in the middle ofexecution.
|||Thank you very much for all your replies.
I tried the above idea and worked perfectly.
The problem which was bugging me finally got solved.
Thanx once again