Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Calling WCF service from SQL CLR Stored procedure Expand / Collapse
Author
Message
Posted Monday, November 24, 2008 6:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:41 PM
Points: 78, Visits: 283
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.
Post #607488
Posted Monday, November 24, 2008 6:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 31, 2014 1:03 AM
Points: 215, 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?
Post #607496
Posted Monday, November 24, 2008 6:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:41 PM
Points: 78, Visits: 283
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.
Post #607499
Posted Monday, November 24, 2008 7:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 31, 2014 1:03 AM
Points: 215, 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?
Post #607507
Posted Monday, November 24, 2008 7:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:41 PM
Points: 78, Visits: 283
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.
Post #607509
Posted Monday, November 24, 2008 7:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #607529
Posted Sunday, February 8, 2009 6:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #652504
Posted Monday, February 9, 2009 2:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 5:56 PM
Points: 185, 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.
Post #652609
Posted Monday, February 9, 2009 12:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 12:41 PM
Points: 78, Visits: 283
Yes. That's what I have used to consume WCF service.
Adding it as a Web reference works.


Thanks.

Gunjan.
Post #653115
Posted Monday, February 9, 2009 1:25 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 42,756, Visits: 35,850
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 2008, MVP
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

Post #653179
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse