none
SOAP/HTTP Endpoints: Deprecated

    Question

  • SQL2008 Upgrade Advisor has raised the error that the endpoints we are using for all of our websites are being deprecated and I should start using Windows Communication Foundation.

     

    But, I've not been able to find any articles about how to convert existing endpoints to WCF.  And, incredibly, I search this forum and find no hits on 'endpoints' or 'SOAP'.  How can this be when the forum abstract specifically mentions 'SOAP/HTTP'?

     

    Where should I start looking for documentation on converting SOAP endpoints to WCF?

    Thursday, August 28, 2008 7:32 PM

Answers

  • Cristy - I am going to respond to PaulNations first just because his question is faster. Yours will take a little while longer.

     

    PaulNations - I actually think you've asked a pretty good question. Essentially, "if it ain't broke don't fix it" right? Well I'll provide you with some of the reasons Microsoft has deprecation Native Web Services and then you can evaluate for yourself if you think it's appropriate for you to move.

     

    1. As of SQL Server 2008, Native Web Services is deprecated. This means that Microsoft has entered maintainence mode for Native Web Services. Support will be removed in a future version and innovation in this space is no longer taking place.

     

    2. This will help Microsoft to better align with existing Microsoft technologies.


    3. SQL Server 2005 Native Web Services provided users with a simple mechanism to expose existing Stored Procedures (SP) and User Defined Functions (UDF) as web methods.  Since the functionality runs inside the SQL Server process itself, SQL Server 2005 Native Web Services cannot leverage a typical “web farm” to overcome the high processing cost of text XML parsing. Utilizing a mid-tier, such as IIS and ASP.Net, can help to spread out this cost among a group of web servers to reduce XML processing load on the SQL Server.


    Evaluation
    Number one and three are the things that are going to impact you. Point number one will impact you the longer and longer you plan to use Native Web Services.
    Point number three is especially important. An ASP.Net mid-tier scales much better than the traditional Native Web Services solution.

     

    Tuesday, September 09, 2008 1:14 AM
  • Cristy,

    So let's get our basic goal down. We're going to build a webservice that will contain a function that will call your SP getProducts(). Sound good? OK.

     

    You should first know how to create the service.

    • Open Visual Studio 2005.
    • Go to File>New>Web Site>ASP.NET Web Service
    • Make sure your Location is HTTP
    • Make sure that Location is pointed to a place to IIS recognizes as a Virtual Directory.
    • Make sure your language is Visual C#

     

    To use Windows Authentication please add the following (if they aren't there) to your Web.config file:

    Code Snippet
    <identity impersonate="true" />
    <authentication mode="Windows"/>

     

     

    In the last Web Service I used these (I'm providing them only so you won't have to hunt):

    Code Snippet
    using System;
    using System.Web;
    using System.Web.Services;
    using System.Web.Services.Protocols;
    using System.Data.SqlTypes;
    using System.Data.SqlClient;
    using System.Data;
    using System.Xml;
    using System.IO;
    using System.Collections;
    using System.Text;

     

     

    Do you see something like this?

    Code Snippet
    [WebService(Namespace = "http://tempuri.org/")]http://tempuri.org/">http://tempuri.org/")]<P< A> align=left> 

     

    Change the namespace to whatever namespace you used when you created your endpoint.

     

    Please also note this:

     

    Code Snippet
    public class Service : System.Web.Services.WebService

     

     

    This must match the following found in your .asmx file:

    So that should help walk you through the initial setup, but how do you actually call your SP? That's the real meat of the issue. I advise you to look at your WSDL generated code file. Copy and paste the function header from that.


    Code Snippet

    [SoapDocumentMethod(Action = "http://tempuri.org/sample")]
    [WebMethod]
    public System.Data.DataSet GetCustomerInfo2([System.Xml.Serialization.XmlElementAttribute(IsNullable = true)] SqlInt32 myLimit)
    {
        DataSet resultSet = new DataSet();

        //This is for Windows Authentication
        string connectionString = "Server=(local);Integrated Security=True;Database=AdventureWorks;Persist Security Info=True;";

        //Create the SP call
        string function = "sample " + myLimit.ToString();

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            //Create my objects
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet set = new DataSet();
            SqlCommand cmd = new SqlCommand(function, conn);

            //Execute command and fill the DataSet
            cmd.CommandType = CommandType.Text;
            adapter.SelectCommand = cmd;
            adapter.Fill(set);
            return set;
        }
    }

     

     

    That function header is completely copied from the WSDL generated code file. But see, essentially you just need to build an ASP.Net function that calls the SP that you have. Once you do that tell your existing client application to start using this new ASP.Net web service.

    Wednesday, September 10, 2008 1:17 AM

