<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
CLR Integration and Programming.
»
Calling WCF service from SQL CLR Stored...
15 posts, Page 1 of 2
1
2
»»
Calling WCF service from SQL CLR Stored procedure
Rate Topic
Display Mode
Topic Options
Author
Message
Gunjan
Gunjan
Posted Monday, November 24, 2008 6:37 AM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, May 10, 2013 9:44 AM
Points: 78,
Visits: 251
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
Girish Bhat
Girish Bhat
Posted Monday, November 24, 2008 6:49 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, February 22, 2013 1:02 AM
Points: 206,
Visits: 425
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
Gunjan
Gunjan
Posted Monday, November 24, 2008 6:56 AM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, May 10, 2013 9:44 AM
Points: 78,
Visits: 251
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
Girish Bhat
Girish Bhat
Posted Monday, November 24, 2008 7:08 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, February 22, 2013 1:02 AM
Points: 206,
Visits: 425
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
Gunjan
Gunjan
Posted Monday, November 24, 2008 7:12 AM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, May 10, 2013 9:44 AM
Points: 78,
Visits: 251
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
Jonathan Kehayias
Jonathan Kehayias
Posted Monday, November 24, 2008 7:41 AM
SSCommitted
Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696,
Visits: 1,742
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
Jonathan Kehayias
Jonathan Kehayias
Posted Sunday, February 08, 2009 6:23 PM
SSCommitted
Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696,
Visits: 1,742
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
Paul_J
Paul_J
Posted Monday, February 09, 2009 2:39 AM
SSC-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
Gunjan
Gunjan
Posted Monday, February 09, 2009 12:22 PM
SSC Journeyman
Group: General Forum Members
Last Login: Friday, May 10, 2013 9:44 AM
Points: 78,
Visits: 251
Yes. That's what I have used to consume WCF service.
Adding it as a Web reference works.
Thanks.
Gunjan.
Post #653115
GilaMonster
GilaMonster
Posted Monday, February 09, 2009 1:25 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 37,687,
Visits: 29,944
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 »
15 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
