SQL Server Stored Procedure Call WebService

1. Publish WebService. The service content is to query the user name through the mobile phone number.

2. Write a stored procedure

 Create   Procedure   [  DBO  ] . [  Proc_callwebservice  ]  @ Parameter   Varchar ( 20  ) As  Begin      Declare   @ OBJ   Int      Declare   @ URL   Varchar ( 200  )  Declare   @ Response   Varchar ( 5000  )  Set   @ URL  =  '  Http: // localhost/zyr/webservicedemo. asmx/getusernamebymobile? Mobile =  '  +  @ Parameter  +  ''      Exec Sp_oacreate '  Msxml2.serverxmlhttp  ' , @ OBJ  Out  Exec Sp_oamethod @ OBJ , '  Open  ' , Null , '  Get  ' , @ URL  , False  Exec Sp_oamethod @ OBJ , '  Send  '     Exec Sp_oagetproperty @ OBJ , '  Responsetext  ' , @ Response  Out  Select   @ Response   [  Response  ]      Exec Sp_oadestroy @ OBJ  End 

Note:

Sp_oacreate: Create an OLE object instance.

• The first parameter isProgramThe identifier (progid) or the Class Identifier (CLSID ).
• The second parameter is the returned object token, which must be a local variable of the int type and must be used in subsequent methods. Output parameter, which must be an out or output parameter.

Sp_oamethod: Call the OLE object method.

• The first parameter is the object token.
• The second parameter is the method name.
• The third parameter is the return value of the method. If the method returns an object, the parameter type is int. Output parameter, which must be an out or output parameter.
• The fourth and later parameters are the method parameter values. If the method parameter is an output parameter, the out or output parameter must be added.
• Sp_oamethod can also be used to obtain attribute values.

Sp_oageterrorinfo gets OLE automation error information.

• The first parameter is the object token.
• The second parameter is the source of the error message. Output parameter, which must be an out or output parameter.
• The third parameter is the description of the error. Output parameter, which must be an out or output parameter.

Sp_oadestroy releases the created OLE object.

• The first parameter is the object token.

In addition, sp_oagetproperty gets the attribute value of the OLE object. sp_oasetproperty sets the attribute of the OLE object as a new value. sp_oastop stops the OLE Automatic stored procedure execution environment within the server range. These are relatively simple.

Iii. Execution of Stored Procedures

 
Exec [DBO].[Proc_callwebservice] '1526022 ****'
 

The result is as follows:

<? XML version = "1.0" encoding = "UTF-8"?> <String xmlns = "http://app.cloud-erp.cn/"> Wu Yanhua </string>

4. Possible Errors

1. Question 1

Solution:

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

View the current settings of OLE Automation procedures.

 
ExecSp_configure'OLE Automation procedures';Go
 

Enable OLE Automation procedures.

Sp_configure'Show advanced options',1;GoReconfigure;GoSp_configure'OLE Automation procedures',1;GoReconfigure;Go
 

2. Question 2

 Solution:

Add

    WebServices  >    protocols  >    Add   name  =" httppost " />     Add   name   =" httpget " />     protocols  >    WebServices  >