Calling WCF service from SQL CLR Stored procedure

Question

Calling WCF service from SQL CLR Stored procedure

Gunjan

Hall of Fame

Points: 3226
More actions
November 24, 2008 at 6:37 am

#196208

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.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Girish Bhat Ten Centuries Points: 1323 More actions · Answer 1

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 Hall of Fame Points: 3226 More actions · Answer 2

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 Ten Centuries Points: 1323 More actions · Answer 3

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 Hall of Fame Points: 3226 More actions · Answer 4

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 One Orange Chip Points: 26778 More actions · Answer 5

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 One Orange Chip Points: 26778 More actions · Answer 6

Niels Berglund published a article today on how to consume a WCF Service from SQL CLR.

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

Paul_J Hall of Fame Points: 3698 More actions · Answer 7

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 Hall of Fame Points: 3226 More actions · Answer 8

Yes. That's what I have used to consume WCF service.

Adding it as a Web reference works.

Thanks.

Gunjan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

Gunjan Hall of Fame Points: 3226 More actions · Answer 10

It comes into my requirement.

I am calling my WCF service (As a web reference) to generate and return the SSRS report into byte array and doing the further processing.

Thanks.

Gunjan.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 11

GilaMonster (2/9/2009)
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?

I can see some use in calling a WCF Service from SQL, but it is very limited, and they are corner cases only. Thinks like one company I know has a really archaic file based "database" system that has millions of files in it that they may not necessarily want to pull up into their newer system. They currently use a .NET app that connects to a service to get information from this legacy information, so a similar way could be done to hook the data through SQL Server for use in conjunction with SQL Server data. Like I said far fetch corner case, and I probably wouldn't go that route myself because th payload cost for the service call and resulting tabular datastream back would be to high memory wise.

WimVM-436440 SSC Journeyman Points: 84 More actions · Answer 12

I was looking for some examples on how to consume a WCF service with SQL service broker. When reading this I am a bit confused... :hehe:

I need to enable an automated system for our external contractors/customers to setup services (general things like creating users, directories, ...) on our systems. The external contractors are NOT trusted employees, so one of the primary goals is security. They need to pay per object they use/create.

The actual execution of the code that is creating accounts, directories,... is done by calling an internally hosted WCF service (on IIS 7). This works fine.

My point is that I was searching a good driving force to use this WCF service. Therefore my idea was to use SQL Service Broker. All data (number of objects per customer, ...) is currently in a SQL 2005 database (we plan to migrate to 2008 before launching it).

The flow would look like this:

1. secure website with WCF services that just store the contractors request in the database tables

2. a trigger (?) passes this request to the service broker

3. the service broker calls the secure WCF services that actually execute the action (eg.: create user).

4. service broker reports back that action was succes or not (and handles of invoicing...)

By doing this I wanted to:

- move the load away from the externally available web server that hosts the WCF to the backend and more performant SQL server

- add more security (the WCF exposed to the external world only inserts values in a table no real action is done)

- when for some reason something goes wrong with a script (timeout, error, ...) we can replay it and no error is send directly to the external executor.

- we can delay executing (stop processing in the weekend or when systems are patched for example) while customers still can add changes

- require a form of auditing; who creates what, when, how many, ...

- ...

BUT, when reading the previous posts I wanted to know what your idea is of this plan? The key in this workflow is SQL that activates the WCF service with service broker.

Is it a good idea or do you suggest other things? The options I have are:

- CLR stored procedure makes the WCF call to a hosted IIS website (some security issues may be a problem I was told)

- deploy the WCF to a Windows service, would be easier(?)

If there are some basic example, then they are more then welcome.

Peter Maddin Valued Member Points: 52 More actions · Answer 13

One use for calling a WCF service from a CLR stored proc is provide a more robust means to gain access to Active Directory User account information.

I have used a linked server to AD and then created a view for users to gain access to the information.

The problem I found with this approach is that any multivalued AD attribute caused an error when it contained more than one attribute. This limits this approach to using only single values attributes. This may be enough for some.

I would prefer a more robust solution after having errors because after including an atribute that worked while it had one value but not after it had more than one. One had to find out which one was the cause and eliminate it.

There may be other uses but this one is the main one for me.

Miron Berlin SSC-Addicted Points: 414 More actions · Answer 14

Did you try to lookup Broker to Web Service Bridge on codeplex? Thought it was one of the coolest features to integrate as add - on or just built as part of SQL Server.