Iniciar sesiónCerrar
Correo:
Contraseña:
Entrar
Recordar sesión en este navegador
Iniciar sesiónIniciar sesiónCrear cuentaCrear cuenta
LWP » Foros » SQL Server » Consumir WebService - Stored

SQL Server - Consumir WebService - Stored

Volver
Nuevo Tema
<<>>
 
Vista:
Imágen de perfil de Mariano
Val: 2
Ha disminuido su posición en 21 puestos en SQL Server (en relación al último mes)
Gráfica de SQL Server

Consumir WebService - Stored

Publicado por Mariano (1 intervención) el 11/11/2020 20:58:49
Buenas tardes.
Tengo un procedimiento para consumir datos de un webService, mediante Soap, pero no me devuelve datos.
Había cargado la consulta en el foro de SQL, perdón si lo cargo nuevamente, es porque estoy conociendo el foro.

Copio el código y adjunto imagen del resultado.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
ALTER proc [dbo].[IBIS_WebService_Call]
      @URI varchar (400) = 'http://IPSERVICIO/wcAdmin/services/ServiceDataBykom?wsdl',
      @methodName varchar(3) = 'GET',
      @requestBody varchar(8000) = '',
      @SoapAction varchar(255) = 'consultarMovilesFlota',
      @UserName nvarchar(100) = 'usuario', -- Domain\UserName or UserName
      @Password nvarchar(100) = 'clave',
      @responseText nvarchar(max) output
as
SET NOCOUNT ON
IF    @methodName = ''
BEGIN
      select FailPoint = 'Method Name must be set'
      return
END
set   @responseText = 'FAILED'
DECLARE @objectID int
DECLARE @hResult int
DECLARE @source varchar(255), @desc varchar(255)
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
                  source = @source,
                  description = @desc,
                  FailPoint = 'Create failed',
                  MedthodName = @methodName
      goto destroy
      return
END
-- open the destination URI with Specified method
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'Open failed',
            MedthodName = @methodName
      goto destroy
      return
END
-- set request headers
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'SetRequestHeader failed',
            MedthodName = @methodName
      goto destroy
      return
END
 
-- set soap action
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'SetRequestHeader failed',
            MedthodName = @methodName
      goto destroy
      return
END
declare @len int
set @len = len(@requestBody)
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'SetRequestHeader failed',
            MedthodName = @methodName
      goto destroy
      return
END
/*
-- if you have headers in a table called RequestHeader you can go through them with this
DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500)
DECLARE RequestHeader CURSOR
LOCAL FAST_FORWARD
FOR
      SELECT      HeaderKey, HeaderValue
      FROM RequestHeaders
      WHERE       Method = @methodName
OPEN RequestHeader
FETCH NEXT FROM RequestHeader
INTO @HeaderKey, @HeaderValue
WHILE @@FETCH_STATUS = 0
BEGIN
      --select @HeaderKey, @HeaderValue, @methodName
      EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue
      IF @hResult <> 0
      BEGIN
            EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
            SELECT      hResult = convert(varbinary(4), @hResult),
                  source = @source,
                  description = @desc,
                  FailPoint = 'SetRequestHeader failed',
                  MedthodName = @methodName
            goto destroy
            return
      END
      FETCH NEXT FROM RequestHeader
      INTO @HeaderKey, @HeaderValue
END
CLOSE RequestHeader
DEALLOCATE RequestHeader
*/
-- send the request
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody
IF    @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'Send failed',
            MedthodName = @methodName
      goto destroy
      return
END
 
declare @statusText varchar(1000), @status varchar(1000)
-- Get status text
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out
exec sp_OAGetProperty @objectID, 'Status', @status out
select @status, @statusText, @methodName
-- Get response text
exec sp_OAGetProperty @objectID, 'responseText', @responseText out
IF @hResult <> 0
BEGIN
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT
      SELECT      hResult = convert(varbinary(4), @hResult),
            source = @source,
            description = @desc,
            FailPoint = 'ResponseText failed',
            MedthodName = @methodName
      goto destroy
      return
END
destroy:
      exec sp_OADestroy @objectID
SET NOCOUNT OFF

Return
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder