HTTP Endpoint with parameter function to calculates Longitude and Latitude distance in SQL-Server 2005:
I wrote an article for introduction of HTTP Endpoint (web service version of SQL-Server 2005) and have used simple stored procedure in it at:
If you are new to HTTP Endpoint and yet to create your first HTP Endpoint than please do stop reading this article and go for any of the above link to know more about HTTP Endpoint.
You are reading this line so, now I am assuming that you are aware with basic concept of HTTP Endpoint. Let us move further:
We are going to create one function that will calculate Latitude and Longitude distance in miles. Note: I have searched mathematical formula for calculating distance from internet. I have just used that formula in my function just to demonstrate the use of parameter function in HTTP Endpoint.
CREATE FUNCTION DistanceOfLatLon(
You can run above function directly in sql server and test its usage by:
You will get result 20.750219476997 miles
Now we will implement this function in HTTP Endpoint.
CREATE ENDPOINT FindLatLong –This will create HTTP Endpoint
AUTHORIZATION sa –this is something absolutely optional authoraization for db owner
STATE = STARTED — State could be STARTED, STOPPED and DISABLE
AS HTTP — You can create HTTP or TCP endpoint
PATH = ‘/SQLLatLong’, –virtual path, will be used in adding reference in web or windows app.
AUTHENTICATION = (INTEGRATED), –authentication type for endpoint
PORTS = (CLEAR), — PORT coulbe be all (CLEAR) or may be SSL
SITE = ‘localhost’ –site name, in this case “localhost” as I am running it locally
FOR SOAP –protocol type
WEBMETHOD ‘getLetLong’ — you can define more than one webmethod also to expose
NAME = ‘[AdventureWorks].[dbo].[DistanceOfLatLon]’, SCHEMA = STANDARD,
FORMAT = ALL_RESULTS
WSDL = DEFAULT, –this will generate WSDL as per request
BATCHES = DISABLED –you could enable BATCHES but it becomes security thread
I am assuming that you have created Endpoint in Adventureworks database and you are using “localhost” in your endpoint as a site name. You can test Endpoint by running following URL in any of the internet browser.
Now, you can implement your HTTP Endpoint in your Windows or Web application. Steps to use HTTP Endpoint in your application, has been given in my previous article. You can find that link at the top of this article.
After adding the reference in your windows application, you can write following code in your button click to print the value of calculation in your label on the same form.
private void button1_Click(object sender, EventArgs e)
localhost1.FindLatLong finding = new ForHTTPendPoint.localhost1.FindLatLong();
finding.Credentials = System.Net.CredentialCache.DefaultCredentials;
label1.Text = finding.getLetLong(33.0, -84.0, 33.3, -84.0).ToString();
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah