sql-serverlinked-servergraph-store-protocol

Insert from SQL to ORACLE with LINKED SERVER


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

Solution

  • 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)))