Creating a SOAP sqlSession supported client application

SQL 2005 Native Web Services enables support for login environment settings through the use of SOAP Headers. The set of environment settings include:

Some of these such as:
are input values only (ie. SQL server does not return these SOAP Headers as part of a response)

sqlSessions on the other hand can be returned as a SOAP header as part of a response. As the name suggests, sqlSessions is used when you want to establish a session that spans multiple SOAP requests/connections.  You may be wondering how do I make use of this functionality.  Please refer to for additional information on how SOAP sessions work.

The standard method of exposing a SQL function and/or stored procedure as a webmethod applies here.
1) Create the function and/or stored procedure
2) Create an SOAP endpoint to expose the function and/or stored procedure; please remember to enable session support on the endpoint

 CREATE ENDPOINT sql_endpoint
  SESSION_TIMEOUT = 3600 -- in seconds

   Please refer to for additional information on CREATE ENDPOINT.
3) Create the client application; for Visual Studio 2005, you can simply add "Web Reference" to the project.
4) Add a new code file item to add the SOAP extension header sample library to the project; Copy and paste the contents of the C# Code Listing for the SQL SOAP Header Sample Class Library and save it with the name "SqlSoapHeader.cs".
   Please refer to for the C# code listing of SQL supported SOAP Header class library.
5) Select Show All Files and expand the Web References node and select the Reference.cs file.
6) In the Code Editor window, update the Reference.cs file to add a public variable to the endpoint definition class. The variable must be of the same SqlSoapHeader class as the type of SOAP optional header.
   For example, to add a public property variable called "sqlSession" of the SqlSoapHeader.sqlSession class to the Reference.cs file under the endpoint class definition ("sql_endpoint"), you would add the following line of code:

  public class sql_endpoint : System.Web.Services.Protocols.SoapHttpClientProtocol {
 public SqlSoapHeaders.sqlSession sqlSession;

7) Add the appropriate SoapHeaderAttribute property to the Web method that you want to enable the SOAP header for.
   For sessions, you would add the following line of code:

      [System.Web.Services.Protocols.SoapHeaderAttribute("sqlSession", Direction=SoapHeaderDirection.InOut)]
     public object[] sqlbatch(string BatchCommands, ...

   Note: Since the sqlSession SOAP Header is sent in both request and respons SOAP messages, the "Direction" property must be set to "InOut".
8) Utilize the sqlSession support in the application:
   For example, to call SqlBatch across different requests but sharing the same session:

      sql_endpoint proxy = new sql_endpoint();
     proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
     proxy.sqlSession = new SqlSoapHeader.sqlSession();
     proxy.sqlSession.initiate = true;
     proxy.sqlSession.MustUnderstand = true; 

     SqlParameter[] myParams = null;
     object[] res = proxy.sqlbatch("SET language Italian; SELECT DATENAME(month, GETDATE()) AS 'Month Name'", ref myParams);
     proxy.sqlSession.timeoutSpecified = false;
     res = proxy.sqlbatch("select @@language", ref myParams);
     proxy.sqlSession.terminate = true;
     proxy.sqlSession.timeoutSpecified = false;
     res = proxy.sqlbatch("SELECT DATENAME(month, GETDATE()) AS 'Month Name'", ref myParams); That's it.Jimmy WuSQL Server ProtocolsDisclaimer: This posting is provided "AS IS" with no warranties, and confers no rights