Can I host the web service in SQL Express?

In WCF, web services can be hosted in many processes other than IIS. Can I
write my CLR extension (web service) to the SQL Express? SQL Server
Enterprise edition supports native web service, but it requires IIS.
Thanks.
HaoHello Hao,

> Can I
> write my CLR extension (web service) to the SQL Express?
If you mean, "can I write an CLR-based function or procedure and host that
in an instance of SQL Express?" then the answer is yes.
If you mean, "can I create an HTTP endpoint in an SQL Express instance?"
then the answer is no. You'll get error 7878 for your efforts.

> SQL Server
> Enterprise edition supports native web service, but it requires IIS.
Um, no, that's the point of HTTP endpoints.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||There are two challenges for deploying a full SQL edition as providing web
services.
1. Customers have to buy full SQL.
2. IIS is required.
Please correct me if the above two requirements are wrong.
Therefore it seems that the only work around is to write my own windows
service in WCF to expose web services and have this windows service talk to
SQL Express. Am I on the right track?
Thanks.
Hao
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:18f2bcb184248c93ffc0278bcf5@.news.microsoft.com...
> Hello Hao,
>
> If you mean, "can I write an CLR-based function or procedure and host that
> in an instance of SQL Express?" then the answer is yes.
> If you mean, "can I create an HTTP endpoint in an SQL Express instance?"
> then the answer is no. You'll get error 7878 for your efforts.
>
> Um, no, that's the point of HTTP endpoints.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||> There are two challenges for deploying a full SQL edition as providing
> web services.
> 1. Customers have to buy full SQL.
Define full SQL. HTTP endpoints are availble in the fairly cheap workgroup
SKU and higher.

> 2. IIS is required.
HTTP Endpoints DO NOT require IIS on XP, Server 2003 or higher.

> Therefore it seems that the only work around is to write my own windows
service in WCF to expose web services and have this windows service talk
to SQL Express. Am I on the right track?
If you want to use SQLExpress then the basic answer is yes.
Alternatively, you could use Mono to develop an ASMX service and deploy what
ever
HTTP stack you like.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Can I host the web service in SQL Express?

In WCF, web services can be hosted in many processes other than IIS. Can I
write my CLR extension (web service) to the SQL Express? SQL Server
Enterprise edition supports native web service, but it requires IIS.
Thanks.
Hao
Hello Hao,

> Can I
> write my CLR extension (web service) to the SQL Express?
If you mean, "can I write an CLR-based function or procedure and host that
in an instance of SQL Express?" then the answer is yes.
If you mean, "can I create an HTTP endpoint in an SQL Express instance?"
then the answer is no. You'll get error 7878 for your efforts.

> SQL Server
> Enterprise edition supports native web service, but it requires IIS.
Um, no, that's the point of HTTP endpoints.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||There are two challenges for deploying a full SQL edition as providing web
services.
1. Customers have to buy full SQL.
2. IIS is required.
Please correct me if the above two requirements are wrong.
Therefore it seems that the only work around is to write my own windows
service in WCF to expose web services and have this windows service talk to
SQL Express. Am I on the right track?
Thanks.
Hao
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:18f2bcb184248c93ffc0278bcf5@.news.microsoft.co m...
> Hello Hao,
>
> If you mean, "can I write an CLR-based function or procedure and host that
> in an instance of SQL Express?" then the answer is yes.
> If you mean, "can I create an HTTP endpoint in an SQL Express instance?"
> then the answer is no. You'll get error 7878 for your efforts.
>
> Um, no, that's the point of HTTP endpoints.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
|||> There are two challenges for deploying a full SQL edition as providing
> web services.
> 1. Customers have to buy full SQL.
Define full SQL. HTTP endpoints are availble in the fairly cheap workgroup
SKU and higher.

> 2. IIS is required.
HTTP Endpoints DO NOT require IIS on XP, Server 2003 or higher.

> Therefore it seems that the only work around is to write my own windows
service in WCF to expose web services and have this windows service talk
to SQL Express. Am I on the right track?
If you want to use SQLExpress then the basic answer is yes.
Alternatively, you could use Mono to develop an ASMX service and deploy whatever
HTTP stack you like.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Calling WebServices Asynchronously from SQL CLR

I'm assuming it's possible to call a web service asynchronously from a SQL CLR function, is this correct or is there a fundamental flaw in calling the asynchronous methods?

I've tried coding a sample and despite creating a new thread, which in turn calls the web service asynchronously and using newthread.join I can never get the webmethodasync_completed sub to be called.

There is a fundamental flaw in using SQL CLR in this fashion. For starters, the thread you spawn is not visible through any management tools for SQL Server. So if the thread runs amuck, there is no way (that I have found yet) to kill that thread. Of course, you will need to deploy an UNSAFE assembly.

As for asynchronous call from SQL CLR, I would be willing to bet a very small amount of money (since I haven't ried it myself, mind you) that it isn't possible.

So can I ask, what exactly is it that you are trying to do? Perhaps there is another way to find a solution to your issue that doesn't involve the techniques you have already tried doing.

Calling WebServices Asynchronously from SQL CLR

I'm assuming it's possible to call a web service asynchronously from a SQL CLR function, is this correct or is there a fundamental flaw in calling the asynchronous methods?

I've tried coding a sample and despite creating a new thread, which in turn calls the web service asynchronously and using newthread.join I can never get the webmethodasync_completed sub to be called.

There is a fundamental flaw in using SQL CLR in this fashion. For starters, the thread you spawn is not visible through any management tools for SQL Server. So if the thread runs amuck, there is no way (that I have found yet) to kill that thread. Of course, you will need to deploy an UNSAFE assembly.

As for asynchronous call from SQL CLR, I would be willing to bet a very small amount of money (since I haven't ried it myself, mind you) that it isn't possible.

So can I ask, what exactly is it that you are trying to do? Perhaps there is another way to find a solution to your issue that doesn't involve the techniques you have already tried doing.

Calling webservice from CLR Stored Procedure

I would like to be able to call an webservice from a SP, I'm able to add a
webreference to the webservice in the database project, but how to call it i
n
code, and how to set up the code access illudes me?
any suggestions?
.LPHi LP,
What version of SQL Server are you using?
In SQL Server 2005 you can write a CLR to consume the webservice, check out
my article and samples here: http://sqlserverfaq.com?eid=58 there are
samples and even a blogcast of me showing exactly how to do it.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
".LP" <.LP@.discussions.microsoft.com> wrote in message
news:3DF46544-C919-4C90-B101-2CC906A248E8@.microsoft.com...
>I would like to be able to call an webservice from a SP, I'm able to add a
> webreference to the webservice in the database project, but how to call it
> in
> code, and how to set up the code access illudes me?
> any suggestions?
> .LP|||I am using Sql Server 2005, but want to call an extarnal webservice (asmx)
from the clr-code Sp.
The purpose beeing; to send an notification from the database, from an
trigger with the ID of the Inserted/updated record
and the code I'm looking for is to call an webservice, not just consume xml.
.LP
"Tony Rogerson" wrote:

> Hi LP,
> What version of SQL Server are you using?
> In SQL Server 2005 you can write a CLR to consume the webservice, check ou
t
> my article and samples here: http://sqlserverfaq.com?eid=58 there are
> samples and even a blogcast of me showing exactly how to do it.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> ".LP" <.LP@.discussions.microsoft.com> wrote in message
> news:3DF46544-C919-4C90-B101-2CC906A248E8@.microsoft.com...
>
>|||The code samples does call a webservice - the msn search webservice for one.
Basically, you need to use CLR to encapsulate the web service call.
Tony
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
".LP" <LP@.discussions.microsoft.com> wrote in message
news:8FAC07F5-81CE-468F-B1BC-DE91C9F8BDF8@.microsoft.com...
>I am using Sql Server 2005, but want to call an extarnal webservice (asmx)
> from the clr-code Sp.
> The purpose beeing; to send an notification from the database, from an
> trigger with the ID of the Inserted/updated record
> and the code I'm looking for is to call an webservice, not just consume
> xml.
> .LP
> "Tony Rogerson" wrote:
>

Calling webservice from CLR

I have a webservice that I would like to call from SQL Server 2005. I have done alot of testing and am pretty familiar with how CLR works and how to create assemblies in SQL. My problem is that I don't know much about .net and am not sure what kind of project I need to create in Visual Studios to accomplish calling the webservice. I've tried googling and have not found much that is of use to me.

Any help would be appreciated

Aaron
You need to

1) creat SQL Server Project and create CRL stored Procedure.

2) add your wsdl file to web reference that points to your web service

3) call web method in your CRL stored Procedure

The following link might be useful to you.

http://davidhayden.com/blog/dave/archive/2006/04/25/2924.aspx

Calling webservice from CLR

I have a webservice that I would like to call from SQL Server 2005. I have done alot of testing and am pretty familiar with how CLR works and how to create assemblies in SQL. My problem is that I don't know much about .net and am not sure what kind of project I need to create in Visual Studios to accomplish calling the webservice. I've tried googling and have not found much that is of use to me.

Any help would be appreciated

Aaron
You need to

1) creat SQL Server Project and create CRL stored Procedure.

2) add your wsdl file to web reference that points to your web service

3) call web method in your CRL stored Procedure

The following link might be useful to you.

http://davidhayden.com/blog/dave/archive/2006/04/25/2924.aspx

Calling WCF Service from SQL CLR

Is it possible to call a WCF service from a SQL CLR.

We have had success calling standard web services by including a Proxy in the Assembly but I wondered if it's possible to call out to a WCF service as the enpoint configuraion is done with an external XML file and I don't know if this will work in the same way. We may want to alter the wcf configuration depending on where the service is sitting, same box or remote.

Please do not respond with 'why do you want to do this' the reson we want to do this is because we use Service broker to recieve incoming messages and want to trigger a stored procedure to do some work on an external service when it recives some data.

Cheers,

Darren

hi check out this link for more details

http://blogs.neudesic.com/blogs/shaun_collett/archive/2007/04/29/6050.aspx

Calling WCF Service from SQL CLR

Is it possible to call a WCF service from a SQL CLR.

We have had success calling standard web services by including a Proxy in the Assembly but I wondered if it's possible to call out to a WCF service as the enpoint configuraion is done with an external XML file and I don't know if this will work in the same way. We may want to alter the wcf configuration depending on where the service is sitting, same box or remote.

Please do not respond with 'why do you want to do this' the reson we want to do this is because we use Service broker to recieve incoming messages and want to trigger a stored procedure to do some work on an external service when it recives some data.

Cheers,

Darren

hi check out this link for more details

http://blogs.neudesic.com/blogs/shaun_collett/archive/2007/04/29/6050.aspx

Calling T SQL stored proc from CLR stored proc

I would like to know if the following is possible/permissible:

myCLRstoredproc (or some C# stored proc)

{

//call some T SQL stored procedure spSQL and get the result set here to work with

INSERT INTO #tmpCLR EXECUTE spSQL

}

spSQL

(

INSERT INTO #tmpABC EXECUTE spSQL2

)

spSQL2

(

// some other t-sql stored proc

)

Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.

Why bother with the temp-tables, why not call directly into spSQL2 from your CLR proc?

Niels
|||

Well, the spSQL is doing a lot more than just calling spSQL2. Besides, I can't touch spSQL and spSQL2. I know it looks ugly, but it seems this might be what I need to do in my current problem.

Just let me know if it is possible or the CLR will throw the nested execution of stored procedure error?

Thanks.