another blog :: Fight the fear of SQL with SQLHub.com

please visit

http://www.sqlhub.com  for further new tips and tricks of SQL-Server and .NET. I have booked this domain specially for SQL Server blogging.

Ritesh Shah
http://www.SQLHub.Com
Fight the fear of SQL

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

Complex Aggregate Function example in SQL-Server 2005 with SubQuery:

In general analysis and reporting purpose, sometime we need to find department name, total salary paid to each department, total number of project department is working for and average salary being paid to each department. This is data administrative department may frequently asked for. Let us have a practical example of the same with GROUP BY clause, AGGREGATE function and SubQuery. For the demonstration purpose, we need to create following three tables and have to insert some data in it.

 

Create Table Department

(

      deptNo INT IDENTITY(1,1) NOT NULL,

      deptName VARCHAR(15) NOT NULL,

      Description VARCHAR(50) NULL

)

 

Create Table Employee

(

      empNo INT IDENTITY(1,1) NOT NULL,

      empName VARCHAR(50) NOT NULL,

      deptNo      INT NOT NULL,

      Salary INT NOT NULL

)

 

Create Table Project

(

      projNo INT IDENTITY(1,1) NOT NULL,

      projName VARCHAR(50) NOT NULL,

      deptNo INT NOT NULL    

)

 

 

Create Table Department

(

      deptNo INT IDENTITY(1,1) NOT NULL,

      deptName VARCHAR(15) NOT NULL,

      Description VARCHAR(50) NULL

)

INSERT INTO Department(deptName,Description)

SELECT ‘MIS1’,‘MIS1’ UNION ALL

SELECT ‘MIS2’,‘MIS2’

 

Create Table Employee

(

      empNo INT IDENTITY(1,1) NOT NULL,

      empName VARCHAR(50) NOT NULL,

      deptNo      INT NOT NULL,

      Salary INT NOT NULL

)

INSERT INTO Employee(empName,deptNo,Salary)

SELECT ‘Ritesh’,1,50000 UNION ALL

SELECT ‘Bihag’,2,29000 UNION ALL

SELECT ‘Rajan’,1,30000 UNION ALL

SELECT ‘Alka’,2,19000

 

Create Table Project

(

      projNo INT IDENTITY(1,1) NOT NULL,

      projName VARCHAR(50) NOT NULL,

      deptNo INT NOT NULL    

)

INSERT INTO Project(projName,deptNo)

SELECT ‘A’,1 UNION ALL

SELECT ‘B’,2 UNION ALL

SELECT ‘C’,1

 

Now, we are going to find department name, salary paid to each department, project – department is working for and last but not least average salary paid to each department in order to find efficiency of department.

SELECT Dept.deptname, emp.salary, prj.[Count],emp.salary/prj.[count] as ‘AverageSalary/DEPT’

FROM Department Dept,

(SELECT deptNo, sum(salary) salary FROM Employee GROUP BY deptNo) emp,

(SELECT deptNo, count(*) [Count] FROM Project GROUP BY deptNo) prj

WHERE Dept.deptNo=emp.deptNo and emp.deptNo=prj.deptNo

Above query has used simple SUM and COUNT aggregate function with subquery as table. As I gave example for Aggregate function, let me write down something more for an Aggregate function for those who are new to it.

Aggregate function will return only single row with computed value which summarizes the original result set. It will not return all row in return set. When you use aggregate function first result set returns the row set based on FROM clause and WHERE condition and then filtered records only will be aggregated.

Aggregate functions are expressions so it is mandatory to give column name to it explicitly or else it will return NULL as a column name, if you observe we gave name to all aggregated column like ‘AverageSalary/DEPT’. If we wouldn’t have done that, it would have return NULL as column name.

Aggregate function will not handle precision of numeric column, it is completely based on the source column. However, you can convert the results to other data type as well.

 

Reference: Ritesh Shah

Subquery with GROUPING as a Table in SQL-Server 2005:

Subquery with GROUPING as a Table in SQL-Server 2005:

Subquery can be used as a derived table in FORM clause of the T-SQL query. Believe me this is really powerful approach and can bring you out of some difficult situation. I have already previously written some articles on Subquery, you can refer those for getting basic idea about subquery, correlated subquery,CTE etc.

Let us create two tables for demonstration and insert some data in it.

–Create first table

CREATE TABLE CricketerDetails

(

ID INT IDENTITY(1,1) CONSTRAINT pk_cricketID PRIMARY KEY NOT NULL,

Name VARCHAR(25) NOT NULL,

Country VARCHAR(10) NOT NULL

)

 

–Insert data into first table

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Sachin Tendulkar’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Steve Waugh’,‘Australia’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Saurav Ganguly’,‘India’)

INSERT INTO CricketerDetails (Name,Country) VALUES(‘Jaisurya’,‘Sri Lanka’)

 

–Create second table

CREATE TABLE PersonalScore

(

MatchName VARCHAR(15) NOT NULL,

Run INT NOT NULL,

CricketerID INT NOT NULL CONSTRAINT fk_cricketid REFERENCES CricketerDetails(id)

)

 

–Insert data into second table

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,100,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,10,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,17,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘LG cup’,0,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,1)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,99,2)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,137,3)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-06’,10,4)

INSERT INTO PersonalScore(MatchName,Run,CricketerID) VALUES(‘WC-1998’,190,1)

 

After finishing above task, let us see how we can get cricketer name, country and his score with simple JOIN clause.

SELECT C.Name,c.country,MAX(p.Run)

FROM PersonalScore p join CricketerDetails c

ON p.cricketerid=c.id

GROUP BY C.name,c.country

We have achieved whatever we wanted. If you observed we have to put every column in group by clause which is not included in AGGREGATE function. In this case, those are Name and Country. What if you don’t want to put those two columns in group by clause as this is something messy sometime. Let us see how can achieve the same results with the help of derived subquery as table.

SELECT C.Name,c.country,p.Run

FROM CricketerDetails c

JOIN

(

      SELECT Cricketerid,MAX(run) as Run FROM PersonalScore GROUP BY CricketerID

)  p

ON c.ID=p.CricketerID

See, we have used subqery in JOIN clause rather than any table and achieved the same results. This technique of relational algebra may prove very powerful sometime in difficult situation.

 

Reference: Ritesh Shah

UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT) Difference of INTERSECT and INNER JOIN:

UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT):

You might have read my previous articles on JOIN as per those articles; JOIN is multiplication of data whereas UNION is addition.

UNION does nothing other than stack the data of multiple result sets in one result sets. While using the UNION you have to consider few things like: column name, number and aliases should be mentioned in first part of the SELECT. ORDER BY clause will sort the complete result sets, it will use the column name of first SELECT statement and should be write down after the last SELECT statement.

Let us create two demo tables to use UNION.

–create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16)

)

 

–create second table for demo

CREATE TABLE BIKE

(

ID int IDENTITY(1,1),

BikeName VARCHAR(16)

)

 

–Insert records in CAR table

INSERT INTO CAR

SELECT ‘HONDA crv’ UNION ALL

SELECT ‘Mahi. Raunalt’ UNION ALL

SELECT ‘Test’

 

–Isert records in BIKE table.

INSERT INTO BIKE

SELECT ‘HONDA Karishma’ UNION ALL

SELECT ‘Bajaj DTSI’ UNION ALL

SELECT ‘Test’

 

Now, both the select statement of above two tables will be merged and will return single result sets.

–use of union all

SELECT ID,CarName FROM CAR

UNION ALL

SELECT ID,BikeName FROM BIKE

Order by CarName

Above query returns all the records of both tables. Though we gave ORDER BY for CarName but it will sort complete second column which include some data of bikeName as well. You cannot give ORDER BY on BikeName column.

Above query was just an example, you can use UNION for less than or equal to 256 tables. This is not at all small amount.

