Share via

Sample code to create Native XML Web Service using SQL 2005 Transact-SQL script

  • Article

"CREATE ENDPOINT" Transact-SQL allows us to create Native XML Web services using Transact-SQL. (Refer Books online for details)

The beauty of this example is: neither we create web service project in visual studio, nor we need data access layer to access SQL data. All that we need to do is copy and execute Transact-Sql script on your Sql server which will create desired end point.

 Create Native XML Web Service

We will create Native XML Web Service with

1. Virtual directory https://PDesai222/MySqlEndPoint

2. Three web methods

         I. Web method 'GetDbInfo' that executes system stored procedure 'sp_helpdb' in master db and lists Sql server databases

       II. Web method 'GetSqlLogins' that executes 'xp_loginInfo' system stored proc in master db and lists Sql server login accounts

      III. And web method 'GetTableList' that executes 'sp_tables' system tables and returns master db table names

Simplely copy and execute following Sql script on your Sql server which will create desired Native XML Web Service.

 

Transact-SQL to create My_Sql_EndPoint

 /**********************************************************

In this example ‘PDesai222’ is my SQL server name change it

Please change it to your server name before executing script

 

Also change Grant permission statement to reflect your login

Instead of [MyDomain\MyLogin]

 **********************************************************/

 DROP ENDPOINT my_sql_endpoint;

 

GO

 

 

 

CREATE ENDPOINT my_sql_endpoint

STATE = STARTED

AS HTTP(

 

PATH = '/MySqlEndPoint',

AUTHENTICATION = (INTEGRATED ),

PORTS = ( CLEAR ),

SITE = 'PDesai222'

)

 

FOR SOAP (

 

WEBMETHOD 'GetDbInfo'

(name='master.sys.sp_helpdb',

SCHEMA=STANDARD ),

 

WEBMETHOD 'GetTableList'

 (name='master.sys.sp_Tables',

 SCHEMA=STANDARD ),

 

WEBMETHOD 'GetSqlLogings'

 (name='master.sys.xp_logininfo',

 SCHEMA=STANDARD ),

 

WSDL = DEFAULT,

SCHEMA = STANDARD,

DATABASE = 'master',

NAMESPACE = 'https://www.GulbargaOnline.com/'

 

);

 

GO

 

GRANT CONNECT ON ENDPOINT::my_sql_endpoint TO [MyDomain\MyLogin]

 

Verify Native XML Web Service

 Open browser (internet explorer) and type Url https://pdesai222/MySqlEndPoint?wsdl

How to retrieve data using XML Native Web Service?

  1.  Create a C# ASP.NET web site project in Visual studio and add web reference to SOAP end point you just created (https://pdesai222/MySqlEndPoint?wsdl) and name it as pdesai222
  2. Add a GridView control and three Buttons by replacing your Default.Aspx code with following code

 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 <html xmlns="https://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

    <table style="width: 853px">

    <tr></tr> <tr><td>

    <asp:Button ID="GetDbInfo" Text="GetDbInfo" runat="server" CommandName="GetDbInfo" OnClick="Button_Click"/>

    </td><td>

    <asp:Button ID="GetSqlLogings" Text="GetSqlLogings" runat="server" CommandName="GetSqlLogings" OnClick="Button_Click"/>

    </td><td>

    <asp:Button ID="GetTableList" Text="GetTableList" runat="server" CommandName="GetTableList" OnClick="Button_Click"/>

    </td></tr><tr></tr>

    <tr>

    <td colspan = "3">

        <asp:GridView ID="GridView1" runat="server" Width="100%">

        </asp:GridView>

        </td> </tr>

    </table>

    </div>

    </form>

</body>

</html>

 

 

 

   3. Edit code behind page Default.Aspx.CS Add using to refer your web service

 using pdesai222;

  1. Copy following code to code behind page Default.Aspx.CS

   protected void Button_Click(object sender, EventArgs e)

    {

        pdesai222.my_sql_endpoint proxy = new my_sql_endpoint();

        proxy.UseDefaultCredentials = true;

        Object[] retvalue = null;

        DataSet dataSet = null;

 

        switch ((sender as Button).CommandName)

        {

            case "GetDbInfo":

                retvalue = proxy.GetDbInfo(null);

                dataSet = (DataSet)retvalue[0];

                break;

 

            case "GetSqlLogings":

                System.Data.SqlTypes.SqlString privilege = new System.Data.SqlTypes.SqlString("Not wanted");

 

                retvalue = proxy.GetSqlLogings(null, null, ref privilege);

                dataSet = (DataSet)retvalue[0];

            break;

 

            case "GetTableList":

                retvalue = proxy.GetTableList(null, null, null, null, true);

                dataSet = (DataSet) retvalue[4];

                break;

 

        }

 

        this.GridView1.DataSource = dataSet;

     this.GridView1.DataBind();

      }

 Build and run your web site project and run it. Click command button to make SOAP request to your Navtive XML web service.

Comments

  • Anonymous
    June 06, 2007
    I've read a number of articles on this subject and they all give examples using localhost. In practice, you're more likely to connect to Sql server from a client (different PC). So this is what I tried and using Visual Studio 2005, when I sepecified the URL http://pdesai222/MySqlEndPoint?wsdl I got a SOAP response saying I could not log into Sql Server because I did not specify login credentials. So how do you do this in VS2005 or any get-arounds ? It would be more helpful if people supplied an example using a real world scenario.

  • Anonymous
    June 10, 2007
    The comment has been removed

  • Anonymous
    June 16, 2007
    Following SQL server versions support Create EndPoint feature • Microsoft SQL Server 2005 Standard Edition • Microsoft SQL Server 2005 Developer Edition • Microsoft SQL Server 2005 Enterprise Edition • Microsoft SQL Server 2005 Workgroup Edition Visit url listed below for SQL Server 2005 feature and version comparision info http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

  • Anonymous
    September 21, 2007
    follow the step, I got the under exception when call web method, {"The request failed with HTTP status 401: Unauthorized."} why?

  • Anonymous
    September 25, 2007
    The comment has been removed

  • Anonymous
    September 18, 2009
    Thanks for this tutorial it help me jumpstart my SQL webservices knowledge. I made a few changes: Adding impersonation to my web.config helped a lot: <identity impersonate="true" /> Also set the site to not use anonymous login, and to "Digest authentication for Windows domain servers" in IIS. And added a port to the endpoint since I am running a local webserver too (so port 80 was being used) CLEAR_PORT = 8088, My final endpoint creation looks like this: CREATE ENDPOINT my_sql_endpoint STATE = STARTED AS HTTP( PATH = '/MySqlEndPoint', AUTHENTICATION = (INTEGRATED ), PORTS = ( CLEAR ), CLEAR_PORT = 8088, SITE = 'localhost' ) FOR SOAP ( WEBMETHOD 'spWho2' (name='master.sys.sp_who2', SCHEMA=STANDARD ), WEBMETHOD 'GetDbInfo' (name='master.sys.sp_helpdb', SCHEMA=STANDARD ), WEBMETHOD 'GetTableList' (name='master.sys.sp_Tables', SCHEMA=STANDARD ), WEBMETHOD 'GetSqlLogings' (name='master.sys.xp_logininfo', SCHEMA=STANDARD ), WSDL = DEFAULT, SCHEMA = STANDARD, DATABASE = 'master', NAMESPACE = 'http://localhost/' );