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

Multi thread on SQLCLR Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2008 4:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2008 1:49 AM
Points: 2, Visits: 3
Hi all,

I need to retrieve some value from 100 different servers trough DBLINK. This connection is trough a VPN on Internet.
Each call take time, and the total off the 100 call take 5 minutes.

I would write a SQLCLR proc and work with a ThreadPool to execute many call on the same time.

It's possible, you have some simple sample or a guide line.

Thank for your help.

Michel
Post #543963
Posted Wednesday, July 30, 2008 5:33 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
I don't know about threading and SQLCLR, but I do know that you can do this with Linked Servers and Service Broker.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #543978
Posted Wednesday, July 30, 2008 9:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
While you can do threading in SQLCLR, it is UNSAFE and not recommended. This seems like a job for a SSIS package and a staging table to me. The SSIS package can be wrapped in a SQL Agent job, and called from a stored procedure with sp_start_job, or through service broker. I would strongly advise against doing this in SQLCLR for a number of reasons.

First SQLCLR runs in the MemToLeave memory area of the VAS. This is a very limited space, and you are likely to overflow it and have a memory abort of your CLR process which will unload the app domain.
Second, if you have a runaway process it could cause an unyielding scheduler which may cause a stack dump in SQL, and again unloading of the CLR process and your assembly.

CLR has its place in SQL, and what can and can't be done is highly limited, and for a number of really good reasons. There are much better tools for solving this problem than CLR.


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 #544035
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse