Click here to monitor SSC
SQLServerCentral is supported by Redgate
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
Posted Monday, February 9, 2009 1:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 3, 2016 10:22 AM
Points: 78, Visits: 309
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.


Post #653189
Posted Monday, February 9, 2009 1:39 PM



Group: General Forum Members
Last Login: Friday, January 1, 2016 7:07 AM
Points: 1,688, Visits: 1,807
GilaMonster (2/9/2009)
Gunjan (2/9/2009)
Yes. That's what I have used to consume WCF service.


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.

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 #653195
Posted Monday, July 6, 2009 2:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 17, 2009 12:58 PM
Points: 4, Visits: 15
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...

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.

Post #747550
Posted Tuesday, April 6, 2010 7:34 PM


Group: General Forum Members
Last Login: Thursday, August 26, 2010 1:33 AM
Points: 18, Visits: 12
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.
Post #898060
Posted Wednesday, February 15, 2012 3:52 PM


Group: General Forum Members
Last Login: Friday, September 23, 2016 2:04 AM
Points: 174, Visits: 210
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.
Post #1252806
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse