Accessing database from User Defined Aggregate

Hi,
I am trying to access the database (using SqlConnection and then SQLCommand)
from within an CLR User Defined Aggregate, but SQL 2005 always return that
"Data access is not allowed in this context".
Anybody knows how can I do this?
Thanks,
FernandoAFAIK, you cannot do this.
What is your need to do this?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:6025EC30-71C4-4BD7-B96C-FE62C98A33D9@.microsoft.com...
> Hi,
> I am trying to access the database (using SqlConnection and then
> SQLCommand)
> from within an CLR User Defined Aggregate, but SQL 2005 always return that
> "Data access is not allowed in this context".
> Anybody knows how can I do this?
> Thanks,
> Fernando|||SriSamp,
Thanks for your response. While doing aggregation I may need to access
additional data for computing my calculation.
It is not a trivial aggregations (sum, avg, etc.). This is specific for some
statistics calculation for non-linear equations.
Thanks,
Custodio
"SriSamp" wrote:
> AFAIK, you cannot do this.
> What is your need to do this?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Fernando" <Fernando@.discussions.microsoft.com> wrote in message
> news:6025EC30-71C4-4BD7-B96C-FE62C98A33D9@.microsoft.com...

Accessing a web service using clr in SQL 2005

I need to access a billing webservice from SQL. I createde a new c# class project and made a web refrence to the web service "ProdBilling".

Here is the code of my assembly

using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
namespace PaymentProc
{
public class PaymentProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ChargeCard(int account, int amount)
{
string Response;
ProdBilling.Service serv = new ProdBilling.Service();
Response = serv.ChargeCard(account, amount);
SqlContext.Pipe.Send(Response);
}
}
}

I then ran WSDL

wsdl /oaymentProc.cs /naymentProc http://ProdWeb1/PaymentProc/PaymentProc.asmx

Then compliled

csc /target:library PaymentProc.cs

and added the assembly
CREATE ASSEMBLY PaymentProc from 'D:\ProdCode\PaymentProc.dll' WITH
PERMISSION_SET = UNSAFE

I cannot figure out how to refrence the chargecard method

I have tried

CREATE PROCEDURE PaymentProc
@.Account int,
@.Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard

It seems wsdl.exe put all this serialization code

namespace PaymentProc {
using System.Diagnostics;
using System.Web.Services;
using System.ComponentModel;
using System.Web.Services.Protocols;
using System;
using System.Xml.Serialization;

///
[System.CodeDom.Compiler.GeneratedCodeAttribute("wsdl", "2.0.50727.42")]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Web.Services.WebServiceBindingAttribute(Name="ServiceSoap", Namespace="http://ProdWeb1/PaymentProc")]
public partial class PaymentProc : System.Web.Services.Protocols.SoapHttpClientProtocol {

private System.Threading.SendOrPostCallback ChargeCardOperationCompleted;

///
public PaymentProc()
{
this.Url = "http://ProdWeb1/PaymentProc/PaymentProc.asmx";
}

///
public event ChargeCardCompletedEventHandler ChargeCardCompleted;

///
[System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://ProdWeb1/PaymentProc/ChargeCard", RequestNamespace="http://ProdWeb1/PaymentProc", ResponseNamespace="http://ProdWeb1/PaymentProc", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]
public string ChargeCard(int account, int amount) {
object[] results = this.Invoke("ChargeCard", new object[] {
account,
amount});
return ((string)(results[0]));
}
.................

When I run

CREATE PROCEDURE PaymentProc
@.Account int,
@.Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard

I get error

Method, property or field 'ChargeCard' of class 'PaymentProc.PaymentProc' in assembly 'PaymentProc' is not static.

Any ideas? This seemsed so straitforward in the beginning.

Change your ChargeCard CLR method (the one you are marking as a proc) to some other name, and then change the CREATE PROCEDURE statement to use that changed name. That should hopefully do it.

Niels
|||

I think my problem originates from the fact that you cannot complie an assembly using a web refrence using csc. I redesigned my assembly with a web refrence to http://ProdWeb1/PaymentProc/PaymentProc.asmx called ProdBilling which I can test operation ChargeCard successfully.

using System;

using System.Collections.Generic;

using System.Text;

namespace PayProcAssembly

{

public class PaymentProcessing

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static string ChargeCard(int Account, float Amount)

{

ProdBilling.PayProcessing serv = new ProdBilling.PayProcessing();

string result = serv.ChargeCard(Account, Amount);

SqlContext.Pipe.Send(result);

}

}

}

When I run

csc /target:library PaymentProcessing.cs

I get

Error: The type or namespace name 'ProdBilling could not be found (are you missing a using directive or an assembly reference?)

I was told I needed to create a proxy using WSDL.exe but it seems when I ran

wsdl /o PaymentProc.cs /n PaymentProc http://ProdWeb1/PaymentProc/PaymentProc.asmx

It messed up my code. I cant even find the web refrence anymore. Is this correct that I have to use wsdl or is there an easier way?

Accessing a web service using clr in SQL 2005

I need to access a billing webservice from SQL. I createde a new c# class project and made a web refrence to the web service "ProdBilling".

Here is the code of my assembly

using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
namespace PaymentProc
{
public class PaymentProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ChargeCard(int account, int amount)
{
string Response;
ProdBilling.Service serv = new ProdBilling.Service();
Response = serv.ChargeCard(account, amount);
SqlContext.Pipe.Send(Response);
}
}
}

I then ran WSDL

wsdl /oaymentProc.cs /naymentProc http://ProdWeb1/PaymentProc/PaymentProc.asmx

Then compliled

csc /target:library PaymentProc.cs

and added the assembly
CREATE ASSEMBLY PaymentProc from 'D:\ProdCode\PaymentProc.dll' WITH
PERMISSION_SET = UNSAFE

I cannot figure out how to refrence the chargecard method

I have tried

CREATE PROCEDURE PaymentProc
@.Account int,
@.Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard

It seems wsdl.exe put all this serialization code

namespace PaymentProc {
using System.Diagnostics;
using System.Web.Services;
using System.ComponentModel;
using System.Web.Services.Protocols;
using System;
using System.Xml.Serialization;

///
[System.CodeDom.Compiler.GeneratedCodeAttribute("wsdl", "2.0.50727.42")]
[System.Diagnostics.DebuggerStepThroughAttribute()]
[System.ComponentModel.DesignerCategoryAttribute("code")]
[System.Web.Services.WebServiceBindingAttribute(Name="ServiceSoap", Namespace="http://ProdWeb1/PaymentProc")]
public partial class PaymentProc : System.Web.Services.Protocols.SoapHttpClientProtocol {

private System.Threading.SendOrPostCallback ChargeCardOperationCompleted;

///
public PaymentProc()
{
this.Url = "http://ProdWeb1/PaymentProc/PaymentProc.asmx";
}

///
public event ChargeCardCompletedEventHandler ChargeCardCompleted;

///
[System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://ProdWeb1/PaymentProc/ChargeCard", RequestNamespace="http://ProdWeb1/PaymentProc", ResponseNamespace="http://ProdWeb1/PaymentProc", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)]
public string ChargeCard(int account, int amount) {
object[] results = this.Invoke("ChargeCard", new object[] {
account,
amount});
return ((string)(results[0]));
}
.................

When I run

CREATE PROCEDURE PaymentProc
@.Account int,
@.Amount int
AS
EXTERNAL NAME PaymentProc.[PaymentProc.PaymentProc].ChargeCard

I get error

Method, property or field 'ChargeCard' of class 'PaymentProc.PaymentProc' in assembly 'PaymentProc' is not static.

Any ideas? This seemsed so straitforward in the beginning.

Change your ChargeCard CLR method (the one you are marking as a proc) to some other name, and then change the CREATE PROCEDURE statement to use that changed name. That should hopefully do it.

Niels
|||

I think my problem originates from the fact that you cannot complie an assembly using a web refrence using csc. I redesigned my assembly with a web refrence to http://ProdWeb1/PaymentProc/PaymentProc.asmx called ProdBilling which I can test operation ChargeCard successfully.

using System;

using System.Collections.Generic;

using System.Text;

namespace PayProcAssembly

{

public class PaymentProcessing

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static string ChargeCard(int Account, float Amount)

{

ProdBilling.PayProcessing serv = new ProdBilling.PayProcessing();

string result = serv.ChargeCard(Account, Amount);

SqlContext.Pipe.Send(result);

}

}

}

When I run

csc /target:library PaymentProcessing.cs

I get

Error: The type or namespace name 'ProdBilling could not be found (are you missing a using directive or an assembly reference?)

I was told I needed to create a proxy using WSDL.exe but it seems when I ran

wsdl /o PaymentProc.cs /n PaymentProc http://ProdWeb1/PaymentProc/PaymentProc.asmx

It messed up my code. I cant even find the web refrence anymore. Is this correct that I have to use wsdl or is there an easier way?

Access SMO objects in CLR proc

I would like to write some CLR procs that use SMO objects. In visual studio I am unable to add refrences to the SMO objects. How can I do this?

Thanks

Bert

Because CLR was designed to work within the SQL Server engine the references available for a CLR assembly are limited. The assemblies actually run within the SQL Server context, not as operating system processes.

What would you want to do using SMO that you can't do using Transact-SQL?

|||

You are unable to add them because SMO is dependent on Batchparser90.dll which is half managed/half unmanaged code, so SQL server will not load it. They don't show up in visual studio for this reason.

If you want to use assemblies that don't show up, try creating just a normal class library and then use the CREATE ASSEMBLY T-SQL command. You will have to load all of your external assemblies as well, so I would see if they load before you start writing it, as some will and some wont.

Access SMO objects in CLR proc

I would like to write some CLR procs that use SMO objects. In visual studio I am unable to add refrences to the SMO objects. How can I do this?

Thanks

Bert

Because CLR was designed to work within the SQL Server engine the references available for a CLR assembly are limited. The assemblies actually run within the SQL Server context, not as operating system processes.

What would you want to do using SMO that you can't do using Transact-SQL?

|||

You are unable to add them because SMO is dependent on Batchparser90.dll which is half managed/half unmanaged code, so SQL server will not load it. They don't show up in visual studio for this reason.

If you want to use assemblies that don't show up, try creating just a normal class library and then use the CREATE ASSEMBLY T-SQL command. You will have to load all of your external assemblies as well, so I would see if they load before you start writing it, as some will and some wont.

Access java webservices from CLR function on sql server in DMZ.

Hello,
First excuse me for my bad english.
I have developed a sql server funtion CLR calling external webservices (java
).
Everything works perfectly when i execute the function on my test server.
But i need to deploy it on my production sql server 2005, but the cluster is
in DMZ (private network), and from it i don't have a direct access on
internet.
What is the best secure solution :
- NAT?
- install a sql express on the web server, and connect the cluster on it?
thanks for your response.
DraganNo response for me doesn't matter
I found my answer alone. For everyone who wan't to use a CLR UDF table
fonction who get some data from distant web service, and where the sql serve
r
was in private network, behind a webserver simply mail me
Have a nice weekend
"Draggi" wrote:

> Hello,
> First excuse me for my bad english.
> I have developed a sql server funtion CLR calling external webservices (ja
va).
> Everything works perfectly when i execute the function on my test server.
> But i need to deploy it on my production sql server 2005, but the cluster
is
> in DMZ (private network), and from it i don't have a direct access on
> internet.
> What is the best secure solution :
> - NAT?
> - install a sql express on the web server, and connect the cluster on it?
> thanks for your response.
> Dragan|||> I found my answer alone. For everyone who wan't to use a CLR UDF table
> fonction who get some data from distant web service, and where the sql
> server
> was in private network, behind a webserver simply mail me
Why not post your solution here so that others with the same problem can
easily find the answer?
Hope this helps.
Dan Guzman
SQL Server MVP
"Draggi" <Draggi@.discussions.microsoft.com> wrote in message
news:08984EC0-25A3-4E42-BB63-8E291C275BF1@.microsoft.com...[vbcol=seagreen]
> No response for me doesn't matter
> I found my answer alone. For everyone who wan't to use a CLR UDF table
> fonction who get some data from distant web service, and where the sql
> server
> was in private network, behind a webserver simply mail me
> Have a nice weekend
>
> "Draggi" wrote:
>