Calling WebService in SQL2K5 - Error generating XML ...

Hi,

I've created a stored procedure in SQL Server 2005 (September CTP) that calls a web service hosted on a local computer.  Here is the error message that I am getting:


Msg 6522, Level 16, State 1, Procedure GetProductCost, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'GetProductCost':
System.InvalidOperationException: There was an error generating the XML document. ---> System.Security.SecurityException: Request failed.
System.Security.SecurityException:
   at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationWriter1.Write4_GetProductCost(Object[] p)
   at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer.Serialize(Object objectToSerialize, XmlSerializationWriter writer)
   at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String id)
System.InvalidOperationException:
   at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle, String id)
   at System.Xml.Serialization.XmlSerializer.Serialize(XmlWriter xmlWriter, Object o, XmlSerializerNamespaces namespaces, String encodingStyle)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Serialize(SoapClientMessage message)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at WebServiceDemo1.NorthwindWebServices.GetProductCost(Int32 productID)
   at ProductQuery.GetProductCost(Int32 productID, Decimal& productCost)
.

(1 row(s) affected)

SQL Script used for testing
--------------------------
DECLARE @id INT
DECLARE @cost DECIMAL

SET @id = 13
SET @cost = 0.0

EXEC dbo.getproductcost @id, @cost

SELECT @cost


Process used to create the stored procedure
-----------------------------------------------
USE master
GO

CREATE ASYMMETRIC KEY WebClientDemo1Key FROM EXECUTABLE FILE = '.........\ WebClientDemo1.dll'
GO

CREATE LOGIN WebClientDemo1Login FROM ASYMMETRIC KEY WebClientDemo1Key
GO

GRANT EXTERNAL ACCESS ASSEMBLY TO WebClientDemo1Login
GO

USE CLRDemo1
GO

CREATE ASSEMBLY WebClientDemo1 from ' .........\WebClientDemo1.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

CREATE ASSEMBLY WebClientDemo1XML from '......\WebClientDemo1.XmlSerializers.dll’ WITH PERMISSION_SET = SAFE
GO

CREATE PROCEDURE dbo.GetProductCost(@id int, @cost decimal out)
AS
EXTERNAL NAME WebClientDemo1.ProductQuery.GetProductCost
GO


Web service ... NorthwindWebServices.asmx
--------------------------------------------------
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;

[WebService(Namespace = "http://localhost/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class NorthwindWebServices : System.Web.Services.WebService
{
 public NorthwindWebServices()
 {

 }
 [WebMethod]
 public decimal GetProductCost(int productID)
 {
  SqlConnection cnn = new SqlConnection("Data Source=DESKTOP;Initial Catalog=Northwind;Integrated Security=True");
  cnn.Open();

  SqlCommand cmd = new SqlCommand("select unitprice from dbo.products where productid = " + productID.ToString(), cnn);
  cmd.CommandType = CommandType.Text;

  SqlDataReader rdr = cmd.ExecuteReader();
  rdr.Read();

  decimal unitcost = rdr.GetDecimal(0);

  rdr.Close();
  cnn.Close();

  return unitcost;
 }
}


Web service ... NorthwindWebServices.cs
----------------------------------------------------------
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;

[WebService(Namespace = "http://localhost/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class NorthwindWebServices : System.Web.Services.WebService
{
 public NorthwindWebServices()
 {

 }
 [WebMethod]
 public decimal GetProductCost(int productID)
 {
  SqlConnection cnn = new SqlConnection("Data Source=DESKTOP;Initial Catalog=Northwind;Integrated Security=True");
  cnn.Open();

  SqlCommand cmd = new SqlCommand("select unitprice from dbo.products where productid = " + productID.ToString(), cnn);
  cmd.CommandType = CommandType.Text;

  SqlDataReader rdr = cmd.ExecuteReader();
  rdr.Read();

  decimal unitcost = rdr.GetDecimal(0);

  rdr.Close();
  cnn.Close();

  return unitcost;
 }
}


To create the proxy class ...
------------------------------------------------------
wsdl /par:wsdlparams.txt /n:WebServiceDemo1 http://localhost/WebServiceDemo1/NorthwindWebServices.asmx wsdl


Where wsdlparams.txt is ...
------------------------------------------------------
<wsdlParameters xmlns='http://microsoft.com/webReference/'>
  <language>c#</language>
  <protocol>Soap</protocol>
  <nologo>true</nologo>
  <sharetypes>false</sharetypes>
  <webReferenceOptions>
    <codeGenerationOptions>properties oldAsync</codeGenerationOptions>
  </webReferenceOptions>
</wsdlParameters>


Proxy class ... NorthwindWebServices.cs
--------------------------------------------------------------
//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:2.0.50215.44
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

//
// This source code was auto-generated by wsdl, Version=2.0.50215.44.
//
namespace WebServiceDemo1 {
    using System.Diagnostics;
    using System.Web.Services;
    using System.ComponentModel;
    using System.Web.Services.Protocols;
    using System;
    using System.Xml.Serialization;
   
   
    /// <remarks/>
    [System.Diagnostics.DebuggerStepThroughAttribute()]
    [System.ComponentModel.DesignerCategoryAttribute("code")]
    [System.Web.Services.WebServiceBindingAttribute(Name="NorthwindWebServicesSoap", Namespace="http://localhost/")]
    public partial class NorthwindWebServices : System.Web.Services.Protocols.SoapHttpClientProtocol {
       
        /// <remarks/>
        public NorthwindWebServices() {
            this.Url = "http://localhost/WebServiceDemo1/NorthwindWebServices.asmx";
        }
       
        /// <remarks/>
        [System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://localhost/GetProductCost", RequestNamespace="http://localhost/", ResponseNamespace="http://localhost/", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]
        public decimal GetProductCost(int productID) {
            object[] results = this.Invoke("GetProductCost", new object[] {
                        productID});
            return ((decimal)(results[0]));
        }
       
        /// <remarks/>
        public System.IAsyncResult BeginGetProductCost(int productID, System.AsyncCallback callback, object asyncState) {
            return this.BeginInvoke("GetProductCost", new object[] {
                        productID}, callback, asyncState);
        }
       
        /// <remarks/>
        public decimal EndGetProductCost(System.IAsyncResult asyncResult) {
            object[] results = this.EndInvoke(asyncResult);
            return ((decimal)(results[0]));
        }
    }
}


ProductQuery.cs
--------------------------------------
using System;
using System.Collections.Generic;
using System.Text;

public class ProductQuery
{
      public static void GetProductCost(int productID, ref decimal productCost)
      {
            WebServiceDemo1.NorthwindWebServices nthweb = new WebServiceDemo1.NorthwindWebServices();
            productCost = nthweb.GetProductCost(productID);
      }
}


Sign the assembly ...
-------------------------------------
Created a key pair using ...
      sg –k sgKey.snk

Added the key to the project file


Created an xml serialization assembly ....
------------------------------------------------
sgen /a:WebClientDemo1.dll /compiler:/keyfile:..\..\sgKey.snk /k /o:.




When I use the same proxy class inside a Windows form it works!

Any help would be much appreciated.

Thanks!