HELP :( - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

HELP :(

Author

Topic

carla
Starting Member

12 Posts

Posted - 2011-01-13 : 12:14:12

im having issues running my job, i getting this error message, and i dont have idea what am i missing, please help me

Message
Executed as user: GRUMA\dbservice. 2011 [SQLSTATE 01000] (Message 0) 4 [SQLSTATE 01000] (Message 0) Conversion failed when converting datetime from character string. [SQLSTATE 22007] (Error 241). The step failed.

this is the code on the job:::

DECLARE @day as int
DECLARE @month as int
DECLARE @year as varchar(4)
DECLARE @FechaActual as varchar(12)
DECLARE @futuredate as datetime

SET @day = Day(GetDate())
SET @month = Month(GetDate())
SET @year = Year(GetDate())

set @futuredate = DATEADD ( day , 30, getdate() )
select @futuredate
set @futuredate = getdate()

SELECT * FROM tblDominio where dtmDominioFechaVencimiento >= dateadd(dd,datediff(dd,0,getdate()),0) and dtmDominioFechaVencimiento < dateadd(mm,datediff(mm,0,getdate())+3,0)+1

Exec spInsertaDominiosXVencer @FechaActual
Exec spNotificacionDominiosXVencer @FechaActual

carla

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-13 : 12:19:55

It's likely happening inside either of those two stored procedures.

I don't see @FechaActual being set in your code. Could you post all of your code?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

carla
Starting Member

12 Posts

Posted - 2011-01-13 : 12:24:01

thanks Tara, actually I think is on the second stored procedure...

this is the code of the second... thnks a LOT!!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[spNotificacionDominiosXVencer] (@dtmFecha datetime )
AS
Declare @chrTo varchar(1000)
Declare @Body varchar(8000)
Declare @Body2 varchar(8000)
Declare @NumRegistros varchar(100)

Declare @idMesVencimiento int
Declare @MesVencimiento int
Declare @AnioVencimiento int

DECLARE @intDominioId numeric
DECLARE @strDominio varchar(200)
DECLARE @dtmDominioFechaVencimiento datetime

SELECT @idMesVencimiento = Month(dtmDominioFechaVencimiento) FROM tblDominio WHERE blnPendientexEnviar = 1 And Day(dtmEnvio) = Day(@dtmFecha) And Month(dtmEnvio) = Month(@dtmFecha) And Year(dtmEnvio) = Year(@dtmFecha)
SELECT @AnioVencimiento = Year(dtmDominioFechaVencimiento) FROM tblDominio WHERE blnPendientexEnviar = 1 And Day(dtmEnvio) = Day(@dtmFecha) And Month(dtmEnvio) = Month(@dtmFecha) And Year(dtmEnvio) = Year(@dtmFecha)
SELECT @MesVencimiento = (CASE @idMesVencimiento WHEN 1 THEN 'Enero' WHEN 2 THEN 'Febrero' WHEN 3 THEN 'Marzo' WHEN 4 THEN 'Abril' WHEN 5 THEN 'Mayo' WHEN 6 THEN 'Junio' WHEN 7 THEN 'Julio' WHEN 8 THEN 'Agosto' WHEN 9 THEN 'Septiembre' WHEN 10 THEN 'Octubre' WHEN 11 THEN 'Noviembre' WHEN 12 THEN 'Diciembre' END)

Select @chrTo = 'jgarciaco@gruma.com;cgarciaro@gruma.com'
Select @NumRegistros = count(*) FROM tblDominio WHERE blnPendientexEnviar = 1 And Month(dtmDominioFechaVencimiento) = @idMesVencimiento

If @NumRegistros > 0 And Len(@chrTo) > 0
BEGIN
SELECT @Body = '<html><body><center><style>.tdl {border-left:''1px solid #CFDBCD'';border-bottom:''1px solid #CFDBCD'';} .tdr {border-left:''1px solid #CFDBCD'';border-bottom:''1px solid #CFDBCD'';border-right:''1px solid #CFDBCD''} .tdhl {border-left:''1px solid #CFDBCD'';border-bottom:''1px solid #CFDBCD'';border-top:''1px solid #CFDBCD'';} .tdhr {border-top:''1px solid #CFDBCD'';}.tb {border-left:''1px solid #CFDBCD'';border-bottom:''1px solid #CFDBCD'';border-top:''1px solid #CFDBCD'';border-right:''1px solid #CFDBCD'';}</style><link rel=''stylesheet'' href=''http://aplicagrumaweb/estilos.css'' type=''text/css''>'
SELECT @Body = @Body + ' <TABLE width="70%" align="center" BORDER="0" cellspacing="1" cellpadding="1"">'
SELECT @Body = @Body + ' <TR><TD align="right"><font color=000000>'+ CONVERT(varchar,Day(GETDATE())) + '/'+ CONVERT(varchar,Month(GETDATE())) +'/'+ CONVERT(varchar,YEAR(GETDATE())) +'</b><br></font></TD></tr>'
SELECT @Body = @Body + ' <TR><TD class=tb bgcolor="#AEC6C9"><font color=000000><b>BIENES INFORMATICOS - Notificación de dominios por vencer:'+ @MesVencimiento +'-'+ Convert(varchar,@AnioVencimiento)+'</b><br></font></TD></tr></Table><br>'
SELECT @Body = @Body + ' <table class=tb BGCOLOR="#F1F1e5" width="70%" align="center" border="0"><TR><TD width="90%"><p align="justify"><font color=000000>Por este medio le informamos estan por vencer '+ Convert(varchar,@NumRegistros) + ' Dominio(s) '
SELECT @Body = @Body + ' correspondientes al período '+ @MesVencimiento +'-'+ Convert(varchar,@AnioVencimiento)
SELECT @Body = @Body + ' <TR><TD><TABLE BORDER=1 align=center cellspacing=0 cellpadding= 0 width=80% >'
SELECT @Body = @Body + ' <TR><TD><b>Dominio</b></TD>'
SELECT @Body = @Body + ' <TD><b>Fecha vencimiento</b></TD>'
SELECT @Body = @Body + ' </TR>'

DECLARE Dominio_cursor CURSOR FOR
SELECT
intDominioId,
strDominio,
dtmDominioFechaVencimiento
FROM dbo.tblDominio a
WHERE
/*--dtmEnvio = @dtmFecha and blnPendientexEnviar= 1 */
blnPendientexEnviar = 1 And Day(dtmEnvio) = Day(@dtmFecha) And Month(dtmEnvio) = Month(@dtmFecha) And Year(dtmEnvio) = Year(@dtmFecha)

OPEN Dominio_cursor

FETCH NEXT FROM anexo_cursor
INTO
@intDominioId,
@strDominio,
@dtmDominioFechaVencimiento

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Body = @Body + ' <TR><TD>'+ @strDominio +'</TD>'
SELECT @Body = @Body + ' <TD>'+ CONVERT(varchar,@dtmDominioFechaVencimiento) +'</TD>'
SELECT @Body = @Body + ' </TR>'

FETCH NEXT FROM Dominio_cursor
INTO
@intDominioId,
@strDominio,
@dtmDominioFechaVencimiento

END

CLOSE Dominio_cursor
DEALLOCATE Dominio_cursor

SELECT @Body = @Body + ' </TABLE></TD></TR>'
SELECT @Body = @Body + ' <TR><TD><font color=000000><br>Sin otro particular.<br>'
SELECT @Body = @Body + ' Grumaweb<br><br></font></TD></tr>'
SELECT @Body = @Body + '</TABLE></center></body></html>'

Print 'Valor:::::' + @NumRegistros
Print 'Valor E::' + @chrTo
PRINT 'Valor:1___' + @Body

exec sp_send_cdosysmail 'jgarciaco@gruma.com;cgarciaro@gruma.com',@chrTo ,' Aviso de Dominios x Vencer', @Body

/*PRINT 'eRROR::::' + CONVERT(Varchar,@@ERROR)*/

IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
UPDATE tblDominio
SET blnPendientexEnviar = 0, blnError=1, strEnvioComentarios= 'Error:' + CONVERT(Varchar,@@ERROR)
WHERE blnPendientexEnviar = 1 And Day(dtmEnvio) = Day(@dtmFecha) And Month(dtmEnvio) = Month(@dtmFecha) And Year(dtmEnvio) = Year(@dtmFecha)
PRINT 'Ocurrió un error al mandar el correo'
--RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
UPDATE tblDominio
SET blnPendientexEnviar = 0, blnEnviado=1,blnError=0,
strEnvioComentarios= 'Aviso enviado exitosamente'
WHERE blnPendientexEnviar = 1 And Day(dtmEnvio) = Day(@dtmFecha) And Month(dtmEnvio) = Month(@dtmFecha) And Year(dtmEnvio) = Year(@dtmFecha)

END
END

carla

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-13 : 12:24:50

Oh boy! Sorry I just don't have the time to troubleshoot something as long as that. Maybe someone else can help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

carla
Starting Member

12 Posts

Posted - 2011-01-13 : 12:28:28

ahahaha no worries =) i understand!!

quote:
Originally posted by tkizer

Oh boy! Sorry I just don't have the time to troubleshoot something as long as that. Maybe someone else can help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

carla

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-13 : 17:19:54

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155176

Carla. The error is pretty specific..you are sending a STRING value into a datetime , with an invalid date.

In the prior post, it appeared to be @FechaActual, and I suggested you make sure all dates were declared as datetime.

Look at every variable you are populating with a date and verify that any varchar or char columns containing potential datetime values have actual/valid dates.

The error would occur if @FechaActual was an empty string.

In the post here you show:
[code]
DECLARE @day as int
DECLARE @month as int
DECLARE @year as varchar(4)
DECLARE @FechaActual as varchar(12) <---why is this a string?
DECLARE @futuredate as datetime

SET @day = Day(GetDate())
SET @month = Month(GetDate())
SET @year = Year(GetDate())

set @futuredate = DATEADD ( day , 30, getdate() )
select @futuredate
set @futuredate = getdate() <===why the re-set?

Exec spInsertaDominiosXVencer @FechaActual <===this variable isn't populated when this runs based on code above
Exec spNotificacionDominiosXVencer @FechaActual

Leave all dates as datetime datatypes. If you must convert the date to a string use something like convert(varchar(10),@datevariable,112)

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources