I have been thinking and trying to expand my knowledge of different tools. One thing I have never had personal experience with is getting website data that users enter, and have that loaded into a SQL Server Database. There are several tables in this database and I need a tool/advise on which is a good tool to use to have that data loaded into my data warehouse after end users enter information for different fields into the website. I am an experienced SQL user but have never needed to do this. This wouldn't be needed for any enterprise application, but more for personal use/knowledge. Can anyone provide any insight on the best tool/api they have used to make this happen. Obviously I know this can be done from sharepoint but I am looking for other ideas. Thanks in advance

2 Answers 2


You can obviously do it manually in PHP with any open connection, but there are so many security things you have to account for.

If your starting out your better off using a PHP framework which handles a lot of the under the hood work for you. If there's a security issue with the framework the framework maintainers will handle it.

You must validate all user inputted data to avoid hackers slipping junk or hostile things into your form.

A framework also allows you to generate a working product faster, and your not constantly worried about the fine details.

  • Thanks for the reply. Each user to the website would have their own login/pw with encryption.
    – Audi_boi
    Apr 5, 2019 at 14:49
  • Yes you can have that, I just started working with Laravel and it has the ability to have usernames and passwords. I am sure other frameworks have this ability. The password is obviously encrypted. Are you asking about encrypting more than that?
    – cybernard
    Apr 5, 2019 at 15:52

You can do it directly from a Stored Procedure:

Enable Ole Automation Procedures:

sp_configure 'show advanced options', 1

sp_configure 'Ole Automation Procedures', 1

Just run this:

Declare @Object as Int;
DECLARE @hr  int
Declare @json as table(Json_Table nvarchar(max))

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'open', NULL, 'get',
                 'http://overpass-api.de/api/interpreter?data=[out:json];area[name=%22Auckland%22]-%3E.a;(node(area.a)[amenity=cinema];way(area.a)[amenity=cinema];rel(area.a)[amenity=cinema];);out;', --Your Web Service Url (invoked)
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
select * from @json
-- Parse the JSON string
SELECT * FROM OPENJSON((select * from @json), N'$.elements')
WITH (   
      [type] nvarchar(max) N'$.type'   ,
      [id]   nvarchar(max) N'$.id',
      [lat]   nvarchar(max) N'$.lat',
      [lon]   nvarchar(max) N'$.lon',
      [amenity]   nvarchar(max) N'$.tags.amenity',
      [name]   nvarchar(max) N'$.tags.name'     
EXEC sp_OADestroy @Object

This query will retrieve data about all the cinemas in Auckland from OpenStreetMap.

Yes, is a REST call directly from SSMS so you can create a stored procedure and your SQL Server will automatically call your website.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.