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 job that run ssis pckg

Hi,

i have a job that run ssis pckgs and it failed when the server is log off with this error msg:

Executed as user: xxxxx\dwhadmin. ...0.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:15:00 AM Error: 2007-07-11 09:15:01.71 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-07-11 09:15:26.77 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PackagePassword" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-07-11 09:15:26.77 Code: 0xC0016016 Source: Description: Faile... The package execution fa... The step failed.

what can be the problem?

thanks.

Hi Steam5,

There are already answers aviled in this forums. I got it resolved in my case. There are several ways.

One way to deploy an SSIS package into a new machine where you actually created this package is to Use "Save copy of <Your SSIS Pkg> as" on the File menu in BIDS. Use EncryptAllSensitivewithPassword option with a password. Now this package can be executed on another Box using dtexec with /De option to decrypt the password.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

thanks for the reply but it's not working.

:-( .

any other idea?

|||

Hi

We get slightly different errors here with SSIS, but the step succeeds.

Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 09:55:33 Finished: 09:55:34 Elapsed: 1.15 seconds. The package executed successfully. The step succeeded.

Have you tried hardcoding the password into the connection string in the job step? Sometimes depending on the type of connection (oracle OLDBD) the password is not deployed with the package and you need to override the connection string with the one in the job, including the password.

Hope it helps, bit of a long shot.

Cheers

Matt

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.
Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:

> When in em, I try to open a table and return all rows, I get a weird error:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest that
> it might be MDAC. I cannot install MDAC either. It says that the operation
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.
|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installation
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:
> When in em, I try to open a table and return all rows, I get a weird error:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest that
> it might be MDAC. I cannot install MDAC either. It says that the operation
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installation
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:
> > When in em, I try to open a table and return all rows, I get a weird error:
> > "The query cannot be executed because some o fthe files are missing or not
> > registered."
> > Also, I cannot connect to the database with a dataadapter in vs (2003)
> > I can, however, execute an SQL in Query Analyzer.
> > I have reinstalled several times. Some of the Google articles suggest that
> > it might be MDAC. I cannot install MDAC either. It says that the operation
> > is not supported by the OS (xp Pro) The components are already installed.
> > Any input would be greatly appreciated.

Problem with Enterprise Manager

When in em, I try to open a table and return all rows, I get a weird error:
"The query cannot be executed because some o fthe files are missing or not
registered."
Also, I cannot connect to the database with a dataadapter in vs (2003)
I can, however, execute an SQL in Query Analyzer.
I have reinstalled several times. Some of the Google articles suggest that
it might be MDAC. I cannot install MDAC either. It says that the operation
is not supported by the OS (xp Pro) The components are already installed.
Any input would be greatly appreciated.Hi
A few questions!
Did you try and remove the software before re-installing?
Did you rung the MDAC component checker to see if it says your installation
is consistent?
Have you tried to apply any service packs?
You may wish to un-register/re-register all the dlls in {Installation
Directory}\80\Tools\Binn?
John
"walter1234" wrote:

> When in em, I try to open a table and return all rows, I get a weird error
:
> "The query cannot be executed because some o fthe files are missing or not
> registered."
> Also, I cannot connect to the database with a dataadapter in vs (2003)
> I can, however, execute an SQL in Query Analyzer.
> I have reinstalled several times. Some of the Google articles suggest tha
t
> it might be MDAC. I cannot install MDAC either. It says that the operatio
n
> is not supported by the OS (xp Pro) The components are already installed.
> Any input would be greatly appreciated.|||I just ran into this issue myself, I've tried the reinstall etc. I'm going
to try and un-register/re-register all of the DLLs in that directory but
there are 46 of them. Any suggestions on an easy way to do that?
I also tried the suggestions in Article ID: 315868.
Cory Blythe
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A few questions!
> Did you try and remove the software before re-installing?
> Did you rung the MDAC component checker to see if it says your installatio
n
> is consistent?
> Have you tried to apply any service packs?
> You may wish to un-register/re-register all the dlls in {Installation
> Directory}\80\Tools\Binn?
> John
> "walter1234" wrote:
>

Problem with DTS

Hi,
I was wondering if anyone has ever had this problem before.
I have a whole host of DTS packages that are executed from one DTS package.
The DTS packages essentially get data from a Sybase database and puts the
data into a text file, runs a simple Stored proc which etc, nothing really
complicated.
Problem is lately when I run the DTS package which is called from a Batch
file which is executed by scheduler, random packages seem to go into a
continuous loop and the package continues to insert data into the text file
until such time as my server runs out of space.
Any ideas anyone.
Thanks
KNever seen that problem.
Do you have the tasks set to "Close connection on completion"
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Kathy Bezuidenhout" <Kathy.Bezuidenhout@.nospam.aig.com> wrote in message
news:ab2cnTWLr4q05gqiU-KYjQ@.is.co.za...
> Hi,
> I was wondering if anyone has ever had this problem before.
> I have a whole host of DTS packages that are executed from one DTS
package.
> The DTS packages essentially get data from a Sybase database and puts the
> data into a text file, runs a simple Stored proc which etc, nothing
really
> complicated.
> Problem is lately when I run the DTS package which is called from a Batch
> file which is executed by scheduler, random packages seem to go into a
> continuous loop and the package continues to insert data into the text
file
> until such time as my server runs out of space.
> Any ideas anyone.
> Thanks
> K
>

Problem with deleting connection from Connection Manager

Hallo,

I have deleted one of my file connection from Connection Manager (and

Executed ProcessTask which used it), however

trying to load the package I always get the error:

"Error loading MyPackage.dtsx: The connection "MyDeletedConnectionName" is not found.
This error is thrown by Connections collection when the specific connection element is not found."

I tried to Clean, Rebulid my project, but it doesn't help.

Does anybody know, how to solve it? Where this reference is saved?

Thank you.

Anna

Try to open the package XML in a text editor and look for that connection name.

HTH,

Bob

|||

I am sorry, it was my mistake -

I forgot to delete the configuration file...

it works fine now.

Anna