Instance of calling WebService in SQL Server

1.Ole Automation Procedures Server configuration options

When OLE Automation procedures is enabled, calls to sp_OACreate will start the OLE shared execution environment.

You can use the sp_configure system stored procedure to view and change the current value of the Ole Automation procedures option.

'Show advanced options1;  GORECONFIGURE;  GO'Ole Automation procedures1;  GORECONFIGURE;  GO              

2. Write the SQL code and execute

Declare@ServiceUrlAsvarchar1000)Declare@UrlAddressvarchar500)--WebService address: Start with HTTP, end with Slash, for example ' http://webservice.webxml.com.cn/WebServices/MobileCodeWS.asmx/'Set@UrlAddress=‘http://webservice.webxml.com.cn/WebServices/MobileCodeWS.asmx/‘Declare@FunNamevarchar50)--Method name called in WebService: for example ' Getmobilecodeinfo 'Set@FunName=‘Getmobilecodeinfo‘--The following parameter corresponds to the [parameter name] of 4 parameters in WebService.Declare@P1varchar800),@P2varchar100)Set@P1=‘Mobilecode‘Set@P2=‘Userid‘Declare@P1_Valuevarchar100),@P2_Valuevarchar100)Set@P1_Value=‘13800138000‘Set@P2_Value=‘‘Set@ServiceUrl=@UrlAddress+@FunName+‘?‘+@P1+‘=‘+@P1_Value+‘&‘+@P2+‘=‘+@P2_ValueDeclare@ObjectAsIntDeclare@ResponseTextAsVarchar (8000)Exec sp_OACreate‘MSXML2. XMLHTTP‘,@ObjectOut;Exec sp_OAMethod@Object,‘Open‘,Null‘Get ",  @ServiceUrl,  false ' exec sp_OAMethod  @Object,  send ' responsetext @ResponseText  OUTPUT select  @ResponseText exec sp_OADestroy  @Object go           

Be aware that the returned result is an XML encoding with parsing.

Tags: SQL, WebService

Instance of calling WebService in SQL Server