sp error 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

sp error help =)

Author

Topic

carla
Starting Member

12 Posts

Posted - 2011-01-12 : 18:58:10

hello again everyone, hope yall having a great day, im havin an issue running a job, I keep receiving this error message that says:

Message
Conversion failed when converting datetime from character string. [SQLSTATE 22007] (Error 241). The step failed.

this is the job command:
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 @FechaActual = @month + '/' + @day + '/'+ @year

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

*if I remove the las "exec spNotificacionDominiosXVencer @FechaActual" I doesnt appear to have issues running the job, but If I leave it, it appears the error message that I previously put

this is the second stored procedure that im calling:

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 numeric
Declare @MesVencimiento varchar(100)
Declare @AnioVencimiento numeric

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

columnas en el Comando SELECT

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>'

href=http://rioplata08:92/ComputoPersonal/autoriza_cp.asp>Pantalla de autorizaciones de SABI´s</a> </TD></TR></TABLE></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)
--PRINT 'Aviso enviado exitosamente'
--RETURN(0)
END
END

carla

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-12 : 19:35:23

SET @day = Day(GetDate())
SET @month = Month(GetDate())
SET @year = Year(GetDate())
--SET @FechaActual = @month + '/' + @day + '/'+ @year

This can be simplified to

SET @FechActual = convert(varchar(12),getdate(),101)

The error is specific enough that any varchar column you are putting a date into, the error means at some point later it is failing to convert that string back to a valid date.

Also you are passing @FechaActual (declared as a string) and passing it into the procedure [dbo].[spNotificacionDominiosXVencer] (@dtmFecha datetime ) as a datetime.

Just declare @FechaActual as a datetime. If you need to strip the time stamp off of it, use

SET @FechaActual = datadd(day,0,datediff(d,0,getdate())

ALL datetime variables should be declare as dates, and if needed use the convert funtion to make it a string where needed.

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

carla
Starting Member

12 Posts

Posted - 2011-01-13 : 10:26:12

Thnks for ur help!

quote:
Originally posted by dataguru1971

SET @day = Day(GetDate())
SET @month = Month(GetDate())
SET @year = Year(GetDate())
--SET @FechaActual = @month + '/' + @day + '/'+ @year

This can be simplified to

SET @FechActual = convert(varchar(12),getdate(),101)

The error is specific enough that any varchar column you are putting a date into, the error means at some point later it is failing to convert that string back to a valid date.

Also you are passing @FechaActual (declared as a string) and passing it into the procedure [dbo].[spNotificacionDominiosXVencer] (@dtmFecha datetime ) as a datetime.

Just declare @FechaActual as a datetime. If you need to strip the time stamp off of it, use

SET @FechaActual = datadd(day,0,datediff(d,0,getdate())

ALL datetime variables should be declare as dates, and if needed use the convert funtion to make it a string where needed.

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

carla

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources