db datatypes: plz

Showing posts with label plz. Show all posts

Wednesday, March 28, 2012

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Â®
>
>

Monday, February 20, 2012

Problem with Date Search "Plz Help Me quickly"

I am trying to search for stored files "for example from date: 15/12/2003 to: 24/6/2006" and when i press search no results appeare the following is the database code:

1 public DataTable searchData(string fileNo,string Title,string dFrom,string dTo,string brief)
2 {
3 string str ="";
4
5 str ="select * from Tb_File where Active = 1 ";
6
7 if (fileNo !="")
8 str +=" and FileNo='" + fileNo +"'";
9 if (Title !="")
10 str +=" and Title like '%" + Title +"%' ";
11 if (brief !="")
12 str +=" and Brief like '%" + brief +"%' ";
13 if (dFrom !="")
14 str +=" and DFrom >= convert(datetime,'" + Convert.ToDateTime(dFrom).ToShortDateString() +"',103) ";
15 if (dTo !="")
16 str +=" and DTo < convert(datetime,'" + Convert.ToDateTime(dTo).ToShortDateString() +"',103) ";
17
18 ole.Open();
19 SqlDataAdapter DA =new SqlDataAdapter(str, ole);
20 DataTable DT =new DataTable();
21 DA.Fill(DT);
22 ole.Close();
23 return DT;
24
25 }

i am using sql 2000, with Visual Studio 2005.

When you use dates in a database, always use the format yyyymmdd as this will eliminate any potential date format issues.

|||

Thanks for your advice, But i have to use this format ==> so do u have a solution ??

ca8msm:
When you use dates in a database, always use the format yyyymmdd as this will eliminate any potential date format issues.

|||

Hi BigSmile,

you can use Profiler to find out what query is executed actually when you run your code. This might give you an idea of where it has gone wrong.

-Kishore

|||

BigSmile:
But i have to use this format

Why?! You want to fix the sql to return the correct records, and it looks like it could potentially be a date formatting problem, so you should use the correct format...

|||

There is probably some ambiguity in the date formats on both ends (e.g. yyddmm and yymmdd).

To avoid this you can write your dates out explicitly as "yyyy-MMM-dd" as in this example: "2007-Oct-20".
This is parsed by SQLServer correctly and avoids all ambiguities.

Writing this out is more code than you are currently writing, but is worth it to ensure 100% accuracy.

I also note you are using string concatenation - you should look at using System.Text.StringBuilder object instead.
Combining many string together produces lots of copies in memory as the string objects are immutable - StringBuilder has only one memory allocation as all appends done are on the same object.

try something like:

System.Text.Stringbuilder sb = new System.Text.StringBuilder(500);
sb.Append("Select * from tb_File where Active =1 ");

if( fileNo != "")
sb.Append(" and FileNo='" + fileNo + "'");

..etc
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);
...etc.

You could then write the dates out using this stringbuilder:

DateTime fromdate =Convert.ToDateTime(dFrom);
sb.Append( fromdate.Year.ToString() );
sb.Append("-");
sb.Append( fromdate.Month.ToString("MMM") );
sb.Append("-");
sb.Append(" fromDate.Day.ToString() );

|||

You should also read this article onSQL Injection as your site is potentially very insecure.

|||

Thanks, i'll try then mark it as an answer if it apply.

foreachbiscuit:
There is probably some ambiguity in the date formats on both ends (e.g. yyddmm and yymmdd).
To avoid this you can write your dates out explicitly as "yyyy-MMM-dd" as in this example: "2007-Oct-20".
This is parsed by SQLServer correctly and avoids all ambiguities.
Writing this out is more code than you are currently writing, but is worth it to ensure 100% accuracy.
I also note you are using string concatenation - you should look at using System.Text.StringBuilder object instead.
Combining many string together produces lots of copies in memory as the string objects are immutable - StringBuilder has only one memory allocation as all appends done are on the same object.
try something like:
System.Text.Stringbuilder sb = new System.Text.StringBuilder(500);
sb.Append("Select * from tb_File where Active =1 ");
if( fileNo != "")
sb.Append(" and FileNo='" + fileNo + "'");
..etc
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);
...etc.
You could then write the dates out using this stringbuilder:
DateTime fromdate =Convert.ToDateTime(dFrom);
sb.Append( fromdate.Year.ToString() );
sb.Append("-");
sb.Append( fromdate.Month.ToString("MMM") );
sb.Append("-");
sb.Append(" fromDate.Day.ToString() );

|||

i rewrite it as follows:

public DataTable searchData(string fileNo,string Title,string dFrom,string dTo,string brief) { StringBuilder sb =new StringBuilder(500); sb.Append("select * from Tb_File where Active = 1 ");if (fileNo !="") sb.Append(" and FileNo='" + fileNo +"'");if (Title !="") sb.Append(" and Title like '%" + Title +"%'");if (brief !="") sb.Append(" and Brief like '%" + brief +"%'");if (dFrom !="") { DateTime fromdate = Convert.ToDateTime(dFrom); sb.Append(" and DFrom >= '"); sb.Append( fromdate.Year.ToString() ); sb.Append("-"); sb.Append( fromdate.Month.ToString("MMM") ); sb.Append("-"); sb.Append( fromdate.Day.ToString() ); sb.Append("'"); }if (dTo !="") { DateTime todate = Convert.ToDateTime(dTo); sb.Append(" and DTo < '"); sb.Append(todate.Year.ToString()); sb.Append("-"); sb.Append(todate.Month.ToString("MMM")); sb.Append("-"); sb.Append(todate.Day.ToString()); sb.Append("'"); } ole.Open(); SqlDataAdapter DA =new SqlDataAdapter(sb.ToString(), ole); DataTable DT =new DataTable(); DA.Fill(DT); ole.Close();return DT; }

but there still an error and this is the error message:

Syntax error converting datetime from character string.

System.Data.SqlClient.SqlException was unhandled by user code
  Message="Syntax error converting datetime from character string."
  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=16
  LineNumber=1
  Number=241
  Procedure=""
  Server="localhost"
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.HasMoreRows()
       at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
       at System.Data.SqlClient.SqlDataReader.Read()
       at System.Data.ProviderBase.DataReaderContainer.Read()
       at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
       at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
       at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
       at DataAccess.searchData(String fileNo, String Title, String dFrom, String dTo, String brief) in c:\Documents and Settings\Yousef\My Documents\Visual Studio 2005\WebSites\FAProgram\App_Code\DataAccess.cs:line 124
       at FileSearch.bindData() in c:\Documents and Settings\Yousef\My Documents\Visual Studio 2005\WebSites\FAProgram\FileSearch.ascx.cs:line 35
       at FileSearch.btnSearch_Click(Object sender, EventArgs e) in c:\Documents and Settings\Yousef\My Documents\Visual Studio 2005\WebSites\FAProgram\FileSearch.ascx.cs:line 61
       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Plz it is the final part of my project and there is no time, i need your quick replies

|||You need to see what sb.ToString() equals before you execute it. I suggest you start debugging your application and take notice of the previous suggestions that people have given to you (such as using profiler and looking at what SQL Injection means).

|||

Hi BigSmile.
I think I gave you the incorrect code for the writing of the month as I hadn't tested it here.
To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA =new SqlDataAdapter(sb.ToString(), ole);

...and see what the value of sb.ToString() is.
I imagine it is something like "2007-MMM-10"

I think the correct lines should be:

sb.Append( fromdate.ToString("MMM") );

sb.Append( todate.ToString("MMM") );

|||

Sorry, it doesn't work.. i apply and this is the result:

this is the existing files:

and this is the results:

foreachbiscuit:
Hi BigSmile.
I think I gave you the incorrect code for the writing of the month as I hadn't tested it here.
To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA =new SqlDataAdapter(sb.ToString(), ole);

...and see what the value of sb.ToString() is.
I imagine it is something like "2007-MMM-10"
I think the correct lines should be:
sb.Append( fromdate.ToString("MMM") );
sb.Append( todate.ToString("MMM") );

|||

ca8msm:
You need to see what sb.ToString() equals before you execute it. I suggest you start debugging your application and take notice of the previous suggestions that people have given to you (such as using profiler and looking at what SQL Injection means).

foreachbiscuit:
To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);
...and see what the value of sb.ToString() is.

Are you actually bothering to listen to anyones replies? Read the two quotes above and act on the advice given.

|||

Thanks alot i think i found the problem

the string builder contains an incorrect string value:

{select * from Tb_File where Active = 1 and Brief like '%03/11/2007%' and DTo < '2007-Mar-9'}

ca8msm:
ca8msm:
You need to see what sb.ToString() equals before you execute it. I suggest you start debugging your application and take notice of the previous suggestions that people have given to you (such as using profiler and looking at what SQL Injection means).
foreachbiscuit:
To catch this, you should put a breakpoint on your line:
SqlDataAdapter DA = new SqlDataAdapter(sb.ToString(), ole);
...and see what the value of sb.ToString() is.

Are you actually bothering to listen to anyones replies? Read the two quotes above and act on the advice given.

|||

Hi BigSmile,

Looking at your code, the only way this is possible is that you have the parameters coming into the method mixed up.
You are putting the "from date" value in the "brief" string parameter of the searchData method.
This is evident by the fact that the date is not being formatted with the "MMM" format.

(if so, can you also mark this as the answer to the solution! thanks).

Wednesday, March 28, 2012

Problem with Jobs... Plz help!

Problem with Jobs... Plz help!

Problem with Jobs... Plz help!

Monday, February 20, 2012

Problem with Date Search "Plz Help Me quickly"

db datatypes

Blog Archive

About Me