Export (0) Print
Expand All

Using the Custom WSDL Application

To set up the custom WSDL generator sample application, you have to perform the following tasks:

  • Create a custom WSDL handler application to support your specific WSDL.
    To complete this task, see Building the Custom WSDL Application.
  • Configure your installation of SQL Server 2005 to register and use the custom WSDL handler.

This task involves creating a deployment script that executes the following modifications to your installation of SQL Server 2005:

  • Adds the custom WSDL assembly (CustomWSDL.dll) to the server and registers it for use with the ADD ASSEMBLY statement.
  • Creates any additional stored procedures that your custom WSDL handler application depends upon for correct operation.
  • Creates, or modifies, an HTTP endpoint at the instance of SQL Server so that it will use your custom WSDL handler and return a custom WSDL response, instead of the default or simple WSDL response.

The following procedure assumes that you are logged in as a local administrator on a computer that is running SQL Server 2005, or that you can connect to it remotely with administrative privileges.

Part 1: Configure the Server

  1. In SQL Server Management Studio, click New Query and connect to the server.
  2. Copy the following Transact-SQL script into the query window.
    USE master 
    GO
    
    -- Drop myWSDL procedure if it exists.
    IF (SELECT count(*) FROM sysobjects WHERE name = 'myWSDL') = 1 
    DROP PROCEDURE myWSDL
    GO
    
    -- Drop CustomWSDL assembly if it exists.
    DROP ASSEMBLY CustomWSDL
    GO
    
    -- Update the path to the compiled assembly as necessary.
    CREATE ASSEMBLY CustomWSDL FROM 'C:\temp\CustomWSDL.dll'
    GO 
    
    -- Create a stored procedure to map to the common lanugage
    -- runtime (CLR) method As with any other SQL Server stored procedure
    -- that maps to a CLR method, the actual stored procedure name 
    -- ('myWSDL') can be arbitrarily specified.
    CREATE PROCEDURE myWSDL
    (
    @endpointID as int,
    @isSSL as bit,
    @host as nvarchar(256),
    @queryString as nvarchar(256),
    @userAgent as nvarchar(256)
    )
    AS EXTERNAL NAME CustomWSDL.[MSSql.CustomWSDL].GenerateWSDL
    GO
    
    -- Follow the security guidelines set up for your environment.
    -- The following example is meant to be used for development or 
    -- testing purposes only.
    GRANT EXEC on myWSDL to [PUBLIC]
    GO
    
    -- The following is a sample stored procedure (InOut) that
    -- demonstrates the configuration of an HTTP endpoint. 
    -- If the InOut stored procedure already exists, it is dropped.
    IF (SELECT count(*) FROM sysobjects WHERE name = 'InOut') = 1 DROP PROC InOut
    GO
    
    CREATE PROC InOut
             @InParam int,
             @OutParam nvarchar(100) output
    AS
    SELECT * FROM syslanguages WHERE langid = @InParam
    SELECT @OutParam = [name] FROM syslanguages WHERE langid = @InParam
    PRINT @OutParam
    SELECT * FROM syslanguages WHERE langid = @InParam FOR XML raw, XMLSCHEMA
    RETURN 1
    GO
    GRANT EXEC on InOut to [PUBLIC]
    
    -- The following creates a sample HTTP endpoint to demonstrate 
    -- the endpoint setup. If the sample endpoint already exists, it
    -- is first dropped.
    IF (SELECT count(*) FROM [msdb].sys.http_endpoints WHERE name = 'sql_endpoint') = 1
    DROP ENDPOINT sql_endpoint
    GO
    
    CREATE ENDPOINT sql_endpoint
             STATE=STARTED
    AS HTTP (
             SITE='*',
             PATH='/sql/WSDL',
             PORTS=(CLEAR),
             CLEAR_PORT=80,
             AUTHENTICATION=(DIGEST, INTEGRATED)
    )
    FOR SOAP
    (
             WEBMETHOD 'http://myNS.com/'.'InOut' ( NAME='master.dbo.InOut' ),
             DATABASE = 'master',
             WSDL='master.dbo.myWSDL',
             Batches=enabled,
             SCHEMA = STANDARD
    )
    
    GRANT CONNECT ON ENDPOINT::sql_endpoint to [PUBLIC]
    
  3. Execute the script.

Part 2: Testing the Handler

To make sure the custom WSDL handler functions correctly, try using the modified URL query strings to request a custom WSDL. For example, if the instance of SQL Server 2005 that you are connecting to is named MyServer and the previous script is used, it should have an endpoint path established on the server of sql/WSDL, which will respond by using the custom WSDL handler. Therefore, to test connecting to this endpoint and provide a custom WSDL request, you would use a URL such as the following in the HTTP Web browser client:

http://MyServer/sql/WSDL?wsdlargument

The value of argument can be any one of the following custom WSDL identifiers that support the strings for each of the different client and WSDL types.

Custom WSDL URL Description

everett

For simple Web clients developed by using Visual Studio 2003 developer tools.

jbuilder

For simple Web clients developed by using Borland JBuilder 9.0 developer tools.

glue

For simple Web clients developed by using webMethods Glue 5.0.1 developer tools.

As shown in the following table, the <argument> value returns a simple WSDL (all XSD native types) for all three of these custom choices; however, if you want the full extended WSDL, you can append extended to the query argument string.

Simple WSDL identifier Extended WSDL identifier

http://MyServer/sql/WSDL?wsdleverett

http://MyServer/sql/WSDL?wsdleverettextended

http://MyServer/sql/WSDL?wsdljbuilder

http://MyServer/sql/WSDL?wsdljbuilderextended

http://MyServer/sql/WSDL?wsdlglue

http://MyServer/sql/WSDL?wsdlglueextended

Community Additions

ADD
Show:
© 2014 Microsoft