Hello again, in this post we will see how to perform an Hypertext transfer protocol (HTTP) request through SQL server query. However remember that this action is not a general practice, it should be performed for specific cases like for instances:
- Data to be posted to some system at day end or specific time interval
- XML or JSON based data needs to be retrieved from integrated systems
- In a case where security is not a major concern (like internal systems)
This method is supposed to be less efficient in terms of execution time as compared to the traditional web request done by the web applications. So I would recommend to use this only if you have a specific requirement like ones mentioned above or if you do not have any other way then this.
Following are steps to create HTTP request via SQL query:
- First check if OLE (Object Linking and Embedding) Automation Procedures are enabled from the following built-in stored procedure:
EXEC sp_configure 'Ole Automation Procedures';
 |
| The config_value and run_value should be 1 |
2. If not enabled already then execute another built-in SQL SP of sp_configure:
exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
3. Now to perform a simple request consider the below query where data is retrieved in JSON format:
DECLARE @obj int,
@url VarChar(MAX),
@response VarChar(8000),
@requestHeader VarChar(MAX),
@requestBody VarChar(MAX),
@status int
SET @url = 'http://65.181.112.199:88/default.aspx?source=CategoryCombo'
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
Exec sp_OAMethod @obj, 'status', @status OUTPUT
EXEC sp_OADestroy @obj
SELECT @response [RESPONSE], @status [Status]
4. Result:
For more information visit stack overflow:
No comments:
Post a Comment