Call a webservice from T SQL

Question

Post reply

Call a webservice from T SQL

emanuele 8962

SSC-Addicted

Points: 445
More actions
September 7, 2017 at 1:20 am

#348899

Good morning,
I need to call a webservice from T SQL,
how can I do?
This call have user and password and i have tihs result tha i save on my table sql:

I look forward to your thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

You can use SSIS to call a web service. You can't do it directly with T-SQL.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

emanuele 8962 SSC-Addicted Points: 445 More actions · Answer 2

Thom A - Thursday, September 7, 2017 1:37 AM
You can use SSIS to call a web service. You can't do it directly with T-SQL.

Thank you for your replay, could you please provide me an example or a link to check?

Thanks

Thom A SSC Guru Points: 99130 More actions · Answer 3

emanuele 8962 - Thursday, September 7, 2017 1:42 AM
Thom A - Thursday, September 7, 2017 1:37 AM
You can use SSIS to call a web service. You can't do it directly with T-SQL.
Thank you for your replay, could you please provide me an example or a link to check?
Thanks

Web Services are unique, so you'll find it difficult to find a example for your specific needs, and I have no details of what your set up is. A Google (Other Search Engines are available) yields a lot of results on how to use SSIS to call a web service, however, if you have specific set up questions, or have difficulties post back here with details on exactly what they are.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Although you can do this using sp_OACreate and WinHttp.WinHttpRequest.5.1, I would normally recommend using alternatives such as SSIS.
😎

emanuele 8962 SSC-Addicted Points: 445 More actions · Answer 5

Thom A - Thursday, September 7, 2017 1:55 AM
emanuele 8962 - Thursday, September 7, 2017 1:42 AM
Thom A - Thursday, September 7, 2017 1:37 AM
You can use SSIS to call a web service. You can't do it directly with T-SQL.
Thank you for your replay, could you please provide me an example or a link to check?
Thanks
Web Services are unique, so you'll find it difficult to find a example for your specific needs, and I have no details of what your set up is. A Google (Other Search Engines are available) yields a lot of results on how to use SSIS to call a web service, however, if you have specific set up questions, or have difficulties post back here with details on exactly what they are.

Thanks

emanuele 8962 SSC-Addicted Points: 445 More actions · Answer 6

Eirikur Eiriksson - Thursday, September 7, 2017 2:04 AM
Although you can do this using sp_OACreate and WinHttp.WinHttpRequest.5.1, I would normally recommend using alternatives such as SSIS.
😎

Thanks

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

Or Powershell. You can a heck of a lot more from there than other places. Combining something like a web service with data queries will be pretty simple through there. Lots of examples of querying SQL Server from Powershell and calling web services from Powershell available online. Just combine the two and you're off to the races.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

Grant Fritchey - Thursday, September 7, 2017 4:28 AM
Or Powershell. You can a heck of a lot more from there than other places. Combining something like a web service with data queries will be pretty simple through there. Lots of examples of querying SQL Server from Powershell and calling web services from Powershell available online. Just combine the two and you're off to the races.

Good point Grant!
😎
My preferred architecture is to have application servers handling external communications and restrict the SQL Servers to the database role without the ability to do any external communication, neither incoming nor outgoing, call me paranoid but this has worked very well so far, never had any security issues with my servers, ever!

emanuele 8962 SSC-Addicted Points: 445 More actions · Answer 9

Eirikur Eiriksson - Thursday, September 7, 2017 5:39 AM
Grant Fritchey - Thursday, September 7, 2017 4:28 AM
Or Powershell. You can a heck of a lot more from there than other places. Combining something like a web service with data queries will be pretty simple through there. Lots of examples of querying SQL Server from Powershell and calling web services from Powershell available online. Just combine the two and you're off to the races.
Good point Grant!
😎
My preferred architecture is to have application servers handling external communications and restrict the SQL Servers to the database role without the ability to do any external communication, neither incoming nor outgoing, call me paranoid but this has worked very well so far, never had any security issues with my servers, ever!

ok ok Thanks

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 10

Grant Fritchey - Thursday, September 7, 2017 4:28 AM
Or Powershell. You can a heck of a lot more from there than other places. Combining something like a web service with data queries will be pretty simple through there. Lots of examples of querying SQL Server from Powershell and calling web services from Powershell available online. Just combine the two and you're off to the races.

Similar, you can use Python scripts, which have a great deal more support for this. Same with using SSIS to execute script tasks on them too.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 11

Eirikur Eiriksson - Thursday, September 7, 2017 5:39 AM
Good point Grant!
😎
My preferred architecture is to have application servers handling external communications and restrict the SQL Servers to the database role without the ability to do any external communication, neither incoming nor outgoing, call me paranoid but this has worked very well so far, never had any security issues with my servers, ever!

We couldn't agree more. While it's possible to shoehorn almost any behavior into SQL Server, just because we can do something doesn't make it a good idea. Further, the more we're forcing behavior, the more difficult maintenance becomes, upgrades, troubleshooting, performance tuning, backups, etc. Using the hammer as a hammer and the screwdriver as a screwdriver seems to turn out better than anything else.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 12

xsevensinzx - Thursday, September 7, 2017 6:13 AM
Grant Fritchey - Thursday, September 7, 2017 4:28 AM
Or Powershell. You can a heck of a lot more from there than other places. Combining something like a web service with data queries will be pretty simple through there. Lots of examples of querying SQL Server from Powershell and calling web services from Powershell available online. Just combine the two and you're off to the races.
Similar, you can use Python scripts, which have a great deal more support for this. Same with using SSIS to execute script tasks on them too.

While I don't know Python, I can also get behind this approach.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning