Click here to Skip to main content
11,640,445 members (61,376 online)
Click here to Skip to main content

How to invoke a Web Service from a Stored Procedure

, 26 Jul 2012 CPOL 238.3K 110
Rate this:
Please Sign up or sign in to vote.
Learning how to make a call to a Web Service from a Stored Procedure.

Introduction

On one occasion an old friend, was planning some applications  using NET and sockets, and needed to call a WebServices from a stored procedure.

In this article I will share this experience so that other programmers can learn to call a WebService sending parameters from a Stored Procedure. 

Step 1 

We must create our WebServices Project in Visual Studio.

Step 2 

Then proceed to create the methods that we use in our service, in my case believe 6 methods which are:  Greet (string Param1) and expects a String as the parameter, HelloWord() does not expect any parameters, and the  Add, subtract, and Divide proliferation (Num1 int, int num2) want  two integers as parameters.

Step 3 

Proceed to create our stored procedure that will have all the code to invoke the Web Services we have just created, as in all my articles database I use is AdventureWorks, which is the basis of test data that brings SQL Server.

Step 4 

Then publish the web service on our IIS Web Server

Step 5 

We proceed to our Store Procedure Coding the data of our Web Service.

Step 6 

In the procedure to create, we pass a parameter which is the parameter that is waiting Saludar() method, if they want to use the other methods, should create another parameter because as I mentioned above the other methods are expecting 2 parameters integer type. 

Something that is also very important when using the sp_OAMethod Stored Procedure this awaiting the POST or GET method, default almost always send POST, but if we sent this method, we can not display the XML reading generated by our Web Service, so we must use the GET method.

Step 7 

Proceed to test our Web Service through your browser, type the address of the IIS where the published our Web Service. http://localhost/WebServices/Service1.asmx in the same show all the methods that  we created in our Web Service.

Step 8 

Select the method that we use and invoke from our Stored Procedure, Saludar(). Then proceed to write the parameter you want to happen to the Web Service,  after this we click on Invoke.

Step 9  

After that it will open another page in your browser, the information contained in XML and the parameter to write.

Step 10   

We proceed to execute the stored procedure we just created to invoke our web service.

Step 11    

After running our procedure and send our parameters we get the same result we got when we run it through your browser.

Results    

Look at the comparison and noticed that the same result.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Omar Frometa
CEO FV Tech
Dominican Republic Dominican Republic
MCTS - SQL Server 2008
Microsoft Certified Technology Specialist

CETEX - Exactus ERP
Exactus Technician Certificate

You may also be interested in...

Comments and Discussions

 
QuestionCalling web service through stored procedure Pin
hemant bhor18-May-15 19:17
memberhemant bhor18-May-15 19:17 
QuestionUse of User and Password in the WebServices Pin
Member 1169421915-May-15 11:39
memberMember 1169421915-May-15 11:39 
GeneralMy vote of 5 Pin
mfp@novosoft.eu18-Mar-15 5:27
membermfp@novosoft.eu18-Mar-15 5:27 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun1-Mar-15 19:38
memberHumayun Kabir Mamun1-Mar-15 19:38 
QuestionNull value Return from SP Pin
mudgilsks4-Dec-14 3:17
professionalmudgilsks4-Dec-14 3:17 
AnswerRe: Null value Return from SP Pin
rollypsp15-Jan-15 9:01
memberrollypsp15-Jan-15 9:01 
QuestionWill this work in SQL 2005? Pin
Member 1073592613-Oct-14 20:01
memberMember 1073592613-Oct-14 20:01 
QuestionGetting Null Response Pin
Ashish Kumar kansal18-Sep-14 21:38
professionalAshish Kumar kansal18-Sep-14 21:38 
AnswerRe: Getting Null Response Pin
Omar Frometa19-Sep-14 2:21
memberOmar Frometa19-Sep-14 2:21 
GeneralMy vote of 5 Pin
Mladen Borojevic21-Aug-14 21:50
memberMladen Borojevic21-Aug-14 21:50 
QuestionMy Vote of 5* Pin
Mas1112-Mar-14 20:26
memberMas1112-Mar-14 20:26 
QuestionNeed how to call WCF Service from strored procedure. Pin
tosukum22-Dec-13 20:26
membertosukum22-Dec-13 20:26 
QuestionCustom Soap Headers Pin
alemos11-Dec-13 3:12
memberalemos11-Dec-13 3:12 
QuestionReturing Null Pin
vijay kumar bollu29-Aug-13 19:27
membervijay kumar bollu29-Aug-13 19:27 
QuestionGetting a null response. Pin
PulkitGulati14-Jul-13 17:04
memberPulkitGulati14-Jul-13 17:04 
AnswerRe: Getting a null response. Pin
khader abdul20-Aug-13 23:24
memberkhader abdul20-Aug-13 23:24 
GeneralRe: Getting a null response. Pin
John Giblin31-Mar-15 4:14
memberJohn Giblin31-Mar-15 4:14 
GeneralMy vote of 5 Pin
Shrikant21927-Jun-13 0:18
memberShrikant21927-Jun-13 0:18 
GeneralMy vote of 5 Pin
prosthor30-May-13 1:53
memberprosthor30-May-13 1:53 
GeneralMy vote of 5 Pin
Sheikh Muhammad Haris26-Feb-13 21:39
memberSheikh Muhammad Haris26-Feb-13 21:39 
QuestionError Pin
walder gonzalez27-Dec-12 10:52
memberwalder gonzalez27-Dec-12 10:52 
GeneralMy vote of 2 Pin
Eric Lapouge21-Nov-12 6:43
memberEric Lapouge21-Nov-12 6:43 
GeneralMy vote of 5 Pin
Sperneder Patrick31-Oct-12 5:43
memberSperneder Patrick31-Oct-12 5:43 
QuestionExcellent Pin
Musthafa (Member 379898)21-Oct-12 16:26
memberMusthafa (Member 379898)21-Oct-12 16:26 
GeneralMy vote of 5 Pin
Mihai MOGA8-Aug-12 5:45
memberMihai MOGA8-Aug-12 5:45 
GeneralMy vote of 5 Pin
Kanasz Robert5-Aug-12 9:55
mvpKanasz Robert5-Aug-12 9:55 
GeneralMy vote of 5 Pin
Monjurul Habib31-Jul-12 7:48
memberMonjurul Habib31-Jul-12 7:48 
GeneralMy vote of 5 Pin
Anurag Gandhi30-Jul-12 5:34
memberAnurag Gandhi30-Jul-12 5:34 
GeneralMy vote of 5 Pin
prelate28-Jul-12 8:12
memberprelate28-Jul-12 8:12 
GeneralMy vote of 5 Pin
hahoang61199027-Jul-12 19:13
memberhahoang61199027-Jul-12 19:13 
GeneralMy vote of 4 Pin
Christian Amado27-Jul-12 11:57
memberChristian Amado27-Jul-12 11:57 
GeneralThanks & Question Pin
ii_noname_ii27-Jul-12 1:55
memberii_noname_ii27-Jul-12 1:55 
GeneralRe: Thanks & Question Pin
ii_noname_ii27-Jul-12 2:00
memberii_noname_ii27-Jul-12 2:00 
GeneralRe: Thanks & Question Pin
Sacha Barber28-Jul-12 19:08
mvpSacha Barber28-Jul-12 19:08 
GeneralRe: Thanks & Question Pin
Ankur\m/31-Jul-12 22:37
memberAnkur\m/31-Jul-12 22:37 
GeneralRe: Thanks & Question Pin
Jan Steyn31-Jul-12 22:46
memberJan Steyn31-Jul-12 22:46 
GeneralMy vote of 5 Pin
IsmailLunat26-Jul-12 20:50
memberIsmailLunat26-Jul-12 20:50 
GeneralNice Pin
sund7wells26-Jul-12 18:51
membersund7wells26-Jul-12 18:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150731.1 | Last Updated 26 Jul 2012
Article Copyright 2012 by Omar Frometa
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid