Llamada a Web Service con TSQL
Pregunta
-
Hola amigos.
Estoy desarrollando un procedimiento almacenado que realiza una llamada a un Web Service pero llevo ya mas de una semana y no he conseguido hacer que funcione.
He leido en varios foros que lo recomendable en estos casos es realizarlo con una CLR pero en mi caso esto no es posible.
El procedimiento consigue establecer conexion con el servidor e incluso llega a enviar el mensage SOAP pero siempre recibo un mensage de error.
El servicio web al que llamo es publico: http://www.webservicex.net/ValidateEmail.asmx. En esta misma direccion se encuentra descrito el Envelope SOAP que debera ser enviado al servicio web.
A continuacion os dejo el codigo para que le echeis un vistazo por si se os ocurre algo o veis algun fallo. Tambien os dejo el mensaje de error.
CREATE PROCEDURE getWebServices (@url VARCHAR(4000))AS
SET NOCOUNT ON
DECLARE @xml INT
DECLARE @hr INT
DECLARE @src VARCHAR(255)
DECLARE @desc VARCHAR(255)
DECLARE @param VARCHAR(8000)
DECLARE @resultado VARCHAR(8000)
DECLARE @hv VARCHAR(1000)
/*
POST /ValidateEmail.asmx HTTP/1.1
Host: www.webservicex.net
Content-Type: text/xml; charset=utf-8
Content-Length: 556
SOAPAction: "http://www.webservicex.net/IsValidEmail"
*/
--/*
SET @param='
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns
oap="http://schemas.xmlsoap.org/soap/envelope/"><soap:Body>
<IsValidEmail xmlns="http://www.webservicex.net">
<Email>correo@hotmail.com</Email>
</IsValidEmail>
</soap:Body>
</soap:Envelope>
'
--*/
/*
* Creamos una instancia de un objeto OLE.
* @xml identifica el objeto OLE creado y se utiliza en llamadas a otros procedimientos almacenados de Automatización OLE.
*/
EXEC @hr = master.dbo.sp_OACreate 'MSXML2.ServerXMLHTTP', @xml OUTPUT
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
PRINT 'Peta en la dll'
GOTO FinalizarError
END
EXEC @hr = master.dbo.sp_OAMethod @xml, 'readyState()', @hv OUTPUT
PRINT '============================'
PRINT @hv
PRINT '============================'
/*
* Llama a un método de un objeto OLE.
* 'Open' es el nombre de método del objeto OLE al que se llama.
* Se pone NULL porque este metodo no devuelve ningun valor de retorno.
* Se pasa el parametro 'GET'.
* Se pasa el parametro @url donde esta almacenada la URL del Web Services.
*/
DECLARE @prueba VARCHAR(8000)
EXEC @hr = master.dbo.sp_OAMethod @xml,'Open',NULL,'POST',@url,0--,'+til','saptil1'
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
PRINT 'Peta en el Open'
GOTO FinalizarError
END
EXEC @hr = master.dbo.sp_OAMethod @xml, 'readyState()', @hv OUTPUT
PRINT '============================'
PRINT @hv
PRINT '============================'
EXEC @hr = master.dbo.sp_OAMethod @xml,'setRequestHeader("Host", "www.webservicex.net")', NULL
--/*
EXEC @hr = master.dbo.sp_OAMethod @xml,'setRequestHeader("Content-Type", "text/xml")', null
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
GOTO FinalizarError
END
--*/
--/*
EXEC @hr = master.dbo.sp_OAMethod @xml,'setRequestHeader("content-transfer-encoding","utf-8")', NULL
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
GOTO FinalizarError
END
--*/
--/*
EXEC @hr = master.dbo.sp_OAMethod @xml,'setRequestHeader("Content-Length", "82")', NULL
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
GOTO FinalizarError
END
--*/
--/*
EXEC @hr = master.dbo.sp_OAMethod @xml,'setRequestHeader("SOAPAction", "http://www.webservicex.net/IsValidEmail")', NULL
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
GOTO FinalizarError
END
--*/
/*
* Llama a un método de un objeto OLE.
* 'Send' es el nombre de método del objeto OLE al que se llama.
* @param es el parametro que se le pasa.
*/
EXEC @hr = master.dbo.sp_OAMethod @xml,'Send',NULL,@param
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
PRINT 'Peta en el Send'
GOTO FinalizarError
END
EXEC @hr = master.dbo.sp_OAMethod @xml, 'readyState()', @hv OUTPUT
DECLARE @statusText VARCHAR(1000), @status VARCHAR(1000)
-- Get status text
EXEC sp_OAGetProperty @xml, 'StatusText', @statusText out
EXEC sp_OAGetProperty @xml, 'Status', @status out
SELECT @status AS Status, @statusText AS Msg_Status
EXEC @hr = master.dbo.sp_OAMethod @xml, 'readyState()', @hv OUTPUT
PRINT '============================'
PRINT @hv
PRINT '============================'
/*
WHILE(@hv!=4)
BEGIN
EXEC @hr =master.dbo.sp_OAMethod @xml, 'waitForResponse(10)', null
EXEC @hr =master.dbo.sp_OAMethod @xml, 'readyState()', @hv OUTPUT
END
--*/
/*
* Obtiene el valor de una propiedad de un objeto OLE.
* La propiedad 'responseText' devuelve la respuesta como una cadena.
* @resultado variable donde se recoge el resultado. Dependiendo de lo que devuelva el WS se recogera en una variable o no.
*/
EXECUTE master.dbo.sp_OAGetProperty @xml,'responseText',@resultado OUTPUT
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUTPUT, @desc OUTPUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
GOTO FinalizarError
END
PRINT '============================'
PRINT ISNULL(@resultado,'No hay resultado')
PRINT '============================'
/*
* Destruye un objeto OLE creado.
*/
EXEC @hr = master.dbo.sp_OADestroy @xml
IF @hr <> 0
BEGIN
EXEC master.dbo.sp_OAGetErrorInfo @xml, @src OUT, @desc OUT
SELECT convert(varbinary(4),@hr) hr, @src [Src], @desc [desc]
GOTO FinalizarError
END
Finalizar:
PRINT 'Finalizado Correcto'
RETURN 0
FinalizarError:
PRINT 'Finalizado InCorrecto'
RETURN 1
/*
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
/*
La ultima parte que esta comentada es para activar este tipo de procedimientos que por defecto estan desactivados en SQL Server 2005. Tambien es necesario una cuenta que tenga persmisos de sistema.
A continuacion os adjunto el mensaje de error:
<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns
oap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body>
<soap:Fault>
<faultcode>soap
erver</faultcode><faultstring>System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Xml.XmlException: The root element is missing.
at System.Xml.XmlTextReader.Read()
at System.Xml.XmlReader.MoveToContent()
at System.Web.Services.Protocols.SoapServerProtocolHelper.GetHelper(SoapServerProtocol protocol)
at System.Web.Services.Protocols.SoapServerProtocol.ReadParameters()
at System.Web.Services.Protocols.WebServiceHandler.Invoke()
at System.Web.Services.Protocols.WebServiceHandler.CoreProcessRequest()
--- End of inner exception stack trace ---</faultstring>
<detail />
</soap:Fault>
</soap:Body>
</soap:Envelope>
Algo que no estoy seguro si tengo bien es el "Content-Length", he empleado varios valores, el que esta puesto en el codigo corresponde al tamaño del Body SOAP.
La llamada al procedimento es esta:EXECUTE dbo.getWebServices 'http://www.webservicex.net/ValidateEmail.asmx'
Cualquier ayuda que podais prestarme sera bien recibida.
Gracias.