All replies

  • Well SQL Server 2008 is a pretty new product and so our forums for it, as you can see, still get low amounts of traffic compared to our othe SQL Server forums. So you might try searching our this Data Access forum:

     

    http://forums.microsoft.com/msdn/showforum.aspx?forumid=87&siteid=1

     

    I don't have any resources for migrating from Native Web Services to WCF but another migration path is to move to ASP.Net. We have a whitepaper in the works that covers migrating from Native Web Services to ASP.Net. Is ASP.Net an option?

    Thursday, August 28, 2008 11:55 PM
  • For me these method (ASP.Net) is an option. But, until now, I didn't find any resources that realy help to migrate from native web services (stored procedures) to ASP.NET Web Services. So, if you can help me, please do.


    Thursday, September 04, 2008 7:27 AM
  • The web sites that consume the endpoints are written in ASP.Net.  I'm not clear on what we would be converting.

     

    The sites are using Object Data Sources through a data access layer of VS.Net code that processes the calls to the database endpoints. 

    Thursday, September 04, 2008 8:00 PM
  • PaulNations - Maybe I don't understand your question, but here goes. The idea is that right now your application is directly calling methods written in SQL that reside on the SQL Server. But, what you would do, is have your client call those same methods on a webserver rather than on a SQL Server. Those methods would accomplish the same thing - they would just be written in ASP.Net rather than in SQL. Those methods on the webserver connect to your SQL Server to obtain the results and then send them back to your client, thus acting sort like a middle man (or middle tier).

     

    Pavel - Yes I can help you. The problem is that this is a complicated problem and unfortunately I don't know of any published resources, though we're working on it (slowly). At a high level, this is what you need to do. We can delve into any point you like.

     

    Assumptions

    ·         You have a SOAP endpoint and applicable SPs/UDFs.

    ·         You have a client application that connects to a SOAP endpoint and queries applicable SPs/UDFs on a SQL Server.

     

    Overview

    The following is an overview of what steps are involved in the migration process.

     

    ·         You must start with a client application that uses SOAP methods exposed by SQL Server Native Web Services. The client application has a "web reference" to SQL Server's WSDL.

    ·         Then create a new ASP.Net web service. You can then use this new web service to expose, to your client, all of the methods associated with that particular endpoint. Thus, this webservice now takes the place of your SOAP WSDL.

    ·         Now that you have a ASP.Net web service that can call your SPs and UDFs, you simply go back to your client application and remove the "web reference" to SQL Server that you had previously and add a new "web reference" to your new ASP.Net web service. Once you do that your dependency on your WSDL should be gone and your migration complete.

    Friday, September 05, 2008 2:06 AM
  • Thank you very much for your reply, but my real problem is that I was never written an ASP webservice and I am not familiar with ASP.Net. I am very familiar with SQL Server and I wrote some stored procedures like

    create procedure getProducts @Category varchar(20) as
    begin
    select 1 as Tag,null as Parent, ...
    ...
    where Category=@Category
    for xml explicit

    select Category,min(Price) as pMin from products...
    group by Category for xml raw
    end
    alter endpoint webs for SOAP (add webmethod 'getProducts' (name='db.dbo.getProducts'))

    In these example these stored procedure returns two SQLXML streams with XML data. The client that consumes these data is written in Adobe Flex and with integrated authentication (because of wsdl) it works fine.
    Now I don't know how to begin and write some ASP.Net code for exposing these sp's to SOAP. The articles that I read about WebServices in ASP.Net or WCF show me some very complicated code (for me) to do some basic stuff like get scalar value. I din't find articles that explain how to get xml data from SQL Server and expose that to a endpoint.

    So, if you have some tips please help me.

    Cristy
    Saturday, September 06, 2008 2:21 PM
  • Tres;

     

    I'm not really trying to be dense (maybe I was just born that way), but why would I need to create an ASP.Net web service that references the SPs & UDFs on my SQL server when my website is already directly referencing those same procs & funcs?

     

    Monday, September 08, 2008 7:20 PM
  • Cristy - I am going to respond to PaulNations first just because his question is faster. Yours will take a little while longer.

     

    PaulNations - I actually think you've asked a pretty good question. Essentially, "if it ain't broke don't fix it" right? Well I'll provide you with some of the reasons Microsoft has deprecation Native Web Services and then you can evaluate for yourself if you think it's appropriate for you to move.

     

    1. As of SQL Server 2008, Native Web Services is deprecated. This means that Microsoft has entered maintainence mode for Native Web Services. Support will be removed in a future version and innovation in this space is no longer taking place.

     

    2. This will help Microsoft to better align with existing Microsoft technologies.


    3. SQL Server 2005 Native Web Services provided users with a simple mechanism to expose existing Stored Procedures (SP) and User Defined Functions (UDF) as web methods.  Since the functionality runs inside the SQL Server process itself, SQL Server 2005 Native Web Services cannot leverage a typical “web farm” to overcome the high processing cost of text XML parsing. Utilizing a mid-tier, such as IIS and ASP.Net, can help to spread out this cost among a group of web servers to reduce XML processing load on the SQL Server.


    Evaluation
    Number one and three are the things that are going to impact you. Point number one will impact you the longer and longer you plan to use Native Web Services.
    Point number three is especially important. An ASP.Net mid-tier scales much better than the traditional Native Web Services solution.

     

    Tuesday, September 09, 2008 1:14 AM
  • Cristy,

    So let's get our basic goal down. We're going to build a webservice that will contain a function that will call your SP getProducts(). Sound good? OK.

     

    You should first know how to create the service.

    • Open Visual Studio 2005.
    • Go to File>New>Web Site>ASP.NET Web Service
    • Make sure your Location is HTTP
    • Make sure that Location is pointed to a place to IIS recognizes as a Virtual Directory.
    • Make sure your language is Visual C#

     

    To use Windows Authentication please add the following (if they aren't there) to your Web.config file:

    Code Snippet
    <identity impersonate="true" />
    <authentication mode="Windows"/>

     

     

    In the last Web Service I used these (I'm providing them only so you won't have to hunt):

    Code Snippet
    using System;
    using System.Web;
    using System.Web.Services;
    using System.Web.Services.Protocols;
    using System.Data.SqlTypes;
    using System.Data.SqlClient;
    using System.Data;
    using System.Xml;
    using System.IO;
    using System.Collections;
    using System.Text;

     

     

    Do you see something like this?

    Code Snippet
    [WebService(Namespace = "http://tempuri.org/")]http://tempuri.org/">http://tempuri.org/")]<P< A> align=left> 

     

    Change the namespace to whatever namespace you used when you created your endpoint.

     

    Please also note this:

     

    Code Snippet
    public class Service : System.Web.Services.WebService

     

     

    This must match the following found in your .asmx file:

    So that should help walk you through the initial setup, but how do you actually call your SP? That's the real meat of the issue. I advise you to look at your WSDL generated code file. Copy and paste the function header from that.


    Code Snippet

    [SoapDocumentMethod(Action = "http://tempuri.org/sample")]
    [WebMethod]
    public System.Data.DataSet GetCustomerInfo2([System.Xml.Serialization.XmlElementAttribute(IsNullable = true)] SqlInt32 myLimit)
    {
        DataSet resultSet = new DataSet();

        //This is for Windows Authentication
        string connectionString = "Server=(local);Integrated Security=True;Database=AdventureWorks;Persist Security Info=True;";

        //Create the SP call
        string function = "sample " + myLimit.ToString();

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            //Create my objects
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet set = new DataSet();
            SqlCommand cmd = new SqlCommand(function, conn);

            //Execute command and fill the DataSet
            cmd.CommandType = CommandType.Text;
            adapter.SelectCommand = cmd;
            adapter.Fill(set);
            return set;
        }
    }

     

     

    That function header is completely copied from the WSDL generated code file. But see, essentially you just need to build an ASP.Net function that calls the SP that you have. Once you do that tell your existing client application to start using this new ASP.Net web service.

    Wednesday, September 10, 2008 1:17 AM
  • To Deprecate such a great and useful feature, is an extremely poor move in my opinion.

     

    I can understand the desire to improve something to be more scalable, and there is room to do that.  But to improve things by eliminating a great feature that is well suited for simple and efficient implementations is not the way to do it.

     

    Our company currently uses Native XML web services in SQL 2005 for some systems to provide a lightweight and cost effective web-service implementation, that allows cross-platform applications to reach SQL server through a single interface.

     

    I would strongly recommend this feature be carried on, as it was a huge selling feature of SQL 2005.  Okay, for those folks who needs to process millions of transactions through a web service, then also allow them to take the more complicated route.  But Native XML web services is an excellent tool to bridge many simple SOA applications.

     

    If our company (5000+ employees)  were forced to go out and purchase additional servers and additional development tools everytime we needed to implement a straightforward web-service,  my recommendation would be:  either stay with SQL 2005, or to start looking at open-source web-services instead .

     

     

     

     

    Friday, November 28, 2008 4:41 PM
  • OK, I'm back again.  My original posting related to preparing to move to SQL 2008, we're now moving and I must do something about the endpoints.  I am still floored by the lack of support Microsoft is giving us on this topic.  But I have found a How To on Windows Communication Foundation and am working through it.  But now I'm only on the 3rd topic and have already hit a brick wall that is not addressed in the How To.

    When I step through the console app it throws an exception with error message: HTTP could not register URL http://+:8000/ServiceModelSamples/Services/. Your process does not have access rights to this namespace (see http://go.microsoft.com/fwlink/?LinkId=70353 for details). The linked article discusses Configuring HTTP and HTTPS using different tools for the different OSes. 

    I'm running Windows 7 (x64) and tried to use this line:
    netsh http add urlacl url=http://+:8000/ServiceModelSamples/Samples user=ADHE/pauln
    and I'm getting Create SDDL failed, Error: 1332 The parameter is incorrect.

    I don't know where to go from here. 

    I've looked forward into the How To tutorial far enough to realize that it never progresses beyond the insipid Calculator service.  How do these things connect to the database and present an interface for a website to communicate with? 

    I am anxiously awaiting a reply, because my boss is expecting to see a website in front of his new SQL 2008 machine.
    • Proposed as answer by Durward White Friday, April 30, 2010 7:19 PM
    Wednesday, August 26, 2009 6:26 PM
  • You can write services yourself in WCF that communicate with a database (WCF is a framework for web services that can interoperate with databases through DataContext), but for one that's written (and supported) you can look into ADO.NET Data Services (http://msdn.m icrosoft.com/en-us/data/bb931106.aspx) or .NET RIA Services. There's also a library known as ASP.NET Dynamic Data for building data-driven websites.
     
    Hope this is helpful,
    Bob Beauchemin
    "PaulNations" wrote in message news:cfacd746-9781-4c5 0-9384-3ea229c719e1...
    OK, I'm back again.  My original posting related to preparing to move to SQL 2008, we're now moving and I must do something about the endpoints.  I am still floored by the lack of support Microsoft is giving us on this topic.  But I have found a How To on Windows Communication Foundation and am working through it.  But now I'm only on the 3rd topic and have already hit a brick wall that is not addressed in the How To.

    When I step through the console app it throws an exception with error message: HTTP could not register URL http://+:8000/Se rviceModelSamples/Services/. Your process does not have access rights to this namespace (see http://go.mic rosoft.com/fwlink/?LinkId=70353 for details). The linked article discusses Configuring HTTP and HTTPS using different tools for the different OSes. 

    I'm running Windows 7 (x64) and tried to use this line:
    netsh http add urlacl url=http://+:8000/ServiceModelSamples/Samples user=ADHE/pauln
    and I'm getting Create SDDL failed, Error: 1332 The parameter is incorrect.

    I don't know where to go from here. 

    I've looked forward into the How To tutorial far enough to realize that it never progresses beyond the insipid Calculator service.  How do these things connect to the database and present an interface for a website to communicate with? 

    I am anxiously awaiting a reply, because my boss is expecting to see a website in front of his new SQL 2008 machine.
    Wednesday, August 26, 2009 11:34 PM
  • I know I can write services to connect to databases, the SQL 2008 upgrade advisor informed me that I would have to do just that.  I'm trying to learn how to do it and with every example I've worked through so far I keep running into road blocks.  In my post above I was using a Microsoft How To and ran into the specific problem I inquired about relating to a netsh error.

    I started another example How To today from 15 Seconds, and I'm running into a different problem with the svcutil.  The specific error I get is: Cannot obtain Metadata from http://localhost/MyProjects/15Seconds/WCFBasics/MathService.svc?WSDL and then it says: If this is a WCF service ... please refer to ... http://go.microsoft.com/fwlink/?LinkId=65455 That link is a long discussion about publishing metadata.  I added the code to the web.config file and still can't run the svcutil.

    This has gone beyond ridiculous and is now bordering on the decision to scrap SQL 2008 and never move beyond 2005.


    Can no one point me to a WCF How To - that works and that does something with a database?  Why is every WCF how to article I've come across prattling on about the insipid calculator?
    Thursday, August 27, 2009 7:33 PM
  • Maybe this is too simple of an answer but I noticed in your post that this was your command

    netsh http add urlacl url=http://+:8000/ServiceModelSamples/Samples user=ADHE/pauln

    It seems to me that the user parameter is incorrect because you are using a forward slash instead of a backwards slash.  Try this:

    netsh http add urlacl url=http://+:8000/ServiceModelSamples/Samples user=ADHE\pauln 

     

    Friday, April 30, 2010 7:22 PM