I have the next SP in sql server 2008. If I execute this SP from the SQL SERVER MANAGMENT (rigth click, execute stored procedure), it does not have any problem, everything is executed correctly, but when this SP is called from the WEB (a web page external to my servers), step 3 is not executed. This step use a LINKED SERVER to Oracle 11G. The log registers the next error:
Conversion failed when converting date and/or time from character string
USE [Boton_Pago_Plus]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PC_Guarda_Transaccion]
(@id_intento int,@RUTSINGUION nchar(12),@valor_pagado numeric, @id_Servipag int, @datos_Servipag nchar(300))
AS
BEGIN
Set ANSI_NULLS ON;
Set ANSI_WARNINGS ON;
SET NOCOUNT ON;
BEGIN TRY
-- Insert statements for procedure here
DECLARE @RUTSINGUION VARCHAR(500);
DECLARE @INDEXRUT INT;
--STEP 1
INSERT INTO Transaccion (Rut_cliente,Valor_pagado,Id_servipag,Fecha_ingreso,Datos_servipag,Id_intento)
VALUES (@RUTSINGUION,@valor_pagado,@id_Servipag,GETDATE(),@datos_Servipag,@id_intento);
--STEP 2
UPDATE Intento SET Fecha_cierre = GETDATE(), Boleta = @id_Servipag where Id_intento = @id_intento;
--STEP 3
INSERT OPENQUERY (ORA_PEHUEN_PLUS_UTILS, 'SELECT * FROM METLIFE.BLACKLIST')
VALUES (@RUTSINGUION,'METLF','CL',1,NULL,CONVERT(VARCHAR(10), GETDATE(), 103),DATEADD(DAY,5, CONVERT(VARCHAR(10), GETDATE(), 103))
,'servipag',CONVERT(VARCHAR(10), GETDATE(), 103),'PAGO SERVIPAG','Inhibido 5 dias pago servipag')
END TRY
BEGIN CATCH
INSERT INTO LOG_ERROR
(
NUMERO_ERROR ,
SEVERIDAD_ERROR ,
ESTADO_ERROR ,
PROCEDIMIOENTO ,
LINEA_ERROR ,
MENSAJE,
FECHA_ERROR
)
SELECT ERROR_NUMBER() AS errNumber
, ERROR_SEVERITY() AS errSeverity
, ERROR_STATE() AS errState
, ERROR_PROCEDURE() AS errProcedure
, ERROR_LINE() AS errLine
, ERROR_MESSAGE() AS errMessage,
GETDATE()
END CATCH
END
I was send varchar to the insert with the convert format varchar. This line send the date without hour, and it works.
DATEADD(DAY,5,(CAST(GETDATE() AS date)))