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 (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 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.

Thanks.

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

Group: General Forum Members
Points: 2878 Visits: 1807
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.

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
WimVM-436440
WimVM-436440
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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... 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
Peter Maddin
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 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.
Miron Berlin
Miron Berlin
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 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.
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