新聞財經娛樂♬資訊 News Economy

CREATE proc [dbo].[spHTTPRequest]      @URI varchar(2000) = '',      @methodName varchar(50) = '',      @requestBody varchar(8000) = '',      @SoapAction varchar(255),      @UserName nvarchar(100), -- DomainUserName or UserName      @Password nvarchar(100),      @responseText varchar(8000) outputasSET NOCOUNT ONIF    @methodName = ''BEGIN      select FailPoint = 'Method Name must be set'      returnENDset   @responseText = 'FAILED'DECLARE @objectID intDECLARE @hResult intDECLARE @source varchar(255), @desc varchar(255)EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUTIF @hResult <> 0BEGIN      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      returnEND-- open the destination URI with Specified methodEXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @PasswordIF @hResult <> 0BEGIN      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      returnEND-- set request headersEXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'IF @hResult <> 0BEGIN      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      returnEND-- set soap actionEXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapActionIF @hResult <> 0BEGIN      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      returnENDdeclare @len intset @len = len(@requestBody)EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @lenIF @hResult <> 0BEGIN      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      returnEND/*-- if you have headers in a table called RequestHeader you can go through them with thisDECLARE @HeaderKey varchar(500), @HeaderValue varchar(500)DECLARE RequestHeader CURSORLOCAL FAST_FORWARDFOR      SELECT      HeaderKey, HeaderValue      FROM RequestHeaders      WHERE       Method = @methodNameOPEN RequestHeaderFETCH NEXT FROM RequestHeaderINTO @HeaderKey, @HeaderValueWHILE @@FETCH_STATUS = 0BEGIN      --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, @HeaderValueENDCLOSE RequestHeaderDEALLOCATE RequestHeader*/-- send the requestEXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBodyIF    @hResult <> 0BEGIN      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      returnENDdeclare @statusText varchar(1000), @status varchar(1000)-- Get status textexec sp_OAGetProperty @objectID, 'StatusText', @statusText outexec sp_OAGetProperty @objectID, 'Status', @status outselect @status, @statusText, @methodName-- Get response textexec sp_OAGetProperty @objectID, 'responseText', @responseText outIF @hResult <> 0BEGIN      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      returnENDdestroy:      exec sp_OADestroy @objectIDSET NOCOUNT OFFGO                                  

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: