SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calling WCF service from SQL CLR Stored procedure


Calling WCF service from SQL CLR Stored procedure

Author
Message
Gunjan
Gunjan
SSC Veteran
SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)

Group: General Forum Members
Points: 266 Visits: 309
Hi



I am trying to call the WCF service from SQL CLR Stored procedure. I am able to create the procedure. but when I am executing the stored proc. it is showing the following error



Msg 6522, Level 16, State 1, Procedure SubmitFaxRequest, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "SubmitFaxRequest":

System.TypeInitializationException: The type initializer for 'System.ServiceModel.ClientBase`1' threw an exception. ---> System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All

The demanded resources were: Synchronization, ExternalThreading

System.Security.HostProtectionException:

at System.ServiceModel.DiagnosticUtility.GetUtility()

at System.ServiceModel.DiagnosticUtility.get_Utility()

at System.ServiceModel.ClientBase`1..cctor()

System.TypeInitializationException:

at System.ServiceModel.ClientBase`1.InitializeChannelFactoryRef()

at System.ServiceModel.ClientBase`1..ctor()

at OutboundFaxClient..ctor()

at StoredProcedures.SubmitFaxRequest()





What could be problem?





Thanks

Thanks.

Gunjan.
Girish Bhat
Girish Bhat
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 428
What is the permission you used when you executed your CREATE ASSEMBLY statement? Also, are you using any Message Boxes within your CLR code?
Gunjan
Gunjan
SSC Veteran
SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)

Group: General Forum Members
Points: 266 Visits: 309
Hi,

Thanks for the response.

I have tried Unsafe and Safe mode to create tha assembly. One more thing I want to point out is I am trying to create the xmlserializer.dll using sgen but I am getting the following error.


Error: The top XML element 'PharmaFaxResponse' from namespace '' references dist
inct types SqlServerWCFService.Service1.PharmaFaxResponse and PharmaFax.PharmaFa
xResponse. Use XML attributes to specify another XML name or namespace for the e
lement or types.
If you would like more help, please type "sgen /?"

Thanks.

Gunjan.
Girish Bhat
Girish Bhat
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 428
I am not sure of your SGen error and the permission errors are related. For the SGen error you may want to look at the link: http://social.msdn.microsoft.com/Forums/en-US/asmxandxml/thread/fa59738e-11e1-45f8-a81f-613c6bb377db/ . It may give you some ideas.

For the permission error, can you check if you are using any console.writeline or message boxes in you code?
Gunjan
Gunjan
SSC Veteran
SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)

Group: General Forum Members
Points: 266 Visits: 309
No I am not using any console.writeline or message boxes in my code. My method of WCF service does some processing and inserts data into the databse.

Thanks.

Gunjan.
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2982 Visits: 1807
I actually discussed the issue of using WCF in SQL CLR with some members of the SQLCAT team and Microsoft this past week since it seems to becoming more popular for people to attempt, and I have yet to see where someone actually succeeded in anything but the most simplistic service call which did absolutely nothing. WCF was not considered a valid SQLCLR implementation by anyone I spoke to, which is good to hear since that has been my stance on the subject since it was first broached on another forum earlier this year.

You have two other options that are better for a number of reasons. First you can look at the External Activator for Service Broker in SQL Server 2008 which is an additional download you can use. In SQL 2005, you can hack together a SqlDependency if you are trying to make data change based calls to the service, and let the service run a watcher against SQL Server data and changes to the data trigger events in the service to bring it alive for processing.

The other option is to go to BizTalk, which by the way was designed and intended to do multi-service/SOA integration between your systems like you are trying to do. You need to expand your toolset and use the correct tools to solve the problem.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2982 Visits: 1807
Niels Berglund published a article today on how to consume a WCF Service from SQL CLR.

http://nielsb.wordpress.com/sqlclrwcf/

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Paul_J
Paul_J
Old Hand
Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)Old Hand (311 reputation)

Group: General Forum Members
Points: 311 Visits: 637
One option is to expose an http endpoint in your wcf service and simply treat it as a web service from your clr stored proc (ie. add a Web Reference and not a Service Reference). I've done this and it works as expected.
Gunjan
Gunjan
SSC Veteran
SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)

Group: General Forum Members
Points: 266 Visits: 309
Yes. That's what I have used to consume WCF service.
Adding it as a Web reference works.

Thanks.

Gunjan.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90927 Visits: 45284
Gunjan (2/9/2009)
Yes. That's what I have used to consume WCF service.

Why?

SQL is a database engine, not an application server. Why is the database calling WCF services?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search