SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sp error help =)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

carla
Starting Member

Mexico
12 Posts

Posted - 01/12/2011 :  18:58:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 01/12/2011 :  19:35:23  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote

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.

Go to Top of Page

carla
Starting Member

Mexico
12 Posts

Posted - 01/13/2011 :  10:26:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000