Intersection Union:  Intersection Union find common row in both data sets.  As soon as you read this statement, you will say, ohh, this could be done by INNER JOIN as well. So, the answer is INNER JOIN matches two table horizontally and INTERSECTION matches two datasets vertically.  There is one more difference in INTERSECTION and INNER JOIN is that, Intersection query will see NULL as common and includes the row in the intersection and INNER JOIN will not even includes two different row with NULL value in result sets.

Now, have a look at INTERSET usage in below query.

–use of intersect union

SELECT ID,CarName FROM CAR

INTERSECT

SELECT ID,BikeName FROM BIKE

Order by CarName

You will get only one row which is same in both the table.

EXCEPT:  Except (a.k.a. DIFFERENCE UNION)  finds records exists in one data sets and not available in another datasets. In above case we have three records in CAR table but third record is exist in BIKE table as well so EXCEPT will display only first two records. Have a look at below query.

–use of EXCEPT

SELECT ID,CarName FROM CAR

EXCEPT

SELECT ID,BikeName FROM BIKE

Order by CarName

Reference:  Ritesh Shah

Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:

Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:

I wrote my previous article about different type of JOIN in SQL-Server 2005. Due to the space constraint in that article, complex example has not been given there, only few simple examples. I felt to explode this topic further by giving solution to some really needed business logic.

If you want to create report for blood testing laboratory, who used to check samples of employee of different companies for different purposes. Following should be the table structure, we will be creating three table. One will store information about Order received from different companies. Another one will be the detail of that order, means which company sent how much samples of how much employee?? And the last one is further more details about how much test should be performs on which sample???

Let us create three tables.

–CREATING FIRST ORDER TABLE

USE [AdventureWorks]

GO

/****** Object: Table [dbo].[Orders] Script Date: 03/08/2009 12:24:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Orders](

[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[OrderDate] [datetime] NOT NULL,

[CompanyName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

[OrderID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

Now, child table of above.

USE [AdventureWorks]

GO

/****** Object: Table [dbo].[OrderDetails] Script Date: 03/08/2009 12:37:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[OrderDetails](

[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[SampleOfEmployee] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[SampleReceivedDate] [datetime] NULL,

PRIMARY KEY CLUSTERED

(

[SampleNumber] ASC

)

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [fk_orderid] FOREIGN KEY([OrderID])

REFERENCES [dbo].[Orders] ([OrderID])

GO

ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [fk_orderid]

One more child table.

USE [AdventureWorks]

GO

/****** Object: Table [dbo].[sampledetails] Script Date: 03/08/2009 12:43:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[sampledetails](

[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[TestType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

PRIMARY KEY CLUSTERED

(

[SampleNumber] ASC,

[TestType] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

Now, this is time to insert few records on above tables.

–Received three order from two company

INSERT INTO Orders

SELECT ‘L1000’,‘2009-03-01’,‘Test Inc.’ UNION ALL

SELECT ‘L1001’,‘2009-03-02’,‘Zeel Inc.’ UNION ALL

SELECT ‘L1002’,‘2009-03-04’,‘ABC Inc.’

–received two sample for first order, one sample for second order

–sample for third order yet to receive

INSERT INTO OrderDetails

SELECT ‘L1000’,‘L1000-01’,‘James’,‘2009-03-01’ UNION ALL

SELECT ‘L1000’,‘L1000-02’,‘John’,‘2009-04-01’ UNION ALL

SELECT ‘L1001’,‘L1001-01’,‘Smita’,‘2009-03-05’

–details of sample, which test suppose to be performed

–on which sample

INSERT INTO SampleDetails

SELECT ‘L1000-01’,‘Cancer’ UNION ALL

SELECT ‘L1000-01’,‘AIDS’ UNION ALL

SELECT ‘L1000-02’,‘BP’ UNION ALL

SELECT ‘L1001-01’,‘AIDS’ UNION ALL

SELECT ‘L1001-01’,‘Cancer’

Well, now we are ready to pull data out of three tables with different type of JOIN which we have seen in my previous article.

NOTE: many different logic can be used to perform following tasks, I am giving this just to explain the use of JOIN otherwise there are some more facility which can handle all of the below situation other than join.

If you want to get OrderID, OrderDate, Company (who gave the order??), SampleNumber, EmployeeName (for which employee company has sent the sample??), TestType (which test to be performed in above tables?)

You can use following query which has used one LEFT OUTER JOIN and one INNER JOIN.

SELECT O.OrderID,O.OrderDate,O.CompanyName,

OD.SampleofEmployee,OD.SampleNumber,SD.TestType

FROM

ORDERS AS O

LEFT OUTER JOIN

ORDERDETAILS AS OD

ON

O.OrderID=OD.OrderID

INNER JOIN

SampleDetails AS SD

ON

OD.SampleNumber=SD.SampleNumber

If there is a need to find how many samples came from which company. Use following query with aggregate function COUNT.

SELECT

O.CompanyName,Count(SampleNumber) as ‘TOTAL’

FROM

ORDERS AS O

LEFT OUTER JOIN

ORDERDETAILS AS OD

ON

O.OrderID=OD.OrderID

GROUP BY O.CompanyName

ORDER BY TOTAL DESC

Reference: Ritesh Shah

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:

Merging data is heart of SQL and it shows the depth of relational algebra as well as the power and flexibility of SQL. SQL is excellent at selecting the data and SQL Developer can be master by understanding the relational algebra.

Join seems known technology but it is fun all time. Developer can make very creative query with joins by making data twist and shout. Pulling the data out of join could be the answer of very big code of programming language with iteration. It is much faster than any loop in your code. I promise you will have fun by learning this powerful technique.

SQL-Server 2005 is relational database so you always need to join more than one table in order to get complete information out of data tables. Since, tables are self explanatory itself, order of table doesn’t matter.

This article is moved to following link:

http://blog.extreme-advice.com/2009/03/08/complete-ref-of-sql-server-join-inner-join-left-outer-join-right-outer-join-full-outer-join-in-sql-server-2005/

Delete many (multiple) records in bunch: SQL-Server 2005

 Delete many (multiple) records in bunch: SQL-Server 2005

 

I had one task few months back. I was asked to delete 3 million records from one table containing total of 8 million records. You may think, ohh delete….. simple, execute delete statement with condition and sit back on your chair with relax. J Its not that simple, If I would have done it, I would have lost my database due to many reasons. I don’t want to go in details about those reasons. Let us focus on our main topic.

 

USE adventureworks

GO

— creating one temporary table from AdventureWork database

— to perform delete operation.

SELECT * into AdventureWorks.DBO.TempPurchaseOrderDetail FROM Purchasing.PurchaseOrderDetail

–Above query has created 8788 row in my temp table

GO

 

 

–Wants to delete rocords based on where condition of

–following query….

— it has 6624 RECORDS in my table

SELECT * FROM TempPurchaseOrderDetail WHERE PurchaseOrderID<3000

 

Now, we will create one stored procedure which will delete all records from my table which will meet specific conditions.

 

 

CREATE PROC DeleteInBunch

@NumOfRow INT –number given for this variable, will be used with TOP in DELETE statement

AS

SET NOCOUNT ON

–INFINITE loop to iterate until and unless it will finish deleting records

WHILE(1=1)

BEGIN

      DELETE TOP (@NumOfRow) FROM TempPurchaseOrderDetail WHERE PurchaseOrderID<3000

      –LOOP will not break until we will reach to @@ROWCOUND=0

      IF @@ROWCOUNT=0

      BEGIN

            PRINT(‘Records have been delete!!!!’)

            BREAK

      END

END

 

So, finally we have created stored procedure; now let us see whether it actually works!!!

 

–SP will delete all records for specific condition in the bunch of 100

EXEC DeleteInBunch 100

 

Reference: Ritesh Shah

 

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:

Image store in SQL-Server 2005 database and retrieve it in asp.net application with C#:

Storing image in sql-server 2005 database is quiet simple task. All you need to deal with binary datatype. One more benefit you will get by storing image in database is that you are free from burden of managing lots of image folder and its path consistency.

Herewith, I am describing one simple web application in ASP.NET which will store the image in database and will retrieve it right from there.

This article is move to following link:

http://blog.extreme-advice.com/2009/03/07/image-store-in-sql-server-2005-database-and-retrieve-it-in-asp-net-application-with-c/

Useful queries tips for All – SQL user to SQL DBA (SQL Server 2005)

 Useful queries  tips for All – SQL user to SQL DBA (SQL Server 2005)

You might be aware with SQL-Server query. You might even know simple T-SQL query but sometime in order to get some specific results, we need to use some special query. Here are some of them. Use it and enjoy the power of Microsoft SQL Server

 

This one is for getting list of all available objects in current database with schema name.

select s.name As ‘Schema’ , so.name as ‘Object’

from

sys.objects AS so

JOIN

sys.schemas as s

ON

s.schema_id=so.schema_id

where so.type<>‘S’

Order by s.name ASC

 

If you wish to get see how much space have been used by which object in your current database, you can use following query:

select 

      OBJECT_NAME(object_id) as ObjectName,

      SUM (reserved_page_count) * 8192/ 1024 as Reserved_In_KB,

      SUM(used_page_count) * 8192 / 1024 as Used_In_kb

from

      sys.dm_db_partition_stats

group by

      OBJECT_NAME(object_id)

 

Sometime it is useful to see what kind of rights you have on server wide. You can use following query for same. It will give you list of permission you have on SQL-Server based on user account by which you have logged in.

USE AdventureWorks

GO

SELECT * FROM fn_my_permissions(NULL, ‘SERVER’);

GO

 

Sometime it is useful to see what kind of rights you have in database. You can use following query for same. It will give you list of permission you have in database based on user account by which you have logged in.

USE AdventureWorks

GO

SELECT * FROM fn_my_permissions(NULL, ‘DATABASE’);

GO

 

Following query will be used to see buffer cache hit ratio. It is something like how efficiently your pages are residing in buffer.  More you are closure to 100%, good you are utilizing your buffer cache.

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BCHitRatio]

FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters 

        WHERE counter_name = ‘Buffer cache hit ratio’

          AND object_name = ‘SQLServer:Buffer Manager’) a 

     JOIN

     (SELECT *, 1 x FROM sys.dm_os_performance_counters 

        WHERE counter_name = ‘Buffer cache hit ratio base’

          and object_name = ‘SQLServer:Buffer Manager’) b

      ON

      a.object_name=b.object_name

 

 

 

If you wish to see the see all network instance of SQL-Server 2005. You can use following command from command prompt.

Osql –L

 

Reference: Ritesh Shah

HTTP Endpoint with parameter function to calculates Longitude and Latitude distance in SQL-Server 2005

 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:

http://ritesh-a-shah.blogspot.com/2009/03/http-endpoint-in-microsoft-sql-server.html

Or

https://riteshshah.wordpress.com/2009/03/06/http-endpoint-in-microsoft-sql-server-2005-implemented-in-visaul-studio-2008/

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(

        @Latitude1 Float,

        @Longitude1 Float,

        @Latitude2 Float,

        @Longitude2 Float

) Returns Float

 As

Begin

 Return 3963.0*acos(

    sin(@Latitude1/57.295779513082323)

        * sin(@Latitude2/57.295779513082323)

    + cos(@Latitude1/57.295779513082323)

        * cos(@Latitude2/57.295779513082323)

        * cos((@Longitude2–@Longitude1)/57.295779513082323)

)

End

 

You can run above function directly in sql server and test its usage by:

SELECT [AdventureWorks].[dbo].[DistanceOfLatLon](33.0,-84.0,33.3,-84.0)

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.

http://localhost/SQLLatLong?WSDL

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